ClickHouse物化视图

雨点打透心脏的1/2处 2024-03-23 16:07 37阅读 0赞

#### 目录 ####

*  1 概述
 *   *  1.1 物化视图与普通视图的区别
     *  1.2 优缺点
     *  1.3 基本语法
 *  2 案例实操
 *   *  2.1 准备测试用表和数据
     *  2.2 创建物化视图
     *  2.3 导入增量数据
     *  2.4 导入历史数据

--------------------

## 1 概述 ##

![在这里插入图片描述][fd9ec36680f047e6b803f09722ee9348.png]

ClickHouse 的物化视图是一种查询结果的持久化，它确实是给我们带来了查询效率的提升。用户查起来跟表没有区别，它就是一张表，它也像是一张时刻在预计算的表，创建的过程它是用了一个特殊引擎，加上后来 as select，就是 create 一个 table as select 的写法。  
  “查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表 join 之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照（snapshot）

### 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.  创建物化视图的限制
    
    > 1.  必须指定物化视图的 engine 用于数据存储
    > 2.  TO \[db\].\[table\]语法的时候，不得使用 POPULATE。
    > 3.  查询语句(select）可以包含下面的子句： DISTINCT, GROUP BY, ORDER BY, LIMIT…
    > 4.  物化视图的 alter 操作有些限制，操作起来不大方便。
    > 5.  若物化视图的定义使用了 TO \[db.\]name 子语句，则可以将目标表的视图 卸载 DETACH 再装载 ATTACH
2.  物化视图的数据更新
    
    1.  物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
    2.  POPULATE 关键字决定了物化视图的更新策略：
        
        >  *  若有 POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table … as
        >  *  若无  
        >     POPULATE 则物化视图在创建之后没有数据，只会在创建只有同步之后写入 源表的数据
        >  *  clickhouse 官方并不推荐使用  
        >     POPULATE，因为在创建物化视图的过程中同时写入 的数据不能被插入物化视图。
    3.  物化视图不支持同步删除，若源表的数据不存在（删除了）则物化视图的数据仍然保留
    4.  物化视图是一种特殊的数据表，可以用 show tables 查看
    5.  物化视图数据的删除：
    6.  物化视图的删除：

## 2 案例实操 ##

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

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

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

1.  导入一些数据

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 关键字进行全量更新

### 2.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;

### 2.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;

[fd9ec36680f047e6b803f09722ee9348.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/23eed4dfa33f47c3985c421501676168.png
[Link 1]: https://so.csdn.net/so/search?q=%E6%95%B0%E6%8D%AE%E6%A8%A1%E5%9E%8B&spm=1001.2101.3001.7020