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 一個非常重要的功能，同時也做了很多優化和函數擴展，雖然在某些情況可能會帶來一定的風險（比如增加錯誤字段導致寫入失敗等問題），但是也是可以在使用中留意避免的，不能因噎廢食。本文主要講解了

1. 物化視圖的創建、新增維度和指標，聚合函數的使用和一些注意事項；

2. 物化視圖結合字典的使用；

3. 通過物化視圖組合指標寬表。

歡迎大家指出文章中的問題，我會及時修改。感興趣的可以順着文章或者下載代碼嘗試，同時也歡迎交流 clickhouse 的相關使用經驗和案例分享，一起學習，一起進步。

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/Yzgx3QQcYCqrr7V9xsxfhQ