CLickhouse 物化视图--干货记录(亲验证)

最新推荐文章于 2025-05-09 22:00:00 发布

原创

最新推荐文章于 2025-05-09 22:00:00 发布 · 2.7k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #大数据 #clickhouse #物化视图

文章详细介绍了ClickHouse中的物化视图与普通视图的区别，包括其存储方式、查询速度、优缺点。物化视图通过预先计算和存储结果提高查询效率，但可能导致存储占用增加。创建物化视图有两种方式，一种指定存储引擎，另一种指定目标表。文中通过实例展示了如何创建和使用物化视图，并探讨了其在数据更新和优化时的行为，特别提到了SummingMergeTree和AggregatingMergeTree在聚合场景的应用。

1、普通视图VS物化视图

普通视图不保存数据，保存的仅仅是查询语句，查询的时候还是从原表读取数据，可以将普通视图理解为是个子查询。--中看不中用

物化视图则是把查询的结果根据相应的引擎存入到了磁盘或内存中，对数据重新进行了组织，你可以理解物化视图是完全的一张新表。是一种查询结果的持久化

2、物化视图的特点、优缺点

特点：允许显式目标表（创建视图两种方式的一种to db.table）、累加式、写入触发器（预聚合触发器）、持久化（空间换时间）、join左表触发、源表数据的改变不会影响物化视图(如update, delete, drop partition)、空间换时间

优点：查询速度快，要是把物化视图这些规则全部写好，它比原数据查询快了很多，总的行数少了，因为都预计算好了。

缺点：

它的本质是一个流式数据的使用场景，是累加式的技术，所以要用历史数据做去重、去更新这样的分析，在物化视图里面是不太好用的。在某些场景的使用也是有限的。（选择规划好使用场景）

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

3、两种创建方式、语句

推荐使用第二种间接创建方式（to db.table）：需要手动先建立目标表、可控制TTL；否则不支持嵌套视图 (多个物化视图继续聚合一个新的视图)

物化视图和普通视图最大的区别是物化视图实际存储了一份数据。用户查询的时候和表没有区别，更像是一张时刻在预计算的表。在创建物化视图的时候也需要定义存储引擎。

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

不指定 TO [db].[table]的时候，必须要指定 ENGINE

指定TO [db].[table] 目标表的时候，不能使用POPULATE关键字

POPULATE关键字有什么用？

若指定了POPULATE关键字，会把表中现有数据存储到视图中，否则只会写入创建视图之后的数据。

然而如果对数据的精确度要求比较高，不建议使用POPULATE关键字，因为在创建视图过程中插入表中的数据并不会写入视图，会造成数据的丢失。

POPULATE:创建视图的时候会把历史数据全部物化一次。官方并不推荐这种加POPULATE 的做法,原因就是我们在同步数据的时候原始表的数据可能存在被插入的情况，这样做会造成数据的丢失

创建物化视图有两种方式

3.1直接创建

即创建物化视图时不带TO [db].[table]，且必须指定ENGINE用于存储数据的表引擎，和建表类似，这种方法ClickHouse会创建一个隐藏的目标表（私有表）来保存视图数据，可以通过SHOW TABLES查看(inner_id开头的表)；删除视图，私有表会消失；

3.2间接创建

即使用TO [db].[table]创建物化视图，[db].[table]必须是一张已经存在的表，用来保存视图数据，此时创建物化视图相当于在表上面附加了一个物化视图。需要注意，间接创建不能使用POPULATE关键字。

4、简单使用-熟悉建表数据

ClickHouse源码阅读计划（三）物化视图的概念、场景、用法和源码实现-Nosql-About云开发-梭伦科技

如果不使用物化视图，需要每次运行查询以交互方式统计结果。但是对于大型表，提前计算它们更快，更节省资源

这里主要是熟悉物化视图的两种创建方式、感受一下不同

4.1使用指定ENGINE方法创建

基础数据准备：

--模拟业务表 用户在某个时间点的下载量
CREATE TABLE IF NOT EXISTS download (
when DateTime,
userid UInt32,
bytes Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (userid, when);
--插入数据
INSERT INTO download
SELECT
now() + number * 60 as when,
25,
rand() % 100000000
FROM system.numbers
LIMIT 5000;
--
SELECT * FROM download;
--查看报表数据
SELECT
toStartOfDay(when) AS day,
userid,
count() as downloads,
sum(bytes) AS bytes
FROM download
GROUP BY userid, day;
--
drop table download ;

使用POPULATE 创建一个每日计数下载量的物化视图表--源表中的插入的5000条数据已经物化物化视图表中

drop view  IF  EXISTS download_daily_mv;
CREATE MATERIALIZED VIEW  IF NOT EXISTS  download_daily_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(day)
ORDER BY (userid, day) POPULATE
AS
SELECT 
    toStartOfDay(when) AS day, 
    userid, 
    count() AS downloads, 
    sum(bytes) AS bytes
FROM download
GROUP BY 
    userid, 
    day;
--    
SELECT * FROM download_daily_mv ORDER BY day,userid ;
drop view  IF  EXISTS download_daily_mv;