Delete Rows using timestamp column cassandra

Question

I want to delete data between timestamp from my table.

CREATE TABLE propatterns_test.test (
clientId text,
meterId text,
meterreading text,
date timestamp,
PRIMARY KEY (meterId, date) );

My delete query is:

DELETE FROM test  WHERE meterid = 'M5' AND date > '2016-12-27 10:00:00+0000';

Which returned this error :

InvalidRequest: Error from server: code=2200 [Invalid query] 
    message="Invalid operator < for PRIMARY KEY part date"

After that I tried to delete a specific row :

DELETE FROM test  WHERE meterid = 'M5' AND date = '2016-12-27 09:42:30+0000';

Actually the table contains the same record, but it was not deleted.

This is what my data looks like:

 meterid | date                     | clientid | meterreading
---------+--------------------------+----------+--------------
      M5 | 2016-12-27 09:42:30+0000 |      RDS |      35417.8
      M5 | 2016-12-27 09:42:44+0000 |      RDS |      35417.8
      M5 | 2016-12-27 09:47:20+0000 |      RDS |      35417.8
      M5 | 2016-12-27 09:47:33+0000 |      RDS |      35417.8

Nothing is deleting from table. So how can I delete data between timestamp dates which is part of the primary key?

Answer 1

I see a couple of things happening here. First of all, like iconnj mentioned, range deletes are not possible in versions prior to Cassandra 3.0.

Secondly, your single-row delete attempt is failing (I believe) due to the fact that you are not accounting for the milliseconds present on the timestamp. You can see this if you nest your date column inside the timestsampasblob and blobasbigint functions:

aploetz@cqlsh:stackoverflow> SELECT meterid,date,blobAsBigint(timestampAsBlob(date)) 
    FROM propatterns WHERE meterid='M5';

 meterid | date                     | system.blobasbigint(system.timestampasblob(date))
---------+--------------------------+---------------------------------------------------
      M5 | 2016-12-27 09:42:30+0000 |                                     1482831750000
      M5 | 2016-12-30 17:31:53+0000 |                                     1483119113231
      M5 | 2016-12-30 17:32:08+0000 |                                     1483119128812

(3 rows)

Note the zeros on the end of the 2016-12-27 09:42:30+0000 row, that I explicitly INSERTed from your example. Note that the two rows I INSERTed using the dateof(now()) nested functions actually has the milliseconds as the last three digits on the timestamps.

Watch what happens when I take those three digits and add them as milliseconds when I delete one of the rows:

aploetz@cqlsh:stackoverflow> DELETE FROM propatterns WHERE meterid='M5'
    AND date='2016-12-30 17:32:08.812+0000';
aploetz@cqlsh:stackoverflow> SELECT meterid,date,blobAsBigint(timestampAsBlob(date))
    FROM propatterns WHERE meterid='M5';

 meterid | date                     | system.blobasbigint(system.timestampasblob(date))
---------+--------------------------+---------------------------------------------------
      M5 | 2016-12-27 09:42:30+0000 |                                     1482831750000
      M5 | 2016-12-30 17:31:53+0000 |                                     1483119113231

(2 rows)

In summary:

• You cannot perform range deletes prior to Cassandra 3.0.
• You cannot delete individual rows keyed by timestamps without specifying milliseconds, if milliseconds are indeed present.

Answer 2

Delete with range clause is possible in C* 3.0 onwards. Looking at the error you got I think you are on a pre 3.0 version in which case you won't be able to do this via CQL