Range query - Data modeling for time series in CQL Cassandra

Question

I have a table like this:

CREATE TABLE test ( partitionkey text, rowkey text, date timestamp, policyid text, policyname text, primary key (partitionkey, rowkey));

with some data:

partitionkey | rowkey | policyid | policyname | date

     p1 |   r1 |    pl1 |  plicy1 | 2007-01-02 00:00:00+0000
     p1 |   r2 |    pl2 |  plicy2 | 2007-01-03 00:00:00+0000
     p2 |   r3 |    pl3 |  plicy3 | 2008-01-03 00:00:00+0000

I want to be able to find:

1/ data from a particular partition key
2/ data from a particular partition key & rowkey
3/ Range query on date given a partitionkey

1/ and 2/ are trivial:

select * from test where partitionkey='p1';

partitionkey | rowkey | policyid | policyname | range

    p1 |     r1 |      pl1 |     plicy1 | 2007-01-02 00:00:00+0000
    p1 |     r2 |      pl2 |     plicy2 | 2007-01-03 00:00:00+0000

but what about 3/? Even with an index it doesnt work:

create index i1 on test (date);

select * from test where partitionkey='p1' and date = '2007-01-02';

partitionkey | rowkey | policyid | policyname | date

  p1 |  r1 |   pl1   plicy1 | 2007-01-02 00:00:00+0000

but

select * from test where partitionkey='p1' and date > '2007-01-02';

Bad Request: No indexed columns present in by-columns clause with Equal operator

Any idea? thanks, Matt

Answer 1

CREATE TABLE test ( partitionkey text, rowkey text, date timestamp, policyid text, policyname text, primary key (partitionkey, rowkey));

First of all, you really should use more descriptive column names instead of partitionkey and rowkey (and even date, for that matter). By looking at those column names, I really can't tell what kind of data this table is supposed to be indexed by.

select * from test where partitionkey='p1' and date > '2007-01-02';

Bad Request: No indexed columns present in by-columns clause with Equal operator

As for this issue, try making your "date" column a part of your primary key.

primary key (partitionkey, rowkey, date)

Once you do that, I think your date range queries will function appropriately.

For more information on this, check out DataStax Academy's (free) course called Java Development With Apache Cassandra. Session 5, Module 104 discusses how to model time series data and that should help you out.