clickhouse物化视图详解

clickhouse物化视图有常见的两种模式

假设源表定义如下：

create table source_table(id int32, age int32, create_time DateTime) engine=mergeTree() partition by toYYMMDD(create_time) order by id 

模式一 隐式创建目标表:

CREATE MATERIALIZED VIEW hide_agg_mv_view(
id int32, 
min_age AggregateFunction(min, int32),
max_age AggregateFunction(max,int32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY tuple() ORDER BY (id)
POPULATE
AS SELECT
id,
minState(age) as min_age,
maxState(age) as max_age
FROM source_table
GROUP BY id

模式一会隐式的创建目标表，使用show table命令可以看到和物化视图相关的两张表：
a. 物化视图定义表：hide_agg_mv_view
b. 实际数据存放表：inner.hide_agg_mv_view
使用这种模式创建的物化视图的优点是支持populate初始化操作，也就是可以把源表数据初始化到物化视图中，不过这个初始化的过程中，源表中所有的数据insert操作都会丢失，生产环境中使用务必注意。

模式二： 显示定义目标表

定义目标数据表

CREATE TABLE target_data_table (
  id int32,
  min_age AggregateFunction(min, int32),
  max_age AggregateFunction(max,int32)
)
ENGINE = AggregatingMergeTree()
PARTITION BY tuple()
ORDER BY (id)

物化视图定义表

CREATE MATERIALIZED VIEW explicit_agg_mv_view
TO target_data_table 
AS SELECT
id,
minState(age) as min_age,
maxState(age) as max_age
FROM source_table
WHERE create_time >= toDate('2022-01-01')
GROUP BY id

这样模式二和物化视图相关的就有两张表:
a. 视图定义表: explicit_agg_mv_view
b. 实际数据存放目标表：target_data_table
**模式二显式定义目标表的缺点是不能够使用populate初始化视图的数据，所以这种情况下，我们需要手动加载视图创建之前的源表中的数据，这就是我们这里使用where条件创建视图的原因：我们本意是通过where条件把源表中新增的记录都包含进来，而对于不满足where条件的源表中的旧的记录我们可以这样导入：

INSERT INTO target_data_table 
SELECT
id,
minState(age) as min_age,
maxState(age) as max_age
FROM source_table
WHERE create_time < toDate('2022-01-01')
GROUP BY id

这样就可以把源表中之前的记录也插入到了目标表中了,当然如果创建物化视图的过程中你可以先停止原表的写入的话，也可以不需要where条件，直接导入源表的所有数据到目标表后再把原表写入放开，这样也是可以的**

1. 模式二相对于模式一还有的一个优势是可以修改视图的定义，drop掉物化视图然后再创建一个新的视图，这个过程中目标数据表的数据依然存在，而对于模式一来说，drop掉物化视图会顺带着删除目标数据表，不利于修改视图的模式定义
2. 不管是模式一还是模式二，当进行数据查询时，不论无论是查询视图表还是查询目标数据表,sql语句的聚合操作依然要存在，因为clickhouse的合并操作只会在适当的时候发生，而且只是分区内的聚合，所以sql聚合仍然需要如下所示:

SELECT 
    id, 
    maxMerge(min_age) AS minAge, 
    minMerge(max_age) AS maxAge
FROM target_data_table 
GROUP BY id

参考文献:
https://www.cnblogs.com/gentlescholar/p/15043393.html