clickhouse 物化视图_ClickHouse物化视图使用

简介

物化视图：MATERIALIZED VIEW

物化视图支持表引擎，数据的保存形式由表的引擎决定，在用于插入数据的表上，创建多个物化视图，每个物化视图根据业务需求对数据做转换。

ClickHouse中的物化视图的实现更像是插入触发器。如果视图查询中存在某种汇总，则仅适用于这批新插入的数据。
对源表的现有数据进行的任何更改(例如更新，删除，删除分区等)都不会更改物化视图。
物化视图的功能：

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

创建语法

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. 1.物化视图创建好之后，若源表被写入新数据则物化视图也会同步更新
   2.POPULATE 关键字决定了物化视图的更新策略：
   若有POPULATE 则在创建视图的过程会将源表已经存在的数据一并导入，类似于 create table ... as
   若无POPULATE 则物化视图在创建之后没有数据，只会在创建只有同步之后写入源表的数据.
   clickhouse 官方并不推荐使用populated，因为在创建物化视图的过程中同时写入的数据不能被插入物化视图。
   3.物化视图不支持同步删除，若源表的数据不存在(删除了)则物化视图的数据仍然保留

2. 4.物化视图是野种特殊的数据表，可以用show tables 查看

案列

基于单机表引擎MergeTree

假设有一个表download来记录用户下载的信息，并且可以追踪用户每天下的信息：

创建download表

CREATE TABLE download_mergetree (

when DateTime,

userid UInt32,

bytes Float32

) ENGINE=MergeTree

PARTITION BY toYYYYMM(when)

ORDER BY (userid, when)

插入数据

INSERT INTOdownload_mergetree

SELECT

now() + number * 60 aswhen,

25,

rand() % 100000000

FROM system.numbers

LIMIT 5000;

查看数据

SELECT   * from test.download_mergetree

SELECT   COUNT() from test.download_mergetree

计算：

统计每个用户每天下载的次数和流量：

SELECT

toStartOfDay(when) ASday,

userid,

count() as downloads,

sum(bytes) AS bytes

FROM download_mergetree

GROUP BY userid, day

ORDER BY userid, day

我们可以通过每次运行查询来交互式地为计算这些每日总计，但是对于大型表，提前计算它们更快，更节省资源。
因此，最好将结果放在单独的表格中，该表格可以连续跟踪每天每个用户的下载总数。

创建物化视图：

CREATE MATERIALIZED VIEW download_daily_mv_mergetree

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_mergetree

GROUP BY userid, day

创建物化视图的语法说明：
首先，实例化视图定义允许使用类似于CREATE TABLE的语法，因为此命令实际上将创建一个隐藏的目标表来保存视图数据。
我们使用旨在简化总和计数的ClickHouse引擎：SummingMergeTree。对于计算聚合的物化视图，推荐使用此引擎。
其次，视图定义包括关键字POPULATE。这告诉ClickHouse将计算download表中的现有数据，就像刚插入一样，新加入的数据一样会被计算。
第三，视图定义包括SELECT语句，该语句定义了在加载视图时如何转换数据。该查询在表中的新数据上运行，以计算每天的下载量和每个用户ID的总字节数。
该查询本质上与我们交互式进行的查询相同，只是在这种情况下，结果将放入隐藏的目标表中。我们可以跳过排序，因为视图定义已经确保了排序顺序。查询：  

SELECT * FROMdownload_daily_mv_mergetree

ORDER BY day, userid

模拟产生新的数据：

INSERT INTOdownload_mergetree

SELECT

now() + number * 60 aswhen,

22,

rand() % 100000000

FROM system.numbers

LIMIT 5000

查询验证

---源数据查询  

SELECT

toStartOfDay(when) ASday,

userid,

count() as downloads,

sum(bytes) AS bytes

FROM download_mergetree

GROUP BY userid, day

ORDER BY userid, day

---物化视图查询

SELECT * FROMdownload_daily_mv_mergetree

结果如下

聚合计算

SELECT

toStartOfMonth(day) ASmonth,

userid,

sum(downloads),

sum(bytes)

FROMdownload_daily_mv_mergetree

GROUP BY userid, monthWITH TOTALS

ORDER BY userid, month

基于集群分布式表引擎ReplicatedMergeTree

创建本地表

CREATE TABLEdownload_remergetree (

when DateTime,

userid UInt32,

bytes Float32

) ENGINE=ReplicatedMergeTree('/clickhouse/tables/{shard}/download_remergetree','{replica}')

PARTITION BYtoYYYYMM(when)

ORDER BY (userid, when)

创建分布式表

CREATE TABLEtest.dis_download_remergetree ON CLUSTER default

 AS test.download_remergetree

ENGINE =Distributed(default, test, download_remergetree, rand());

插入数据

INSERT INTOtest.dis_download_remergetree

SELECT

now() + number * 60 aswhen,

25,

rand() % 100000000

FROM system.numbers

LIMIT 5000;

查询

SELECT   * FROM dis_download_remergetree;

SELECT   COUNT() FROM dis_download_remergetree;

创建物化视图

CREATE MATERIALIZED VIEW test.dis_download_mv_remergetree

ENGINE =ReplicatedSummingMergeTree('/clickhouse/tables/{shard}/test/dis_download_mv_remergetree','{replica}') PARTITION BY toYYYYMM(day)ORDER BY (userid, day)

POPULATE

AS SELECT

toStartOfDay(when)AS day,

userid,

count() as downloads,

sum(bytes) AS bytes

FROMtest.dis_download_remergetree

GROUP BY userid, day

通过物化视图查询数据

SELECT * FROM test.dis_download_mv_remergetreeORDER BY day, userid

插入新的数据

INSERT INTOtest.dis_download_remergetree

SELECT

now() + number * 60 aswhen,

22,

rand() % 100000000

FROM system.numbers

LIMIT 5000

通过视图再次查询数据查询数据

聚合查询

通过物化视图实现聚合查询

SELECT

toStartOfMonth(day) ASmonth,

userid,

sum(downloads),

sum(bytes)

FROMtest.dis_download_mv_remergetree

GROUP BY userid, monthWITH TOTALS

ORDER BY userid, month