时间戳

首页 > 解决方案 > ClickHouse物化视图生成太慢

performance - ClickHouse物化视图生成太慢

问题描述

我们将原始事件收集到 ClickHouse 表中。表结构：

CREATE TABLE IF NOT EXISTS raw_events
(
    owner_id                  UInt32,
    user_id                   UInt32,
    event_datetime            DateTime,
    event_type_id             UInt8,
    unique_id                 FixedString(18),
    data                      String,
    attr_1                    UInt32,
    attr_2                    UInt32
)
    engine = MergeTree PARTITION BY toYYYYMMDD(event_datetime)
    ORDER BY (owner_id, user_id, event_type_id, event_datetime);

目前raw_events表包含120000000 (120M) 行。

我们的目标是向我们的用户展示这些事件的一些汇总统计信息，因此我们创建了一个物化视图：

CREATE MATERIALIZED VIEW test
ENGINE = AggregatingMergeTree() PARTITION BY (date)
ORDER BY (owner_id, user_id, date)
AS SELECT
       toYYYYMMDD(event_datetime) as date,
       owner_id,
       user_id,
       multiIf(
                   event_type_id == 1, 10,
                   event_type_id == 2, 20,
                   event_type_id == 3, 30,
                   event_type_id == 4, 40,
                   event_type_id == 32, 50,
                   event_type_id >= 64, 60,
                   0
           ) as status,
       attr_1,
       attr_2,
       COUNT() as count,
       COUNT(DISTINCT unique_id) as unique_count
   FROM raw_events
   GROUP BY owner_id, user_id, date, status, attr_1, attr_2
   ORDER BY owner_id, user_id, date;

如果我们单独运行选择查询，生成单个 owner_id 的响应大约需要 1 秒。但是为同一个选择创建一个物化视图需要太多时间。在执行创建物化视图查询后，它只生成了200 条记录，并且花了大约 10 分钟。因此，为当前的 120M 记录表完全构建视图似乎需要几天时间。

我错过了什么？也许订单/组子句有一些技巧可以让它运行得更快？目前，我只运行 select+group 查询而不是使用物化视图要容易得多。

附加问题：有没有办法检查现有表的物化视图构建进度？

标签： performanceaggregatequery-optimizationclickhousematerialized-views

解决方案

  1. AggregatingMergeTree --ORDER BY 用作折叠规则。
  2. AggregatingMergeTree -- 必须与 AggregateFunctions + -State + -Merge 组合器一起使用
  3. ORDER BY -- 过多
CREATE MATERIALIZED VIEW test
ENGINE = AggregatingMergeTree() PARTITION BY (date)
ORDER BY (owner_id, user_id, date, status, attr_1, attr_2)         ---<<<<<<<<<<<<<<<<<<<<-----------
AS SELECT
       toYYYYMMDD(event_datetime) as date,
       owner_id,
       user_id,
       multiIf(
                   event_type_id == 1, 10,
                   event_type_id == 2, 20,
                   event_type_id == 3, 30,
                   event_type_id == 4, 40,
                   event_type_id == 32, 50,
                   event_type_id >= 64, 60,
                   0
           ) as status,
       attr_1,
       attr_2,
       countState() as count,                                  ---<<<<<<<<<<<<<<<<<<<<-----------
       uniqState(unique_id) as unique_count        ---<<<<<<<<<<<<<<<<<<<<-----------
   FROM raw_events
   GROUP BY owner_id, user_id, date, status, attr_1, attr_2

https://gist.github.com/den-crane/a72614fbe6d23eb9c2f1bce40c66893f

https://gist.github.com/den-crane/49ce2ae3a688651b9c2dd85ee592cb15

https://den-crane.github.io/Everything_you_should_know_about_materialized_views_commented.pdf

推荐阅读