Clickhouse 创建和删除视图的方法

Clickhouse 通过create MATERIALIZED VIEW 来创建物化视图,通过drop view 来删除视图。本文给出一些例子。

版本

通过如下命令,查看Clickhouse的版本号

localhost :) select version();

SELECT version()

Query id: b664382b-e7d2-4f65-8965-59881fbd7ecf

┌─version()─┐
│ 21.4.5.46 │
└───────────┘

1 rows in set. Elapsed: 0.019 sec.

创建视图

Clickhouse 通过create MATERIALIZED VIEW 来创建物化视图, 其支持各种标准的排序,分区等参数。

CREATE MATERIALIZED VIEW IF NOT EXISTS testdb.mv_t2_collectdate
ENGINE = SummingMergeTree
PARTITION BY collectdate
ORDER BY collectdate AS
SELECT
    collectdate,
    count(*) AS visit
FROM testdb.t2
GROUP BY collectdate

删除视图

Clickhouse 通过drop view 来删除视图。

localhost :) drop view testdb.mv_t2_collectdate;

DROP VIEW testdb.mv_t2_collectdate

Query id: ee1bc0a5-ba9f-4b6d-b139-e1ace4b7b4fb

Ok.

0 rows in set. Elapsed: 0.001 sec.