详解clickhouse中的物化视图

在讲物化视图前,我们先来回顾一下什么是视图：
视图是由若干个字段以及若干条记录构成(也常称为虚标)，它与表有很多相似的地方，视图中的数据源来自于原表,视图本身不存储数据,视图它保存的仅仅是一条select语句，并没有保存真正的数据。

那什么是ck中的物化视图呢 :物化视图是包括一个查询结果的数据库对象，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，简单的来理解就是它在普通视图的基础上加上了视图中select后所存储的数据.

CK中物化视图的基本语法:
CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT …

也是create语法，会创建一个隐藏的目标表来保存视图数据。也可以TO 表名，保存到一张显式的表。没有加TO表名，表名默认就是 .inner.物化视图名.

物化视图中需要注意的几点:
1.必须指定物化视图的engine 用于数据存储
2.TO [db].[table]语法的时候，不得使用POPULATE。
3.查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
4.物化视图的alter *** 作有些限制， *** 作起来不大方便。
5.物化视图是种特殊的数据表，可以用show tables 查看

建表语句，其中id为用户编号, sku_id为商品编号

create table order_detail 
(
   id String,
   sku_id  String,
   pay_number Int32,
   pay_amount Int32, 
   order_date Date 
)
ENGINE = MergeTree()
partition by toYYYYMMDD(order_date)
order by (id,sku_id);

插入数据

insert into order_detail values
('001','a',2,20,'2021-08-13'),
('002','a',3,30,'2021-08-16'),
('002','b',2,40,'2021-08-16');

创建物化视图:这里想说明一点就是创建物化视图时一般情况下引擎可以选用SummingMergeTree，因为该引擎支持以主键分组，对数值型指标做自动累加。每当表的parts做后台merge的时候，主键相同的所有记录会被加和合并成一行记录，可以大大节省空间。

CREATE MATERIALIZED VIEW order_mv1
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(order_date) ORDER BY (id,order_date)
AS SELECT
id,
order_date,
sum(pay_number) as number,
sum(pay_amount) as amount
FROM order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;

可见，物化视图与表一样，也可以指定表引擎、分区键、主键和表设置参数

这是我们在当前数据库中show tables看一下,发现在视图order_mv1创建过程中出现了一个.inner_id.54ccb54b-83aa-49f4-94cc-b54b83aae9f4的表,这也就是持久化物化视图数据的表。

这里我们顺便也全表扫描一下order_mv1这个视图,发现里面什么数据也没有,这时你可能会想WHERE order_date > '2021-08-14’虽然过滤出原始表的1条数据为什么剩下的两条显示不出来呢,其实是因为我们在创建order_mv1时没有添加POPULATE参数,这个参数默认可以在创建物化视图时将select 后的字段填充进去，否则物化视图时不会导入数据进来的,我们将在后续详解POPULATE.

这时我们在原表中再次插入一部分数据.

insert into order_detail values
('003','b',2,40,'2021-08-12'),
('003','a',2,20,'2021-08-16'),
('003','c',1,30,'2021-08-16'),
('004','a',2,20,'2021-08-16'),
('004','d',5,200,'2021-08-16'),
('005','a',5,50,'2021-08-17'),
('006','d',3,120,'2021-08-18');

此时查看order_mv1视图可以看到where过滤后的数据

这时我们查找下持久化物化视图数据的表.inner_id.54ccb54b-83aa-49f4-94cc-b54b83aae9f4也可以查找出来相同的数据,因为此表的结果也就是物化视图order_mv1过滤后的结果。

聚合 *** 作: 查找2021-8-17号以后的用户ID和用户所消费的总金额以及对应的日期

select id,order_date,sum(amount) from `.inner_id.54ccb54b-83aa-49f4-94cc-b54b83aae9f4` where order_date >='2021-08-17' 
group by id,order_date;

其实从.inner.xxx表来看当原始表的数据更新时,他的结果也会更新,物化视图是指通过SQL语句从一张表或者多张表查询出来的数据集做持久化存储,它通过SQL更新可以通过自带的触发器同步数据到物化视图中。所以，广义上理解物化视图可以看作是 ‘快照’ 。

上述还留有一个POPULATE没有说明,在其他条件不变的情况下我们在创建order_mv1的基础上增加POPULATE并命名为order_mv2看看有什么区别.

CREATE MATERIALIZED VIEW order_mv2
ENGINE=SummingMergeTree
PARTITION BY toYYYYMMDD(order_date) ORDER BY (id,order_date) 
POPULATE  AS SELECT
id,
order_date,
sum(pay_number) as number,
sum(pay_amount) as amount
FROM order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;

我们此时全表扫描一下order_mv2表看下,可以看到在where条件的基础上它将我们的数据已经全部导入了进来。

但是这里不得不说就是官方并不推荐这种加POPULATE 的做法,原因就是我们在同步数据的时候原始表的数据可能存在被插入的情况，这样做会造成数据的丢失。

总结：
物化视图查询如此之快？
思路也是空间换时间,因为物化视图这些规则已经全部写好并且条件所过滤后的数据已经存储在了本地表中，所以它比原数据查询快了很多，总的行数少了，因为都预计算好了。
缺点：它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。