Cassandra equivalent of group by

I'm in initial stages of data modeling in Cassandra for an application. This application has existing relational persistence layer, which has to be replaced with Cassandra.

Application uses a table named login_log for users, which provides last login time for any users in all applications.

Here's the CQL that I'm using to create this table

create table login_log ( user_id int, application_name text, login_date timestamp, primary key (user_id, application_name, login_date) ) with clustering order by (application_name asc, login_date desc) 

user_id is partition key. application_name and login_date are clustering keys. This table maintains a history of logins, data is never deleted in it.

I'm trying to retrieve last login date of given user_id for all applications in one query.

If I were writing a SQL query to do the same it would look something like below

select user_id, application_name, max(login_date) from login_log group by user_id, application_name 

However it cannot be done in Cassandra, since there's no group by clause or aggregation functions. Arguably clustering columns are already grouped but I'm unable to create a query which retrieves latest login_date for all applications in one query.

If were doing it for one application CQL would look like below

select * from login_log where user_id = ? and application_name = ? limit 1 

Since cluster are already ordered by login_date, order by is not needed. I need to extend the same query to retrieve data for all applications in one go.

Is it possible to do this in Cassandra? If not is there a data modeling technique that will allow me to do this?

Any hint is appreciated.

1 Answer

GROUP BY is supported from version 3.10 check improvement ticket here and Cassandra official documentation.