Clickhouse 专栏---物化视图 MATERIALIZED VIEW

迈不过友情╰ 2022-08-28 12:54 541阅读 0赞

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

clickhouse中的物化视图：

Important  
  
Materialized views in ClickHouse are implemented more like insert triggers. If there’s some aggregation in the view query,  
it’s applied only to the batch of freshly inserted data. Any changes to existing data of source table  
(like update, delete, drop partition, etc.) doesn’t change the materialized view.  
  
  
ClickHouse中的物化视图的实现更像是插入触发器。 如果视图查询中存在某种汇总，则仅适用于这批新插入的数据。  
对源表的现有数据进行的任何更改（例如更新，删除，删除分区等）都不会更改物化视图。

物化视图的功能：

物化视图可以计算聚合，从Kafka读取数据，实现最后点查询以及重组表主索引和排序顺序。除了这些功能之外，实例化视图  
还可以在大量节点上很好地扩展，并可以处理大型数据集。它们是ClickHouse的独特功能之一。

应用场景:求和

假设有一个表download来记录用户下载的信息，并且可以追踪用户每天下的信息：  
  
CREATE TABLE 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  
toStartOfDay(when) AS day,  
userid,  
count() as downloads,  
sum(bytes) AS bytes  
FROM download  
GROUP BY userid, day  
ORDER BY userid, day  
  
我们可以通过每次运行查询来交互式地为计算这些每日总计，但是对于大型表，提前计算它们更快，更节省资源。  
因此，最好将结果放在单独的表格中，该表格可以连续跟踪每天每个用户的下载总数。  
  
创建物化视图：  
  
CREATE MATERIALIZED VIEW 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  
  
创建物化视图的语法说明：  
首先，实例化视图定义允许使用类似于CREATE TABLE的语法，因为此命令实际上将创建一个隐藏的目标表来保存视图数据。  
我们使用旨在简化总和计数的ClickHouse引擎：SummingMergeTree。对于计算聚合的物化视图，推荐使用此引擎。  
  
其次，视图定义包括关键字POPULATE。这告诉ClickHouse将计算download表中的现有数据，就像刚插入一样，新加入的数据一样会被计算。  
  
第三，视图定义包括SELECT语句，该语句定义了在加载视图时如何转换数据。该查询在表中的新数据上运行，以计算每天的下载量和每个用户ID的总字节数。  
该查询本质上与我们交互式进行的查询相同，只是在这种情况下，结果将放入隐藏的目标表中。我们可以跳过排序，因为视图定义已经确保了排序顺序。  
  
查询：  
SELECT \* FROM download\_daily\_mv  
ORDER BY day, userid  
LIMIT 5  
  
  
模拟产生新的数据：  
INSERT INTO download  
SELECT  
now() + number \* 60 as when,  
22,  
rand() % 100000000  
FROM system.numbers  
LIMIT 5000  
查询核对：  
SELECT \* FROM download\_daily\_mv ORDER BY userid, day  
  
  
聚合数据：  
SELECT  
toStartOfMonth(day) AS month,  
userid,  
sum(downloads),  
sum(bytes)  
FROM download\_daily\_mv  
GROUP BY userid, month WITH TOTALS  
ORDER BY userid, month

![watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5aSn5pWw5o2u5p625p6E5biIUG9ueQ_size_19_color_FFFFFF_t_70_g_se_x_16][]

[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBA5aSn5pWw5o2u5p625p6E5biIUG9ueQ_size_19_color_FFFFFF_t_70_g_se_x_16]: /images/20220828/30b92c26fc55473bbbb108b54abebcd8.png