Clickhouse中的預聚合引擎

疾風老頭發表於2022-03-16

作者: LemonNan

原文地址: https://mp.weixin.qq.com/s/qXlmGTr4C1NjodXeM4V9pA

注: 轉載需註明作者及原文地址

介紹

本文將介紹 Clickhouse 中的 SummingMergeTree 以及 AggregatingMergeTree 預聚合引擎，它們均繼承自 MergeTree ，屬於 MergeTree 引擎家族，關於 MergeTree 還沒有看過的朋友可以先看一下之前的文章：MergeTree 索引原理，這裡就只討論這兩個引擎的使用。

SummingMergeTree

SummingMergeTree 引擎會在資料插入後，定期進行合併，Clickhouse 會將同一個分割槽內相同主鍵的資料會合併成一行，如果同時存在多個分割槽，則非常可能存在多行相同主鍵的資料，所以在進行查詢的時候，需要使用 sum() 以及 group by 進行聚合。一個主鍵如果對應非常多的資料行，使用 SummingMergeTree 能 非常有效的減少資料儲存所佔用的空間（僅有預聚合引擎表的情況）以及加快聚合查詢。

合併規則

  • 預設為除主鍵外所有的數值型別欄位合併求和，列的集合引數由表定義時 SummingMergeTree([columns]) 的column 決定，column 欄位不允許出現在主鍵中以及必須為數值型別，如果建表時沒有指定 column ，則預設為 除主鍵外所有的數值型別欄位
  • 如果合併時所有列中的資料都為0，該主鍵行資料將會被刪除
  • 如果列不在主鍵中，且不在彙總欄位中，則從現有的列中隨機選一個值
  • 不會合並位於主鍵中的欄位

舉個?

這裡拿一個使用者的購買記錄作為例子，包含欄位有：時間、使用者id、價格、物品id

資料建表

# 建表sql
create database if not exists test;
create table if not exists test.shopping_record(
	`shop_time` DateTime64(3, 'UTC') COMMENT '購買時間',
	`user_id` String COMMENT '使用者id',
  `price` Decimal(6,2) COMMENT '購買價格',
  `product_id` String COMMENT '物品id'
) ENGINE = SummingMergeTree(price)
partition by toYYYYMM(shop_time)
order by (toYYYYMMDD(shop_time), user_id)

插入資料

# 使用者1購買記錄
insert into shopping_record values ('2022-02-23 16:43:33.000','user_1',70,'product_1'),('2022-02-23 16:43:22.000','user_1',20.33,'product_1'),('2022-02-23 17:43:44.000','user_1',433.99,'product_1'),('2022-03-10 18:43:55.000','user_1',76.23,'product_1'),('2022-03-11 19:43:15.000','user_1',99,'product_1'),('2022-03-10 20:43:32.000','user_1',37,'product_1');

這裡檢視資料，可以看到引擎已經預先聚合了一部分資料

手動合併

執行完手動合併後，再次進行資料的查詢，如果資料有合併的話，相同主鍵的資料會合並進行price求和，由於資料量過小導致查詢時結果已經聚合，所以這裡多次插入上面的 user_1 的資料

手動合併資料

# 手動合併資料
optimize table shopping_record final;

合併後查詢結果

這裡展示的是該引擎的自動聚合效果，但是在實際查詢的時候，還需要對查詢進行 sum 以及 group by，一部分原因是由於資料可能還沒來得及聚合，還有一部分原因是資料可能於多個分割槽中，此時需要對多個分割槽進行聚合

-- 由於建表時主鍵為 toYYYYMMDD(shopping_time)，所以這裡根據日期來統計
select toYYYYMMDD(shop_time) as day, user_id, sum(price) from shopping_record group by day, user_id order by day

下面是查詢結果

新資料插入

這裡再增加使用者2的購買記錄

# 使用者2購買記錄，這裡模擬的時候插入了2次
insert into shopping_record values ('2022-02-23 16:43:33.000','user_2',33.33,'product_1'),('2022-02-24 16:43:22.000','user_2',99.99,'product_2'),('2022-02-26 10:00:00.000','user_2',78.3,'product_3');

執行手動合併sql後進行查詢

可以看到資料根據日期以及使用者進行了預聚合

AggregatingMergeTree

AggregatingMergeTree 也是預聚合引擎的一種，跟 SummingMergeTree 不同的是 AggregatingMergeTree 可以指定各種聚合函式，而 SummingMergeTree 只能處理數值求和 的情況。

在使用 AggregatingMergeTree 儲存的時候需要使用 state 結尾函式 儲存中間狀態值

查詢的時候使用 merge 結尾函式處理 state 的中間狀態值

照樣舉個?

這裡拿 “電商平臺的書本訪問次數以及訪問時長” 作為例子

資料建表

# 書本的檢視記錄
create table if not exists test.book(
	`user_id` String COMMENT '使用者id',
	`book_id` String COMMENT '書本id',
	`view_time` Int32 COMMENT '頁面檢視時間,單位秒',
	`create_time` DateTime64(3, 'UTC') COMMENT '建立時間'
) ENGINE = MergeTree()
partition by toYYYYMM(create_time)
order by (create_time, book_id)

# 書本瀏覽記錄的預聚合 AggregatingMergeTree, 這裡使用的是物化檢視，物化檢視很多操作跟普通表相同
CREATE MATERIALIZED VIEW IF NOT EXISTS test.book_mv 
engine = AggregatingMergeTree()
partition by day
order by (day, book_id)
as select toYYYYMMDD(create_time) as day,
book_id as book_id,
count() as visit,
sumState(view_time) as sum_view_time
from test.book
group by day, book_id

插入資料

insert into test.book values
('user_1', 'book_1', 100, '2022-02-23 16:00:00'),
('user_2', 'book_1', 112, '2022-02-23 17:35:00'),
('user_1', 'book_1', 94, '2022-02-23 18:05:00'),
('user_1', 'book_1', 67, '2022-02-23 20:05:00'),
('user_1', 'book_1', 30, '2022-02-25 16:00:00'),
('user_1', 'book_2', 245, '2022-02-23 16:10:00'),
('user_1', 'book_2', 39, '2022-02-23 19:10:00'),
('user_2', 'book_2', 78, '2022-02-23 23:17:00'),
('user_2', 'book_2', 60, '2022-03-10 09:49:00'),
('user_2', 'book_3', 30, '2022-03-10 10:49:00'),
('user_2', 'book_4', 44, '2022-03-10 11:49:00'),
('user_2', 'book_5', 75, '2022-03-10 12:49:00'),
('user_2', 'book_6', 20, '2022-03-10 13:49:00');

結果查詢

查詢原始資料表

# 因為這裡是物化檢視使用的預聚合引擎，所以查預詢預聚合結果的話要查物化檢視
select day, book_id, sum(visit), sumMerge(sum_view_time) as view_time from book_mv group by day, book_id order by day, book_id;

預聚合結果如下圖

從圖裡可以看到，預聚合引擎將原始瀏覽記錄聚合成了所需資訊，每天每本書的瀏覽次數、每天每本書總的頁面瀏覽時間

總結

使用聚合引擎在某些統計的情況下，可以很好的節省儲存空間（單預聚合引擎表情況下）以及加快資料聚合查詢，但是通常會包含兩張表， MergeTree 的原始資料表以及包含預聚合引擎的資料表，兩張表的情況下是需要 拿空間換時間，上面的話就是物化檢視持久化預聚合結果，除原始表外佔用額外空間，但是查詢預聚合引擎表可以加快聚合查詢。

最後

歡迎掃描下方二維碼或搜尋公眾號 LemonCode , 一起交流學習!

相關文章