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 · Answer 1 · 2013-10-23T08:05:37.077

1

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;

edited Oct 23 '13 at 08:05

answered Oct 22 '13 at 10:05

Dmitry Shohov

322
2
9

Thanks Dmitry. I cannot switch ts and status since I want columns to be sorted by time stamp. For the queries like, give me last 100 columns where status is 1, I would not know timestamp(ts) of last column. Also query you suggested(select * from at_data where acct_bucket_id = '1:1' and ts = 1 and status = 1 order by ts desc;) wouldn't work for same reason as my example query didn't work. – user2903819 Oct 22 '13 at 21:48
`select * from at_data where acct_bucket_id = '1:1' and status = 1 order by status desc limit 100;` This query will give you last 100 rows and it will be sorted by ts in descending order. – Dmitry Shohov Oct 23 '13 at 07:21
You don't need to order by ts. Cassandra has datamodel that is Map>. When you create PRIMARY KEY (acct_bucket_id, status, ts, side, instrument_id), acct_bucket_id becomes your PartitionKey and the rest form composite ColumnName in SortedMap. When in your query you add order by status desc - it means order ColumnName with all it columns including ts – Dmitry Shohov Oct 23 '13 at 07:31
I understand that I do not need to order by ts since column names are sorted in a row. As my example suggests, I created a table, created secondary index on status and ran query 'select * from at_data where acct_bucket_id = '1:1' and status = 1 order by ts desc limit 100;'. cqlsh gives me error, Bad Request: ORDER BY with 2ndary indexes is not supported. I did NOT create secondary index on 'ts'. I just created secondary index on 'status'. – user2903819 Oct 24 '13 at 18:12

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

1 Answers1