ClickHouse物化视图

ClickHouse的物化视图是一种查询结果的持久化存在是为了带来查询效率的提升。它就是一张逻辑表是一张时刻在预计算的表创建的过程是用一个特殊引擎加上后来 as select就是 create 一个 table as select 的写法普通视图普通视图不保存数据保存的仅是查询语句查询的时候还是从原表读取数据可以将普通视图理解为是个子查询。物化视图物化视图是把查询的结果根据相应的引擎存入到了磁盘或内存中对数据重新进行了组织你可以理解物化视图是完全的一张新表优点查询速度快要是把物化视图这些规则全部写好它比原数据查询快了很多总的行数少了因为都预计算好了。缺点本质是一个流式数据的使用场景是累加式的技术所以要用历史数据做去重、去核这样的分析在物化视图里面是不太好用的如果一张表加了好多物化视图在写这张表的时候就会消耗很多机器的资源比如数据带宽占满、存储一下子增加了很多CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE engine] [POPULATE] AS SELECT ...使用 create 创建一个物化视图会创建一个隐藏的目标表来保存视图数据也可以 TO 表名保存到 一 张显式的表。没有加 TO 表名表名默认就是 .inner.物化视图名创建 pm 性能数据表性能表以 start_time 和 ne_name 为组合主键day_id 为分区ReplacingMergeTree 为合并引擎pm 性能数据表CREATE TABLE test_pm( start_time DateTime comment 数据时间, name String comment 名称, pm String comment pm, day_id String comment 天 ) engineReplacingMergeTree() partition by day_id primary key (start_time,name) order by (start_time,name)cm 配置数据表create table test_cm( insert_time DateTime comment 插入时间, name String comment 名称, cm String comment cm, day_id String comment 天 ) engineReplacingMergeTree() partition by day_id PRIMARY key name order by name数据-- pm 性能数据 INSERT INTO table test_pm values(now(),NE_01,100,2024-01-17); INSERT INTO table test_pm values(now(),NE_02,100,2024-01-17); INSERT INTO table test_pm values(now(),NE_03,100,2024-01-17); INSERT INTO table test_pm values(now(),NE_04,100,2024-01-17); -- 模拟重复数据 INSERT INTO table test_pm values(now(),NE_01,200,2024-01-17); INSERT INTO table test_pm values(now(),NE_01,500,2024-01-17); select * from test_pm -- cm 配置数据 INSERT INTO table test_cm values(now(),NE_01,10,2024-01-17); INSERT INTO table test_cm values(now(),NE_02,10,2024-01-17); INSERT INTO table test_cm values(now(),NE_03,10,2024-01-17); INSERT INTO table test_cm values(now(),NE_04,10,2024-01-17);通过 ne_name 将 cm 配置表中的数据关联到 pm性能表中达到扩充表字段目的。特别注意不建议添加 populate 关键字原因在最下面create materialized view test_view engineReplacingMergeTree() partition by day_id primary key (start_time,name) order by (start_time,name) populate as select pm.start_time, pm.name, pm.pm, cm.cm, pm.day_id from test_pm pm left join test_cm cm on pm.namecm.name select * from test_view1、populate 参数不建议添加这个参数会导致历史数据的计算如果不添加此参数则物化视图创建之后只对新增数据进行物化视图的计算。2、不使用 populate 又想刷新历史数据可以使用 insert into Table2(field1,field2,field3,...) select value1,value2,... from Table1 方式进行数据刷入。以下举例说明-- 将 2024年以来的数据重新刷入到物化视图中物化视图使用了 ReplacingMergeTree 引擎可以根据主键合并将最新的结果数据更新进去 insert into default.test_mv select a.insert_time, a.start_time, a.ne_name, a.pm, b.cm, a.day_id from default.test_pm a left join test_cm b on a.ne_name b.ne_name where a.start_time 202401010000