CODEWITHSUNDEEP
cassandracql
BeepBoop
BeepBoop

Reputation: 1312

Cassandra - Delete Time Series Rows in cqlsh

Running Cassandra 2.0.11 and I'm having difficulty deleting a row of time series data in CQLSH. Since I'm unable to use > or < in the WHERE clause of a DELETE statement, I'm assuming I need the exact time

Schema:

CREATE TABLE account_data_by_user (
      user_id int,
      time timestamp,
      account_id int,
      account_desc text,
      ...
PRIMARY KEY ((user_id), time, account_id)

Row in question:

user_id | time                     | account_id | account_desc     | 
--------+--------------------------+-----------------+------------------+-
      1 | 2015-02-20 08:51:55-0600 |               1 |             null |

Attempting:

DELETE 
FROM account_data_by_user 
WHERE user_id = 1 and time = '2015-02-20 08:51:55-0600' and account_id = 1

The above executes successfully, but the row is still there. I'm assuming the cqlsh output [time] is the problem.

I should note that I can delete a row like this through cqlengine.Model.delete, but I'm not sure what it's executing to accomplish the delete.

Upvotes: 1

Views: 755

Answers (1)

BeepBoop
BeepBoop

Reputation: 1312

So after much google, I've discovered the blob conversion functions from this JIRA issue: https://issues.apache.org/jira/browse/CASSANDRA-5870

Query: 

SELECT user_id, host_account_id, blobasbigint(timestampasblob(time)) 
FROM account_data_by_user where user_id

Returns:

user_id | account_id | blobasbigint(timestampasblob(time))                                                                        
---------+-----------------+-------------------------------------                                                                       
   1 |               1 |                       1424458973126                                                                        
   1 |          184531 |                       1423738054142


DELETE
FROM account_data_by_user 
WHERE user_id = 1 and time = 1424458973126 and host_account_id = 1;

This successfully removed the desired row.

Upvotes: 1

Related Questions