文章目录
- 物化视图
- 1 概述
- 1.1 物化视图与普通视图的区别
- 1.2 优缺点
- 1.3 基本语法
- 1.4 创建物化视图的限制
- 1.5 物化视图的数据更新
- 2 案例实操
- 2.1 准备测试用表和数据
- 2.2 创建物化视图
- 3.3 导入增量数据
- 3.4 导入历史数据
物化视图
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 创建物化视图的限制
- 必须制定物化视图的engine用于数据存储。
- TO[DB].[TABLE]语法的时候,不得使用POPULATE。
- 查询语句可以包含下面的子句:DISTINCT, GROUP BY, ORDER BY, LIMIT…
- 物化视图的alter操作有些限制,操作起来不太方便。
- 若物化视图使用了TO [db.]name 子语句,则可以将目标表的视图 卸载DETACH 再装载 ATTACH
1.5 物化视图的数据更新
- 物化视图创建好之后,若原表被写入新数据则物化视图也会同步更新。
- POPULATE关键字决定了物化视图的更新策略:
- 托幼POPULATE则在创建视图的过程会将原表已经存在的是护具一并导入,类似于 create table … as …
- 若无POPULATE则物化视图在创建之后没有数据,只会在创建只有同步之后写入原表的数据
- clickhouse官方不推荐使用POPULATE,因为在创建物化视图的过程中同事写入的数据不能被插入物化视图
- 物化视图不支持同步删除,若原表的数据不存在则物化视图的数据仍然保留。
- 物化视图是一种特殊的数据表,可以用show tables 查看
- 物化视图数据的删除
- 物化视图的删除
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_testSELECTEventDate, CounterID, UserID, URL, Income FROM hits_v1 limit 10000;
2.2 创建物化视图
-- 建表语句CREATE MATERIALIZED VIEW hits_mv ENGINE=SummingMergeTreePARTITION BY toYYYYMM(EventDate) ORDER BY (EventDate, intHash32(UserID)) AS SELECTUserID,EventDate,count(URL) as ClickCount,sum(Income) AS IncomeSumFROM hits_testWHERE EventDate >= '2014-03-20'-- 设置更新点,该时间点之前的数据可以另外通过insert into select …… 的方式进行插入GROUP BY UserID,EventDate;-- 或者可以用下列语法,表 A 可以是一张 mergetree 表CREATE MATERIALIZED VIEW 物化视图名 TO 表 AAS SELECT FROM 表 B;-- 不建议添加 populate 关键字进行全量更新
3.3 导入增量数据
-- 导入增量数据INSERT INTO hits_test SELECTEventDate, CounterID, UserID, URL, Income FROM hits_v1 WHERE EventDate >= '2014-03-23' limit 10;-- 查询物化视图SELECT * FROM hits_mv;
3.4 导入历史数据
-- 导入增量数据INSERT INTO hits_mvSELECT UserID, EventDate, count(URL) as ClickCount, sum(Income) AS IncomeSumFROM hits_testWHERE EventDate = '2014-03-20'GROUP BY UserID,EventDate-- 查询物化视图SELECT * FROM hits_mv;