Clickhouse物化视图详解

物化视图

1 概述

物化视图是对查询结果的持久化，能够提升查询效率，查询起来就像查询一张表一样。

1.1 物化视图与普通视图的区别

普通视图保存的不是数据，保存的是查询语句。查询的时候还是从原表读取数据。物化视图则是把查询的结果根据响应的引擎存入到了磁盘或内存中，对数重新进行了组织，可以理解物化视图是完全的一张新表。

1.2 优缺点

优点：速度快，写好规则后，会比原数据查询快了很多。
缺点：物化视图的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去核这样的分析不适合物化视图，如果一张表有多个物化视图，在写入这张表的时候就会消耗很多机器的资源，比如数据贷款沾满，存储增加很多。

1.3 基本语法

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

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

1.4 创建物化视图的限制

1. 必须制定物化视图的engine用于数据存储。
2. TO[DB].[TABLE]语法的时候，不得使用POPULATE。
3. 查询语句可以包含下面的子句：DISTINCT, GROUP BY, ORDER BY, LIMIT…
4. 物化视图的alter操作有些限制，操作起来不太方便。
5. 若物化视图使用了TO [db.]name 子语句，则可以将目标表的视图 卸载DETACH 再装载 ATTACH

1.5 物化视图的数据更新

1. 物化视图创建好之后，若原表被写入新数据则物化视图也会同步更新。
2. POPULATE关键字决定了物化视图的更新策略：
   1. 托幼POPULATE则在创建视图的过程会将原表已经存在的是护具一并导入，类似于 create table … as …
   2. 若无POPULATE则物化视图在创建之后没有数据，只会在创建只有同步之后写入原表的数据
   3. clickhouse官方不推荐使用POPULATE，因为在创建物化视图的过程中同事写入的数据不能被插入物化视图
3. 物化视图不支持同步删除，若原表的数据不存在则物化视图的数据仍然保留。
4. 物化视图是一种特殊的数据表，可以用show tables 查看
5. 物化视图数据的删除
6. 物化视图的删除

2 案例实操

对于一些确定的数据模型，可将统计指标通过物化视图的方式进行构建，这样可避免查询时重复计算的过程，物化视图会在有新数据插入式进行更新。

2.1 准备测试用表和数据

-- 建表语句
CREATE TABLE hits_test
(
 EventDate Date, 
 CounterID UInt32, 
 UserID UInt64, 
 URL String, 
 Income UInt8
)
ENGINE = MergeTree()
PARTITION BY toYYYYMM(EventDate)
ORDER BY (CounterID, EventDate, intHash32(UserID))
SAMPLE BY intHash32(UserID)
SETTINGS index_granularity = 8192

-- 导入数据
INSERT INTO hits_test 
 SELECT 
 EventDate,
 CounterID,
 UserID,
 URL,
 Income 
FROM hits_v1 
limit 10000;

2.2 创建物化视图

-- 建表语句
CREATE MATERIALIZED VIEW hits_mv 
ENGINE=SummingMergeTree
PARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) 
AS SELECT
UserID,
EventDate,
count(URL) as ClickCount,
sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate >= '2014-03-20'  -- 设置更新点,该时间点之前的数据可以另外通过insert into select …… 的方式进行插入
GROUP BY UserID,EventDate;

-- 或者可以用下列语法，表 A 可以是一张 mergetree 表
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A
AS SELECT FROM 表 B;
-- 不建议添加 populate 关键字进行全量更新

3.3 导入增量数据

-- 导入增量数据
INSERT INTO hits_test 
SELECT 
 EventDate,
 CounterID,
 UserID,
 URL,
 Income 
FROM hits_v1 
WHERE EventDate >= '2014-03-23' 
limit 10;

-- 查询物化视图
SELECT * FROM hits_mv;

3.4 导入历史数据

-- 导入增量数据
INSERT INTO hits_mv
SELECT
 UserID,
 EventDate,
 count(URL) as ClickCount,
 sum(Income) AS IncomeSum
FROM hits_test
WHERE EventDate = '2014-03-20'
GROUP BY UserID,EventDate
-- 查询物化视图
SELECT * FROM hits_mv;