Cassandra, counters and delete by field

Question

I've this specific use case. I'm storing counters in a table associated with a timestamp:

CREATE TABLE IF NOT EXISTS metrics(
  timestamp timestamp,
  value counter,
  PRIMARY KEY ((timestamp))
);

And I would like to delete all the metrics whose timestamp is lower than a specific value, for example:

DELETE FROM metrics WHERE timestamp < '2015-01-22 17:43:55-0800';

But this command is returning the following error:

code=2200 [Invalid query] message="Invalid operator < for PRIMARY KEY part timestamp"

How could I implement this functionality?

Answer 1

For a delete to work, you will need to provide a precise key with an equals operator. Deleting with a greater/less than operator does not work. Basically, you would have to obtain a list of the timestamps that you want to delete, and iterate through them with a (Python?) script or short (Java/C#) program.

One possible solution (if you happen to know how long you want to keep the data for), would be to set a time to live (TTL) on the data. On a table with counter columns, you cannot do it as a part of an UPDATE command. The only option, is to set it when you create the table:

CREATE TABLE IF NOT EXISTS metrics(
  timestamp timestamp,
  value counter,
  PRIMARY KEY ((timestamp))
) WITH default_time_to_live=259200;

This will remove all data put into the table after 3 days (259200 seconds).

EDIT

And it turns out that the possible solution really isn't possible. Even though Cassandra lets you create a counter table with a default_time_to_live set, it doesn't enforce it.

Back to my original paragraph, the only way to execute a DELETE is to provide the specific key that you are deleting. And for counter tables, it looks like that is probably the only way possible.