Cassandra Slice Query for parts of CompositeType

3
votes

I have a composite column made of four fields: (field1, field2, field3, field4). I need to perform following slice queries: 1. Get All composite columns where field2 has a specific value and other fields do not matter. Let us say I have three composite columns for a row key: ('ACTIVE', '35', 'Name', 'Time'), ('INACTIVE', '35', 'City', 'Country'), and ('PASSIVE', '25', 'Time', 'Zone'). Given the row key and value of field3 as '35' I should get results with first two composite columns. Any suggestion will be highly appreciated.

1
cassandracomposite-key
How many different values can field1 have? Columns with composite keys are sorted by the first dimension first, then the next and so on. To retrieve all the values with field2=x you have to do at least |field1| reads, or scan the entire row. Have you considered swapping field2 and field1? That would make this query easier. – tom.wilkie
@tom.wilkie, you should make this an answer instead of a comment. – Tyler Hobbs
I need to perform queries using field1 as well that is the reason I put it at first place. Also, in my case there are only three unique values that field1 can have(ACTIVE, INACTIVE, and PASSIVE). Field2 is an ID and can have millions of unique values. Same is the case with rest of the fields. – user1064949

1 Answers

1
votes

How many different values can field1 have? Columns with composite keys are sorted by the first dimension first, then the next and so on. To retrieve all the values with field2=x you have to do at least |field1| reads, or scan the entire row. Have you considered swapping field2 and field1? That would make this query easier.

If you need to do queries for all values with field1=x, and you separately want to do queries with field2=y, you could also consider inserting your data twice, with different ordering for the dimensions of the composite keys. This works best if you date is write-once. If this isn't acceptable, then the only option is to do three queries:

["ACTIVE";y;"";""] -> ["ACTIVE";y;"";""] ["INACTIVE";y:"";""] -> ["INACTIVE";y:"";""] ["PASSIVE";y;"";"" -> ["PASSIVE";y:"";""]

This wouldn't work so well if there were more than three possible values for field1.