CAS with CQL in Cassandra

Question

I'm trying to model some time series data in Cassandra which I had been able to do with the older thrift client but CQL seems to be throwing me off.

I want to add a NEW column to my row IF a specific column value matches.

My table definition is:

CREATE TABLE TestTable (
   key int,
   base uuid,
   ts int,                   // Timestamp (column name)
   val text,                 // Timestamp value (column value)
   PRIMARY KEY (key, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

What I'm guessing it'd look like is:

Row | UUID | TS  | TS | TS
--- | ---- | --- | ---| ---
 1  | id1  |  1  |  2 |  3
--- | ---  | --- | ---| ---
 2  | id2  |  1  |  5 |  6

So essentially, I can have a bunch of Timestamps for a given row and a SINGLE UUID for a row. The UUID needs to be updated for each new insert of a TS column.

So inserts in a row work just fine:

insert into TestTable(key, base, ts, val) values (1, dfb63886-91a4-11e6-ae22-56b6b6499611, 50, 'one')

But I'm failing to figure out a way, using CQL, to INSERT a new column in a row using Cassandra transactions (CAS).

This one fails:

insert into TestTable(key, base, ts, val) values (1, dfb63886-91a4-11e6-ae22-56b6b6499611, 70, 'four') if base = dfb63886-91a4-11e6-ae22-56b6b6499611;

with the error:

SyntaxException: <ErrorMessage code=2000 [Syntax error in CQL query] message="line 1:106 mismatched input 'base' expecting K_NOT (..., 70, 'four') if [base] =...)">

And the query:

update TestTable set val = 'four', ts=70  where key = 1 if base = dfb63886-91a4-11e6-ae22-56b6b6499611;

fails with the error:

InvalidRequest: code=2200 [Invalid query] message="PRIMARY KEY part ts found in SET part"

I'm trying to figure out how to model the data properly so that I only have one UUID per row and can have multiple columns without having to explicitly define them during table creation, since it can vary quite a bit.

IIRC, it was easy doing this with the thrift client but using that isn't an option =/

Carlos Monroy Nieblas Carlos Monroy Nieblas · Accepted Answer · 2016-10-14T04:39:49

There is a nice tutorial regarding data series here

In a nutshell, your composite key will be your unique identifier (like the UUID that you were proposing) and a timestamp, so you will be able to add as many events/values associated to a UUID

CREATE TABLE IF NOT EXISTS TestTable (
   base uuid,
   ts timestamp,             // Timestamp (column name)
   value text,                 // Timestamp value (column value)
   PRIMARY KEY (base, ts)
) WITH CLUSTERING ORDER BY (ts DESC);

Adding values will have the same UUID with different times:

INSERT INTO TestTable (base, ts, value) 
    VALUES (467286c5-7d13-40c2-92d0-73434ee8970c, dateof(now()), 'abc');

INSERT INTO TestTable (base, ts, value) 
    VALUES (467286c5-7d13-40c2-92d0-73434ee8970c, dateof(now()), 'def');

cqlsh:test> SELECT * FROM TestTable WHERE base = 467286c5-7d13-40c2-92d0-73434ee8970c;

 base                                 | ts                              | value
--------------------------------------+---------------------------------+-------
 467286c5-7d13-40c2-92d0-73434ee8970c | 2016-10-14 04:13:42.779000+0000 |   def
 467286c5-7d13-40c2-92d0-73434ee8970c | 2016-10-14 04:12:50.551000+0000 |   abc

(2 rows)

Updating can be done in any of the columns, except the ones used as keys, the errors displayed in the update statement was caused by the "IF" statement and because it was tried to update ts which is part of the composite key.

INSERT INTO TestTable (base, ts, value)
       VALUES (ffb0bb8e-3d67-4203-8c53-046a21992e52, dateof(now()), 'bananas');

SELECT * FROM TestTable WHERE base = ffb0bb8e-3d67-4203-8c53-046a21992e52 AND ts < dateof(now());

 base                                 | ts                              | value
--------------------------------------+---------------------------------+---------
 ffb0bb8e-3d67-4203-8c53-046a21992e52 | 2016-10-14 04:17:26.421000+0000 | apples

(1 rows)

UPDATE TestTable SET value = 'apples' WHERE base = ffb0bb8e-3d67-4203-8c53-046a21992e52;

SELECT * FROM TestTable WHERE base = ffb0bb8e-3d67-4203-8c53-046a21992e52 AND ts < dateof(now());

 base                                 | ts                              | value
--------------------------------------+---------------------------------+---------
 ffb0bb8e-3d67-4203-8c53-046a21992e52 | 2016-10-14 04:17:26.421000+0000 | bananas

(1 rows)

CAS with CQL in Cassandra

1 Answers