Cassandra : Data Modelling

Question

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.

While you haven't posted your table definition, I can tell you that what you are doing is not as straight-forward as it is in the relational world. If you need to UPDATE your timestamp, then it cannot be a part of your PRIMARY KEY. — Aaron
@Aaron : If i don't add the timestamp to the primary key then i am unable to perform range queries using timestamp. These are two opposing constraints i am having. I was creating a table as follows : CREATE TABLE IF NOT EXISTS HAS.MACRecord (MACAdd VARCHAR PRIMARY KEY,Timestamp timestamp, Record int, position int); Is it possible to perform range queries on timestamp without it being part of the primary key? — srai

Jim Meyer Jim Meyer · Accepted Answer · 2015-09-28T17:11:54

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.

Cassandra : Data Modelling

1 Answers