CODEWITHSUNDEEP
cassandracassandra-2.0
Nilesh
Nilesh

Reputation: 2180

Delete Rows using timestamp column cassandra

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?

Upvotes: 3

Views: 6672

Answers (3)

fmi
fmi

Reputation: 193

In Cassandra 3 you can use the "...from Y using timestamp XXX where ..." command:

create table mytime (
    location_id text,
    tour_id text,
    mytime timestamp,
PRIMARY KEY (location_id, tour_id));

INSERT INTO mytime (location_id, tour_id, mytime) values ('location1', '1', toTimeStamp(now()));
INSERT INTO mytime (location_id, tour_id, mytime) values ('location1', '2', toTimeStamp(now()));

Be aware: the value you need to use for the timestamp is nanoseconds not miliseconds:

select location_id, mytime, blobAsBigint(mytime), WRITETIME(mytime) from mytime;

location_id |mytime                  |system.blobasbigint(mytime) |writetime(mytime) |
------------|------------------------|----------------------------|------------------|
location1   |2018-11-28-09.53.52.110 |1543395232110               |1543395232109517  |
location1   |2018-11-28-09.53.52.742 |1543395232742               |1543395232740055  |

So now you can do

delete from mytime using timestamp 1543395232109517 where location_id = 'location1';

Which correctly deletes the entry <= 1543395232109517:

select location_id, mytime, blobAsBigint(mytime), WRITETIME(mytime) from mytime;

location_id |mytime                  |system.blobasbigint(mytime) |writetime(mytime) |
------------|------------------------|----------------------------|------------------|
location1   |2018-11-28-09.53.52.742 |1543395232742               |1543395232740055  |

Upvotes: 0

Aaron
Aaron

Reputation: 57788

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.

Upvotes: 3

Nishant
Nishant

Reputation: 161

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

Upvotes: 1

Related Questions