clickhouse21.7.11.3 物化视图

作者：马育民 • • 阅读：10109

视图

视图是由若干个字段以及若干条记录构成(也常称为虚表)，它与表有很多相似的地方，特点如下：

  • 视图保存的是一条 select 语句，并没有保存真正的数据。
  • 视图中的数据源来自于原表

物化视图

物化视图也是视图，但包括 查询结果数据 ，是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。

原理

空间换时间，物化视图这些规则已经全部写好，条件过滤后的数据已经存储在了本地表中，所以它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点

  • 本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析，在物化视图里面是不太好用的。

  • 如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

语法

CREATE [MATERIALIZED] VIEW [IF NOT EXISTS] [db.]table_name [TO[db.]name] [ENGINE = engine] [POPULATE] AS SELECT …

解释：

  • 创建一个 隐藏的目标表 来保存视图数据，表名默认就是 .inner.物化视图名。
  • 可以通过 TO 表名 指定表名，保存到一张 显式的表。

注意:

  • 必须指定物化视图的 engine 用于数据存储
  • TO [db].[table] 语法的时候，不得使用 POPULATE 关键字
  • 查询语句(select）可以包含下面的子句：DISTINCT, GROUP BY, ORDER BY, LIMIT…
  • 物化视图的 alter 操作有些限制，操作起来不大方便。
  • 物化视图是种特殊的数据表，可以用 show tables 查看

例子

建表

其中id为用户编号, sku_id为商品编号

create table t_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 t_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 v_order_detail
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 t_order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;

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

显示所有表

执行下面语句：

show tables

执行结果如下：

name
.inner_id.8964eff3-bd51-407d-8964-eff3bd51007d

出现了一个 .inner_id.8964eff3-bd51-407d-8964-eff3bd51007d 的表，这也就是持久化物化视图数据的表。

查询视图

select * FROM  v_order_detail

没有记录，原因： 建视图时没有 POPULATE 关键字，默认可以在创建物化视图时将 select 后的字段填充进去，否则物化视图时不会导入数据进来的

向原表插入数据

insert into t_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');

查询视图

select * FROM  v_order_detail

可以看到 where 过滤后的数据

查询物化视图的数据表

select * from `.inner_id.8964eff3-bd51-407d-8964-eff3bd51007d

也可以查找出来相同的数据，因为此表的结果也就是物化视图过滤后的结果。

POPULATE

创建视图时，使用 POPULATE ，先建视图，然后自动将数据导入了进来

注意：官方并不推荐使用 POPULATE，在创建视图、导入数据时，原始表可能插入新数据，而新数据不会导入到视图中，造成数据丢失

例子

CREATE MATERIALIZED VIEW v_order_detail2
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 t_order_detail
WHERE order_date > '2021-08-14'
GROUP BY id,order_date;

参考：
https://cloud.tencent.com/developer/article/1988528

原文出处：/show_1IX3RS1WH4pB.html

吉ICP备15003043号