图文精华

About云-梭伦科技»专题 › 技术学习(版主发帖区) › 大数据学习 › Hadoop大数据架构 › 总结型› ClickHouse物化视图在微信的实战经验
分享

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

Mirinda 2021-11-11 11:06:38 发表于 总结型 [显示全部楼层] 回帖奖励 阅读模式 关闭右栏 1 1990
问题导读：
1.熟悉ClickHouse吗?
2.ClickHouse的应用场景你能说出几个？
3.文章对你使用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。

  1. create table ods.user_dim_local on cluster cluster

  2. (

  3. day Date comment '数据分区-天',

  4. uid UInt32 default 0 comment 'uid',

  5. platform String default '' comment '平台 android/ios',

  6. country String default '' comment '国家',

  7. province String default '' comment '省及直辖市',

  8. isp String default '' comment '运营商',

  9. app_version String default '' comment '应用版本',

  10. os_version String default '' comment '系统版本',

  11. mac String default '' comment 'mac',

  12. ip String default '' comment 'ip',

  13. gender String default '' comment '性别',

  14. age Int16 default -1 comment '年龄'

  15. )

  16. engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.user_dim_local','{replica}')

  17. PARTITION BY day

  18. PRIMARY KEY day

  19. ORDER BY day

  20. TTL day + toIntervalDay(3) + toIntervalHour(3)

  21. SETTINGS index_granularity = 8192

  22. 

  23. --drop table dim.user_dim_dis on cluster cluster;

  24. create table dim.user_dim_dis on cluster cluster

  25. as ods.user_dim_local

  26. engine=Distributed(cluster,ods,user_dim_local,rand());
复制代码

物品维度数据

通过代码生成100个物品。

  1. create table ods.item_dim_local on cluster cluster

  2. (

  3. day Date comment '数据分区-天',

  4. item_id UInt32 default 0 comment 'item_id',

  5. type_id UInt32 default 0 comment 'type_id',

  6. price UInt32 default 0 comment 'price'

  7. )

  8. engine = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.item_dim_local','{replica}')

  9. PARTITION BY day

  10. PRIMARY KEY day

  11. ORDER BY day

  12. TTL day + toIntervalDay(3) + toIntervalHour(3)

  13. SETTINGS index_granularity = 8192

  14. 

  15. --drop table dim.item_dim_dis on cluster cluster;

  16. create table dim.item_dim_dis on cluster cluster

  17. as ods.item_dim_local

  18. engine=Distributed(cluster,ods,item_dim_local,rand());
复制代码

action_001行为数据

通过代码生成最近3小时的数据，模拟用户的实际访问，主要是曝光、点击、和曝光时间3个指标

  1. --drop table ods.action_001_local on cluster cluster;

  2. create table ods.action_001_local on cluster cluster (

  3. day Date default toDate(second) comment '数据分区-天(Date)'

  4. ,hour DateTime default toStartOfHour(second) comment '数据时间-小时(DateTime)'

  5. ,second DateTime default '1970-01-01 08:00:00' comment '数据时间-秒'

  6. ,insert_second DateTime default now() comment '数据写入时间'

  7. ,platform String default '' comment '平台 android/ios'

  8. ,ip String default '' comment 'client-ip'

  9. ,isp String default '' comment '运营商'

  10. ,uid UInt32 default 0 comment 'uid'

  11. ,ver String default '' comment '版本'

  12. ,item_id UInt32 default 0 comment '物品id'

  13. ,show_cnt UInt32 default 0 comment '曝光次数'

  14. ,click_cnt UInt32 default 0 comment '点击次数'

  15. ,show_time UInt32 default 0 comment '曝光时间'

  16. )

  17. engine=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.action_001_local','{replica}')

  18. PARTITION BY day

  19. PRIMARY KEY (day,hour)

  20. ORDER BY (day,hour,platform,item_id)

  21. TTL day + toIntervalDay(10) + toIntervalHour(4)

  22. SETTINGS index_granularity = 8192

  23. ;

  24. --drop table dws.action_001_dis on cluster cluster;

  25. create table dws.action_001_dis on cluster cluster

  26. as ods.action_001_local

  27. engine=Distributed(cluster,ods,action_001_local,rand());
复制代码

action_002 行为数据

通过代码生成最近3小时的数据，模拟用户点击之后的一些其他操作。这里对指标简单命名。

  1. --drop table ods.action_002_local on cluster cluster;

  2. create table ods.action_002_local on cluster cluster (

  3. day Date default toDate(second) comment '数据分区-天(Date)'

  4. ,hour DateTime default toStartOfHour(second) comment '数据时间-小时(DateTime)'

  5. ,second DateTime default '1970-01-01 08:00:00' comment '数据时间-秒'

  6. ,insert_second DateTime default now() comment '数据写入时间'

  7. ,platform String default '' comment '平台 android/ios'

  8. ,ip String default '' comment 'client-ip'

  9. ,isp String default '' comment '运营商'

  10. ,uid UInt32 default 0 comment 'uid'

  11. ,ver String default '' comment '版本'

  12. ,item_id UInt32 default 0 comment '商品id'

  13. ,action_a_cnt UInt32 default 0 comment 'actionA次数'

  14. ,action_b_cnt UInt32 default 0 comment 'actionB次数'

  15. ,action_c_cnt UInt32 default 0 comment 'actionC次数'

  16. ,action_a_time UInt32 default 0 comment 'actionA时间'

  17. ,action_b_time UInt32 default 0 comment 'actionA时间'

  18. ,action_c_time UInt32 default 0 comment 'actionA时间'

  19. ,action_d_sum UInt32 default 0 comment 'action_d_sum'

  20. ,action_e_sum UInt32 default 0 comment 'action_e_sum'

  21. ,action_f_sum UInt32 default 0 comment 'action_f_sum'

  22. )

  23. engine=ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/ods.action_002_local','{replica}')

  24. PARTITION BY day

  25. PRIMARY KEY (day,hour)

  26. ORDER BY (day,hour,platform,item_id)

  27. TTL day + toIntervalDay(10) + toIntervalHour(4)

  28. SETTINGS index_granularity = 8192

  29. ;

  30. --drop table dws.action_002_dis on cluster cluster;

  31. create table dws.action_002_dis on cluster cluster

  32. as ods.action_002_local

  33. engine=Distributed(cluster,ods,action_002_local,rand());
复制代码

这里准备两份日志，主要是为了文章后半部分提到的物化视图的进阶用法，解决某些join场景。

物化视图的简单case场景

在action_log接入到clickhouse之后，就可以直接通过分布式表去查询了。但是，随着数据量的慢慢积累，比如action_001，它是主页的曝光和点击的数据，一天可能会非常大，百亿级别。这个时候，查一天的去重uv可能还能勉强接受，但是查一周，查一月可能就没法玩了，耗时上巨大，有些也可能超过内存限制。得提速，业务不管你实现方案，要看数据结果，这个时候，物化视图就派上用场了。A产品整理后提了一个诉求，希望可以看到每小时的每个商品的主页统计指标。有时也可能要查1周，1月。经过梳理得到了下面这个需求



时间(最细小时)
商品id
平台
版本
曝光人数



曝光次数



点击人数



点击次数



人均曝光时间



每次平均曝光时间



人均点击次数



ctr




首先，在创建物化视图前评估一下数据量。物化视图会计算当前批次的数据汇总一次，然后根据维度自动merge聚合统计的指标，但是不会跨节点和分区，所以理想状况下，数据量的估算sql为

  1. select uniqCombined(hostName(),hour,item_id,platform,ver)

  2. from dws.action_001
复制代码

经过计算发现，数据量只是原表的1/n，主要取决于数据的重合度，这个只是最完美的理想状态，但是实际上差距也不会很大，已经比原表少很多数据量了。现在需求明确，也估算完数据量了，在这个数据量下，查询1周或者1月的数据是完全可以接受的。开搞物化视图。


创建过程

首先贴下官方文档https://clickhouse.tech/docs/en/ ... ements/create/view/物化视图的创建有两种方式，一种是

  1. CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER]

  2. ENGINE = engine

  3. AS SELECT
复制代码

这种创建物化视图的好处是创建简单，避免自己写错聚合函数类型带来数据上的写入失败。缺点是alter有局限性，每次更改都需要替换或者修改物化视图的计算逻辑，而且也不能实现文章后续的有限替代join场景。第二种方式是先创建一个存储表，存储表是[Replicated]AggregatingMergeTree，然后通过创建的物化视图使用to的方式写入到存储表中，相当于存储的数据和计算的逻辑分为了两张表分别处理。

  1. CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db.]table_name [ON CLUSTER] TO db.]name

  2. AS SELECT
复制代码

因为已经指定了存储的表，所以物化视图的创建也不需要指定engine，在查询中，查物化视图和查实际的存储表得到一样的数据，因为都是来自于同一份存储数据。在建表之前还有个细节，TO db.name 后面的表不一定是本地表对本地表，还可以本地表对分布式表，可以基于shard_key处理一些分桶策略，但是会存在写放大的问题，导致集群写入频率增大，负载提高，可以但是慎用。必须要注意的是，from的表一定是本地表。这里大家区分下存储表和计算表两个名词，后续的场景会用到。以下为完整的建表sql 创建ReplicatedAggregatingMergeTree作为数据存储表和分布式表

  1. --drop table dwm.mainpage_stat_mv_local on cluster cluster;

  2. create table dwm.mainpage_stat_mv_local on cluster cluster

  3. (

  4. day Date comment '数据分区-天'

  5. ,hour DateTime comment '数据时间-小时(DateTime)'

  6. ,platform String comment '平台 android/ios'

  7. ,ver String comment '版本'

  8. ,item_id UInt32 comment '物品id'

  9. ,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'

  10. ,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'

  11. ,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'

  12. ,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'

  13. ,show_time_sum  SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'

  14. )

  15. engine=ReplicatedAggregatingMergeTree('/clickhouse/tables/{layer}-{shard}/dwm.mainpage_stat_mv_local','{replica}')

  16. PARTITION by day

  17. PRIMARY KEY (day,hour)

  18. ORDER by (day,hour,platform,ver,item_id)

  19. TTL day + toIntervalDay(92) + toIntervalHour(5)

  20. SETTINGS index_granularity = 8192

  21. 

  22. --drop table dws.mainpage_stat_mv_dis on cluster cluster

  23. create table dws.mainpage_stat_mv_dis on cluster cluster

  24. as dwm.mainpage_stat_mv_local

  25. engine=Distributed(cluster,dwm,mainpage_stat_mv_local,rand());

  26. 

  27. 创建物化视图作为计算逻辑并使用to将数据流向ReplicatedAggregatingMergeTree

  28. 

  29. create  MATERIALIZED VIEW dwm.mv_main_page_stat_mv_local on cluster cluster to dwm.mainpage_stat_mv_local (

  30. day Date comment '数据分区-天'

  31. ,hour DateTime comment '数据时间-小时(DateTime)'

  32. ,platform String comment '平台 android/ios'

  33. ,ver String comment '版本'

  34. ,item_id UInt32 comment '物品id'

  35. ,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'

  36. ,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'

  37. ,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'

  38. ,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'

  39. ,show_time_sum  SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'

  40. )

  41. AS SELECT day

  42.      ,hour

  43.      ,platform

  44.      ,ver

  45.      ,item_id

  46.      ,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv

  47.      ,sum(a.show_cnt) as show_cnt

  48.      ,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv

  49.      ,sum(a.click_cnt) as click_cnt

  50.      ,sum(toUInt64(show_time/1000)) as show_time_sum

  51. from ods.action_001_local as a

  52. group by

  53.       day

  54.      ,hour

  55.      ,platform

  56.      ,ver

  57.      ,item_id
复制代码

查询数据

  1. SELECT

  2.     day,

  3.     platform,

  4.     uniqCombinedMerge(shown_uv) AS shown_uv,

  5.     sum(shown_cnt) AS shown_cnt,

  6.     uniqCombinedMerge(click_uv) AS click_uv,

  7.     sum(click_cnt) AS click_cnt,

  8.     sum(show_time_sum) AS show_time_sum

  9. FROM dws.mainpage_stat_mv_dis

  10. GROUP BY

  11.     day,

  12.     platform

  13. 

  14. Query id: f6d4d3dd-33f1-408e-92a7-4901fcad50aa

  15. 

  16. ┌────────day─┬─platform─┬─shown_uv─┬─shown_cnt─┬─click_uv─┬─click_cnt─┬─show_time_sum─┐

  17. │ 2021-06-06 │ ios      │     5000 │         0 │     4509 │    554927 │        781679 │

  18. │ 2021-06-05 │ android  │     9613 │         0 │     5249 │    342910 │        491502 │

  19. │ 2021-06-06 │ android  │     9995 │         0 │     8984 │   1126905 │       1570323 │

  20. │ 2021-06-05 │ ios      │     4819 │         0 │     2636 │    175932 │        248274 │

  21. └────────────┴──────────┴──────────┴───────────┴──────────┴───────────┴───────────────┘

  22. 

  23. 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.)
复制代码

处理中的细节

这个地方再细描述下物化视图的处理逻辑，先贴一下官方说明

  1. 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

1android1.1{1001,1002,1003,1004}

2android1.2{1009,1010,1130,1131}

3android1.1{2001,3002,1003,3004}

4android1.2{2009,1010,2130,2131}


在写入到表之后没有merge之前，存储的实际是4个批次的数据，在这个时候进行计算时，计算过程会聚合，这个中间状态会合并，但是这个时候如果直接使用uniqCombined计算这个中间状态会得到什么样的结果呢，我们举例说明下

  1. SELECT

  2.     platform,

  3.     ver,

  4.     uniqCombined(xx)

  5. FROM

  6. (

  7.     SELECT

  8.         platform,

  9.         ver,

  10.         uniqCombinedState(uid) AS xx

  11.     FROM

  12.     (

  13.         SELECT

  14.             a.1 AS platform,

  15.             a.2 AS ver,

  16.             a.3 AS uid

  17.         FROM system.one

  18.         ARRAY JOIN [('android', '1.1', 1001), ('android', '1.1', 1002), ('android', '1.1', 1003), ('android', '1.1', 1004)] AS a

  19.     )

  20.     GROUP BY

  21.         platform,

  22.         ver

  23.     UNION ALL

  24.     SELECT

  25.         platform,

  26.         ver,

  27.         uniqCombinedState(uid) AS xx

  28.     FROM

  29.     (

  30.         SELECT

  31.             a.1 AS platform,

  32.             a.2 AS ver,

  33.             a.3 AS uid

  34.         FROM system.one

  35.         ARRAY JOIN [('android', '1.2', 1009), ('android', '1.2', 1010), ('android', '1.2', 1130), ('android', '1.2', 1131)] AS a

  36.     )

  37.     GROUP BY

  38.         platform,

  39.         ver

  40.     UNION ALL

  41.     SELECT

  42.         platform,

  43.         ver,

  44.         uniqCombinedState(uid) AS xx

  45.     FROM

  46.     (

  47.         SELECT

  48.             a.1 AS platform,

  49.             a.2 AS ver,

  50.             a.3 AS uid

  51.         FROM system.one

  52.         ARRAY JOIN [('android', '1.1', 2001), ('android', '1.1', 3002), ('android', '1.1', 1003), ('android', '1.1', 3004)] AS a

  53.     )

  54.     GROUP BY

  55.         platform,

  56.         ver

  57.     UNION ALL

  58.     SELECT

  59.         platform,

  60.         ver,

  61.         uniqCombinedState(uid) AS xx

  62.     FROM

  63.     (

  64.         SELECT

  65.             a.1 AS platform,

  66.             a.2 AS ver,

  67.             a.3 AS uid

  68.         FROM system.one

  69.         ARRAY JOIN [('android', '1.2', 2009), ('android', '1.2', 1010), ('android', '1.2', 2130), ('android', '1.2', 2131)] AS a

  70.     )

  71.     GROUP BY

  72.         platform,

  73.         ver

  74. )

  75. GROUP BY

  76.     platform,

  77.     ver

  78. 

  79. Query id: 09069556-65a8-42a2-9b0b-c002264a1bb4

  80. 

  81. ┌─platform─┬─ver─┬─uniqCombined(xx)─┐

  82. │ android  │ 1.2 │                2 │

  83. │ android  │ 1.1 │                2 │

  84. └──────────┴─────┴──────────────────┘

  85. 

  86. 2 rows in set. Elapsed: 0.007 sec.
复制代码

这个结果是明显不对的，因为他将这个中间状态也作为了计算的输入重新计算了，所以在使用上一定要注意AggregateFunction中的State状态使用Merge解析才能得到正确的结果。正确的sql

  1. SELECT

  2.     platform,

  3.     ver,

  4.     uniqCombinedMerge(xx) AS uv

  5. FROM

  6. (

  7.     SELECT

  8.         platform,

  9.         ver,

  10.         uniqCombinedState(uid) AS xx

  11.     FROM

  12.     (

  13.         SELECT

  14.             a.1 AS platform,

  15.             a.2 AS ver,

  16.             a.3 AS uid

  17.         FROM system.one

  18.         ARRAY JOIN [('android', '1.1', 1001), ('android', '1.1', 1002), ('android', '1.1', 1003), ('android', '1.1', 1004)] AS a

  19.     )

  20.     GROUP BY

  21.         platform,

  22.         ver

  23.     UNION ALL

  24.     SELECT

  25.         platform,

  26.         ver,

  27.         uniqCombinedState(uid) AS xx

  28.     FROM

  29.     (

  30.         SELECT

  31.             a.1 AS platform,

  32.             a.2 AS ver,

  33.             a.3 AS uid

  34.         FROM system.one

  35.         ARRAY JOIN [('android', '1.2', 1009), ('android', '1.2', 1010), ('android', '1.2', 1130), ('android', '1.2', 1131)] AS a

  36.     )

  37.     GROUP BY

  38.         platform,

  39.         ver

  40.     UNION ALL

  41.     SELECT

  42.         platform,

  43.         ver,

  44.         uniqCombinedState(uid) AS xx

  45.     FROM

  46.     (

  47.         SELECT

  48.             a.1 AS platform,

  49.             a.2 AS ver,

  50.             a.3 AS uid

  51.         FROM system.one

  52.         ARRAY JOIN [('android', '1.1', 2001), ('android', '1.1', 3002), ('android', '1.1', 1003), ('android', '1.1', 3004)] AS a

  53.     )

  54.     GROUP BY

  55.         platform,

  56.         ver

  57.     UNION ALL

  58.     SELECT

  59.         platform,

  60.         ver,

  61.         uniqCombinedState(uid) AS xx

  62.     FROM

  63.     (

  64.         SELECT

  65.             a.1 AS platform,

  66.             a.2 AS ver,

  67.             a.3 AS uid

  68.         FROM system.one

  69.         ARRAY JOIN [('android', '1.2', 2009), ('android', '1.2', 1010), ('android', '1.2', 2130), ('android', '1.2', 2131)] AS a

  70.     )

  71.     GROUP BY

  72.         platform,

  73.         ver

  74. )

  75. GROUP BY

  76.     platform,

  77.     ver

  78. 

  79. Query id: 2a7137a7-f8fb-4b36-a37f-642348ab3ac6

  80. 

  81. ┌─platform─┬─ver─┬─uv─┐

  82. │ android  │ 1.2 │  7 │

  83. │ android  │ 1.1 │  7 │

  84. └──────────┴─────┴────┘

  85. 

  86. 2 rows in set. Elapsed: 0.009 sec.
复制代码

这里使用union all 模拟的是每个批次的写入数据。通过这个case主要是介绍uniqCombined生成中间态和解中间态的过程，避免大家错误使用哈。通过刚才的错误sql也侧面说明了，中间态存储的记录数要小于原表写入的数据，主要是按照group by的字段进行聚合计算得到的。

接着讲第二个场景，pv的计算。一般情况下，pv通常采用sum进行计算，sum计算和uv计算存在一个比较大的差异，那就是结果值可以累加。所以从逻辑上来讲，每批次计算可以直接是结果值，那么在聚合的时候可以再次进行sum操作可以得到正确的结果。那么这个时候除了采用AggregateFunction外存储中间态外也可以选择SimpleAggregateFunction存储每次计算结果，存储开销是不一样的

  1. SELECT byteSize(xx)

  2. FROM

  3. (

  4.     SELECT sumSimpleState(a) AS xx

  5.     FROM

  6.     (

  7.         SELECT 1001 AS a

  8.         UNION ALL

  9.         SELECT 1002 AS a

  10.     )

  11. )

  12. 

  13. Query id: ac6c5354-d59e-49a0-a54f-ea480acc8f3f

  14. 

  15. ┌─byteSize(xx)─┐

  16. │            8 │

  17. └──────────────┘

  18. 

  19. SELECT byteSize(xx)

  20. FROM

  21. (

  22.     SELECT sumState(a) AS xx

  23.     FROM

  24.     (

  25.         SELECT 1001 AS a

  26.         UNION ALL

  27.         SELECT 1002 AS a

  28.     )

  29. )

  30. 

  31. Query id: 01b2ecb5-9e14-4f85-8cc6-5033671560ac

  32. 

  33. ┌─byteSize(xx)─┐

  34. │           16 │

  35. └──────────────┘

复制代码

2倍的存储差距，再来简单测试下查询效率

  1. --SimpleAggregateFunction

  2. SELECT sum(xx)

  3. FROM

  4. (

  5.     SELECT

  6.         a % 1000 AS b,

  7.         sumSimpleState(a) AS xx

  8.     FROM

  9.     (

  10.         SELECT number AS a

  11.         FROM numbers(1000000000)

  12.     )

  13.     GROUP BY b

  14. )

  15. 

  16. Query id: 7c8f4b77-1033-4184-ad2f-1e6719723aca

  17. 

  18. ┌────────────sum(xx)─┐

  19. │ 499999999500000000 │

  20. └────────────────────┘

  21. 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.)

  22. 

  23. --AggregateFunction

  24. SELECT sumMerge(xx)

  25. FROM

  26. (

  27.     SELECT

  28.         a % 1000 AS b,

  29.         sumState(a) AS xx

  30.     FROM

  31.     (

  32.         SELECT number AS a

  33.         FROM numbers(1000000000)

  34.     )

  35.     GROUP BY b

  36. )

  37. 

  38. Query id: 401c0a9f-30fe-4d9a-88b0-1a33ffcf4f43

  39. 

  40. ┌───────sumMerge(xx)─┐

  41. │ 499999999500000000 │

  42. └────────────────────┘

  43. 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如下:

  1. --创建user字典

  2. CREATE DICTIONARY dim.dict_user_dim on cluster cluster (

  3. uid UInt64 ,

  4. platform String default '' ,

  5. country String default '' ,

  6. province String default '' ,

  7. isp String default '' ,

  8. app_version String default '' ,

  9. os_version String default '',

  10. mac String default '' ,

  11. ip String default '',

  12. gender String default '',

  13. age Int16 default -1

  14. ) PRIMARY KEY uid

  15. SOURCE(

  16.   CLICKHOUSE(

  17.     HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'dim' TABLE 'user_dim_dis'

  18.   )

  19. ) LIFETIME(MIN 1800 MAX 3600) LAYOUT(HASHED());

  20. 

  21. --创建item字典

  22. CREATE DICTIONARY dim.dict_item_dim on cluster cluster (

  23. item_id UInt64 ,

  24. type_id UInt32 default 0,

  25. price UInt32 default 0

  26. ) PRIMARY KEY item_id

  27. SOURCE(

  28.   CLICKHOUSE(

  29.     HOST 'localhost' PORT 9000 USER 'default' PASSWORD '' DB 'dim' TABLE 'item_dim_dis'

  30.   )

  31. ) 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这样。

  1. --单value方法1：

  2. SELECT

  3.     dictGet('dim.dict_user_dim', 'platform', toUInt64(uid)) AS platform,

  4.     uniqCombined(uid) AS uv

  5. FROM dws.action_001_dis

  6. WHERE day = '2021-06-05'

  7. GROUP BY platform

  8. 

  9. Query id: 52234955-2dc9-4117-9f2a-45ab97249ea7

  10. 

  11. ┌─platform─┬───uv─┐

  12. │ android  │ 9624 │

  13. │ ios      │ 4830 │

  14. └──────────┴──────┘

  15. 

  16. 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.)

  17. 

  18. --多value方法1：

  19. SELECT

  20.     dictGet('dim.dict_user_dim', 'platform', toUInt64(uid)) AS platform,

  21.     dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,

  22.     uniqCombined(uid) AS uv

  23. FROM dws.action_001_dis

  24. WHERE day = '2021-06-05'

  25. GROUP BY

  26.     platform,

  27.     gender

  28. 

  29. Query id: ed255ee5-9036-4385-9a51-35923fef6e48

  30. 

  31. ┌─platform─┬─gender─┬───uv─┐

  32. │ ios      │ 男     │ 2236 │

  33. │ android  │ 女     │ 4340 │

  34. │ android  │ 未知   │  941 │

  35. │ android  │ 男     │ 4361 │

  36. │ ios      │ 女     │ 2161 │

  37. │ ios      │ 未知   │  433 │

  38. └──────────┴────────┴──────┘

  39. 

  40. 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.)

  41. --单value方法2:

  42. SELECT

  43.     t2.platform AS platform,

  44.     uniqCombined(t1.uid) AS uv

  45. FROM dws.action_001_dis AS t1

  46. INNER JOIN dim.dict_user_dim AS t2 ON toUInt64(t1.uid) = t2.uid

  47. WHERE day = '2021-06-05'

  48. GROUP BY platform

  49. 

  50. Query id: 8906e637-475e-4386-946e-29e1690f07ea

  51. 

  52. ┌─platform─┬───uv─┐

  53. │ android  │ 9624 │

  54. │ ios      │ 4830 │

  55. └──────────┴──────┘

  56. 

  57. 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.)

  58. 

  59. --多value方法2:

  60. SELECT

  61.     t2.platform AS platform,

  62.     t2.gender AS gender,

  63.     uniqCombined(t1.uid) AS uv

  64. FROM dws.action_001_dis AS t1

  65. INNER JOIN dim.dict_user_dim AS t2 ON toUInt64(t1.uid) = t2.uid

  66. WHERE day = '2021-06-05'

  67. GROUP BY

  68.     platform,

  69.     gender

  70. 

  71. Query id: 88ef55a6-ddcc-42f8-8ce3-5e3bb639b38a

  72. 

  73. ┌─platform─┬─gender─┬───uv─┐

  74. │ ios      │ 男     │ 2236 │

  75. │ android  │ 女     │ 4340 │

  76. │ android  │ 未知   │  941 │

  77. │ android  │ 男     │ 4361 │

  78. │ ios      │ 女     │ 2161 │

  79. │ ios      │ 未知   │  433 │

  80. └──────────┴────────┴──────┘

  81. 

  82. 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如下:

  1. --新增维度并添加到索引

  2. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists gender String comment '性别' after item_id,modify order by

  3. (day,hour,platform,ver,item_id,gender);

  4. alter table dwm.mainpage_stat_mv_local on cluster cluster modify column if exists gender String default '未知' comment '性别' after item_id;

  5. alter table dws.mainpage_stat_mv_dis on cluster cluster add column if not exists gender String comment '性别' after item_id;

  6. 

  7. --新增指标

  8. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数';

  9. alter table dws.mainpage_stat_mv_dis on cluster cluster add column if not exists show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数';
复制代码

修改物化视图计算逻辑

  1. drop TABLE dwm.mv_main_page_stat_mv_local on cluster cluster;

  2. CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_local on cluster cluster to dwm.mainpage_stat_mv_local (

  3. day Date comment '数据分区-天'

  4. ,hour DateTime comment '数据时间-小时(DateTime)'

  5. ,platform String comment '平台 android/ios'

  6. ,ver String comment '版本'

  7. ,item_id UInt32 comment '物品id'

  8. ,gender String  comment '性别'

  9. ,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'

  10. ,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'

  11. ,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'

  12. ,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'

  13. ,show_time_sum  SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'

  14. ,show_time_median AggregateFunction(medianExact,UInt32) comment '曝光时长中位数'

  15. )

  16. AS

  17. SELECT day

  18.      ,hour

  19.      ,platform

  20.      ,ver

  21.      ,item_id

  22.      ,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender

  23.      ,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv

  24.      ,sum(a.show_cnt) as show_cnt

  25.      ,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv

  26.      ,sum(a.click_cnt) as click_cnt

  27.      ,sum(toUInt64(show_time/1000)) as show_time_sum

  28.      ,medianExactState(toUInt32(show_time/1000)) as show_time_median

  29. from ods.action_001_local as a

  30. group by

  31.       day

  32.      ,hour

  33.      ,platform

  34.      ,ver

  35.      ,item_id

  36.      ,gender

复制代码

通过这个case主要讲了三个方面，一是外部字典的创建和使用，二是物化视图的增加维度和指标，三物化视图结合字典进行增维。

物化视图的再进阶

本文在创建log的时候创建了2个log，在上面的case中只用到了一个，接下来的case主要讲一个物化视图的进一步用法。

背景

很多时候，我们的日志上报并不是在一个日志中的，比如上文中创建的action_001和action_002，一个是主页物品的曝光和点击，一个是点击进行物品详情的其他行为。这个时候，产品提了一个诉求，希望可以知道曝光到点击，点击到某个更一步的行为的用户转换率。我们最常规的方法是，使用join去将结果关联，这里只是两个log，那么后续有非常多的log，写起join来就会相当麻烦，甚至会有上千行代码去作逻辑处理，效率上也会差很多。所以就衍生了接下来主要讲的用法，基于物化视图实现有限join场景。主要是多个不同日志指标的合并。其实更应该理解为union all max。

可行性分析

物化视图在每批次写入数据之后，后台会按照聚合key进行merge操作，将相同维度的数据的记录聚合在一起，降低数据量，提高查询效率。如果在这一批数据，没有满足条件的列(if组合器)或者并没有写这一指标(指定字段写)，那么指标会怎么存，如果下一批数据写入数据，那么这两批数据的这个指标，会怎么样？答案是存可迭代的空数据(注意这里的不写，存的数据不能理解为null)，同时可以和其他批数据进行合并，没有数据的行会被忽略。

举个例子:

  1. CREATE TABLE test.mv_union_max

  2. (

  3.     `id` UInt32,

  4.     `m1` AggregateFunction(uniqCombined, UInt32),

  5.     `m2` AggregateFunction(sum, UInt32)

  6. )

  7. ENGINE = AggregatingMergeTree

  8. ORDER BY id

  9. 

  10. Query id: 20dcd6cb-e336-4da8-9033-de42527d2bf0

  11. 

  12. Ok.

  13. 

  14. 0 rows in set. Elapsed: 0.103 sec.

  15. 

  16. # 写入数据(这里需要注意指定字段写)

  17. INSERT INTO test.mv_union_max (id, m1) SELECT

  18.     id,

  19.     uniqCombinedState(uid) AS m1

  20. FROM

  21. (

  22.     SELECT

  23.         a1.1 AS id,

  24.         toUInt32(a1.2) AS uid

  25.     FROM system.one

  26.     ARRAY JOIN [(1, 10001), (2, 10002), (3, 10003), (3, 10001)] AS a1

  27. )

  28. GROUP BY id

  29. 

  30. Query id: f04953f6-3d8a-40a6-bf7e-5b15fe936488

  31. 

  32. Ok.

  33. 

  34. 0 rows in set. Elapsed: 0.003 sec.

  35. 

  36. SELECT *

  37. FROM test.mv_union_max

  38. 

  39. Query id: af592a63-b17d-4764-9a65-4ab33e122d81

  40. 

  41. ┌─id─┬─m1──┬─m2─┐

  42. │  1 │ l��

  43.                │    │

  44. │  2 │ $a6� │    │

  45. │  3 │ ��Gwl��

  46.                  │    │

  47. └────┴─────┴────┘

  48. 

  49. 3 rows in set. Elapsed: 0.002 sec.
复制代码

在写入m1指标后显示有3条记录，其中m2为空数据（这里需要注意的是，m2不是null)，如下：

  1. SELECT isNotNull(m2)

  2. FROM test.mv_union_max

  3. 

  4. Query id: b1ac77df-af77-4f2e-9368-2573a7214c99

  5. 

  6. ┌─isNotNull(m2)─┐

  7. │             1 │

  8. │             1 │

  9. │             1 │

  10. └───────────────┘

  11. 

  12. 3 rows in set. Elapsed: 0.002 sec.

  13. 

  14. SELECT toTypeName(m2)

  15. FROM test.mv_union_max

  16. 

  17. Query id: fcb15349-4a33-4253-bf64-37f5dc7078ea

  18. 

  19. ┌─toTypeName(m2)─────────────────┐

  20. │ AggregateFunction(sum, UInt32) │

  21. │ AggregateFunction(sum, UInt32) │

  22. │ AggregateFunction(sum, UInt32) │

  23. └────────────────────────────────┘

  24. 

  25. 3 rows in set. Elapsed: 0.002 sec.

  26. 

  27. 这个时候再写入m2指标，不写入m1指标，那么会发生什么情况。

  28. 

  29. SELECT *

  30. FROM test.mv_union_max

  31. 

  32. Query id: 7eaa2d42-c50e-4467-9dca-55a0b5eab579

  33. 

  34. ┌─id─┬─m1──┬─m2─┐

  35. │  1 │ l��

  36.                │    │

  37. │  2 │ $a6� │    │

  38. │  3 │ ��Gwl��

  39.                  │    │

  40. └────┴─────┴────┘

  41. ┌─id─┬─m1─┬─m2─┐

  42. │  1 │    │ �   │

  43. │  2 │    │ '  │

  44. │  3 │    │ '  │

  45. └────┴────┴────┘

  46. 

  47. 6 rows in set. Elapsed: 0.003 sec.
复制代码

存了6条记录，分别上两次写入的数据。在手动触发merge之前先确认下，查询的数据是否是正确的。

  1. SELECT

  2.     id,

  3.     uniqCombinedMerge(m1) AS m1,

  4.     sumMerge(m2) AS m2

  5. FROM test.mv_union_max

  6. GROUP BY id

  7. 

  8. Query id: 3f92106a-1b72-4d86-ab74-59c7ac53c202

  9. 

  10. ┌─id─┬─m1─┬────m2─┐

  11. │  3 │  2 │ 10001 │

  12. │  2 │  1 │ 10001 │

  13. │  1 │  1 │  2003 │

  14. └────┴────┴───────┘

  15. 

  16. 3 rows in set. Elapsed: 0.003 sec.
复制代码

数据完全正确，首先可以确认的是，就算不后台merge，查询数据是完全符合需求的。

  1. OPTIMIZE TABLE test.mv_union_max FINAL

  2. 

  3. Query id: 62465025-da30-4df0-a597-18c0c4eb1b2f

  4. 

  5. Ok.

  6. 

  7. 0 rows in set. Elapsed: 0.001 sec.

  8. 

  9. cluster-shard1-ck01 :) select * from test.mv_union_max ;

  10. 

  11. SELECT *

  12. FROM test.mv_union_max

  13. 

  14. Query id: f7fb359f-3860-4598-b766-812ac2f65755

  15. 

  16. ┌─id─┬─m1──┬─m2─┐

  17. │  1 │ l��

  18.                │ �   │

  19. │  2 │ $a6� │ '  │

  20. │  3 │ ��Gwl��

  21.                  │ '  │

  22. └────┴─────┴────┘

  23. 

  24. 3 rows in set. Elapsed: 0.002 sec.

  25. SELECT

  26.     id,

  27.     uniqCombinedMerge(m1) AS m1,

  28.     sumMerge(m2) AS m2

  29. FROM test.mv_union_max

  30. GROUP BY id

  31. 

  32. Query id: 2543a145-e540-43dc-8754-101ebb294b5d

  33. 

  34. ┌─id─┬─m1─┬────m2─┐

  35. │  3 │  2 │ 10001 │

  36. │  2 │  1 │ 10001 │

  37. │  1 │  1 │  2003 │

  38. └────┴────┴───────┘

  39. 

  40. 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有些长):

  1. --物化视图存储表新增指标

  2. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_uv AggregateFunction(uniqCombined,UInt32) comment 'acta_uv';

  3. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_cnt SimpleAggregateFunction(sum,UInt64) comment 'acta_cnt';

  4. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_uv AggregateFunction(uniqCombined,UInt32) comment 'actb_uv';

  5. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_cnt SimpleAggregateFunction(sum,UInt64) comment 'actb_cnt';

  6. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_uv AggregateFunction(uniqCombined,UInt32) comment 'actc_uv';

  7. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_cnt SimpleAggregateFunction(sum,UInt64) comment 'actc_cnt';

  8. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists show_bm AggregateFunction(groupBitmap,UInt32) comment 'show_bm';

  9. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists click_bm AggregateFunction(groupBitmap,UInt32) comment 'click_bm';

  10. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists acta_bm AggregateFunction(groupBitmap,UInt32) comment 'acta_bm';

  11. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actb_bm AggregateFunction(groupBitmap,UInt32) comment 'actb_bm';

  12. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actc_bm AggregateFunction(groupBitmap,UInt32) comment 'actc_bm';

  13. alter table dwm.mainpage_stat_mv_local on cluster cluster add column if not exists actd_bm AggregateFunction(groupBitmap,UInt32) comment 'actd_bm';

  14. --物化视图计算表重建 因为medianExact 耗时较大，接下来的case里去掉了。

  15. drop TABLE dwm.mv_main_page_stat_mv_local on cluster cluster;

  16. CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_001_local on cluster cluster to dwm.mainpage_stat_mv_local (

  17. day Date comment '数据分区-天'

  18. ,hour DateTime comment '数据时间-小时(DateTime)'

  19. ,platform String comment '平台 android/ios'

  20. ,ver String comment '版本'

  21. ,item_id UInt32 comment '物品id'

  22. ,gender String  comment '性别'

  23. ,shown_uv AggregateFunction(uniqCombined,UInt32) comment '曝光人数'

  24. ,shown_cnt SimpleAggregateFunction(sum,UInt64) comment '曝光次数'

  25. ,click_uv AggregateFunction(uniqCombined,UInt32) comment '点击人数'

  26. ,click_cnt SimpleAggregateFunction(sum,UInt64) comment '点击次数'

  27. ,show_time_sum  SimpleAggregateFunction(sum,UInt64) comment '总曝光时间/秒'

  28. ,show_bm AggregateFunction(groupBitmap,UInt32) comment 'show_bm'

  29. ,click_bm AggregateFunction(groupBitmap,UInt32) comment 'click_bm'

  30. )

  31. AS

  32. SELECT day

  33.      ,hour

  34.      ,platform

  35.      ,ver

  36.      ,item_id

  37.      ,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender

  38.      ,uniqCombinedStateIf(uid,a.show_cnt>0) as shown_uv

  39.      ,sum(a.show_cnt) as show_cnt

  40.      ,uniqCombinedStateIf(uid,a.click_cnt>0) as click_uv

  41.      ,sum(a.click_cnt) as click_cnt

  42.      ,sum(toUInt64(show_time/1000)) as show_time_sum

  43.      ,groupBitmapStateIf(uid,a.show_cnt>0) as show_bm

  44.      ,groupBitmapStateIf(uid,a.click_cnt>0) as click_bm

  45. from ods.action_001_local as a

  46. group by

  47.       day

  48.      ,hour

  49.      ,platform

  50.      ,ver

  51.      ,item_id

  52.      ,gender

  53. 

  54. drop table dwm.mv_main_page_stat_mv_002_local on cluster cluster;

  55. CREATE MATERIALIZED VIEW dwm.mv_main_page_stat_mv_002_local on cluster cluster to dwm.mainpage_stat_mv_local (

  56. day Date comment '数据分区-天'

  57. ,hour DateTime comment '数据时间-小时(DateTime)'

  58. ,platform String comment '平台 android/ios'

  59. ,ver String comment '版本'

  60. ,item_id UInt32 comment '物品id'

  61. ,gender String  comment '性别'

  62. ,acta_uv AggregateFunction(uniqCombined,UInt32) comment 'acta_uv'

  63. ,acta_cnt SimpleAggregateFunction(sum,UInt64) comment 'acta_cnt'

  64. ,actb_uv AggregateFunction(uniqCombined,UInt32) comment 'actb_uv'

  65. ,actb_cnt SimpleAggregateFunction(sum,UInt64) comment 'actb_cnt'

  66. ,actc_uv AggregateFunction(uniqCombined,UInt32) comment 'actc_uv'

  67. ,actc_cnt SimpleAggregateFunction(sum,UInt64) comment 'actc_cnt'

  68. ,acta_bm AggregateFunction(groupBitmap,UInt32) comment 'acta_bm'

  69. ,actb_bm AggregateFunction(groupBitmap,UInt32) comment 'actb_bm'

  70. ,actc_bm AggregateFunction(groupBitmap,UInt32) comment 'actc_bm'

  71. ,actd_bm AggregateFunction(groupBitmap,UInt32) comment 'actd_bm'

  72. )

  73. AS

  74. SELECT day

  75.      ,hour

  76.      ,platform

  77.      ,ver

  78.      ,item_id

  79.      ,dictGet('dim.dict_user_dim', 'gender',toUInt64(uid)) as gender

  80.      ,uniqCombinedStateIf(uid,a.action_a_cnt>0) as acta_uv

  81.      ,sum(a.action_a_cnt) as acta_cnt

  82.      ,uniqCombinedStateIf(uid,a.action_b_cnt>0) as actb_uv

  83.      ,sum(a.action_b_cnt) as actb_cnt

  84.      ,uniqCombinedStateIf(uid,a.action_c_cnt>0) as actc_uv

  85.      ,sum(a.action_c_cnt) as actc_cnt

  86.      ,groupBitmapStateIf(uid,a.action_a_cnt>0) as acta_bm

  87.      ,groupBitmapStateIf(uid,a.action_b_cnt>0) as actb_bm

  88.      ,groupBitmapStateIf(uid,a.action_c_cnt>0) as actc_bm

  89.      ,groupBitmapStateIf(uid,a.action_d_sum>0) as actd_bm

  90. from ods.action_002_local as a

  91. group by

  92.       day

  93.      ,hour

  94.      ,platform

  95.      ,ver

  96.      ,item_id

  97.      ,gender

  98. 

  99. 操作完成之后就得到了一个物化视图的指标宽表(假设它很宽)。就可以用它来解决一些查询场景。查询场景1：多个日志指标的合并

  100. 

  101. SELECT

  102.     day,

  103.     gender,

  104.     uniqCombinedMerge(shown_uv) AS shown_uv,

  105.     uniqCombinedMerge(click_uv) AS click_uv,

  106.     uniqCombinedMerge(acta_uv) AS acta_uv,

  107.     uniqCombinedMerge(actb_uv) AS actb_uv,

  108.     uniqCombinedMerge(actc_uv) AS actc_uv

  109. FROM dws.mainpage_stat_mv_dis

  110. WHERE day = '2021-06-06'

  111. GROUP BY

  112.     day,

  113.     gender

  114. 

  115. Query id: 5d4eed47-78f1-4c22-a2cd-66a6a4db14ab

  116. 

  117. ┌────────day─┬─gender─┬─shown_uv─┬─click_uv─┬─acta_uv─┬─actb_uv─┬─actc_uv─┐

  118. │ 2021-06-06 │ 男     │     6845 │     6157 │    6845 │    5824 │    4826 │

  119. │ 2021-06-06 │ 未知   │     1421 │     1277 │    1421 │    1232 │    1029 │

  120. │ 2021-06-06 │ 女     │     6734 │     6058 │    6733 │    5776 │    4826 │

  121. └────────────┴────────┴──────────┴──────────┴─────────┴─────────┴─────────┘

  122. 

  123. 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.)

  124. 

  125. --如果使用join的话 这里因为没有分开创建物化视图，只列举语法，所以也不对性能进行对比。

  126. SELECT

  127.     t1.day,

  128.     t1.gender,

  129.     shown_uv,

  130.     click_uv,

  131.     acta_uv,

  132.     actb_uv,

  133.     actc_uv

  134. FROM

  135. (

  136.     SELECT

  137.         day,

  138.         dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,

  139.         uniqCombinedIf(uid, a.show_cnt > 0) AS shown_uv,

  140.         uniqCombinedIf(uid, a.click_cnt > 0) AS click_uv

  141.     FROM dws.action_001_dis AS a

  142.     WHERE day = '2021-06-06'

  143.     GROUP BY

  144.         day,

  145.         gender

  146. ) AS t1

  147. LEFT JOIN

  148. (

  149.     SELECT

  150.         day,

  151.         dictGet('dim.dict_user_dim', 'gender', toUInt64(uid)) AS gender,

  152.         uniqCombinedIf(uid, a.action_a_cnt > 0) AS acta_uv,

  153.         uniqCombinedIf(uid, a.action_b_cnt > 0) AS actb_uv,

  154.         uniqCombinedIf(uid, a.action_c_cnt > 0) AS actc_uv

  155.     FROM dws.action_002_dis AS a

  156.     GROUP BY

  157.         day,

  158.         gender

  159. ) AS t2 USING (day, gender)

  160. 

  161. Query id: 2ab32451-e373-4757-9e25-f089aef1e9f4

  162. 

  163. ┌────────day─┬─gender─┬─shown_uv─┬─click_uv─┬─acta_uv─┬─actb_uv─┬─actc_uv─┐

  164. │ 2021-06-06 │ 男     │     6845 │     6209 │    6845 │    5824 │    4826 │

  165. │ 2021-06-06 │ 未知   │     1421 │     1283 │    1421 │    1232 │    1029 │

  166. │ 2021-06-06 │ 女     │     6734 │     6096 │    6733 │    5776 │    4826 │

  167. └────────────┴────────┴──────────┴──────────┴─────────┴─────────┴─────────┘

  168. 

  169. 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.)

  170. 

  171. 查询场景2：基于bitmap的用户行为分析。

  172. 

  173. SELECT

  174.     day,

  175.     gender,

  176.     bitmapCardinality(groupBitmapMergeState(show_bm)) AS shown_uv,

  177.     bitmapAndCardinality(groupBitmapMergeState(show_bm), groupBitmapMergeState(click_bm)) AS show_click_uv,

  178.     bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm))) AS show_click_a_uv,

  179.     bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm)), groupBitmapMergeState(actb_bm))) AS show_click_ab_uv,

  180.     bitmapAndCardinality(groupBitmapMergeState(show_bm), bitmapAnd(bitmapAnd(bitmapAnd(groupBitmapMergeState(click_bm), groupBitmapMergeState(acta_bm)), groupBitmapMergeState(actb_bm)), groupBitmapMergeState(actc_bm))) AS show_click_abc_uv,

  181.     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

  182. FROM dws.mainpage_stat_mv_dis

  183. WHERE day = '2021-06-06'

  184. GROUP BY

  185.     day,

  186.     gender

  187. 

  188. Query id: b79de70f-6091-4d0a-9a33-12af8f210931

  189. 

  190. ┌────────day─┬─gender─┬─shown_uv─┬─show_click_uv─┬─show_click_a_uv─┬─show_click_ab_uv─┬─show_click_abc_uv─┬─show_click_abcd_uv─┐

  191. │ 2021-06-06 │ 男     │     6845 │          6157 │            6157 │             5244 │              4341 │

  192.   4341 │

  193. │ 2021-06-06 │ 未知   │     1421 │          1277 │            1277 │             1113 │               928 │

  194.    928 │

  195. │ 2021-06-06 │ 女     │     6734 │          6058 │            6057 │             5211 │              4367 │

  196.   4367 │

  197. └────────────┴────────┴──────────┴───────────────┴─────────────────┴──────────────────┴───────────────────┴────────────────────┘

  198. 

  199. 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的相关使用经验和案例分享，一起学习，一起进步。


最新经典文章，欢迎关注公众号

原文链接：https://www.modb.pro/db/70716


回复

使用道具 举报

自定义广告语

已有(1)人评论

跳转到指定楼层
正序浏览
高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

发表新帖
Mirinda

超级版主

关注

  • 123

    主题

  • 125

    帖子

  • 11

    粉丝

TA的主题
关闭

推荐上一条 /2 下一条

关于我们· 联系我们· 加入我们· 小黑屋· 合作伙伴   

Copyright © 2001-2024 About云-梭伦科技 Powered by Discuz! X3.4 Licensed Discuz Team.

简书 / Smrz 京ICP备2020039040号 Wxb 简书网举报电话：021-34700000