Cassandra pagination: How to use get_slice to query a Cassandra 1.2 database from Python using the cql library

Question

I have a Cassandra 1.2 cluster and I'm using it from Python using the cql library. Now I need to implement some paging functionality that seems pretty straightforward using get_slice, but I can't find any documentation on how to use something like this from the cql library:

get_slice("key" : table_key,
      "column_parent" : {"column_family" : "MyColumnFamily"},
      "predicate" :
       { "slice_range" : 
 { "start" : "SomeStartID", 
 "end" : "Z", 
 "reverse" : "false", 
 "count : "100" }
 } )

I've seen this type of syntax on random documentation for get_slice, and it doesn't look like CQL 3 syntax, how can I run this type of queries from Python to a Cassandra 1.2 cluster?, Is this the current way of using get_slice or there is a new syntax or CQL 3 alternative?

Thanks in advance!

Dogs

Here's a closely related question. – Kerriekerrigan

Answer 1

You can do paging in much the same way: set a limit and start at a column name greater than the previous one received. As an example, I created a table test1 in keyspace ks1:

CREATE TABLE test1 (
  a text,
  b text,
  PRIMARY KEY (a, b)
)

Here a is my row key and b is the column name. I then inserted 12 records with a=a and b from a to l. So

cqlsh:ks1> select * from test1;

 a | b
---+---
 a | a
 a | b
 a | c
 a | d
 a | e
 a | f
 a | g
 a | h
 a | i
 a | j
 a | k
 a | l

Then I paged with this python using the CQL driver:

import cql
con = cql.connect('localhost', keyspace='ks1', cql_version='3.0.0')
cursor = con.cursor()
last = ""
while last != None:
    cursor.execute("select * from test1 where a=:a and b>:b limit 5", {"a": "a", "b": last})
    last = None
    for row in cursor:
        print row
        last = row[1]

which pages in batches of 5. The output is:

[u'a', u'a']
[u'a', u'b']
[u'a', u'c']
[u'a', u'd']
[u'a', u'e']
[u'a', u'f']
[u'a', u'g']
[u'a', u'h']
[u'a', u'i']
[u'a', u'j']
[u'a', u'k']
[u'a', u'l']

Breastfeed

Of course you can do this, but the get_slice functionality has a "reverse" to allow you to get the next/previous page, that's something I'm really interested in. Is there a way of doing that using plain CQL 3 and without saving the previous keys? – Mantua

You can't use reverse in get_slice to do paging, all it can do is get the last N columns rather than the first. You can do reverse queries in CQL with ORDER BY. – Breastfeed

Per my related question how would you extend your result in the case that the PRIMARY KEY is a triple (or more)? – Kerriekerrigan

Answer 2

An update for modern versions of Cassandra. All of the CQL3 native drivers can use server side paging (starting with Cassandra 2.0), so no need to go through the hassle of setting it up manually anymore.

Decomposer

Can u please explain how to achieve pagination using CQL3 – Lark

Use one of the current CQL3 drivers, and they do it automatically. – Decomposer

Can you give an example? – Detest