what's the nature ordering by cassandra java driver queryBuilder

Question

i have cassandra table with partition key as email and clustering key as topic(String) and eventAt(timestamp), i need to query the table to get most recent N topics, the recentency is determined by eventAt, here is the query I have:

BuiltStatement builtStatement =
        QueryBuilder.select()
            .all()
            .from("email_table")
            .where(QueryBuilder.eq(Email.COLUMN_EMAIL, bindMarker()))
            .and(QueryBuilder.eq(Email.COLUMN_TOPIC, bindMarker()))
            .limit(bindMarker());

the results are sorted by eventAt column in desc order, though it's the result I expect but wondering how Cassandra handling the query result ordering? I thought it would be order by eventAt column in asc order but apparently it's not

Alex Ott Alex Ott · Accepted Answer · 2020-11-05T07:31:32

Cassandra usually returns data ordered according to the definition of the clustering columns. So if you have table like this:

create table tbl (
  email text,
  topic text,
  eventAt timestamp,
  ....,
  primary key (email, topic, eventAt)
) with clustering order by (topic asc, eventAt desc);

then when you query the data, data is returned in sorted form - as they are stored on the disk - according to the definition in the clustering order by (SSTables is the acronym for sorted string tables), and read sequentially:

first sorted by topic, in the ascending order
inside the specific topic, ordered by eventAt in descending order

You may have a limited support for changing the sorting order when querying the data, but it's just changing the order in which data is read, and if you have the multiple clustering columns, you could be surprised by results - usually it works only for last clustering column that is used in the query.

If you want to have N recent topics, then you need to have another table with eventAt as first clustering column:

create table tbl (
  email text,
  topic text,
  eventAt timestamp,
  ....,
  primary key (email, eventAt, topic)
) with clustering order by (topic asc, eventAt desc);

then you can easily get last N topics by applying select ... from tbl ... limit N.

what's the nature ordering by cassandra java driver queryBuilder

1 Answers