Select row with highest timestamp

user_1775344 0
Viewed 96

I have a table that stores events

CREATE TABLE active_events (
    event_id VARCHAR,
    number VARCHAR,
....
    start_time TIMESTAMP,
    PRIMARY KEY (event_id, number)
);

Now, I want to select an event with the highest start_time. It is possible? I've tried to create a secondary index, but no success.

This is a query I've created

select * from active_call order by start_time limit 1

But the error says ORDER BY is only supported when the partition key is restricted by an EQ or an IN. Should I create some kind of materialized view? What should I do to execute my query?

cassandra
2 Answers

This is an anti-pattern in Cassandra. To order the data you need to read all data and find the highest value. And this will require scanning of data on multiple nodes, and will be very long.

Materialized view also won't help much as order for data only exists inside an individual partition, so you will need to put all your data into a single partition that could be huge and data would be imbalanced.

I can only think of following workaround:

  • Have an additional table that will have all columns of the original table, but with a fake partition key and no clustering columns

  • You do inserts into that table in parallel to normal inserts, but use a fixed value for that fake partition key, and explicitly setting a timestamp for a record equal to start_time (don't forget to multiple by 1000 as timestamp uses microseconds). In this case it will guaranteed to be the value with the highest timestamp as Cassandra won't override it with other data with lower timestamp.

But this doesn't solve a problem with data skew, and all traffic will be handled by fixed number of nodes equal to RF.

Another alternative - use another database.

Alex_Ott_186270

This type of query isn't valid in big data because it requires a full table scan and doesn't scale. It works in traditional relational databases because the dataset is smaller. Imagine you had billions of partitions each with thousands of rows spread across hundreds of nodes. A full table scan in a large cluster will take a very long time if it was allowed.

The error:

ORDER BY is only supported when the partition key is restricted by an EQ or an IN

gets returned because you can only sort the results provided (a) the query is restricted to a partition key, and (b) the rows are ordered by a clustering column. You cannot sort the results based on a column that is not part of the clustering key. Cheers!

user_42695350
Related