Clickhouse 物化视图

SemgHH

已于 2024-08-23 09:08:45 修改

阅读量933

点赞数 30

文章标签： clickhouse

于 2024-08-22 12:42:31 首次发布

本文链接：https://blog.csdn.net/WIKM_INKM/article/details/141425208

版权

reference：
https://clickhouse.com/docs/en/materialized-view

本文来自于ck官方文档，机翻+本人校对。

1.9.1 物化视图

Materialized views

和传统视图（只保存SQL 不存储数据）不同，物化视图通过在后台异步处理数据更新，可以提供快速的数据访问能力。

clickhouse 的传统的物化视图是 “增量物化视图”，对于DELETE操作无法反映到增量物化视图。

ck最新版本提供了一种周期性重新构建、存储的物化视图。Refreshable Materialized views

物化视图允许用户将 “计算成本” 从查询时转换为 “数据插入时” ，从而实现更快的SELECT查询。

和PGsql等事务性数据库不同， Clickhouse的物化视图只是一个触发器。当数据块插入到表中时，他会对数据块运行查询，并将结果集插入到第二个target表中。

Clickhouse的物化视图，会随着流入base表而实时更i性能，其功能更像时不断更新的索引。这和其他数据库有鲜明的对比，在其他数据库中物化视图通常是必须刷新查询的静态快照。(类似于 ClickHouse 中的refreshable materialized views).

1.9.1.1 前言

example：

假设我们想要获取一个帖子每天的赞成票数和反对票数。

在使用toStartOfDay() 函数下，这将会是一个很简单的sql查询：

SELECT toStartOfDay(CreationDate) AS day ,
	   countIf(VoteTypeId=2) AS UpVotes,
	   countIf(VoteTypeId=3) AS DownVotes
FROM votes
GROUP BY day 
ORDER BY day ASC
LIMIT 10

这样的查询已经很快了，但我们能做到更好吗？

如果我们想在插入数据的时候计算，那么我们应该使用物化视图。这个表每天仅应该保存1条数据。如果一条数据插入时，并且target表中已经存在当天数据，那么应当将update操作合并到已有的行中。想要进行这样的增量操作，必须为存储其他列部分。

clickhouse 提供了一种特殊的 SummingMergeTree , 它会将所有拥有“相同Ordering key”的key替换为一行（这一行包含了所有数字列的总结计算）。

下表(target 表)将对所有相同数据合并为一列(先插入行，然后在合并行)，并总结计算所有数字列。

create table votes(
	CreationDate datetime,
    VoteTypeId UInt32 primary key
)ENGINE=MergeTree	


#targe Table
create table up_down_votes_per_day(

    `Day` date,
    `UpVotes` UInt32, 
    `DownVotes` UInt32
    
)ENGINE=SummingMergeTree
ORDER BY Day

然后使用下面的语句创建物化视图：

CREATE materizlized VIEW up_down_votes_per_day_view TO up_down_votes_per_day AS
SELECT toStartOfDay(CreationDate)::Date AS Day,
	   countIf(VoteTypeId=2) AS UpVotes,
	   countIf(VoteTypeId=3) As DownVotes
FROM votes
GROUP BY Day

在创建物化视图时， TO 子句是关键，它表示这个物化视图将数据写到哪个target表。

注意：物化视图是在数据插入时的触发，若创建物化视图以后，期望表内原有的数据触发物化视图，可以手动插入：

INSERT INTO up_down_votes_per_day_view
SELECT toStartOfDay(CreationDate)::Date AS Day,
	   countIf(VoteTypeId=2) AS UpVotes,
	   countIf(VoteTypeId=3) As DownVotes
FROM votes
GROUP BY Day

物化视图中，行的合并是异步的（并且每天的投票有多行），为了在查询到合并所有未完成的行后的结果，我们有2个选项：

在表名后使用 FINAL 关键字

SELECT
    Day,
    UpVotes,
    DownVotes
FROM up_down_votes_per_day
FINAL
ORDER BY Day ASC
LIMIT 10

在查询语句中使用聚合函数：

SELECT Day, sum(UpVotes) AS UpVotes, sum(DownVotes) AS DownVotes
FROM up_down_votes_per_day
GROUP BY Day
ORDER BY Day ASC
LIMIT 10

我们现在使用一些”以每小时分组的name “ 作为假的数据集演示：

我们的业务目标：

我们需要每月对每一个 domain name 进行数据聚合。
也需要每年对 domain names 进行聚合。

想要完成上述目标，你可以有如下选择:

直接使用 SELECT 查询 query 将要读取和聚合的数据。
Prepare the data at the ingest time to a new format

在获取时，以一个新的格式准备数据
在获取某个特定的聚合时准备数据

Preparing the data using Materialized views will allow you to limit the amount of data and calculation ClickHouse needs to do, making your SELECT requests faster.

使用物化视图来准备数据，可以让开发者限制clickhouse需要做的数据量和计算量。并且可以让让 SELECT 语句更快。

1.9.1.2 级联物化视图

首先需要创建一个数据源表（source table），因为我们的目标是聚合数据，而不是独立的行。我们可以解析它，并向物化视图传递信息，丢弃真实的输入数据。

这意味着，我们的目标可以存储，所以我们可以使用NULL 引擎。

CREATE DATABASE IF NOT EXISTS analytics;

CREATE TABLE analytics.hourly_data
(
    `domain_name` String,
    `event_time` DateTime,
    `count_views` UInt64
)
ENGINE = Null

You can create a materialized view on a Null table. So the data written to the table will end up affecting the view, but the original raw data will still be discarded.

开发者可以在 null 表中创建视图。向表中写入数据最终会影响视图，但原始数据会被丢弃。

每月聚合表和物化视图

对于首次物化视图，我们需要创建 target表，在这个例子中target表是 analytics.monthly_aggregated_data,

并且我们会存储每月View的数量。

CREATE TABLE analytics.monthly_aggregated_data
(
    `domain_name` String,
    `month` Date,
    `sumCountViews` AggregateFunction(sum, UInt64)
)
ENGINE = AggregatingMergeTree
ORDER BY (domain_name, month)

物化视图的数据类似于下面查询：

CREATE MATERIALIZED VIEW analytics.monthly_aggregated_data_mv
TO analytics.monthly_aggregated_data
AS
SELECT
    toDate(toStartOfMonth(event_time)) AS month,
    domain_name,
    sumState(count_views) AS sumCountViews
FROM analytics.hourly_data
GROUP BY
    domain_name,
    month

1.9.1.3 可刷新物化视图（实验特性）

最新版的ck提供了可刷新物化视图（Refreshable Materialized View），用于在特定场景下不足"增量物化视图"的短板。

在一些特殊场景下，使用增量物化视图构建远不如周期性重新生成物化视图简单。
我司就遇到了这样的场景，但是生产的ck版本没有支持这个实验性新特性

可刷新的物化视图（Refreshable Materialized View）可以认为在概念上类似于传统 OLTP 数据库中的物化视图。

RMV 存储了指定查询的结果以便快速检索，并减少了重复执行 "资源密集型查询"的开销。

与 ClickHouse 的增量物化视图（ incremental materialized views）不同，可刷新物化视图需要定期对整个数据集执行查询，并将其结果存储在目标表中以便查询。

从理论上讲，执行可刷新物化视图后的结果集应小于原始数据集，这样才能让后续查询的执行速度更快。

什么时候使用可刷新物化视图？

ck的增量物化视图非常强大，通常比刷新物化视图有更好的扩展性（尤其是在需要对单个表执行聚合的情况下）。

但是在某些情况下，增量过程是不适用的（有些问题和增量不兼容或不需要实时更新，定期重建更合适）。

example：

观察下面查询 : 将 postlinks的数据集连接到 posts数据集中。在Denormalizing Data guide.中，讨论了开发者可能希望这样做的原因。

SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
     PostId,
     groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId

post 和 postlinks 表都可能被更新。与其尝试使用增量物化视图实现，不如简单的安排查询按指定时间间隔运行。

刷新物化视图的语法和增量物化视图相同，只需要额外包含 REFRESH 从句：

#enable experimental feature
SET allow_experimental_refreshable_materialized_view = 1



CREATE MATERIALIZED VIEW posts_with_links_mv
REFRESH EVERY 1 HOUR TO posts_with_links AS
SELECT
    posts.*,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Linked' AND p.2 != 0, Related)) AS LinkedPosts,
    arrayMap(p -> (p.1, p.2), arrayFilter(p -> p.3 = 'Duplicate' AND p.2 != 0, Related)) AS DuplicatePosts
FROM posts
LEFT JOIN (
    SELECT
     PostId,
     groupArray((CreationDate, RelatedPostId, LinkTypeId)) AS Related
    FROM postlinks
    GROUP BY PostId
) AS postlinks ON posts_types_codecs_ordered.Id = postlinks.PostId

SemgHH

关注

30
点赞
踩
11

收藏

觉得还不错? 一键收藏
0
评论
Clickhouse 物化视图

example：假设我们想要获取一个帖子每天的赞成票数和反对票数。在使用FROM votesLIMIT 10这样的查询已经很快了，但我们能做到更好吗？如果我们想在插入数据的时候计算，那么我们应该使用物化视图。这个表每天仅应该保存1条数据。如果一条数据插入时，并且target表中已经存在当天数据，那么应当将update操作合并到已有的行中。想要进行这样的增量操作，必须为存储其他列部分。
复制链接

扫一扫