在Cassandra中按时间戳排序最新记录

Question

在Cassandra中按时间戳排序最新记录

9

我试图显示传感器列表的最新值。该列表还应该可以按时间戳进行排序。

我尝试了两种不同的方法。我将传感器的更新时间包含在主键中：

CREATE TABLE sensors (
    customerid int,
    sensorid int,
    changedate timestamp,
    value text,
    PRIMARY KEY (customerid, changedate)
) WITH CLUSTERING ORDER BY (changedate DESC);

然后我可以像这样选择列表：

select * from sensors where customerid=0 order by changedate desc;

这导致了这个结果：

 customerid | changedate               | sensorid | value
------------+--------------------------+----------+-------
          0 | 2015-07-10 12:46:53+0000 |        1 |     2
          0 | 2015-07-10 12:46:52+0000 |        1 |     1
          0 | 2015-07-10 12:46:52+0000 |        0 |     2
          0 | 2015-07-10 12:46:26+0000 |        0 |     1

问题是，我不仅得到了最新的结果，还得到了所有旧值。

如果我从主键中删除更改日期，选择操作就会完全失败。

InvalidRequest: code=2200 [Invalid query] message="Order by is currently only supported on the clustered columns of the PRIMARY KEY, got changedate"

更新传感器数值也不是一个选项：

update overview set changedate=unixTimestampOf(now()), value = '5' where customerid=0 and sensorid=0;
InvalidRequest: code=2200 [Invalid query] message="PRIMARY KEY part changedate found in SET part"

这个失败是因为changedate是主键的一部分。

有没有可能只存储每个传感器的最新值，并保持表按时间戳排序？

编辑：与此同时，我尝试了另一种方法，仅存储最新值。

我使用了这个模式：

CREATE TABLE sensors (
    customerid int,
    sensorid int,
    changedate timestamp,
    value text,
    PRIMARY KEY (customerid, sensorid, changedate)
) WITH CLUSTERING ORDER BY (changedate DESC);

在插入最新的值之前，我会删除所有旧的值。

DELETE FROM sensors WHERE customerid=? and sensorid=?;

但是这种方法失败了，因为changedate不是WHERE子句的一部分。

- user5102859

这似乎是经典的每个组中检索最后一条记录的问题。即获取每个sensorID的最新读数。 - malhal

2个回答

2

你的第一种方法看起来很合理。如果你在查询中添加“limit 1”，你将只得到最新的结果，或者限制为2以查看最新的2个结果，以此类推。

如果你想自动从表中删除旧值，你可以在插入数据时指定数据点的TTL（生存时间）。所以如果你想保留数据点10天，你可以在插入语句中添加“USING TTL 864000”。或者你可以为整个表设置默认的TTL。

- Jim Meyer

我不能在这里使用 TTL，因为传感器报告值的时间跨度没有保证。它可能会保持沉默数天、数周甚至数月。但我仍然需要保留最新的值。 - user5102859

我认为OP想要每个sensorID的最新版本。 - malhal

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Andy Tolbert · Accepted Answer

问题是，我不仅得到了最新的结果，还得到了所有旧值。

由于您正在使用 CLUSTERING ORDER DESC 进行存储，因此始终很容易获取最新记录，您只需要在查询中添加“LIMIT”即可，例如：

select * from sensors where customerid=0 order by changedate desc limit 10;

最多返回10条最新的记录，即使你使用了limit，由于数据已经按照这种方式排序，所以仍然可以保证获取到最新的记录。

“如果我从主键中删除changedate，select语句就完全失败了。” 这是因为除了通过二级索引（我不建议这样做）之外，你无法根据非聚集键（主键的次要部分）来对列进行排序。

“更新传感器值也不是一个选择” 你的更新查询失败是因为在“set”中包含主键的一部分是不合法的。为了使它正常工作，你只需要将changedate包含在where子句中即可，如下所示：

update overview set value = '5' and sensorid = 0 where customerid=0 and changedate=unixTimestampOf(now())

有可能只存储每个传感器的最新值，同时保持表按时间戳排序吗？

您可以通过创建一个名为“latest_sensor_data”的单独表来实现此目的，该表与原表定义相同，但主键除外。主键现在将是“customerid，sensorid”，因此每个传感器只能有1条记录。创建单独的表的过程称为去规范化，这是一种常见的使用模式，特别是在Cassandra数据建模中。当插入传感器数据时，您现在需要同时插入数据到“sensors”和“latest_sensor_data”中。

CREATE TABLE latest_sensor_data (
  customerid int,
  sensorid int,
  changedate timestamp,
  value text,
  PRIMARY KEY (customerid, sensorid)
);

在Cassandra 3.0中，将引入“物化视图”功能'（materialized views）'，这将使这个过程变得不必要，因为您可以使用物化视图来完成它。

现在执行以下查询：

select * from latest_sensor_data where customerid=0

会为该客户提供每个传感器的最新值。

我建议将“sensors”重命名为“sensor_data”或“sensor_history”，以使数据更清晰明了。此外，您应该将主键更改为“customerid，changedate，sensorid”，因为这将允许您在同一日期拥有多个传感器（这似乎是可能的）。