CQL for Cassandra 2.x

Slicing over partition rows

In Cassandra 2.0.6 and later, you can use a new syntax for slicing over rows of a partition if the table has more than one clustering column. Using a conditional operator, you can compare groups of clustering keys to certain values For example:

CREATE TABLE timeline (
   day text,
   hour int,
   min int,
   sec int,
   value text,
   PRIMARY KEY (day, hour, min, sec)
   );
   
   INSERT INTO timeline (day, hour, min, sec, value)
   VALUES ('12 Jan 2014', 3, 43, 12, 'event1');
   INSERT INTO timeline (day, hour, min, sec, value)
   VALUES ('12 Jan 2014', 3, 52, 58, 'event2');
   INSERT INTO timeline (day, hour, min, sec, value)
   VALUES ('12 Jan 2014', 4, 37, 01, 'event3');
   INSERT INTO timeline (day, hour, min, sec, value)
   VALUES ('12 Jan 2014', 4, 37, 41, 'event3');
   INSERT INTO timeline (day, hour, min, sec, value)
   VALUES ('12 Jan 2014', 6, 00, 34, 'event4');
   
   SELECT * FROM timeline;
   
   day         | hour | min | sec | value
   -------------+------+-----+-----+--------
   12 Jan 2014 |    3 |  43 |  12 | event1
   12 Jan 2014 |    3 |  52 |  58 | event2
   12 Jan 2014 |    4 |  37 |   1 | event3
   12 Jan 2014 |    4 |  37 |  41 | event3
   12 Jan 2014 |    6 |   0 |  34 | event4
   

To retrieve events for the 12th of January 2014 between 3:50:00 and 4:37:30, use the new syntax as follows:

SELECT * FROM timeline WHERE day='12 Jan 2014'
   AND (hour, min) >= (3, 50)
   AND (hour, min, sec) <= (4, 37, 30);
   
   day         | hour | min | sec | value
   -------------+------+-----+-----+--------
   12 Jan 2014 |    3 |  52 |  58 | event2
   12 Jan 2014 |    4 |  37 |   1 | event3
   

The new syntax, in this example, uses a conditional operator to compare groups of clustering keys, such as hour, min, and sec, to certain values.

In the WHERE clause, you need to use sequential clustering columns. The sequence must match the sequence of the columns in the table definition. For example:

CREATE TABLE no_column_skipping 
(a int, b int, c int, d int, e int, 
PRIMARY KEY (a, b, c, d))

This WHERE clause does not work:

SELECT ... WHERE a=0 AND (b, d) > (1, 2)

This WHERE clause does not work:

SELECT ... WHERE a=0 AND (b, c) > (1, 2)
Show/hide