ClickHouse物化视图生成太慢 [英] ClickHouse Materialized View generating too slow

问题描述

我们将原始事件收集到 ClickHouse 表中.表结构:

We collect raw events into a ClickHouse table. Table structure:

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) 行.

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

We aim to show our users some aggregate statistics for those events, thus we created a materialized view:

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;

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

If we run the select query separately, it takes around 1 second to generate a response for a single owner_id. But creating a materialized view for the same select takes too much time. After executing create materialized view query it generated just 200 records and it took it ~10 minutes. So it looks like it will take days to completely build a view for the current 120M records table.

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

What am I missing? Maybe there are some tricks for order/group clauses to make it run faster? At the moment it's much easier for me to just run select+group query instead of using a materialized view.

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

Additional question: is there a way to check the progress for materialized view building for an existing table?

推荐答案

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/a72614fbe6d23eb9c2f6a6bce

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

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

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

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

这篇关于ClickHouse物化视图生成太慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！