Search code examples
snappydata

How do I partition data in a column table in SnappyData?

I am unable to figure out the syntax to partition my 'column' table. Here is an example that fails on me as well as many variations on it.

CREATE TABLE SENSOR_DATA_COL_BY_YEAR USING column OPTIONS(PARTITION_BY year_num, buckets '11') AS (SELECT sensor_id,metric,collection_time,value,sensor_time,year AS year_num, month AS month_num from STAGING_1);

And... the error.

ERROR 38000: (SQLState=38000 Severity=-1) (Server=172.31.8.115[1528],Thread[DRDAConnThread_34,5,gemfirexd.daemons]) The exception 'Invalid input 'C', expected dmlOperation, insert, withIdentifier, select or put (line 1, column 1): CREATE TABLE SENSOR_DATA_COL_BY_YEAR USING column OPTIONS(PARTITION_BY year_num, buckets '11') AS (SELECT sensor_id,metric,collection_time,value,sensor_time,year AS year_num, month AS month_num from STAGING_1) ^;' was thrown while evaluating an expression.

Solution

  • column name specified in PARTITION_BY clause should be in quotes "year_num"

    modified query:

    CREATE TABLE SENSOR_DATA_COL_BY_YEAR USING column OPTIONS(PARTITION_BY "year_num", buckets '11') AS (SELECT sensor_id,metric,collection_time,value,sensor_time,year AS year_num, month AS month_num from STAGING_1);