ClickHouse 物化视图在微信的实战经验

标签： dev | 发表时间：2021-06-14 00:00 | 作者：
出处：http://itindex.net/relian

前言

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一个非常重要的功能，同时也做了很多优化和函数扩展，虽然在某些情况可能会带来一定的风险（比如增加错误字段导致写入失败等问题），但是也是可以在使用中留意避免的，不能因噎废食。本文主要讲解了

  1. 物化视图的创建、新增维度和指标，聚合函数的使用和一些注意事项；
  2. 物化视图结合字典的使用；
  3. 通过物化视图组合指标宽表。

欢迎大家指出文章中的问题，我会及时修改。感兴趣的可以顺着文章或者下载代码尝试，同时也欢迎交流clickhouse的相关使用经验和案例分享，一起学习，一起进步。

相关 [clickhouse 物化 视图] 推荐：

ClickHouse 物化视图在微信的实战经验

- - IT瘾-dev
ClickHouse广泛用于用户和系统日志查询场景中，借助腾讯云提供基础设施，微信也在分阶段逐步推进clickhouse的建设和应用，目前作为基础建设的一部分，主要针对于OLAP场景，为业务方提供稳定高效的查询服务. 在业务场景下，实时事件流上报可能会在不同的日志，以不同的格式、途径写入到clickhouse.

ClickHouse Better Practices

- - 简书首页
经过一个月的调研和快速试错，我们的ClickHouse集群已经正式投入生产环境，在此过程中总结出了部分有用的经验，现记录如下. 看官可去粗取精，按照自己项目中的实际情况采纳之. （版本为19.16.14.65）. 因为我们引入ClickHouse的时间并不算长，还有很多要探索的，因此不敢妄称“最佳实践”，还是叫做“更佳实践”比较好吧.

blong/clickhouse .md at master · xingxing9688/blong · GitHub

- -
https://clickhouse.yandex/tutorial.html快速搭建集群参考. https://clickhouse.yandex/reference_en.html官网文档. https://habrahabr.ru/company/smi2/blog/317682/关于集群配置参考.

开源OLAP引擎综评：HAWQ、Presto、ClickHouse

- - InfoQ推荐
谈到大数据就会联想到Hadoop、Spark整个生态的技术栈. 大家都知道开源大数据组件种类众多，其中开源OLAP引擎包含Hive、SparkSQL、Presto、HAWQ、ClickHouse、Impala、Kylin等. 当前企业对大数据的研究与应用日趋理性，那么，如何根据业务特点，选择一个适合自身场景的查询引擎呢.

ClickHouse 权限控制与资源隔离

- - IT瘾-dev
使用clickhouse多半应用在实时数仓项目来支持adhoc查询，为了确保企业数据安全高效的使用，那么权限控制与资源隔离是必不可少的. clickhouse在20.4之后的版本开始支持基于RBAC的访问控制管理；主要包括的功能有：用户创建、角色创建、权限管理以及资源隔离；接下来我们将演示如何使用这些功能.

Greenplum VS ClickHouse (单表11亿数据)

- -
公司的一个报表业务，数据量比较大，用户使用频繁. 为了更好的用户体验，我们之前尝试过多种技术：MongoDB、ElasticSearch、Greenplum 等，但是一直没办法做到大部分查询秒级响应. 前段时间探索了很多大数据产品，无意中发现 ClickHouse，很快就被其极致的性能所吸引. 在一番实验和研究后，我们决定用 ClickHouse 解决这个历史债务.

ClickHouse使用实践与规范

- - IT瘾-dev
ClickHouse作为一款开源列式数据库管理系统（DBMS）近年来备受关注，主要用于数据分析（OLAP）领域. 作者根据以往经验和遇到的问题，总结出一些基本的开发和使用规范，以供使用者参考. 随着公司业务数据量日益增长，数据处理场景日趋复杂，急需一种具有高可用性和高性能的数据库来支持业务发展，ClickHouse是俄罗斯的搜索公司Yandex开源的MPP架构的分析引擎，号称比事务数据库快100-1000倍，最大的特色是高性能的向量化执行引擎，而且功能丰富、可靠性高.

物化视图详解(原创)

- - ITeye博客
物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表. 物化视图存储基于远程表的数据，也可以称为快照. 物化视图可以基于表查询，视图和其它的物化视图. 通常情况下，在复制环境下，物化视图被称为主表，在数据仓库中称为明细表. 对于复制，物化视图允许你在本地维护远程数据的副本,这些副本是只读的.

ClickHouse 在实时场景的应用和优化

- - InfoQ推荐
在介绍实时场景之前，我先简单讲一下早期的离线数据是如何支持的：. 在第一场分享中，技术负责人陈星介绍了 ClickHouse 在字节跳动内部最早支持的两个业务场景，用户行为分析平台和敏捷 BI 平台. 这两个平台的数据主要由分析师或者数仓同学产出，以 T+1 的离线指标为主. 考虑到 ClickHouse 并不支持事务，为了保障数据的一致性，我们在 ClickHouse 系统外实现了一套外部事务：.

clickhouse入门基础知识了解 - 简书

- -
随着业务的迅猛增长，Yandex.Metrica目前已经成为世界第三大Web流量分析平台，每天处理超过200亿个跟踪事件. 能够拥有如此惊人的体量，在它背后提供支撑的ClickHouse功不可没. ClickHouse已经为Yandex.Metrica存储了超过20万亿行的数据，90%的自定义查询能够在1秒内返回，其集群规模也超过了400台服务器.