Cassandra CQL3 - Range Queries

Range Queries using CQL3 :

Once of the major advantage of using Cassandra via thrift was using the hashmap like structure . But with cql3 , it doesn’t appear to be available in a very straight forward way ..

But do not worry , it is very simple . No Maps are not the perfect replacement , because it is not possible to do range queries on a map right now

There is a better way to do it ..

Let us create a column family called testrange

CREATE TABLE testrange (
  key TEXT,
  detail_key int,
  detal_value text,
  PRIMARY KEY (key, detail_key)
);

We are creating the PK as key coupled with detail_key , this will force all the entries with same key to be a single row and each different detail_key will be a column name and we can do a =,<,> query on detail_key

Lets try this now

cqlsh:testkeyspace> describe columnfamily testrange;

CREATE TABLE testrange (

  key text,

  detail_key int,

  detal_value text,

  PRIMARY KEY (key, detail_key)

) WITH

  bloom_filter_fp_chance=0.010000 AND

  caching='KEYS_ONLY' AND

  comment='' AND

  dclocal_read_repair_chance=0.000000 AND

  gc_grace_seconds=864000 AND

  index_interval=128 AND

  read_repair_chance=0.100000 AND

  replicate_on_write='true' AND

  populate_io_cache_on_flush='false' AND

  default_time_to_live=0 AND

  speculative_retry='99.0PERCENTILE' AND

  memtable_flush_period_in_ms=0 AND

  compaction={'class': 'SizeTieredCompactionStrategy'} AND

  compression={'sstable_compression': 'LZ4Compressor'};

Inserting a few records

cqlsh:testkeyspace> insert into testrange (key,detail_key,detal_value) VALUES ('1',1,'number one');

cqlsh:testkeyspace> insert into testrange (key,detail_key,detal_value) VALUES ('1',2,'number two');

cqlsh:testkeyspace> insert into testrange (key,detail_key,detal_value) VALUES ('1',3,'number three');

cqlsh:testkeyspace> insert into testrange (key,detail_key,detal_value) VALUES ('1',4,'number four');

cqlsh:testkeyspace> insert into testrange (key,detail_key,detal_value) VALUES ('1',5,'number five');

cqlsh:testkeyspace> select * from testrange;

 key | detail_key | detal_value

-----+------------+--------------

   1 |          1 |   number one

   1 |          2 |   number two

   1 |          3 | number three

   1 |          4 |  number four

   1 |          5 |  number five

(5 rows)

So far , so good … now lets execute the range queries ..

cqlsh:testkeyspace> select * from testrange where key = '1' and detail_key > 2 and detail_key <=4;

 key | detail_key | detal_value

-----+------------+--------------

   1 |          3 | number three

   1 |          4 |  number four

(2 rows)

Cool , but how do we confirm if we are using the same range query type query that was offered by Cassandra thrift .

Lets jump back to the cli

[default@testkeyspace] list testrange;

Using default limit of 100

Using default cell limit of 100

-------------------

RowKey: 1

=> (name=1:, value=, timestamp=1402037639440000)

=> (name=1:detal_value, value=6e756d626572206f6e65, timestamp=1402037639440000)

=> (name=2:, value=, timestamp=1402037649568000)

=> (name=2:detal_value, value=6e756d6265722074776f, timestamp=1402037649568000)

=> (name=3:, value=, timestamp=1402037659783000)

=> (name=3:detal_value, value=6e756d626572207468726565, timestamp=1402037659783000)

=> (name=4:, value=, timestamp=1402037668866000)

=> (name=4:detal_value, value=6e756d62657220666f7572, timestamp=1402037668866000)

=> (name=5:, value=, timestamp=1402037677374000)

=> (name=5:detal_value, value=6e756d6265722066697665, timestamp=1402037677374000)

1 Row Returned.

Elapsed time: 294 msec(s).

[default@testkeyspace]

So , we have only one row and all out detail_key are columns .. which is almost similar to what we used to do earlier with thrift.