ClickHouse Projection设计

Projection仅在MRS 3.2.0及以上的版本集群中支持。

projection定义

CREATE TABLE test_projection_table(
    level String,
    type String,
    name String,
    city String,
    time DateTime64,
    PROJECTION projection_1(
        SELECT level,
            count()
        GROUP BY level
    ),
    PROJECTION projection_2(
        SELECT type,
            count()
        GROUP BY type
    )
) ENGINE = MergeTree()
ORDER BY (name, level, type)

通过表属性修改方式创建projection

在创建好projection后还可以对projection进行修改，具体语句如下：

ALTER TABLE test_projection_table
ADD PROJECTION projection_3(
        SELECT type,
            level
        GROUP BY type,
            level
)

Projection的使用

如下SQL查询的时候会走表达式：
SELECT type, count() FROM test_projection_table WHERE type = 'A' GROUP BY type;
而如下SQL不会走projection，因为city不在projection的定义中。
SELECT city, count() FROM test_projection_table WHERE type = 'A' GROUP BY city;
具体可以通过explain查看执行计划，如果出现ReadFromStorage (MergeTree(with projection)) ，表示命中projection。