带有过滤器的卡桑德拉分组

Question

我有一个每分钟完成的事件表。我希望能够按时间段过滤这些事件，并聚合小时/天/等的数据。

我的数据模型：

create table min_dev_data (
device TEXT,
event_time BIGINT,
hour BIGINT,
day BIGINT,
value DOUBLE,
PRIMARY KEY ((device), event_time)
)
CREATE MATERIALIZED VIEW hour_dev_data AS
SELECT device, event_time, hour, value
FROM min_dev_data
WHERE hour IS NOT NULL AND value IS NOT NULL 
and event_time IS NOT NULL AND device IS NOT NULL
PRIMARY KEY ((device), hour, event_time)

我的查询是

select hour, sum(value) 
from hour_dev_data 
where device = 'tst' and event_time < 149000000 group by device, hour;

失败并显示错误 code=2200 [无效查询] 消息="主键列"event_time"无法限制，因为前面的列"小时"不受限制">

使其工作的唯一方法是添加允许过滤，这是不可预测的。

如何更改数据模型以解决查询并避免"允许筛选"模式？

Answer 1

您必须主动生成以下结果：

create table min_dev_data (
device TEXT,
event_time BIGINT,
hour BIGINT,
day BIGINT,
value DOUBLE,
PRIMARY KEY ((device), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
create table hour_dev_data (
device TEXT,
hour BIGINT,
day BIGINT,
event_time BIGINT,
value DOUBLE,
PRIMARY KEY ((device), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);
create table day_dev_data (
device TEXT,
day BIGINT,
event_time BIGINT,
value DOUBLE,
PRIMARY KEY ((device), event_time)
) WITH CLUSTERING ORDER BY (event_time DESC);

每个表仅满足一个粒度。

每小时查询分钟数据以获取每个设备的最新小时数据，如下所示：

SELECT * FROM min_dev_data WHERE device = X AND event_time < YYYY

在应用程序级别将其相加，并将此值存储到小时表中：

INSERT INTO hour_dev_data (device, hour, day, event_time, value) VALUES (....);

每天，您都会查询小时表以生成进一步的聚合数据：

SELECT * FROM hour_dev_data WHERE device = X AND event_time < YYYY

在应用程序级别求和，并将此值存储到 day 表中。

请考虑添加某种形式的分桶，因为每隔一分钟，在两个月内，您的分钟表将具有宽分区。如果您以相反的顺序(像我一样)保持表并且仅查询过去几个小时，这应该不是问题。但是，如果您也想及时查询，那么您绝对必须在表中使用存储桶。

Answer 2

我认为你已经做对了，但是你需要将event_time上的过滤器更改为hour上的过滤器。

select hour, sum(value)  
from hour_dev_data  
where device = 'tst' and hour < 1500000000 
group by device, hour;

在对event_time进行筛选时，隐式要求对行进行完全扫描，因为event_time在一小时后聚集。要按event_time过滤，需要检查每个单元格以检查event_time。按hour进行筛选时，它位于群集键中的第一个位置，因此可以有效地对其进行扫描和筛选。请参阅这篇关于允许过滤的文章，以获取更多信息。

我同意 xmas79 的观点，您可能希望在某种程度上进行桶装，也许是按月或按年，具体取决于您的事件频率。如果您总是要查找最新的值，那么将聚类键排序设置为 desc 也可能很有帮助：

CREATE MATERIALIZED VIEW hour_dev_data3 AS
SELECT device, event_time, hour, value
FROM min_dev_data
WHERE hour IS NOT NULL AND value IS NOT NULL 
and event_time IS NOT NULL AND device IS NOT NULL
PRIMARY KEY ((device), hour, event_time)
WITH CLUSTERING ORDER BY (hour DESC);

像xmas79这样的调度聚合表明，由于总和只完成一次，而不是每次完成读取时求和，因此会更有效率，但是它确实增加了更多的维护负担，具体化视图会为您处理它。