cassandra: get columns of a wide row in reverse order with some filtering

Question

I understand that cassandra does not support order by with secondary indexes but I really do not want order by feature. All I want is an ability to get columns in ascending or descending order as stored in column family since cassandra already sorts column names.

I would like to model time series data in cassandra as wide rows. I need to access this data in ascending or descending order. I would also like to create couple of secondary indexes to do select queries. So my column family at_data would look something like,

create table at_data (acct_bucket_id text,
             ... ts varint,
             ... status int,
             ... side int,
             ... instrument_id varint,
             ... data text,
             ... PRIMARY KEY (acct_bucket_id, ts, status, side, instrument_id)
             ... ) with comment = 'audit data';

If I add data to this column family.

insert into at_data (acct_bucket_id, ts, status, side, instrument_id, data) values ('1:1', 1, 1, 1, 1, 'order 1');
insert into at_data (acct_bucket_id, ts, status, side, instrument_id, data) values ('1:1', 2, 1, 1, 1, 'order 2');
insert into at_data (acct_bucket_id, ts, status, side, instrument_id, data) values ('1:1', 3, 2, 1, 1, 'order 3');

and would like to get 'data' in descending order where status matches 1. So I created query, select * from at_data where acct_bucket_id='1:1' and status=1 order by ts desc;

and got error, Bad Request: ORDER BY with 2ndary indexes is not supported.

How do I achieve this?

Thanks, Shridhar

Dmitry Shohov Dmitry Shohov · Accepted Answer · 2013-10-22T10:05:40

You can do select * from at_data where acct_bucket_id = '1:1' and ts = 1 and status = 1 order by ts desc; You can't skip ts in your query, because it goes before status in primary key. And you can't order by status because order by accepts only second key of the primary key.

You can change your primary key to PRIMARY_KEY (acct_bucket_id, status, ts, side, instrument_id) - put status before ts. In this case you can do select * from at_data where acct_bucket_id='1:1' and status = 1 order by status desc;

cassandra: get columns of a wide row in reverse order with some filtering

1 Answers