Clickhouse Projection

背景

Clickhouse一个视图本质还是表，只支持一种order By，不然要维护太多的视图。

物化视图能力有限。

在设计聚合功能时，考虑使用AggregatingMergeTree表引擎，现在有了projections，打算尝试使用一下

操作

ADD PROJECTION​

ALTER TABLE [db.]name [ON CLUSTER cluster] ADD PROJECTION [IF NOT EXISTS] name ( SELECT <COLUMN LIST EXPR> [GROUP BY] [ORDER BY] ) - Adds projection description to tables metadata.

DROP PROJECTION​

ALTER TABLE [db.]name [ON CLUSTER cluster] DROP PROJECTION [IF EXISTS] name - Removes projection description from tables metadata and deletes projection files from disk. Implemented as a mutation.

MATERIALIZE PROJECTION​

ALTER TABLE [db.]table [ON CLUSTER cluster] MATERIALIZE PROJECTION [IF EXISTS] name [IN PARTITION partition_name] - The query rebuilds the projection name in the partition partition_name. Implemented as a mutation.

CLEAR PROJECTION​

ALTER TABLE [db.]table [ON CLUSTER cluster] CLEAR PROJECTION [IF EXISTS] name [IN PARTITION partition_name] - Deletes projection files from disk without removing description. Implemented as a mutation.

The commands ADD, DROP and CLEAR are lightweight in a sense that they only change metadata or remove files.

查看表的投影配置

DDL中查看

可以查询system.parts。里面有个projections的数组。