clickhouse materialized view 物化视图
我们知道数据块中的view只是一个逻辑概念,为了便于写查询语句,把底层各个表的结构和字段隐藏,创建一个新的虚拟表,类似于查询语句,在这个结果上再编写新的语句。
clickhouse提供了一个新的功能,materialized view,可以把一个view的数据存放到磁盘,实例化,而不仅仅是虚拟的逻辑。
这个的好处就是,做报表,迁移数据等。
比如有一个记录用户登录日志的表,如果想统计用户每天登录几次,可以创建一个materialized view,把统计数据存放到另一个表。
https://clickhouse.com/blog/using-materialized-views-in-clickhouse
就拿官方的示例举例
创建保存数据的表
CREATE TABLE wikistat
(
`time` DateTime CODEC(Delta(4), ZSTD(1)),
`project` LowCardinality(String),
`subproject` LowCardinality(String),
`path` String,
`hits` UInt64
)
ENGINE = MergeTree
ORDER BY (path, time);
根据日期查询数据
SELECT
project,
sum(hits) AS h
FROM wikistat
WHERE date(time) = '2015-05-01'
GROUP BY project
ORDER BY h DESC
LIMIT 10
这样每次都会计算,如果使用频率比较高,会浪费性能,一般我们都会自己定时产生报表。clickhouse提供了这个功能。
创建一个新的表(就是平常的用来存放报表的数据库表)
CREATE TABLE wikistat_top_projects
(
`date` Date,
`project` LowCardinality(String),
`hits` UInt32
)
ENGINE = SummingMergeTree
ORDER BY (date, project);
SummingMergeTree
引擎会把不是主键,或者不再ORDER BY中的字段合并,也就是所有ORDER BY字段相同的数据,会合并累加。比如有两条数据,date和project都一样,那么就会合并成一条,并且把hits累加
创建一个物化视图,并且把物化视图的数据写入到一个表
CREATE MATERIALIZED VIEW wikistat_top_projects_mv TO wikistat_top_projects AS
SELECT
date(time) AS date,
project,
sum(hits) AS hits
FROM wikistat
GROUP BY
date,
project;
date(time)
把time从datetime转成date类型,也就是按照天计算。
物化视图创建完成后,每次wikistat插入数据,都会触发该视图的SELECT语句,并且把结果插入到wikistat_top_projects中。wikistat_top_projects又会根据字段做聚合计算,就达到做报表的功能。
除了求和的引擎SummingMergeTree,clickhouse还有用于求平均数等引擎,可以做其他的报表。