clickhouse常见物化视图用法和注意事项

物化视图作用

物化视图主要是使用SummingMergeTree引擎和AggregativeMergeTree引擎创建的，有两种模式：一种是隐式的创建内部表（.inner），另一种是显式的创建支持表,其目的都是为了预先聚合数据，通过对明细表数据进行聚合得到一个物化视图后，后续如果要对聚合的数据进行查询的话速度会快很多，因为聚合后的数据表数据量非常非常小.

物化视图创建

源表定义如下：每个城市每个时间点的温度

CREATE TABLE city_weather(
  time DateTime,
  cityId UInt32,
  temperature Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(time)
ORDER BY (cityId, time)

常见使用模式1:

CREATE MATERIALIZED VIEW weather_daily_mv
ENGINE = AggregativeMergeTree
PARTITION BY toYYYYMM(day) ORDER BY (city, time)
POPULATE  ---这个关键字表明创建视图时初始化视图数据
AS SELECT
  toStartOfDay(time) AS day,
  cityId,
  minState(temperature) as min_temperature,
  maxState(temperature) as max_temperature,
  avgState(temperature) as avg_temperature,
FROM city_weather
GROUP BY cityId, day

该模式在创建视图的时候初始化视图数据，缺点是初始化视图数据期间，新插入的数据被忽略

常见使用模式2:

显式定义支持表
CREATE TABLE weather_daily (
  day DateTime,
  cityId UInt32,
  max_temperature_state AggregateFunction(max, Float32),
  min_temperature_state AggregateFunction(min, Float32),
  avg_temperature_state AggregateFunction(avg, Float32)
)
ENGINE = AggregativeMergeTree()
PARTITION BY day
ORDER BY (cityId, day);
CREATE MATERIALIZED VIEW weather_daily_mv
TO weather_daily
AS SELECT
  toStartOfDay(time) AS day,
  cityId,
  minState(temperature) as min_temperature,
  maxState(temperature) as max_temperature,
  avgState(temperature) as avg_temperature,
FROM city_weather
WHERE when >= toDate('2022-01-01 00:00:00') --这里多一个where条件的目的是让新插入的数据直接插入到视图表和支持表中，而之前的数据通过手动加载的方式插入视图和支持表中，这样就可以起到初始化视图和支持表的目的
GROUP BY cityId, day

物化视图原理

物化视图使用注意事项：

一.物化视图虽然是SummingMergeTree引擎和AggregativeMergeTree引擎创建的，但是如之前例子所示创建的物化视图的话，执行如下语句： select cityId,day,minMerge(min_temperature_value) as min_temp from weather_daily_mv where cityId='GZ' and day='20220910' 得到的结果如下：
|cityId|day|min_temp |
|GZ|20220910|28.0|

|cityId|day|min_temp |
|GZ|20220910|27.0|

我们可以看到同一天同一个尝试有两条最小温度的记录，这是因为数据在物化视图中也是分开存在的，所以查询的时候也需要group by才能得到正确的结果:
select cityId,day,minMerge(min_temperature_value) as min_temp from weather_daily_mv where cityId='GZ' and day='20220910 group by cityId,day'
二. 物化视图的插入性能，我们知道插入到物化视图中的数据是经过数据聚合的结果，而且是每次插入到源表后就会对这部分数据进行聚合然后插入到物化视图中的，所以对插入性能敏感的应用需要注意物化视图的插入性能问题