Results pagination in Cassandra (CQL)

You don’t need to use tokens, if you are using Cassandra 2.0+.

Cassandra 2.0 has auto paging.
Instead of using token function to create paging, it is now a built-in feature.

Now developers can iterate over the entire result set, without having to care that it’s size is larger than the memory. As the client code iterates over the results, some extra rows can be fetched, while old ones are dropped.

Looking at this in Java, note that SELECT statement returns all rows, and the number of rows retrieved is set to 100.

I’ve shown a simple statement here, but the same code can be written with a prepared statement, couple with a bound statement. It is possible to disable automatic paging, if it is not desired. It is also important to test various fetch size settings, since you will want to keep the memorize small enough, but not so small that too many round-trips to the database are taken. Check out this blog post to see how paging works server side.

Statement stmt = new SimpleStatement(
                  "SELECT * FROM raw_weather_data"
                  + " WHERE wsid= '725474:99999'"
                    + " AND year = 2005 AND month = 6");
stmt.setFetchSize(24);
ResultSet rs = session.execute(stmt);
Iterator<Row> iter = rs.iterator();
while (!rs.isFullyFetched()) {
   rs.fetchMoreResults();
   Row row = iter.next();
   System.out.println(row);
}