CODEWITHSUNDEEP
cassandra
srai
srai

Reputation: 1031

Cassandra : Data Modelling

I currently have a table in cassandra called macrecord which looks something like this :

 macadd            | position | record | timestamp
-------------------+----------+--------+---------------------
 23:FD:52:34:DS:32 |        1 |      1 | 2015-09-28 15:28:59

However i now need to make queries which will use the timestamp column to query for a range. I don't think it is possible to do so without timestamp being part of the primary key (macadd in this case) i-e without it being a clustering key.

If i make timestamp as part of the primary key the table looks like below :

 macadd            | timestamp           | position | record
-------------------+---------------------+----------+--------
 23:FD:52:34:DS:32 | 2015-09-28 15:33:26 |        1 |      1

However now i cannot update the timestamp column whenever i get a duplicate macadd. 

update macrecord set timestamp = dateof(now()) where macadd = '23:FD:52:34:DS:32';

gives an error : 

message="PRIMARY KEY part timestamp found in SET part"

I cannot think of an other solution in this case other than to delete the whole row if there is a duplicate value of macadd and then to insert a new row with an updated timestamp.

Is there a better solution to update the timestamp whenever there is a duplicate value of macadd or an alternative way to query for timestamp values in a range in my original table where only macadd is the primary key.

Upvotes: 0

Views: 94

Answers (1)

Jim Meyer
Jim Meyer

Reputation: 9475

To do a range query in CQL, you'll need to have timestamp as a clustering key. But as you have seen, you can't update key fields without doing a delete and insert of the new key.

One option that will become available in Cassandra 3.0 when it is released in October is materialized views. That would allow you to have timestamp as a value column in the base table and as a clustering column in the view. See an example here.

Upvotes: 1

Related Questions