Cassandra中带有CQL的CAS

Question

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);

我猜它看起来是：

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

因此，本质上，我可以为给定的行拥有一堆时间戳，为一行拥有一个UUID。对于TS列的每次新插入，都需要更新UUID。

所以插入一行就可以了：

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

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

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

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

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

我正试图找出如何正确地对数据建模，以便每行只有一个UUID，并且可以有多个列，而不必在表创建期间显式地定义它们，因为它可能会有很大的变化。

IIRC，使用thrift客户机很容易做到这一点，但使用它不是一个选项=/

Answer 1

这里有一个关于数据系列的很好的教程

简而言之，您的复合键将是您唯一的标识符（就像您提出的UUID一样）和时间戳，因此您将能够添加尽可能多的与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);

添加值将具有相同的UUID和不同的时间：

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)

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)