12.ClickHouse之物化视图

Leefs 2022-05-05 PM 1162℃ 0条

[TOC]

### 前言

ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 `as select`，就是 `create` 一个 `table as select` 的写法。

“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）。

### 一、概述

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

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

#### 1.2 优缺点

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

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

在某些场景的使用也是有限的。而且如果一张表加了好多物化视图，在写这张表的时候，就会消耗很多机器的资源，比如数据带宽占满、存储一下子增加了很多。

#### 1.3 基本语法

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

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

+ **创建物化视图的限制**
  + 必须指定物化视图的 engine 用于数据存储;
  + TO [db].[table]语法的时候，不得使用 POPULATE;
  + 查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…;
  + 物化视图的 alter 操作有些限制，操作起来不大方便;
  + 若物化视图的定义使用了 TO [db.]name 子语句，则可以将目标表的视图卸载 DETACH 再装载 ATTACH。

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

### 二、案例实操

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

#### 2.1 准备测试用表和数据

+ **建表**

```sql
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;
```

+ **导入一些数据**

```sql
INSERT INTO hits_test 
SELECT 
	EventDate,
	CounterID,
	UserID,
	URL,
	Income 
FROM hits_v1 
limit 10000;
```

#### 2.2 创建物化视图

```sql
# 建表语句
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 表

```sql
CREATE MATERIALIZED VIEW 物化视图名 TO 表 A AS SELECT FROM 表 B;
```

*注意：不建议添加 populate 关键字进行全量更新;*

#### 2.3 导入增量数据

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

# 查询物化视图
SELECT * FROM hits_mv;
```

#### 2.4 导入历史数据

```sql
# 导入增量数据
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;
```

*附文章来源：*

*《尚硅谷大数据之ClickHouse》*

标签: ClickHouse

非特殊说明，本博所有文章均为博主原创。

如若转载，请注明出处：https://lilinchao.com/archives/2068.html

上一篇 11.ClickHouse之数据一致性

下一篇 13.ClickHouse之MaterializeMySQL引擎

12.ClickHouse之物化视图

评论啦~

栏目分类

标签云

友情链接申请

12.ClickHouse之物化视图

 评论啦~

 栏目分类

标签云

友情链接申请

评论啦~

栏目分类

标签云

友情链接申请