Cassandra 使用二级索引按日期过滤

Question

我需要回答以下问题:

• 返回每个季度(最多 36 个月)的新客户数量
• 列出每季度(最多 36 个月)的新客户

我在 Cassandra 中创建了下表来处理这个问题:

CREATE TABLE first_purchase_by_shopper_date
(
    shop_id                     uuid,
    shopper_id                  uuid,
    dt_first_purchase           timestamp,

    ... (some text fields)

    PRIMARY KEY ((shop_id, shopper_id))
);

为了能够在 Cassandra 中回答这个查询，我需要能够在 dt_first_purchase 字段上过滤这个数据。

但是如果我将 dt_first_purchase 添加到主键，那么它会使该行对购物者来说不是唯一的 - 因此我们在表中得到多个条目 - 但我们只想要每个购物者一个条目。

所以我的插入语句看起来像

Insert into first first_purchase_by_shopper_date (shop_id, shopper_id, dt_first_purchase, ... ) Values(...) If Not Exists;

末尾的 if not exists 确保只有在条目不存在时才写入条目(例如，不对现有记录执行更新。)

我如何在此表上按日期过滤 - dt_first_purchase 列上的二级索引是我唯一的选择 - 这不是不可取的吗？

Answer 1

How can I filter by date on this table - is a secondary index on the dt_first_purchase column my only option - and isn't this undesirable?

您当然可以在 dt_first_purchase 上尝试二级索引(并且按范围查询也需要使用 ALLOW FILTERING 指令)。对于性能(尤其是大型集群)，我不建议这样做。

但首先，了解 Cassandra 是围绕特定数据分区上的特定键返回数据而设计的。这意味着按日期范围查询数据的最佳方法是首先按对模型有意义的键对数据进行分区。例如，如果您有一个如下定义的主键:

PRIMARY KEY ((shop_id), dt_first_purchase, shopper_id)

基本上，哪家商店 (shop_id) 记录了特定购物者 (shopper_id) 的首次购买 (dt_first_purchase) 的记录

根据商店 (shop_id) 对数据进行分区后，您可以查询新购物者的首次购买特定 shop_id，如下所示:

aploetz@cqlsh:stackoverflow> SELECT * 
FROM first_purchase_by_shopper_date 
WHERE shop_id=ce1089f6-c613-4d5b-a975-5dfd677b46f9 
AND dt_first_purchase >= '2014-01-01 00:00:00' 
AND dt_first_purchase < '2014-04-01 00:00:00';

 shop_id                              | dt_first_purchase        | shopper_id                           | value
--------------------------------------+--------------------------+--------------------------------------+-------
 ce1089f6-c613-4d5b-a975-5dfd677b46f9 | 2014-02-12 18:33:22-0600 | a7480417-aaf8-42b1-85dd-5d9a4a30c204 | shopper1
 ce1089f6-c613-4d5b-a975-5dfd677b46f9 | 2014-03-13 11:33:22-0500 | 07db2b71-2dc7-421d-bf73-82a5f6c55f89 | shopper2

(2 rows)

此外，您还可以计算特定商店和日期范围内的首次购买(新购物者)数量，如下所示:

aploetz@cqlsh:stackoverflow> SELECT COUNT(*) FROM first_purchase_by_shopper_date
WHERE shop_id=ce1089f6-c613-4d5b-a975-5dfd677b46f9 
AND dt_first_purchase >= '2014-01-01 00:00:00' 
AND dt_first_purchase < '2014-04-01 00:00:00';

 count
-------
     2

(1 rows)

请注意，此特定示例可能不适用于您的用例。了解它的本质:展示 Cassandra 中分区和查询的工作原理。

有关更多信息，请查看 Patrick McFadin 关于 Getting Started With Time Series Data Modeling 的文章.他讨论了解决与您的用例类似的用例的方法。