Cassandra: Only EQ and IN relation are supported on the partition key (unless you use the token() function)

Question

Table:

CREATE TABLE TEST_PAYLOAD
(
  TIME_STAMP timestamp,
  TYPE text,
  PRIMARY KEY (TIME_STAMP)
);

 time_stamp           | type
--------------------------+----------
 2013-05-15 00:00:00-0700 | sometext
 2013-05-16 00:00:00-0700 | sometext
 2013-05-17 00:00:00-0700 | sometext

SELECT * FROM TEST_PAYLOAD WHERE TIME_STAMP>='2013-05-15 00:00:00-0700';

code=2200 [Invalid query] message="Only EQ and IN relation are supported on the partition key (unless you use the token() function)"

It doesn't work for > or any range selection while it works for = as far index is concerned it has only one primary key there is no partition key. Why is it asking for token()?

I would like to retrieve relative range can be only date or date with time not a specific timestamp that exists in the db.

Answer 1

I guess you are bit confused about the Cassandra terminology.

Please refer here

partition key: The first column declared in the PRIMARY KEY definition

ie, when you create a table like this

CREATE TABLE table {
 key1, 
 key2,
 key3,
 PRIMARY KEY (key1, key2, key3)
}

key1 is called the partition key and key2, key3 are called clustering keys.

In your case you don't have clustering keys, so the single primary key which you declared became the partition key.

Also range queries ( < , >) should be performed on clustering keys.

If you don't have any other candidates for primary key, i think you should remodel your table like this

CREATE TABLE TEST_PAYLOAD
(
  BUCKET varchar,
  TIME_STAMP timestamp,
  TYPE text,
  PRIMARY KEY (BUCKET, TIME_STAMP)
);

For BUCKET you can provide the year or year&month combination. So your keys would look like these 2013, 2014, 06-2014, 10-2014 etc.

So while querying go to the desired bucket and do range scans like TIME_STAMP >= '2013-05-15 00:00:00-0700'