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

最新推荐文章于 2024-05-30 16:58:02 发布

java资深菜鸟

最新推荐文章于 2024-05-30 16:58:02 发布

阅读量2k

点赞数 2

分类专栏： DATAS 文章标签：数据库大数据 clickhouse 物化视图

本文链接：https://blog.csdn.net/bswsf/article/details/131241708

版权

DATAS 专栏收录该内容

9 篇文章 2 订阅

订阅专栏

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;

不使用POPULATE 创建一个每小时计数下载量的物化视图表

drop view IF EXISTS download_hour_mv;
CREATE MATERIALIZED VIEW IF NOT EXISTS download_hour_mv
ENGINE = SummingMergeTree
PARTITION BY toYYYYMM(hour) ORDER BY (userid, hour)
AS SELECT
  toStartOfHour(when) AS hour,
  userid,
  count() as downloads,
  sum(bytes) AS bytes
FROM download 
GROUP BY userid, hour;
--可以看到现在还是个空表，如何物化数据呢
SELECT * from download_hour_mv;
--以直接把源表数据直接insert到mv中
INSERT INTO download_hour_mv SELECT
    toStartOfHour(when) AS hour,
    userid,
    count() AS downloads,
    sum(bytes) AS bytes
FROM download
GROUP BY
    userid,
    hour;
--当然你也可以直接写数据到源表，实现数据的自动同步
INSERT INTO download 
SELECT
    toDateTime('2023-04-24 04:00:00') + (number * (1 / 3)) AS when,
    19,
    rand() % 1000000
FROM system.numbers
LIMIT 10000;
--
drop view IF EXISTS download_hour_mv;

4.2使用 to db.table方法创建

用这种方法创建mv需要用[to db.table]手动指定目标私有表、所有需要先建表。

基础版本--使用简单表

drop table if exists counter;
drop table if exists counter_daily;
drop view  if exists counter_daily_mv;
-- 设备的点击率表
CREATE TABLE IF NOT EXISTS counter (
  when DateTime DEFAULT now(),
  device UInt32,
  value Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (device, when);
--
INSERT INTO counter
  SELECT
    toDateTime('2015-01-01 00:00:00') + toInt64(number/10) AS when,
    (number % 10) + 1 AS device,
    (device * 3) +  (number/10000) + (rand() % 53) * 0.1 AS value
  FROM system.numbers LIMIT 1000000;
--
 SELECT * from counter;
--假设我们需要查询全时间段设备的点击率，最大最小和平均值，我们可用以下SQL
SELECT
  device,
  count(1) AS count,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device
ORDER BY device ASC;
--但是这么做的话查询速度会很慢，因为需要扫全表的数据。如果我们创建一个物化视图表，计算一个日聚合的数据，那么就可以直接汇总每日聚合的数据返回结果。注意这里的maxMerge函数可以理解为在SQL语法层面暴露一个部分值局部聚合的这么一个功能。换句话说，使用SummingMergeTree的物化视图也可以实现AggregatingMergeTree的聚合功能，因此而推荐使用SummingMergeTree。
-- 建立目标表
CREATE TABLE IF NOT EXISTS counter_daily (
  day DateTime,
  device UInt32,
  count UInt64,
  max_value_state Float32,
  min_value_state Float32,
  avg_value_state Float32
)
ENGINE = SummingMergeTree()
PARTITION BY tuple()
ORDER BY (device, day);
--在定义物化视图的时候使用select从源表把数据注入到目标表中
CREATE MATERIALIZED VIEW IF NOT EXISTS counter_daily_mv
TO counter_daily
AS
SELECT
    toStartOfDay(when) as day,
    device,
    count(*) as count,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device, day
ORDER BY device, day;
--一开始物化视图是没有数据的，需要手动把数据加载到目标表中
SELECT
device,
count,
max_value_state,
min_value_state,
avg_value_state
FROM counter_daily_mv;
--历史数据
INSERT INTO counter_daily
SELECT
  toStartOfDay(when) as day,
    device,
    count(*) as count,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device, day
ORDER BY device, day;
-- 查视图 和  目标表效果一样
SELECT
day,
device,
count,
max_value_state,
min_value_state,
avg_value_state
FROM counter_daily_mv
order by day,device ;
--
SELECT
day,
device,
count,
max_value_state,
min_value_state,
avg_value_state
FROM  counter_daily
order by day,device ;
-- 删除视图 目标表还在
drop table if exists counter;
drop table if exists counter_daily;
drop view  if exists counter_daily_mv;

试验版本--主要发现引擎 SummingMergeTree 也能用AggregateFunction，之前了解到指定聚合函数是在引擎AggregatingMergeTree中的功能？SummingMergeTree只是用来分组求和

不解、、、、、

-- 设备的点击率表
CREATE TABLE IF NOT EXISTS counter (
  when DateTime DEFAULT now(),
  device UInt32,
  value Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(when)
ORDER BY (device, when);
--
INSERT INTO counter
  SELECT
    toDateTime('2015-01-01 00:00:00') + toInt64(number/10) AS when,
    (number % 10) + 1 AS device,
    (device * 3) +  (number/10000) + (rand() % 53) * 0.1 AS value
  FROM system.numbers LIMIT 1000000;
--
 SELECT * from counter;
--假设我们需要查询全时间段设备的点击率，最大最小和平均值，我们可用以下SQL
SELECT
  device,
  count(1) AS count,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device
ORDER BY device ASC;
--但是这么做的话查询速度会很慢，因为需要扫全表的数据。如果我们创建一个物化视图表，计算一个日聚合的数据，那么就可以直接汇总每日聚合的数据返回结果。注意这里的maxMerge函数可以理解为在SQL语法层面暴露一个部分值局部聚合的这么一个功能。换句话说，使用SummingMergeTree的物化视图也可以实现AggregatingMergeTree的聚合功能，因此而推荐使用SummingMergeTree。
-- 建立目标表
CREATE TABLE IF NOT EXISTS counter_daily (
  day DateTime,
  device UInt32,
  count UInt64,
  max_value_state AggregateFunction(max, Float32),
  min_value_state AggregateFunction(min, Float32),
  avg_value_state AggregateFunction(avg, Float32)
)
ENGINE = SummingMergeTree()
PARTITION BY tuple()
ORDER BY (device, day);
--在定义物化视图的时候使用select从源表把数据注入到目标表中
CREATE MATERIALIZED VIEW IF NOT EXISTS counter_daily_mv
TO counter_daily
AS
SELECT
    toStartOfDay(when) as day,
    device,
    count(*) as count,
    maxState(value) AS max_value_state,
    minState(value) AS min_value_state,
    avgState(value) AS avg_value_state
FROM counter
GROUP BY device, day
ORDER BY device, day;
--一开始物化视图是没有数据的，需要手动把数据加载到目标表中
SELECT
  device,
  sum(count) AS count,
  maxMerge(max_value_state) AS max,
  minMerge(min_value_state) AS min,
  avgMerge(avg_value_state) AS avg
FROM counter_daily_mv
GROUP BY device
ORDER BY device ASC;
--历史数据
INSERT INTO counter_daily
SELECT
  toStartOfDay(when) as day,
  device,
  count(*) AS count,
  maxState(value) AS max_value_state,
  minState(value) AS min_value_state,
  avgState(value) AS avg_value_state
FROM counter
GROUP BY device, day
ORDER BY device, day;
-- 查视图 和  目标表效果一样
SELECT
  device,
  sum(count) AS count,
  maxMerge(max_value_state) AS max,
  minMerge(min_value_state) AS min,
  avgMerge(avg_value_state) AS avg
FROM counter_daily_mv
GROUP BY device
ORDER BY device ASC;
--
SELECT
  device,
  sum(count) AS count,
  maxMerge(max_value_state) AS max,
  minMerge(min_value_state) AS min,
  avgMerge(avg_value_state) AS avg
FROM counter_daily
GROUP BY device
ORDER BY device ASC;
-- 删除视图 目标表还在
drop table if exists counter;
drop table if exists counter_daily;
drop view  if exists counter_daily_mv;

5、机制验证

5.1 基表新增、删除、修改（视图用SummingMergeTree）

只有新增、会触发物化视图机制。

-- 基础表 人员工资表
drop table IF  EXISTS user;
create table IF NOT EXISTS  user(id UInt8, org String, gh String,name String,salary Decimal(20,2))engine=ReplacingMergeTree() order by (id,name) primary key id ;
insert into user  values(1,'gw','zs','张三',1),(2,'yl','ls','李四',1);
-- 统计同名数量
drop VIEW IF  EXISTS user_mv;
CREATE MATERIALIZED VIEW  IF NOT EXISTS  user_mv
ENGINE = SummingMergeTree(salary)
ORDER BY (org) POPULATE
AS
SELECT   org,  sum(salary) salary  FROM user GROUP BY org ;
--
select * from user_mv ;
--插入 看视图
insert into user values(1,'gw','zs','张三',1);

-- 貌似这个 视图不是实时的触发 需分区优化后才合并
select * from user_mv;
--删除表和数据均不不影响视图内容
DELETE  from user where id = 1;
UPDATE user set salary = 100 where id =1;
--
optimize table user_mv final;
optimize table user final;

5.2 基表新增、删除、修改（视图用AggregatingMergeTree）

只有新增、会触发物化视图机制。验证结果同上

--创建表 t_merge_base 表，使用MergeTree引擎
create table IF NOT EXISTS t_merge_base(id UInt8,name String,age UInt8,loc String,dept String,workdays UInt8,salary Decimal32(2))engine = MergeTree() order by (id,age) primary key id partition by loc;
create materialized view IF NOT EXISTS view_aggregating_mt  engine = AggregatingMergeTree() order by id as select id,name,sumState(salary) as ss from t_merge_base group by id ,name ;
--#向表 t_merge_base 中插入数据
insert into t_merge_base values (1,'张三',18,'北京','大数据',24,10000), (2,'李四',19,'上海','java',22,8000),(3,'王五',20,'北京','java',26,12000);
-- 查看 view_aggregating_mt视图数据
SELECT * from t_merge_base;
select id,name,sumMerge(ss)  from view_aggregating_mt group by id,name;
-- #继续向表 t_merge_base中插入排序键相同的数据
insert into t_merge_base values (1,'张三三',18,'北京','前端',22,5000);
select id,name,sumMerge(ss)  from view_aggregating_mt group by id,name;
--
drop table IF  EXISTS t_merge_base;
drop view  IF  EXISTS view_aggregating_mt;
-- 
optimize table t_merge_base;
optimize table view_aggregating_mt;

5.3 从avg看optimize机制

直接上代码: 务必看完两个例子

总结：单纯求和的可使用 SummingMergeTree;

涉及其他比如：求平均值等聚会，需要用AggregatingMergeTree，且语法必须使用 -State 插入和 -Merge 查询。且无需optimize?.那是因为group by 只有一列，视图的order by也是一列，否则当group by多列，且根据视图的order by列不唯一时还是有重复记录

无需optimize

-- 设备的价格表
drop table if exists counter;
drop view  if exists counter_daily_mv;
--
CREATE TABLE IF NOT EXISTS counter (
  device UInt32,
  value decimal(20,2)
) ENGINE=ReplacingMergeTree
ORDER BY (device);
--
 SELECT * from counter;
--假设我们需要查询全时间段设备的价格，最大最小和平均值，我们可用以下SQL
SELECT
  device,
  count(1) AS count,
  sum(value) as sum_value_state,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device
ORDER BY device ASC;
-- 错误用法
CREATE MATERIALIZED VIEW IF NOT EXISTS counter_daily_mv
ENGINE = AggregatingMergeTree() -- SummingMergeTree AggregatingMergeTree 均不对 
ORDER BY (device) POPULATE
AS
SELECT
  device,
  count(1) AS count,
  sum(value) as sum_value_state,
   max(value) AS max_value_state,
   min(value) AS min_value_state,
   avg(value) AS avg_value_state
FROM counter
GROUP BY device
ORDER BY device ASC;
--
INSERT INTO counter values (1,0);
INSERT INTO counter values (1,0);
INSERT INTO counter values (1,100);
--
SELECT * from counter_daily_mv;
optimize table counter_daily_mv;
SELECT * from counter_daily_mv;

-- 正确用法
drop view if exists counter_daily_mv;
CREATE MATERIALIZED VIEW IF NOT EXISTS counter_daily_mv
ENGINE = AggregatingMergeTree()
ORDER BY (device) POPULATE
AS
SELECT
   device,
   countState(1) as count,
   sumState(value) AS sum_value_state,
   maxState(value) AS max_value_state,
   minState(value) AS min_value_state,
   avgState(value) AS avg_value_state
FROM counter
GROUP BY device
ORDER BY device;
--标准语法
SELECT device,countMerge(count),sumMerge(sum_value_state),maxMerge(max_value_state),minMerge(min_value_state),avgMerge(avg_value_state) FROM counter_daily_mv  group by device;
-- 错误查法
SELECT * from counter_daily_mv;
update counter set value=0 where device = 1;
-- 发现不需要 分析视图即可获取整取数据;
optimize table counter_daily_mv;

视图数据重复，需optimize

drop table  IF  EXISTS t_merge_base;
drop view  IF  EXISTS view_aggregating_mt;
--创建表 t_merge_base 表，使用MergeTree引擎
create table IF NOT EXISTS t_merge_base(id UInt8,
name String,
age UInt8,
loc String,
dept String,
workdays UInt8,
salary Decimal32(2))engine = MergeTree()
order by
(id,
age) ;
--
create materialized view IF NOT EXISTS view_aggregating_mt engine = AggregatingMergeTree()
order by id as
select
	id,
	name,
	sumState(salary) as ss,
	 maxState(salary) AS max_value_state,
	 minState(salary) AS min_value_state,
   avgState(salary) AS avg_value_state
from
	t_merge_base
group by
	id ,
	name ;
--#向表 t_merge_base 中插入数据
insert into t_merge_base values (1,'张三',18,'北京','大数据',24,10000), (2,'李四',19,'上海','java',22,8000),(3,'王五',20,'北京','java',26,12000);
-- 查看 view_aggregating_mt视图数据
select id,name,sumMerge(ss),maxMerge(max_value_state) from view_aggregating_mt group by id,name;
-- #继续向表 t_merge_base中插入排序键相同的数据  名称相同的自动合并了  不同的需要手动optiminze
insert into t_merge_base values (1,'张三1',18,'北京','前端',22,5000);
insert into t_merge_base values (1,'张三1',18,'北京','前端',22,5000);
insert into t_merge_base values (1,'张三',18,'北京','前端',22,5000);
--
select id,name,sumMerge(ss),maxMerge(max_value_state),minMerge(min_value_state),avgMerge(avg_value_state)  from view_aggregating_mt group by id,name;
optimize table view_aggregating_mt;
select id,name,sumMerge(ss),maxMerge(max_value_state),minMerge(min_value_state),avgMerge(avg_value_state)  from view_aggregating_mt group by id,name;

总结

验证后自己的理解

一、物化视图机制

1、只会在基表insert后才会触发视图机制

且每次insert均会触发机制形成一条记录,只和insert的次数有关和每次insert的数量无关。

2、视图的引擎机制同表引擎-验证了SummingMergeTree和AggregatingMergeTree

接着第1点：视图的统计数据折叠效果也和表一样，不会自动实时触发。需要等待或者手动optimize

3、数据源和视图非强关联

接着第1点：除了insert会触发视图机制，其他任何操作（删除/修改数据）、甚至删除基表，视图数据不会变化

极端1：删除表重建再insert数据，视图机制存在，会持续累加

极端1+：删除表重建（表结构变了）再insert数据，报错无法插入，因为视图引用的表结构不存在

--模拟业务表 用户在某个时间点的下载量
drop table IF  EXISTS download ;
--
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 ,
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 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;
--如果重复插入userid为25的数据 相同的day会重复
SELECT * FROM download_daily_mv ORDER BY userid, day;
optimize table download_daily_mv final;
--极端
--模拟业务表 用户在某个时间点的下载量
drop table IF  EXISTS download ;
--删除表重建再插数据
--删除表重建不同表结构 再插数据
CREATE TABLE IF NOT EXISTS download (
whena DateTime,
userida UInt32,
bytesa Float32
) ENGINE=MergeTree
PARTITION BY toYYYYMM(whena)
ORDER BY (userida, whena);
--插入数据 报错，因为视图引用了表字段 删除视图可插入
INSERT INTO download
SELECT
now() + number * 60,
25,
rand() % 100000000
FROM system.numbers
LIMIT 5000;

4、多表join的视图

只有最左侧表发生insert时才触发视图机制

二、场景

自己的理解

1、基表数据规范化管理：基表历史数据不变、同一个统计维度内数据批量insert。

现实的业务场景，源头数据状态不可控，需要人为创造统计-明细数据的关联环境

2、手动optimize视图:

2.1 定时或者使用视图前

2.2基表插入数据后（如果有多个视图引用该表，视图和表关系需要清晰）

3、推翻以上理论? AggregatingMergeTree

随着深入学习，发现规范合理使用AggregatingMergeTree可以解决聚会场景的每次物化带来的重复数据问题。

也只是解决无需等待optimize问题，涉及的明细数据和聚合数据一致性问题还需要从业务和技术手段上考虑。

三、一句话总结

ClickHouse 的物化视图原理并不复杂，在基表有新的数据写入时，如果检测到有物化视图跟它关联，会针对这批写入的数据进行物化操作。带来的问题：每次物化都会产生新的记录，即相同的聚合维度，在视图optimize（自动/手动）前会有重复数据，每次写入重复一次。

四、optimize机制验证

鉴于每次物化产生新的记录，optimize后又合并。对于求和的列没有问题，那么对于求均值的呢？这里就看下5.3 从avg看optimize机制 吧

java资深菜鸟

关注

2
点赞
踩
5

收藏

觉得还不错? 一键收藏
0
评论
CLickhouse 物化视图--干货记录(亲验证)

验证后自己的理解ClickHouse 的物化视图原理并不复杂，在基表有新的数据写入时，如果检测到有物化视图跟它关联，会针对这批写入的数据进行物化操作。带来的问题：每次物化都会产生新的记录，即相同的聚合维度，在视图optimize（自动/手动）前会有重复数据，每次写入重复一次。
复制链接

扫一扫