clickhouse join_物化视图简介与ClickHouse中的应用示例

最新推荐文章于 2024-06-27 22:36:32 发布

weixin_39804620

最新推荐文章于 2024-06-27 22:36:32 发布

阅读量1.5k

点赞数

文章标签： clickhouse join mobx中跟新了数据视图没变化

前言

最近在搞520大促的事情，忙到脚不点地，所以就写些简单省事的吧。

物化视图概念

我们都知道，数据库中的视图(view)是从一张或多张数据库表查询导出的虚拟表，反映基础表中数据的变化，且本身不存储数据。那么物化视图(materialized view)是什么呢？英文维基中给出的描述是相当准确的，抄录如下。

In computing, a materialized view is a database object that contains the results of a query. For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.

The process of setting up a materialized view is sometimes called materialization. This is a form of caching the results of a query, similar to memoization of the value of a function in functional languages, and it is sometimes described as a form of precomputation. As with other forms of precomputation, database users typically use materialized views for performance reasons, i.e. as a form of optimization.

物化视图是查询结果集的一份持久化存储，所以它与普通视图完全不同，而非常趋近于表。“查询结果集”的范围很宽泛，可以是基础表中部分数据的一份简单拷贝，也可以是多表join之后产生的结果或其子集，或者原始数据的聚合指标等等。所以，物化视图不会随着基础表的变化而变化，所以它也称为快照(snapshot)。如果要更新数据的话，需要用户手动进行，如周期性执行SQL，或利用触发器等机制。

产生物化视图的过程就叫做“物化”(materialization)。广义地讲，物化视图是数据库中的预计算逻辑+显式缓存，典型的空间换时间思路。所以用得好的话，它可以避免对基础表的频繁查询并复用结果，从而显著提升查询的性能。它当然也可以利用一些表的特性，如索引。

在传统关系型数据库中，Oracle、PostgreSQL、SQL Server等都支持物化视图，作为流处理引擎的Kafka和Spark也支持在流上建立物化视图。下面来聊聊ClickHouse里的物化视图功能。

ClickHouse物化视图示例

我们目前只是将CK当作点击流数仓来用，故拿点击流日志表当作基础表。

CREATE

w/ SummingMergeTree

如果要查询某个站点一天内分时段的商品点击量，写出如下SQL语句。

SELECT toStartOfHour

这是一个典型的聚合查询。如果各个地域的分析人员都经常执行该类查询(只是改变ts_date与site_id的条件而已)，那么肯定有相同的语句会被重复执行多次，每次都会从analytics_access_log_all这张大的明细表取数据，显然是比较浪费资源的。而通过将CK中的物化视图与合适的MergeTree引擎配合使用，就可以实现预聚合，从物化视图出数的效率非常好。

下面就根据上述SQL语句的查询条件创建一个物化视图，请注意其语法。

CREATE MATERIALIZED

可见，物化视图与表一样，也可以指定表引擎、分区键、主键和表设置参数。商品点击量是个简单累加的指标，所以我们选择SummingMergeTree作为表引擎(上述是高可用情况，所以用了带复制的ReplicatedSummingMergeTree)。该引擎支持以主键分组，对数值型指标做自动累加。每当表的parts做后台merge的时候，主键相同的所有记录会被加和合并成一行记录，大大节省空间。

用户在创建物化视图时，通过AS SELECT ...子句从基础表中查询需要的列，十分灵活。在默认情况下，物化视图刚刚创建时没有数据，随着基础表中的数据批量写入，物化视图的计算结果也逐渐填充起来。如果需要从历史数据初始化，在AS SELECT子句的前面加上POPULATE关键字即可。需要注意，在POPULATE填充历史数据的期间，新进入的这部分数据会被忽略掉，所以如果对准确性要求非常高，应慎用。

执行完上述CREATE MATERIALIZED VIEW语句后，通过SHOW TABLES语句查询，会发现有一张名为.inner.[物化视图名]的表，这就是持久化物化视图数据的表，当然我们是不会直接操作它的。

SHOW

underlying table的关系如下简图所示。

当然，在物化视图上也可以建立分布式表。

CREATE

查询物化视图的风格与查询普通表没有区别，返回的就是预聚合的数据了。

SELECT ts_hour

w/ AggregatingMergeTree

SummingMergeTree只能处理累加的情况，如果不只有累加呢？物化视图还可以配合更加通用的AggregatingMergeTree引擎使用，用户能够通过聚合函数(aggregate function)来自定义聚合指标。举个例子，假设我们要按各城市的页面来按分钟统计PV和UV，就可以创建如下的物化视图。

CREATE MATERIALIZED

利用AggregatingMergeTree产生物化视图时，实际上是记录了被聚合指标的状态，所以需要在原本的聚合函数名(如sum、uniq)之后加上"State"后缀。

SELECT ts_date

我们也可以通过查询system.parts系统表来查看物化视图实际占用的parts信息。

SELECT

The End

继续去忙了，民那晚安吧(啥

后记：

•如果表数据不是只增的，而是有较频繁的删除或修改(如接入changelog的表)，物化视图底层需要改用CollapsingMergeTree/VersionedCollapsingMergeTree；•如果物化视图是由两表join产生的，那么物化视图仅有在左表插入数据时才更新。如果只有右表插入数据，则不更新。