ClickHouse物化视图在微信的实战经验
前言
ClickHouse广泛用于用户和系统日志查询场景中，借助腾讯云提供基础设施，微信也在分阶段逐步推进clickhouse的建设和应用，目前作为基础建设的一部分，主要针对于OLAP场景，为业务方提供稳定高效的查询服务。在业务场景下，实时事件流上报可能会在不同的日志，以不同的格式、途径写入到clickhouse。在之前的使用中，通过查询多个日志表join实现多个指标的整合。用传统JOIN方式，我们遇到如下困难: 1.每个查询会有非常长的代码，有的甚至1500行、2000行sql，使用和理解上特别痛苦; 2.性能上无法满足业务诉求，日志量大会爆内存不足; 如何将这些数据进行整合，以ClickHouse宽表的方式呈现给上层使用，用户可以在一张表中查到所需的所有指标，避免提供多表带来的代码复杂度和性能开销问题？本文将重点介绍如何通过物化视图有效解决上述场景的问题。在介绍之前，先铺垫一下物化视图的简单使用，包括如何创建，如何增加维度和指标，如何结合字典增维等场景。
准备工作
很多情况下，没有场景和数据，就很难感同身受的去了解整个过程，所以在写这篇文章前，利用python的Faker库先生成一些模拟数据，模拟真实场景，以数据入手，来介绍关于物化视图的一些使用经验。环境：wsl单节点 centos7 版本：21.3.12.2-lts 数据库: ods,dim,dwm,dws,test 环境相关配置以及本文后续提到代码和模拟数据，均已上传到github的个人项目中 https://github.com/IVitamin-C/clickhouse-learning，供参考。如有问题，可以提issues或者私信我。
用户维度数据
通过代码生成15000个用户，其中Android 10000，ios 5000。
create table ods.user_dim_local on cluster cluster
(
day Date comment '数据分区-天',
uid UInt32 default 0 comment 'uid',
platform String default '' comment '平台 android/ios',
country String default '' comment '国家',
province String default '' comment '省及直辖市',
isp String default '' comment '运营商',
app_version String default '' comment '应用版本',
os_version String default '' comment '系统版本',
mac String default '' comment 'mac',
ip String default '' comment 'ip',
gender String default '' comment '性别',
age Int16 default -1 comment '年龄'
)
engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.user_dim_local','{replica}')
PARTITION BY day
PRIMARY KEY day
ORDER BY day
TTL day + toIntervalDay(3) + toIntervalHour(3)
SETTINGS index_granularity = 8192
--drop table dim.user_dim_dis on cluster cluster;
create table dim.user_dim_dis on cluster cluster
as ods.user_dim_local
engine=Distributed(cluster,ods,user_dim_local,rand());
物品维度数据
通过代码生成100个物品。
create table ods.item_dim_local on cluster cluster
(
day Date comment '数据分区-天',
item_id UInt32 default 0 comment 'item_id',
type_id UInt32 default 0 comment 'type_id',
price UInt32 default 0 comment 'price'
)
engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.item_dim_local','{replica}')
PARTITION BY day
PRIMARY KEY day
ORDER BY day
TTL day + toIntervalDay(3) + toIntervalHour(3)
SETTINGS index_granularity = 8192
--drop table dim.item_dim_dis on cluster cluster;
create table dim.item_dim_dis on cluster cluster
as ods.item_dim_local
engine=Distributed(cluster,ods,item_dim_local,rand());
action_001行为数据
通过代码生成最近3小时的数据，模拟用户的实际访问，主要是曝光、点击、和曝光时间3个指标
--drop table ods.action_001_local on cluster cluster;
create table ods.action_001_local on cluster cluster (
day Date default toDate(second) comment '数据分区-天(Date)'
,hour DateTime default toStartOfHour(second) comment '数据时间-小时(DateTime)'
,second DateTime default '1970-01-01 08:00:00' comment '数据时间-秒'
,insert_second DateTime default now() comment '数据写入时间'
,platform String default '' comment '平台 android/ios'
,ip String default '' comment 'client-ip'
,isp String default '' comment '运营商'
,uid UInt32 default 0 comment 'uid'
,ver String default '' comment '版本'
,item_id UInt32 default 0 comment '物品id'
,show_cnt UInt32 default 0 comment '曝光次数'
,click_cnt UInt32 default 0 comment '点击次数'
,show_time UInt32 default 0 comment '曝光时间'
)
engine=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.action_001_local','{replica}')
PARTITION BY day
PRIMARY KEY (day,hour)
ORDER BY (day,hour,platform,item_id)
TTL day + toIntervalDay(10) + toIntervalHour(4)
SETTINGS index_granularity = 8192
;
--drop table dws.action_001_dis on cluster cluster;
create table dws.action_001_dis on cluster cluster
as ods.action_001_local
engine=Distributed(cluster,ods,action_001_local,rand());
action_002 行为数据
通过代码生成最近3小时的数据，模拟用户点击之后的一些其他操作。这里对指标简单命名。
--drop table ods.action_002_local on cluster cluster;
create table ods.action_002_local on cluster cluster (
day Date default toDate(second) comment '数据分区-天(Date)'
,hour DateTime default toStartOfHour(second) comment '数据时间-小时(DateTime)'
,second DateTime default '1970-01-01 08:00:00' comment '数据时间-秒'
,insert_second DateTime default now() comment '数据写入时间'
,platform String default '' comment '平台 android/ios'
,ip String default '' comment 'client-ip'
,isp String default '' comment '运营商'
,uid UInt32 default 0 comment 'uid'
,ver String default '' comment '版本'
,item_id UInt32 default 0 comment '商品id'
,action_a_cnt UInt32 default 0 comment 'actionA次数'
,action_b_cnt UInt32 default 0 comment 'actionB次数'
,action_c_cnt UInt32 default 0 comment 'actionC次数'
,action_a_time UInt32 default 0 comment 'actionA时间'
,action_b_time UInt32 default 0 comment 'actionA时间'
,action_c_time UInt32 default 0 comment 'actionA时间'
,action_d_sum UInt32 default 0 comment 'action_d_sum'
,action_e_sum UInt32 default 0 comment 'action_e_sum'
,action_f_sum UInt32 default 0 comment 'action_f_sum'
)
engine=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.action_002_local','{replica}')
PARTITION BY day
PRIMARY KEY (day,hour)
ORDER BY (day,hour,platform,item_id)
TTL day + toIntervalDay(10) + toIntervalHour(4)
SETTINGS index_granularity = 8192
;
--drop table dws.action_002_dis on cluster cluster;
create table dws.action_002_dis on cluster cluster
as ods.action_002_local
engine=Distributed(cluster,ods,action_002_local,rand());
这里准备两份日志，主要是为了文章后半部分提到的物化视图的进阶用法，解决某些join场景。
物化视图的简单case
场景
在action_log接入到clickhouse之后，就可以直接通过分布式表去查询了。但是，随着数据量的慢慢积累，比如action_001，它是主页的曝光和点击的数据，一天可能会非常大，百亿级别。这个时候，查一天的去重uv可能还能勉强接受，但是查一周，查一月可能就没法玩了，耗时上巨大，有些也可能超过内存限制。得提速，业务不管你实现方案，要看数据结果，这个时候，物化视图就派上用场了。A产品整理后提了一个诉求，希望可以看到每小时的每个商品的主页统计指标。有时也可能要查1周，1月。经过梳理得到了下面这个需求
| 时间(最细小时) | 商品id | 平台 | 版本 | |
|---|---|---|---|---|
| 曝光人数 | ||||
| 曝光次数 | ||||
| 点击人数 | ||||
| 点击次数 | ||||
| 人均曝光时间 | ||||
| 每次平均曝光时间 | ||||
| 人均点击次数 | ||||
| ctr |
首先，在创建物化视图前评估一下数据量。物化视图会计算当前批次的数据汇总一次，然后根据维度自动merge聚合统计的指标，但是不会跨节点和分区，所以理想状况下，数据量的估算sql为
select uniqCombined(hostName(),hour,item_id,platform,ver)
from dws.action_001
经过计算发现，数据量只是原表的1/n，主要取决于数据的重合度，这个只是最完美的理想状态，但是实际上差距也不会很大，已经比原表少很多数据量了。现在需求明确，也估算完数据量了，在这个数据量下，查询1周或者1月的数据是完全可以接受的。开搞物化视图。
创建过程
首先贴下官方文档https://clickhouse.tech/docs/en/sql-reference/statements/create/view/物化视图的创建有两种方式，一种是
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER]
ENGINE = engine
AS SELECT
这种创建物化视图的好处是创建简单，避免自己写错聚合函数类型带来数据上的写入失败。缺点是alter有局限性，每次更改都需要替换或者修改物化视图的计算逻辑，而且也不能实现文章后续的有限替代join场景。第二种方式是先创建一个存储表，存储表是[Replicated]AggregatingMergeTree，然后通过创建的物化视图使用to的方式写入到存储表中，相当于存储的数据和计算的逻辑分为了两张表分别处理。
CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] TO db.]name
AS SELECT
因为已经指定了存储的表，所以物化视图的创建也不需要指定engine，在查询中，查物化视图和查实际的存储表得到一样的数据，因为都是来自于同一份存储数据。在建表之前还有个细节，TO db.name 后面的表不一定是本地表对本地表，还可以本地表对分布式表，可以基于shard_key处理一些分桶策略，但是会存在写放大的问题，导致集群写入频率增大，负载提高，可以但是慎用。必须要注意的是，from的表一定是本地表。这里大家区分下存储表和计算表两个名词，后续的场景会用到。以下为完整的建表sql 创建ReplicatedAggregatingMergeTree作为数据存储表和分布式表
--drop table dwm.mainpage_stat_mv_local on cluster cluster;
create table dwm.mainpage_stat_mv_local on cluster cluster
(
day Date comment '数据分区-天'
,hour DateTime comment '数据时间-小时(DateTime)'
,platform String comment '平台 android/ios'
,ver String comment '版本'
,item_id UInt32 comment '物品id'
,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'
,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'
,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'
,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'
,show_time_sum SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'
)
engine=ReplicatedAggregatingMergeTree('/clickhouse/tables/{layer}-{shard}/dwm.mainpage_stat_mv_local','{replica}')
PARTITION by day
PRIMARY KEY (day,hour)
ORDER by (day,hour,platform,ver,item_id)
TTL day + toIntervalDay(92) + toIntervalHour(5)
SETTINGS index_granularity = 8192
--drop table dws.mainpage_stat_mv_dis on cluster cluster
create table dws.mainpage_stat_mv_dis on cluster cluster
as dwm.mainpage_stat_mv_local
engine=Distributed(cluster,dwm,mainpage_stat_mv_local,rand());
创建物化视图作为计算逻辑并使用to将数据流向ReplicatedAggregatingMergeTree
create MATERIALIZED VIEW dwm.mv_main_page_stat_mv_local on cluster cluster to dwm.mainpage_stat_mv_local (
day Date comment '数据分区-天'
,hour DateTime comment '数据时间-小时(DateTime)'
,platform String comment '平台 android/ios'
,ver String comment '版本'
,item_id UInt32 comment '物品id'
,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'
,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'
,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'
,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'
,show_time_sum SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'
)
AS SELECT day
,hour
,platform
,ver
,item_id
,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv
,sum(a.show_cnt) as show_cnt
,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv
,sum(a.click_cnt) as click_cnt
,sum(toUInt64(show_time/1000)) as show_time_sum
from ods.action_001_local as a
group by
day
,hour
,platform
,ver
,item_id
查询数据
SELECT
day,
platform,
uniqCombinedMerge(shown_uv) AS shown_uv,
sum(shown_cnt) AS shown_cnt,
uniqCombinedMerge(click_uv) AS click_uv,
sum(click_cnt) AS click_cnt,
sum(show_time_sum) AS show_time_sum
FROM dws.mainpage_stat_mv_dis
GROUP BY
day,
platform
Query id: f6d4d3dd-33f1-408e-92a7-4901fcad50aa
┌────────day─┬─platform─┬─shown_uv─┬─shown_cnt─┬─click_uv─┬─click_cnt─┬─show_time_sum─┐
│ 2021-06-06 │ ios │ 5000 │ 0 │ 4509 │ 554927 │ 781679 │
│ 2021-06-05 │ android │ 9613 │ 0 │ 5249 │ 342910 │ 491502 │
│ 2021-06-06 │ android │ 9995 │ 0 │ 8984 │ 1126905 │ 1570323 │
│ 2021-06-05 │ ios │ 4819 │ 0 │ 2636 │ 175932 │ 248274 │
└────────────┴──────────┴──────────┴───────────┴──────────┴───────────┴───────────────┘
4 rows in set. Elapsed: 0.013 sec. Processed 58.70 thousand rows, 14.38 MB (4.42 million rows/s., 1.08 GB/s.)
处理中的细节
这个地方再细描述下物化视图的处理逻辑，先贴一下官方说明
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.) does not change the materialized view.
根据说明，物化视图是计算每批次写入原表的数据，假设一批写入了10w,那么物化视图就计算了这10w的数据，然后可能聚合之后就剩1w了写入到表中，剩下的过程就交给后台去merge聚合了，这个时候就要去理解物化视图的核心字段类型，AggregateFunction和SimpleAggregateFunction了。这里主要讲两个场景的计算，去理解这个字段类型，一个是uniqCombined计算uv，一个是sum计算pv。
首先是uv计算场景在大数据量下，使用uniqExact去计算精确uv，存储开销大，不便于网络传输数据，查询耗时长，还容易爆内存。除非个别情况下，不推荐使用。uniqCombined(HLL_precision)(x[, ...]) 官方说明 1.为聚合中的所有参数计算一个散列(为String计算64位散列，否则为32位散列)，然后在计算中使用它。这里只当输入1个或者多个参数时，会先计算一个hash散列，这里的hash随着基数的增大，会发生碰撞。2.使用三种算法的组合:数组、哈希表和带纠错表的HyperLogLog。对于少量不同的元素，使用数组。当数据量较大时，使用哈希表。对于大数量的元素集，使用HyperLogLog，它将占用固定数量的内存。3.确定地提供结果(它不依赖于查询处理顺序)。所以在使用这个函数时，误差主要来源于两个地方，一个是计算散列时的hash碰撞，一个是在基数较大时的HyperLogLog的本身误差。但是从生产使用的表现来说，计算高效且稳定，计算结果确定且误差较小，值得使用。毕竟主要针对分析场景而不是金融等对数据准确性要求非常高的情况。正常计算uniqCombined时返回的是UInt64计算好的结果，因为是uv去重的计算场景，所以在使用物化视图计算每批次数据结果后，这个结果是无法迭代累加得到正确结果的(这里的累加不是加法运算哈)。所以要存储成为可以累加的状态，这个时候就要使用-State函数组合器，并使用AggregateFunction字段存储聚合函数提供的这个可以累加的中间状态而不是结果值。uniqCombinedState会得到AggregateFunction(uniqCombined,[String,UInt,Int])这样的一个字段类型。同时，uniqCombined是一个聚合函数，那么我们在group by之后会得到一个元素的组合，同时不管进行了多少个批次的数据计算，每个批次的计算结果不外乎是上面arr，set，hyperLogLog中的一种（具体会涉及序列化和反序列化，更复杂一些，这里简单理解），本身是支持添加元素或者合并多个的操作的，那么每个批次的计算结果也是可以合并的。以集合举例，我们在两次计算分别得到了
| 批次 | platform | ver | uv | ||
|---|---|---|---|---|---|
| 1 | android | 1.1 | {1001,1002,1003,1004} | ||
| 2 | android | 1.2 | {1009,1010,1130,1131} | ||
| 3 | android | 1.1 | {2001,3002,1003,3004} | ||
| 4 | android | 1.2 | {2009,1010,2130,2131} |
在写入到表之后没有merge之前，存储的实际是4个批次的数据，在这个时候进行计算时，计算过程会聚合，这个中间状态会合并，但是这个时候如果直接使用uniqCombined计算这个中间状态会得到什么样的结果呢，我们举例说明下
SELECT
platform,
ver,
uniqCombined(xx)
FROM
(
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.1', 1001), ('android', '1.1', 1002), ('android', '1.1', 1003), ('android', '1.1', 1004)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.2', 1009), ('android', '1.2', 1010), ('android', '1.2', 1130), ('android', '1.2', 1131)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.1', 2001), ('android', '1.1', 3002), ('android', '1.1', 1003), ('android', '1.1', 3004)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.2', 2009), ('android', '1.2', 1010), ('android', '1.2', 2130), ('android', '1.2', 2131)] AS a
)
GROUP BY
platform,
ver
)
GROUP BY
platform,
ver
Query id: 09069556-65a8-42a2-9b0b-c002264a1bb4
┌─platform─┬─ver─┬─uniqCombined(xx)─┐
│ android │ 1.2 │ 2 │
│ android │ 1.1 │ 2 │
└──────────┴─────┴──────────────────┘
2 rows in set. Elapsed: 0.007 sec.
这个结果是明显不对的，因为他将这个中间状态也作为了计算的输入重新计算了，所以在使用上一定要注意AggregateFunction中的State状态使用Merge解析才能得到正确的结果。正确的sql
SELECT
platform,
ver,
uniqCombinedMerge(xx) AS uv
FROM
(
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.1', 1001), ('android', '1.1', 1002), ('android', '1.1', 1003), ('android', '1.1', 1004)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.2', 1009), ('android', '1.2', 1010), ('android', '1.2', 1130), ('android', '1.2', 1131)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.1', 2001), ('android', '1.1', 3002), ('android', '1.1', 1003), ('android', '1.1', 3004)] AS a
)
GROUP BY
platform,
ver
UNION ALL
SELECT
platform,
ver,
uniqCombinedState(uid) AS xx
FROM
(
SELECT
a.1 AS platform,
a.2 AS ver,
a.3 AS uid
FROM system.one
ARRAY JOIN [('android', '1.2', 2009), ('android', '1.2', 1010), ('android', '1.2', 2130), ('android', '1.2', 2131)] AS a
)
GROUP BY
platform,
ver
)
GROUP BY
platform,
ver
Query id: 2a7137a7-f8fb-4b36-a37f-642348ab3ac6
┌─platform─┬─ver─┬─uv─┐
│ android │ 1.2 │ 7 │
│ android │ 1.1 │ 7 │
└──────────┴─────┴────┘
2 rows in set. Elapsed: 0.009 sec.
这里使用union all 模拟的是每个批次的写入数据。通过这个case主要是介绍uniqCombined生成中间态和解中间态的过程，避免大家错误使用哈。通过刚才的错误sql也侧面说明了，中间态存储的记录数要小于原表写入的数据，主要是按照group by的字段进行聚合计算得到的。
接着讲第二个场景，pv的计算。一般情况下，pv通常采用sum进行计算，sum计算和uv计算存在一个比较大的差异，那就是结果值可以累加。所以从逻辑上来讲，每批次计算可以直接是结果值，那么在聚合的时候可以再次进行sum操作可以得到正确的结果。那么这个时候除了采用AggregateFunction外存储中间态外也可以选择SimpleAggregateFunction存储每次计算结果，存储开销是不一样的
SELECT byteSize(xx)
FROM
(
SELECT sumSimpleState(a) AS xx
FROM
(
SELECT 1001 AS a
UNION ALL
SELECT 1002 AS a
)
)
Query id: ac6c5354-d59e-49a0-a54f-ea480acc8f3f
┌─byteSize(xx)─┐
│ 8 │
└──────────────┘
SELECT byteSize(xx)
FROM
(
SELECT sumState(a) AS xx
FROM
(
SELECT 1001 AS a
UNION ALL
SELECT 1002 AS a
)
)
Query id: 01b2ecb5-9e14-4f85-8cc6-5033671560ac
┌─byteSize(xx)─┐
│ 16 │
└──────────────┘
2倍的存储差距，再来简单测试下查询效率
--SimpleAggregateFunction
SELECT sum(xx)
FROM
(
SELECT
a % 1000 AS b,
sumSimpleState(a) AS xx
FROM
(
SELECT number AS a
FROM numbers(1000000000)
)
GROUP BY b
)
Query id: 7c8f4b77-1033-4184-ad2f-1e6719723aca
┌────────────sum(xx)─┐
│ 499999999500000000 │
└────────────────────┘
1 rows in set. Elapsed: 4.140 sec. Processed 1.00 billion rows, 8.00 GB (241.58 million rows/s., 1.93 GB/s.)
--AggregateFunction
SELECT sumMerge(xx)
FROM
(
SELECT
a % 1000 AS b,
sumState(a) AS xx
FROM
(
SELECT number AS a
FROM numbers(1000000000)
)
GROUP BY b
)
Query id: 401c0a9f-30fe-4d9a-88b0-1a33ffcf4f43
┌───────sumMerge(xx)─┐
│ 499999999500000000 │
└────────────────────┘
1 rows in set. Elapsed: 3.201 sec. Processed 1.00 billion rows, 8.00 GB (312.42 million rows/s., 2.50 GB/s.)
查询上有些许差距，这里的数据是通过numbers()函数生成，但是如果是写入和查询完全通过磁盘io的话，这个差距理论上会非常小，SimpleAggregateFunction会读数据更少，写数据更少，存储差距为刚好一半。其中，几乎所有的聚合函数都可以使用AggregateFunction，而只有某些场景可以使用SimpleAggregateFunction，所以在于推广使用和上层查询统一时，可以只选择使用AggregateFunction。根据业务场景自行取舍。
除了uniqCombined和sum外，还有非常多的聚合函数通过物化视图可以实现，这里主要列举一下uv和pv使用的案例，其他的函数也是相同的用法。这个里有个注意事项，需要注意，AggregateFunction严格要求输入字段的类型，比如1就是UInt8，不能是UInt16，AggregateFunction(sum,UInt32)不能被写入到AggregateFunction(sum,UInt8)里，这个错误在创建物化视图的时候是不会感知到的(建表校验问题，已提issues)，但是在写入的时候是会报错的，所以在错误感知上要弱一些，数据一致性会受到影响。SimpleAggregateFunction和AggregateFunction在sum场景有些不一样，它的输入参数如果是UInt或者Int行，那么它的输入参数只能是UInt64或者Int64，而不是必须按照输入字段。可能的事SimpleAggragateFunction的输出又是下个过程的输入，所以SimpleAggregateFunction(sum,type)中的type是按照输出参数类型去创建，max，min等输入输出同类型的没有这个情况。
物化视图的进阶使用
上面是物化视图的一个简单case，主要针对一些单日志的固化场景处理，减少数据量级，提高查询效率。
背景
其实在实际使用的场景下，经常会遇到一个维度关联的问题，比如将物品的类别带入，用户的画像信息带入等场景。这里简单列举下在clickhouse中做维度补全的操作。主要用到了用户维度数据和物品维度数据两个本地表，基于这两个本地表去生成内存字典，通过内存字典去做关联(字典有很多种存储结构，这里主要列举hashed模式)。
字典处理过程
通过离线导入将数据写入了ods.user_dim_local和ods.item_dim_local两个本地表，然后通过查询dim.user_dim_dis和dim.item_dim_dis两个表提供完整数据(这里只是单机列举案例，集群模式同理)。通过从clickhouse查询数据写入到内存字典中，创建字典的sql如下:
--创建user字典
CREATE DICTIONARY dim.dict_user_dim on cluster cluster (
uid UInt64 ,
platform String default '' ,
country String default '' ,
province String default '' ,
isp String default '' ,
app_version String default '' ,
os_version String default '',
mac String default '' ,
ip String default '',
gender String default '',
age Int16 default -1
) PRIMARY KEY uid
SOURCE(
CLICKHOUSE(
HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'dim' TABLE 'user_dim_dis'
)
) LIFETIME(MIN 1800 MAX 3600) LAYOUT(HASHED());
--创建item字典
CREATE DICTIONARY dim.dict_item_dim on cluster cluster (
item_id UInt64 ,
type_id UInt32 default 0,
price UInt32 default 0
) PRIMARY KEY item_id
SOURCE(
CLICKHOUSE(
HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'dim' TABLE 'item_dim_dis'
)
) LIFETIME(MIN 1800 MAX 3600) LAYOUT(HASHED())
这里创建字典的语法不做详细介绍，想要更深了解可以参考官方文档。如果使用clickhouse查询分布式表提供字典数据来源，建议Host为一个查询代理，避免对某个节点产生负面效应。DB和table也可以使用view封装一段sql实现。字典的数据是冗余在所有节点的，默认字典的加载方式是惰性加载，也就是需要至少一次查询才能将字典记载到内存，避免一些不使用的字典对集群带来影响。也可以通过hash分片的方式将用户指定到某个shard，那么字典也可以实现通过hash分片的方式存储在每个节点，间接实现分布式字典，减少数据存储，篇幅有限不展开介绍。在创建字典之后，可以有两种模式使用字典，一种是通过dictGet，另外一种方式是通过join，如果只查询一个key建议通过dictGet使用，代码复杂可读性高，同时字典查的value可以作为另一个查询的key，如果查多个key，可以通过dictGet或者join。类似于 select 1 as a,a+1 as b,b+1 as c from system.one这样。
--单value方法1：
SELECT
dictGet('dim.dict_user_dim', 'platform', toUInt64(uid)) AS platform,
uniqCombined(uid) AS uv
FROM dws.action_001_dis
WHERE day = '2021-06-05'
GROUP BY platform
Query id: 52234955-2dc9-4117-9f2a-45ab97249ea7
┌─platform─┬───uv─┐
│ android │ 9624 │
│ ios │ 4830 │
└──────────┴──────┘
2 rows in set. Elapsed: 0.009 sec. Processed 49.84 thousand rows, 299.07 KB (5.37 million rows/s., 32.24 MB/s.)
--多value方法1：
SELECT
dictGet('dim.dict_user_dim', 'platform', toUInt64(uid)) AS platform,
dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,
uniqCombined(uid) AS uv
FROM dws.action_001_dis
WHERE day = '2021-06-05'
GROUP BY
platform,
gender
Query id: ed255ee5-9036-4385-9a51-35923fef6e48
┌─platform─┬─gender─┬───uv─┐
│ ios │ 男 │ 2236 │
│ android │ 女 │ 4340 │
│ android │ 未知 │ 941 │
│ android │ 男 │ 4361 │
│ ios │ 女 │ 2161 │
│ ios │ 未知 │ 433 │
└──────────┴────────┴──────┘
6 rows in set. Elapsed: 0.011 sec. Processed 49.84 thousand rows, 299.07 KB (4.70 million rows/s., 28.20 MB/s.)
--单value方法2:
SELECT
t2.platform AS platform,
uniqCombined(t1.uid) AS uv
FROM dws.action_001_dis AS t1
INNER JOIN dim.dict_user_dim AS t2 ON toUInt64(t1.uid) = t2.uid
WHERE day = '2021-06-05'
GROUP BY platform
Query id: 8906e637-475e-4386-946e-29e1690f07ea
┌─platform─┬───uv─┐
│ android │ 9624 │
│ ios │ 4830 │
└──────────┴──────┘
2 rows in set. Elapsed: 0.011 sec. Processed 49.84 thousand rows, 299.07 KB (4.55 million rows/s., 27.32 MB/s.)
--多value方法2:
SELECT
t2.platform AS platform,
t2.gender AS gender,
uniqCombined(t1.uid) AS uv
FROM dws.action_001_dis AS t1
INNER JOIN dim.dict_user_dim AS t2 ON toUInt64(t1.uid) = t2.uid
WHERE day = '2021-06-05'
GROUP BY
platform,
gender
Query id: 88ef55a6-ddcc-42f8-8ce3-5e3bb639b38a
┌─platform─┬─gender─┬───uv─┐
│ ios │ 男 │ 2236 │
│ android │ 女 │ 4340 │
│ android │ 未知 │ 941 │
│ android │ 男 │ 4361 │
│ ios │ 女 │ 2161 │
│ ios │ 未知 │ 433 │
└──────────┴────────┴──────┘
6 rows in set. Elapsed: 0.015 sec. Processed 49.84 thousand rows, 299.07 KB (3.34 million rows/s., 20.07 MB/s.)
从查询结果来看，dictGet要更快一些，同时在代码可读性上也要更好一些，可以结合场景使用。
业务场景
产品随着分析的不断深入，提了一个新的诉求，希望增加1个维度(通过字典获得)，1个指标(这里只是列举下物化视图的维度和指标的添加过程)。维度：gender 指标: 曝光时长中位数
创建过程
因为涉及到新增维度和指标，所以需要对原表进行ddl操作。首先新增维度，新增维度比较麻烦一些，因为不光需要新增字段，还可能需要将新增的字段加到索引里面提高查询效率。操作sql如下:
--新增维度并添加到索引
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists gender String comment '性别' after item_id,modify order by
(day,hour,platform,ver,item_id,gender);
alter table dwm.mainpage_stat_mv_local on cluster cluster modify column if exists gender String default '未知' comment '性别' after item_id;
alter table dws.mainpage_stat_mv_dis on cluster cluster add column if not exists gender String comment '性别' after item_id;
--新增指标
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数';
alter table dws.mainpage_stat_mv_dis on cluster cluster add column if not exists show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数';
修改物化视图计算逻辑
drop TABLE dwm.mv_main_page_stat_mv_local on cluster cluster;
CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_local on cluster cluster to dwm.mainpage_stat_mv_local (
day Date comment '数据分区-天'
,hour DateTime comment '数据时间-小时(DateTime)'
,platform String comment '平台 android/ios'
,ver String comment '版本'
,item_id UInt32 comment '物品id'
,gender String comment '性别'
,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'
,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'
,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'
,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'
,show_time_sum SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'
,show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数'
)
AS
SELECT day
,hour
,platform
,ver
,item_id
,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender
,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv
,sum(a.show_cnt) as show_cnt
,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv
,sum(a.click_cnt) as click_cnt
,sum(toUInt64(show_time/1000)) as show_time_sum
,medianExactState(toUInt32(show_time/1000)) as show_time_median
from ods.action_001_local as a
group by
day
,hour
,platform
,ver
,item_id
,gender
通过这个case主要讲了三个方面，一是外部字典的创建和使用，二是物化视图的增加维度和指标，三物化视图结合字典进行增维。
物化视图的再进阶
本文在创建log的时候创建了2个log，在上面的case中只用到了一个，接下来的case主要讲一个物化视图的进一步用法。
背景
很多时候，我们的日志上报并不是在一个日志中的，比如上文中创建的action_001和action_002，一个是主页物品的曝光和点击，一个是点击进行物品详情的其他行为。这个时候，产品提了一个诉求，希望可以知道曝光到点击，点击到某个更一步的行为的用户转换率。我们最常规的方法是，使用join去将结果关联，这里只是两个log，那么后续有非常多的log，写起join来就会相当麻烦，甚至会有上千行代码去作逻辑处理，效率上也会差很多。所以就衍生了接下来主要讲的用法，基于物化视图实现有限join场景。主要是多个不同日志指标的合并。其实更应该理解为union all max。
可行性分析
物化视图在每批次写入数据之后，后台会按照聚合key进行merge操作，将相同维度的数据的记录聚合在一起，降低数据量，提高查询效率。如果在这一批数据，没有满足条件的列(if组合器)或者并没有写这一指标(指定字段写)，那么指标会怎么存，如果下一批数据写入数据，那么这两批数据的这个指标，会怎么样？答案是存可迭代的空数据(注意这里的不写，存的数据不能理解为null)，同时可以和其他批数据进行合并，没有数据的行会被忽略。
举个例子:
CREATE TABLE test.mv_union_max
(
`id` UInt32,
`m1` AggregateFunction(uniqCombined, UInt32),
`m2` AggregateFunction(sum, UInt32)
)
ENGINE = AggregatingMergeTree
ORDER BY id
Query id: 20dcd6cb-e336-4da8-9033-de42527d2bf0
Ok.
0 rows in set. Elapsed: 0.103 sec.
# 写入数据(这里需要注意指定字段写)
INSERT INTO test.mv_union_max (id, m1) SELECT
id,
uniqCombinedState(uid) AS m1
FROM
(
SELECT
a1.1 AS id,
toUInt32(a1.2) AS uid
FROM system.one
ARRAY JOIN [(1, 10001), (2, 10002), (3, 10003), (3, 10001)] AS a1
)
GROUP BY id
Query id: f04953f6-3d8a-40a6-bf7e-5b15fe936488
Ok.
0 rows in set. Elapsed: 0.003 sec.
SELECT *
FROM test.mv_union_max
Query id: af592a63-b17d-4764-9a65-4ab33e122d81
┌─id─┬─m1──┬─m2─┐
│ 1 │ l��
│ │
│ 2 │ $a6� │ │
│ 3 │ ��Gwl��
│ │
└────┴─────┴────┘
3 rows in set. Elapsed: 0.002 sec.
在写入m1指标后显示有3条记录，其中m2为空数据（这里需要注意的是，m2不是null)，如下：
SELECT isNotNull(m2)
FROM test.mv_union_max
Query id: b1ac77df-af77-4f2e-9368-2573a7214c99
┌─isNotNull(m2)─┐
│ 1 │
│ 1 │
│ 1 │
└───────────────┘
3 rows in set. Elapsed: 0.002 sec.
SELECT toTypeName(m2)
FROM test.mv_union_max
Query id: fcb15349-4a33-4253-bf64-37f5dc7078ea
┌─toTypeName(m2)─────────────────┐
│ AggregateFunction(sum, UInt32) │
│ AggregateFunction(sum, UInt32) │
│ AggregateFunction(sum, UInt32) │
└────────────────────────────────┘
3 rows in set. Elapsed: 0.002 sec.
这个时候再写入m2指标，不写入m1指标，那么会发生什么情况。
SELECT *
FROM test.mv_union_max
Query id: 7eaa2d42-c50e-4467-9dca-55a0b5eab579
┌─id─┬─m1──┬─m2─┐
│ 1 │ l��
│ │
│ 2 │ $a6� │ │
│ 3 │ ��Gwl��
│ │
└────┴─────┴────┘
┌─id─┬─m1─┬─m2─┐
│ 1 │ │ � │
│ 2 │ │ ' │
│ 3 │ │ ' │
└────┴────┴────┘
6 rows in set. Elapsed: 0.003 sec.
存了6条记录，分别上两次写入的数据。在手动触发merge之前先确认下，查询的数据是否是正确的。
SELECT
id,
uniqCombinedMerge(m1) AS m1,
sumMerge(m2) AS m2
FROM test.mv_union_max
GROUP BY id
Query id: 3f92106a-1b72-4d86-ab74-59c7ac53c202
┌─id─┬─m1─┬────m2─┐
│ 3 │ 2 │ 10001 │
│ 2 │ 1 │ 10001 │
│ 1 │ 1 │ 2003 │
└────┴────┴───────┘
3 rows in set. Elapsed: 0.003 sec.
数据完全正确，首先可以确认的是，就算不后台merge，查询数据是完全符合需求的。
OPTIMIZE TABLE test.mv_union_max FINAL
Query id: 62465025-da30-4df0-a597-18c0c4eb1b2f
Ok.
0 rows in set. Elapsed: 0.001 sec.
cluster-shard1-ck01 :) select * from test.mv_union_max ;
SELECT *
FROM test.mv_union_max
Query id: f7fb359f-3860-4598-b766-812ac2f65755
┌─id─┬─m1──┬─m2─┐
│ 1 │ l��
│ � │
│ 2 │ $a6� │ ' │
│ 3 │ ��Gwl��
│ ' │
└────┴─────┴────┘
3 rows in set. Elapsed: 0.002 sec.
SELECT
id,
uniqCombinedMerge(m1) AS m1,
sumMerge(m2) AS m2
FROM test.mv_union_max
GROUP BY id
Query id: 2543a145-e540-43dc-8754-101ebb294b5d
┌─id─┬─m1─┬────m2─┐
│ 3 │ 2 │ 10001 │
│ 2 │ 1 │ 10001 │
│ 1 │ 1 │ 2003 │
└────┴────┴───────┘
3 rows in set. Elapsed: 0.003 sec.
数据是可以后台merge在一起的。所以说通过这个case能简单了解到实现原理和可行性。通过这种方式就可以避免了两个log之间的查询关联，可以通过一个物化视图存储表组织好维度和指标，查询基于一张宽表实现。众所周知，clickhouse的单表性能非常强，能不join就尽量不join，这个场景可以减少一部分join的场景(维度补全通过字典，如果维度基数特别大，可以借用flink或者redis字典或者高并发接口补全，这里不做细述)，便于使用和上层平台的查询规范，另一方面这样也可以减少存储占用，将相同维度的数据尽可能压在一起。
业务场景
随着需求的进一步细化，上报了新的action_002，用来分析用户在进入商品页面后的行为。产品希望可以实现基础指标统计和用户的漏斗分析，(简化一下，对维度没有发生变化)。结合对需求的了解，对原有的物化视图增加了一些指标。这里uv，pv，bitmap3个场景都进行了列举，bitmap也可以实现uv，但是效率上慢一些。新增指标：
| 指标名 | 指标解释 |
|---|---|
| acta_uv | 行为A用户数 |
| acta_cnt | 行为A记录数 |
| actb_uv | 行为B用户数 |
| actb_cnt | 行为B记录数 |
| actc_uv | 行为C用户数 |
| actc_cnt | 行为C记录数 |
| show_bm | 曝光Bitmap |
| click_bm | 点击Bitmap |
| acta_bm | 行为A Bitmap |
| actb_bm | 行为B Bitmap |
| actc_bm | 行为C Bitmap |
| actd_bm | 行为D Bitmap |
action_002从生成逻辑上假设了一条用户交互路径。
a->b->c->d
action_001从生成逻辑上假设了一条用户路径。
show->click
但是为了降低代码复杂度 click->a并没有强制关联(主要讲方法，这个细节忽略)。
操作过程
需要对原有物化视图存储表新增上述所有指标，同时对物化视图计算表001新增show_bm、click_bm，物化视图计算表002为新建的计算表，都会写入到最开始建的物化视图存储表中。操作过程如下(sql有些长):
--物化视图存储表新增指标
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_uv AggregateFunction(uniqCombined,UInt32) comment 'acta_uv';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_cnt SimpleAggregateFunction(sum,UInt64) comment 'acta_cnt';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_uv AggregateFunction(uniqCombined,UInt32) comment 'actb_uv';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_cnt SimpleAggregateFunction(sum,UInt64) comment 'actb_cnt';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_uv AggregateFunction(uniqCombined,UInt32) comment 'actc_uv';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_cnt SimpleAggregateFunction(sum,UInt64) comment 'actc_cnt';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists show_bm AggregateFunction(groupBitmap,UInt32) comment 'show_bm';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists click_bm AggregateFunction(groupBitmap,UInt32) comment 'click_bm';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_bm AggregateFunction(groupBitmap,UInt32) comment 'acta_bm';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_bm AggregateFunction(groupBitmap,UInt32) comment 'actb_bm';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_bm AggregateFunction(groupBitmap,UInt32) comment 'actc_bm';
alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actd_bm AggregateFunction(groupBitmap,UInt32) comment 'actd_bm';
--物化视图计算表重建 因为medianExact 耗时较大，接下来的case里去掉了。
drop TABLE dwm.mv_main_page_stat_mv_local on cluster cluster;
CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_001_local on cluster cluster to dwm.mainpage_stat_mv_local (
day Date comment '数据分区-天'
,hour DateTime comment '数据时间-小时(DateTime)'
,platform String comment '平台 android/ios'
,ver String comment '版本'
,item_id UInt32 comment '物品id'
,gender String comment '性别'
,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'
,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'
,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'
,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'
,show_time_sum SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'
,show_bm AggregateFunction(groupBitmap,UInt32) comment 'show_bm'
,click_bm AggregateFunction(groupBitmap,UInt32) comment 'click_bm'
)
AS
SELECT day
,hour
,platform
,ver
,item_id
,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender
,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv
,sum(a.show_cnt) as show_cnt
,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv
,sum(a.click_cnt) as click_cnt
,sum(toUInt64(show_time/1000)) as show_time_sum
,groupBitmapStateIf(uid,a.show_cnt>0) as show_bm
,groupBitmapStateIf(uid,a.click_cnt>0) as click_bm
from ods.action_001_local as a
group by
day
,hour
,platform
,ver
,item_id
,gender
drop table dwm.mv_main_page_stat_mv_002_local on cluster cluster;
CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_002_local on cluster cluster to dwm.mainpage_stat_mv_local (
day Date comment '数据分区-天'
,hour DateTime comment '数据时间-小时(DateTime)'
,platform String comment '平台 android/ios'
,ver String comment '版本'
,item_id UInt32 comment '物品id'
,gender String comment '性别'
,acta_uv AggregateFunction(uniqCombined,UInt32) comment 'acta_uv'
,acta_cnt SimpleAggregateFunction(sum,UInt64) comment 'acta_cnt'
,actb_uv AggregateFunction(uniqCombined,UInt32) comment 'actb_uv'
,actb_cnt SimpleAggregateFunction(sum,UInt64) comment 'actb_cnt'
,actc_uv AggregateFunction(uniqCombined,UInt32) comment 'actc_uv'
,actc_cnt SimpleAggregateFunction(sum,UInt64) comment 'actc_cnt'
,acta_bm AggregateFunction(groupBitmap,UInt32) comment 'acta_bm'
,actb_bm AggregateFunction(groupBitmap,UInt32) comment 'actb_bm'
,actc_bm AggregateFunction(groupBitmap,UInt32) comment 'actc_bm'
,actd_bm AggregateFunction(groupBitmap,UInt32) comment 'actd_bm'
)
AS
SELECT day
,hour
,platform
,ver
,item_id
,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender
,uniqCombinedStateIf(uid,a.action_a_cnt>0) as acta_uv
,sum(a.action_a_cnt) as acta_cnt
,uniqCombinedStateIf(uid,a.action_b_cnt>0) as actb_uv
,sum(a.action_b_cnt) as actb_cnt
,uniqCombinedStateIf(uid,a.action_c_cnt>0) as actc_uv
,sum(a.action_c_cnt) as actc_cnt
,groupBitmapStateIf(uid,a.action_a_cnt>0) as acta_bm
,groupBitmapStateIf(uid,a.action_b_cnt>0) as actb_bm
,groupBitmapStateIf(uid,a.action_c_cnt>0) as actc_bm
,groupBitmapStateIf(uid,a.action_d_sum>0) as actd_bm
from ods.action_002_local as a
group by
day
,hour
,platform
,ver
,item_id
,gender
操作完成之后就得到了一个物化视图的指标宽表(假设它很宽)。就可以用它来解决一些查询场景。查询场景1：多个日志指标的合并
SELECT
day,
gender,
uniqCombinedMerge(shown_uv) AS shown_uv,
uniqCombinedMerge(click_uv) AS click_uv,
uniqCombinedMerge(acta_uv) AS acta_uv,
uniqCombinedMerge(actb_uv) AS actb_uv,
uniqCombinedMerge(actc_uv) AS actc_uv
FROM dws.mainpage_stat_mv_dis
WHERE day = '2021-06-06'
GROUP BY
day,
gender
Query id: 5d4eed47-78f1-4c22-a2cd-66a6a4db14ab
┌────────day─┬─gender─┬─shown_uv─┬─click_uv─┬─acta_uv─┬─actb_uv─┬─actc_uv─┐
│ 2021-06-06 │ 男 │ 6845 │ 6157 │ 6845 │ 5824 │ 4826 │
│ 2021-06-06 │ 未知 │ 1421 │ 1277 │ 1421 │ 1232 │ 1029 │
│ 2021-06-06 │ 女 │ 6734 │ 6058 │ 6733 │ 5776 │ 4826 │
└────────────┴────────┴──────────┴──────────┴─────────┴─────────┴─────────┘
3 rows in set. Elapsed: 0.025 sec. Processed 48.70 thousand rows, 24.23 MB (1.98 million rows/s., 983.52 MB/s.)
--如果使用join的话 这里因为没有分开创建物化视图，只列举语法，所以也不对性能进行对比。
SELECT
t1.day,
t1.gender,
shown_uv,
click_uv,
acta_uv,
actb_uv,
actc_uv
FROM
(
SELECT
day,
dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,
uniqCombinedIf(uid, a.show_cnt > 0) AS shown_uv,
uniqCombinedIf(uid, a.click_cnt > 0) AS click_uv
FROM dws.action_001_dis AS a
WHERE day = '2021-06-06'
GROUP BY
day,
gender
) AS t1
LEFT JOIN
(
SELECT
day,
dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,
uniqCombinedIf(uid, a.action_a_cnt > 0) AS acta_uv,
uniqCombinedIf(uid, a.action_b_cnt > 0) AS actb_uv,
uniqCombinedIf(uid, a.action_c_cnt > 0) AS actc_uv
FROM dws.action_002_dis AS a
GROUP BY
day,
gender
) AS t2 USING (day, gender)
Query id: 2ab32451-e373-4757-9e25-f089aef1e9f4
┌────────day─┬─gender─┬─shown_uv─┬─click_uv─┬─acta_uv─┬─actb_uv─┬─actc_uv─┐
│ 2021-06-06 │ 男 │ 6845 │ 6209 │ 6845 │ 5824 │ 4826 │
│ 2021-06-06 │ 未知 │ 1421 │ 1283 │ 1421 │ 1232 │ 1029 │
│ 2021-06-06 │ 女 │ 6734 │ 6096 │ 6733 │ 5776 │ 4826 │
└────────────┴────────┴──────────┴──────────┴─────────┴─────────┴─────────┘
3 rows in set. Elapsed: 0.032 sec. Processed 360.36 thousand rows, 5.85 MB (11.11 million rows/s., 180.47 MB/s.)
查询场景2：基于bitmap的用户行为分析。
SELECT
day,
gender,
bitmapCardinality(groupBitmapMergeState(show_bm)) AS shown_uv,
bitmapAndCardinality(groupBitmapMergeState(show_bm), groupBitmapMergeState(click_bm)) AS show_click_uv,
bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm))) AS show_click_a_uv,
bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm)), groupBitmapMergeState(actb_bm))) AS show_click_ab_uv,
bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(bitmapAnd(bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm)), groupBitmapMergeState(actb_bm)), groupBitmapMergeState(actc_bm))) AS show_click_abc_uv,
bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(bitmapAnd(bitmapAnd(bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm)), groupBitmapMergeState(actb_bm)), groupBitmapMergeState(actc_bm)), groupBitmapMergeState(actd_bm))) AS show_click_abcd_uv
FROM dws.mainpage_stat_mv_dis
WHERE day = '2021-06-06'
GROUP BY
day,
gender
Query id: b79de70f-6091-4d0a-9a33-12af8f210931
┌────────day─┬─gender─┬─shown_uv─┬─show_click_uv─┬─show_click_a_uv─┬─show_click_ab_uv─┬─show_click_abc_uv─┬─show_click_abcd_uv─┐
│ 2021-06-06 │ 男 │ 6845 │ 6157 │ 6157 │ 5244 │ 4341 │
4341 │
│ 2021-06-06 │ 未知 │ 1421 │ 1277 │ 1277 │ 1113 │ 928 │
928 │
│ 2021-06-06 │ 女 │ 6734 │ 6058 │ 6057 │ 5211 │ 4367 │
4367 │
└────────────┴────────┴──────────┴───────────────┴─────────────────┴──────────────────┴───────────────────┴────────────────────┘
3 rows in set. Elapsed: 0.052 sec. Processed 48.70 thousand rows, 54.89 MB (944.42 thousand rows/s., 1.06 GB/s.)
还有一些其他用法篇幅有限不展开了，大家自由探索。因为bitmap函数只支持同时输入两个bitmap，所以层级越深需要不断进行合并。不过这个也整合到一个指标，会对基于superset这样的上层平台，配置指标时方便许多，不用通过join实现，也不需要非常多的子查询了，从查询性能上，存储上，都是一个很友好的方案。同时不管是多log分开写多个指标，也可以进行合并写在一个指标，都可以很方便的进行指标整合。
总结
物化视图是clickhouse一个非常重要的功能，同时也做了很多优化和函数扩展，虽然在某些情况可能会带来一定的风险（比如增加错误字段导致写入失败等问题），但是也是可以在使用中留意避免的，不能因噎废食。本文主要讲解了
物化视图的创建、新增维度和指标，聚合函数的使用和一些注意事项； 物化视图结合字典的使用； 通过物化视图组合指标宽表。
欢迎大家指出文章中的问题，我会及时修改。感兴趣的可以顺着文章或者下载代码尝试，同时也欢迎交流clickhouse的相关使用经验和案例分享，一起学习，一起进步。