引言
表引擎在ClickHouse中的作用十分關鍵,直接決定了數據如何存儲和讀取、是否支持併發讀寫、是否支持index、支持的query種類、是否支持主備複製等。
ClickHouse提供了大約28種表引擎,各有各的用途,比如有Lo
系列用來做小表數據分析,MergeTree
系列用來做大數據量分析,而Integration
系列則多用於外表數據集成。再考慮複製表Replicated
系列,分佈式表Distributed
等,紛繁複雜,新用戶上手選擇時常常感到迷惑。
本文嘗試對ClickHouse的表引擎進行梳理,幫忙大家快速入門ClickHouse。
ClickHouse表引擎概覽
下圖是ClickHouse提供的所有表引擎彙總。
一共分為四個系列,分別是Log、MergeTree、Integration、Special。其中包含了兩種特殊的表引擎Replicated、Distributed,功能上與其他表引擎正交,我們後續會單獨寫一篇文章來介紹。
Log系列
Log系列表引擎功能相對簡單,主要用於快速寫入小表(1百萬行左右的表),然後全部讀出的場景。
幾種Log表引擎的共性是:
- 數據被順序append寫到磁盤上;
- 不支持delete、update;
- 不支持index;
- 不支持原子性寫;
- insert會阻塞select操作。
它們彼此之間的區別是:
- TinyLog:不支持併發讀取數據文件,查詢性能較差;格式簡單,適合用來暫存中間數據;
- StripLog:支持併發讀取數據文件,查詢性能比TinyLog好;將所有列存儲在同一個大文件中,減少了文件個數;
- Log:支持併發讀取數據文件,查詢性能比TinyLog好;每個列會單獨存儲在一個獨立文件中。
Integration系列
該系統表引擎主要用於將外部數據導入到ClickHouse中,或者在ClickHouse中直接操作外部數據源。
- Kafka:將Kafka Topic中的數據直接導入到ClickHouse;
- MySQL:將Mysql作為存儲引擎,直接在ClickHouse中對MySQL表進行select等操作;
- JDBC/ODBC:通過指定jdbc、odbc連接串讀取數據源;
- HDFS:直接讀取HDFS上的特定格式的數據文件;
Special系列
Special系列的表引擎,大多是為了特定場景而定製的。這裡也挑選幾個簡單介紹,不做詳述。
- Memory:將數據存儲在內存中,重啟後會導致數據丟失。查詢性能極好,適合於對於數據持久性沒有要求的1億一下的小表。在ClickHouse中,通常用來做臨時表。
- Buffer:為目標表設置一個內存buffer,當buffer達到了一定條件之後會flush到磁盤。
- File:直接將本地文件作為數據存儲;
- Null:寫入數據被丟棄、讀取數據為空;
MergeTree系列
Log、Special、Integration主要用於特殊用途,場景相對有限。MergeTree系列才是官方主推的存儲引擎,支持幾乎所有ClickHouse核心功能。
以下重點介紹MergeTree、ReplacingMergeTree、CollapsingMergeTree、VersionedCollapsingMergeTree、SummingMergeTree、AggregatingMergeTree引擎。
MergeTree
MergeTree表引擎主要用於海量數據分析,支持數據分區、存儲有序、主鍵索引、稀疏索引、數據TTL等。MergeTree支持所有ClickHouse SQL語法,但是有些功能與MySQL並不一致,比如在MergeTree中主鍵並不用於去重,以下通過示例說明。
如下建表DDL所示,test_tbl的主鍵為(id, create_time),並且按照主鍵進行存儲排序,按照create_time進行數據分區,數據保留最近一個月。
CREATE TABLE test_tbl (
id UInt16,
create_time Date,
comment Nullable(String)
) ENGINE = MergeTree()
PARTITION BY create_time
ORDER BY (id, create_time)
PRIMARY KEY (id, create_time)
TTL create_time + INTERVAL 1 MONTH
SETTINGS index_granularity=8192;
寫入數據:值得注意的是這裡我們寫入了幾條primary key相同的數據。
insert into test_tbl values(0, '2019-12-12', null);
insert into test_tbl values(0, '2019-12-12', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(1, '2019-12-13', null);
insert into test_tbl values(2, '2019-12-14', null);
查詢數據: 可以看到雖然主鍵id、create_time相同的數據只有3條數據,但是結果卻有5行。
select count(*) from test_tbl;
┌─count()─┐
│ 5 │
└─────────┘
select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│ 2 │ 2019-12-14 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
由於MergeTree採用類似LSM tree的結構,很多存儲層處理邏輯直到Compaction期間才會發生。因此強制後臺compaction執行完畢,再次查詢,發現仍舊有5條數據。
optimize table test_tbl final;
select count(*) from test_tbl;
┌─count()─┐
│ 5 │
└─────────┘
select * from test_tbl;
┌─id─┬─create_time─┬─comment─┐
│ 2 │ 2019-12-14 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
結合以上示例可以看到,MergeTree雖然有主鍵索引,但是其主要作用是加速查詢,而不是類似MySQL等數據庫用來保持記錄唯一。即便在Compaction完成後,主鍵相同的數據行也仍舊共同存在。
ReplacingMergeTree
為了解決MergeTree相同主鍵無法去重的問題,ClickHouse提供了ReplacingMergeTree引擎,用來做去重。
示例如下:
-- 建表
CREATE TABLE test_tbl_replacing (
id UInt16,
create_time Date,
comment Nullable(String)
) ENGINE = ReplacingMergeTree()
PARTITION BY create_time
ORDER BY (id, create_time)
PRIMARY KEY (id, create_time)
TTL create_time + INTERVAL 1 MONTH
SETTINGS index_granularity=8192;
-- 寫入主鍵重複的數據
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(0, '2019-12-12', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(1, '2019-12-13', null);
insert into test_tbl_replacing values(2, '2019-12-14', null);
-- 查詢,可以看到未compaction之前,主鍵重複的數據,仍舊存在。
select count(*) from test_tbl_replacing;
┌─count()─┐
│ 5 │
└─────────┘
select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 2 │ 2019-12-14 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
-- 強制後臺compaction:
optimize table test_tbl_replacing final;
-- 再次查詢:主鍵重複的數據已經消失。
select count(*) from test_tbl_replacing;
┌─count()─┐
│ 3 │
└─────────┘
select * from test_tbl_replacing;
┌─id─┬─create_time─┬─comment─┐
│ 2 │ 2019-12-14 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 1 │ 2019-12-13 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
┌─id─┬─create_time─┬─comment─┐
│ 0 │ 2019-12-12 │ ᴺᵁᴸᴸ │
└────┴─────────────┴─────────┘
雖然ReplacingMergeTree提供了主鍵去重的能力,但是仍舊有以下限制:
- 在沒有徹底optimize之前,可能無法達到主鍵去重的效果,比如部分數據已經被去重,而另外一部分數據仍舊有主鍵重複;
- 在分佈式場景下,相同primary key的數據可能被sharding到不同節點上,不同shard間可能無法去重;
- optimize是後臺動作,無法預測具體執行時間點;
- 手動執行optimize在海量數據場景下要消耗大量時間,無法滿足業務即時查詢的需求;
因此ReplacingMergeTree更多被用於確保數據最終被去重,而無法保證查詢過程中主鍵不重複。
CollapsingMergeTree
ClickHouse實現了CollapsingMergeTree來消除ReplacingMergeTree的限制。該引擎要求在建表語句中指定一個標記列Sign,後臺Compaction時會將主鍵相同、Sign相反的行進行摺疊,也即刪除。
CollapsingMergeTree將行按照Sign的值分為兩類:Sign=1的行稱之為狀態行,Sign=-1的行稱之為取消行。
每次需要新增狀態時,寫入一行狀態行;需要刪除狀態時,則寫入一行取消行。
在後臺Compaction時,狀態行與取消行會自動做摺疊(刪除)處理。而尚未進行Compaction的數據,狀態行與取消行同時存在。
因此為了能夠達到主鍵摺疊(刪除)的目的,需要業務層進行適當改造:
1) 執行刪除操作需要寫入取消行,而取消行中需要包含與原始狀態行一樣的數據(Sign列除外)。所以在應用層需要記錄原始狀態行的值,或者在執行刪除操作前先查詢數據庫獲取原始狀態行;
2)由於後臺Compaction時機無法預測,在發起查詢時,狀態行和取消行可能尚未被摺疊;另外,ClickHouse無法保證primary key相同的行落在同一個節點上,不在同一節點上的數據無法摺疊。因此在進行count(*)、sum(col)等聚合計算時,可能會存在數據冗餘的情況。為了獲得正確結果,業務層需要改寫SQL,將count()、sum(col)
分別改寫為sum(Sign)、sum(col * Sign)
。
以下用示例說明:
-- 建表
CREATE TABLE UAct
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;
-- 插入狀態行,注意sign一列的值為1
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, 1);
-- 插入一行取消行,用於抵消上述狀態行。注意sign一列的值為-1,其餘值與狀態行一致;
-- 並且插入一行主鍵相同的新狀態行,用來將PageViews從5更新至6,將Duration從146更新為185.
INSERT INTO UAct VALUES (4324182021466249494, 5, 146, -1), (4324182021466249494, 6, 185, 1);
-- 查詢數據:可以看到未Compaction之前,狀態行與取消行共存。
SELECT * FROM UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
-- 為了獲取正確的sum值,需要改寫SQL:
-- sum(PageViews) => sum(PageViews * Sign)、
-- sum(Duration) => sum(Duration * Sign)
SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration
FROM UAct
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │ 6 │ 185 │
└─────────────────────┴───────────┴──────────┘
-- 強制後臺Compaction
optimize table UAct final;
-- 再次查詢,可以看到狀態行、取消行已經被摺疊,只剩下最新的一行狀態行。
select * from UAct;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
CollapsingMergeTree雖然解決了主鍵相同的數據即時刪除的問題,但是狀態持續變化且多線程並行寫入情況下,狀態行與取消行位置可能亂序,導致無法正常摺疊。
如下面例子所示:
亂序插入示例。
-- 建表
CREATE TABLE UAct_order
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;
-- 先插入取消行
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, -1);
-- 後插入狀態行
INSERT INTO UAct_order VALUES (4324182021466249495, 5, 146, 1);
-- 強制Compaction
optimize table UAct_order final;
-- 可以看到即便Compaction之後也無法進行主鍵摺疊: 2行數據仍舊都存在。
select * from UAct_order;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249495 │ 5 │ 146 │ -1 │
│ 4324182021466249495 │ 5 │ 146 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
VersionedCollapsingMergeTree
為了解決CollapsingMergeTree亂序寫入情況下無法正常摺疊問題,VersionedCollapsingMergeTree表引擎在建表語句中新增了一列Version,用於在亂序情況下記錄狀態行與取消行的對應關係。主鍵相同,且Version相同、Sign相反的行,在Compaction時會被刪除。
與CollapsingMergeTree類似, 為了獲得正確結果,業務層需要改寫SQL,將count()、sum(col)
分別改寫為sum(Sign)、sum(col * Sign)
。
示例如下:
亂序插入示例。
-- 建表
CREATE TABLE UAct_version
(
UserID UInt64,
PageViews UInt8,
Duration UInt8,
Sign Int8,
Version UInt8
)
ENGINE = VersionedCollapsingMergeTree(Sign, Version)
ORDER BY UserID;
-- 先插入一行取消行,注意Signz=-1, Version=1
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, -1, 1);
-- 後插入一行狀態行,注意Sign=1, Version=1;及一行新的狀態行注意Sign=1, Version=2,將PageViews從5更新至6,將Duration從146更新為185。
INSERT INTO UAct_version VALUES (4324182021466249494, 5, 146, 1, 1),(4324182021466249494, 6, 185, 1, 2);
-- 查詢可以看到未compaction情況下,所有行都可見。
SELECT * FROM UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ -1 │
│ 4324182021466249494 │ 6 │ 185 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┐
│ 4324182021466249494 │ 5 │ 146 │ 1 │
└─────────────────────┴───────────┴──────────┴──────┘
-- 為了獲取正確的sum值,需要改寫SQL:
-- sum(PageViews) => sum(PageViews * Sign)、
-- sum(Duration) => sum(Duration * Sign)
SELECT
UserID,
sum(PageViews * Sign) AS PageViews,
sum(Duration * Sign) AS Duration
FROM UAct_version
GROUP BY UserID
HAVING sum(Sign) > 0;
┌──────────────UserID─┬─PageViews─┬─Duration─┐
│ 4324182021466249494 │ 6 │ 185 │
└─────────────────────┴───────────┴──────────┘
-- 強制後臺Compaction
optimize table UAct_version final;
-- 再次查詢,可以看到即便取消行與狀態行位置亂序,仍舊可以被正確摺疊。
select * from UAct_version;
┌──────────────UserID─┬─PageViews─┬─Duration─┬─Sign─┬─Version─┐
│ 4324182021466249494 │ 6 │ 185 │ 1 │ 2 │
└─────────────────────┴───────────┴──────────┴──────┴─────────┘
SummingMergeTree
ClickHouse通過SummingMergeTree來支持對主鍵列進行預先聚合。在後臺Compaction時,會將主鍵相同的多行進行sum求和,然後使用一行數據取而代之,從而大幅度降低存儲空間佔用,提升聚合計算性能。
值得注意的是:
- ClickHouse只在後臺Compaction時才會進行數據的預先聚合,而compaction的執行時機無法預測,所以可能存在部分數據已經被預先聚合、部分數據尚未被聚合的情況。因此,在執行聚合計算時,SQL中仍需要使用GROUP BY子句。
- 在預先聚合時,ClickHouse會對主鍵列之外的其他所有列進行預聚合。如果這些列是可聚合的(比如數值類型),則直接sum;如果不可聚合(比如String類型),則隨機選擇一個值。
- 通常建議將SummingMergeTree與MergeTree配合使用,使用MergeTree來存儲具體明細,使用SummingMergeTree來存儲預先聚合的結果加速查詢。
示例如下:
-- 建表
CREATE TABLE summtt
(
key UInt32,
value UInt32
)
ENGINE = SummingMergeTree()
ORDER BY key
-- 插入數據
INSERT INTO summtt Values(1,1),(1,2),(2,1)
-- compaction前查詢,仍存在多行
select * from summtt;
┌─key─┬─value─┐
│ 1 │ 1 │
│ 1 │ 2 │
│ 2 │ 1 │
└─────┴───────┘
-- 通過GROUP BY進行聚合計算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│ 2 │ 1 │
│ 1 │ 3 │
└─────┴────────────┘
-- 強制compaction
optimize table summtt final;
-- compaction後查詢,可以看到數據已經被預先聚合
select * from summtt;
┌─key─┬─value─┐
│ 1 │ 3 │
│ 2 │ 1 │
└─────┴───────┘
-- compaction後,仍舊需要通過GROUP BY進行聚合計算
SELECT key, sum(value) FROM summtt GROUP BY key
┌─key─┬─sum(value)─┐
│ 2 │ 1 │
│ 1 │ 3 │
└─────┴────────────┘
AggregatingMergeTree
AggregatingMergeTree也是預先聚合引擎的一種,用於提升聚合計算的性能。與SummingMergeTree的區別在於:SummingMergeTree對非主鍵列進行sum聚合,而AggregatingMergeTree則可以指定各種聚合函數。
AggregatingMergeTree的語法比較複雜,需要結合物化視圖或ClickHouse的特殊數據類型AggregateFunction一起使用。在insert和select時,也有獨特的寫法和要求:寫入時需要使用-State語法,查詢時使用-Merge語法。
以下通過示例進行介紹。
示例一:配合物化視圖使用。
-- 建立明細表
CREATE TABLE visits
(
UserID UInt64,
CounterID UInt8,
StartDate Date,
Sign Int8
)
ENGINE = CollapsingMergeTree(Sign)
ORDER BY UserID;
-- 對明細表建立物化視圖,該物化視圖對明細表進行預先聚合
-- 注意:預先聚合使用的函數分別為: sumState, uniqState。對應於寫入語法<agg>-State.
CREATE MATERIALIZED VIEW visits_agg_view
ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)
AS SELECT
CounterID,
StartDate,
sumState(Sign) AS Visits,
uniqState(UserID) AS Users
FROM visits
GROUP BY CounterID, StartDate;
-- 插入明細數據
INSERT INTO visits VALUES(0, 0, '2019-11-11', 1);
INSERT INTO visits VALUES(1, 1, '2019-11-12', 1);
-- 對物化視圖進行最終的聚合操作
-- 注意:使用的聚合函數為 sumMerge, uniqMerge。對應於查詢語法<agg>-Merge.
SELECT
StartDate,
sumMerge(Visits) AS Visits,
uniqMerge(Users) AS Users
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;
-- 普通函數 sum, uniq不再可以使用
-- 如下SQL會報錯: Illegal type AggregateFunction(sum, Int8) of argument
SELECT
StartDate,
sum(Visits),
uniq(Users)
FROM visits_agg_view
GROUP BY StartDate
ORDER BY StartDate;
示例二:配合特殊數據類型AggregateFunction使用。
-- 建立明細表
CREATE TABLE detail_table
( CounterID UInt8,
StartDate Date,
UserID UInt64
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate);
-- 插入明細數據
INSERT INTO detail_table VALUES(0, '2019-11-11', 1);
INSERT INTO detail_table VALUES(1, '2019-11-12', 1);
-- 建立預先聚合表,
-- 注意:其中UserID一列的類型為:AggregateFunction(uniq, UInt64)
CREATE TABLE agg_table
( CounterID UInt8,
StartDate Date,
UserID AggregateFunction(uniq, UInt64)
) ENGINE = AggregatingMergeTree()
PARTITION BY toYYYYMM(StartDate)
ORDER BY (CounterID, StartDate);
-- 從明細表中讀取數據,插入聚合表。
-- 注意:子查詢中使用的聚合函數為 uniqState, 對應於寫入語法<agg>-State
INSERT INTO agg_table
select CounterID, StartDate, uniqState(UserID)
from detail_table
group by CounterID, StartDate
-- 不能使用普通insert語句向AggregatingMergeTree中插入數據。
-- 本SQL會報錯:Cannot convert UInt64 to AggregateFunction(uniq, UInt64)
INSERT INTO agg_table VALUES(1, '2019-11-12', 1);
-- 從聚合表中查詢。
-- 注意:select中使用的聚合函數為uniqMerge,對應於查詢語法<agg>-Merge
SELECT uniqMerge(UserID) AS state
FROM agg_table
GROUP BY CounterID, StartDate;
結語
ClickHouse提供了豐富多樣的表引擎,應對不同的業務需求。本文概覽了ClickHouse的表引擎,同時對於MergeTree系列表引擎進行了詳細對比和樣例示範。
在這些表引擎之外,ClickHouse還提供了Replicated、Distributed等高級表引擎,我們會在後續進一步深度解讀。
寫在最後
阿里雲已經率先推出了ClickHouse的雲託管產品,產品首頁地址:雲數據庫ClickHouse,目前正在免費公測中,歡迎大家點擊鏈接申請免費試用。
我們也開通了阿里雲ClickHouse釘釘交流群,通過專業的數據庫專家為客戶提供諮詢、答疑服務。歡迎大家任選如下方式入群交流,我們將會定期推送ClickHouse最佳實踐、操作指南、原理解讀等深度文章。
- 方式一:使用釘釘搜索群號 23300515
- 方式二:使用釘釘掃描下方二維碼