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.
