CODEWITHSUNDEEP
cassandra
shantanuo
shantanuo

Reputation: 32220

range queries in cassandra

The following is working as expected. But who do I execute range queries like "where age > 40 and age < 50"

create keyspace Keyspace1;
use Keyspace1;
create column family Users with comparator=UTF8Type and default_validation_class=UTF8Type and key_validation_class=UTF8Type;

set Users[jsmith][first] = 'John';
set Users[jsmith][last] = 'Smith';
set Users[jsmith][age] = long(42);

get Users[jsmith];
=> (column=age, value=42, timestamp=1341375850335000)
=> (column=first, value=John, timestamp=1341375827657000)
=> (column=last, value=Smith, timestamp=1341375838375000)

Upvotes: 2

Views: 1136

Answers (2)

Tyler Hobbs
Tyler Hobbs

Reputation: 6932

The best way to do this in Cassandra varies depending on your requirements, but the approaches are fairly similar for supporting these types of range queries.

Basically, you will take advantage of the fact that columns within a row are sorted by their names. So, if you use an age as the column name (or part of the column name), the row will be sorted by ages.

You will find a lot of similarities between this and storing time-series data. I suggest you take a look at Basic Time Series with Cassandra for the fundamentals, and the second half of an intro to the latest CQL features that gives an example of a somewhat more powerful approach.

The built-in secondary indexes are basically designed like a hash table, and don't work for range queries unless that range expression accompanies an equality expression on an indexed column. So, you could ask for select * from users where name = 'Joe' and age > 54, but not simply select * from users where age > 54, because that would require a full table scan. See Secondary Indexes doc for more details.

Upvotes: 4

sahid
sahid

Reputation: 2610

You have to create a Secondary index on the column age:

update column family Users with column_metadata=[{column_name: age, validation_class: LongType, index_type: KEYS}];

Then use:

get Users where age > 40 and age < 50

Note: I think: Exclusive operators are not supported since 1.2.

Datastax has a good documentation about that: http://www.datastax.com/dev/blog/whats-new-cassandra-07-secondary-indexes Or you can create and maintain your own secondary index. This is a good link about that: http://www.anuff.com/2010/07/secondary-indexes-in-cassandra.html

Upvotes: 1

Related Questions