「ClickHouse系列」ClickHouse中的物化视图详解

大数据真好玩

发布于 2022-04-27 09:16:05

10.3K0

发布于 2022-04-27 09:16:05

文章被收录于专栏：暴走大数据暴走大数据

在讲物化视图前,我们先来回顾一下什么是视图：

视图是由若干个字段以及若干条记录构成(也常称为虚标)，它与表有很多相似的地方，视图中的数据源来自于原表,视图本身不存储数据,视图它保存的仅仅是一条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.物化视图名。

物化视图中需要注意的几点:

必须指定物化视图的engine 用于数据存储
TO [db].[table]语法的时候，不得使用POPULATE。
查询语句(select）可以包含下面的子句：DISTINCT, GROUP BY, ORDER BY, LIMIT…
物化视图的alter操作有些限制，操作起来不大方便。
物化视图是种特殊的数据表，可以用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条件的基础上它将我们的数据已经全部导入了进来。