1 概述

? 表引擎在ClickHouse中的作用十分關(guān)鍵，表引擎有如下作用：

? （1）數(shù)據(jù)如何存儲，存在哪，數(shù)據(jù)寫到哪, 怎樣讀取數(shù)據(jù)；

? （2）支持哪些查詢以及如何支持；

? （3）并發(fā)數(shù)據(jù)訪問；

? （4）索引的使用；

? （5）是否可以執(zhí)行多線程的請求；

? （6）數(shù)據(jù)如何同步。

2 表引擎系列

2.1 MergeTree系列

? MergeTree系列是對于高負(fù)載任務(wù)的最通用和最實用的表引擎。這些引擎共享的屬性是快速數(shù)據(jù)插入和后續(xù)的后臺數(shù)據(jù)處理。想要高效地一批批寫入數(shù)據(jù)片段，并希望這些數(shù)據(jù)片段在后臺按照一定規(guī)則合并。相比在插入時不斷修改（重寫）數(shù)據(jù)進(jìn)存儲，這種策略會高效很多。MergeTree系列引擎支持?jǐn)?shù)據(jù)復(fù)制、分區(qū)和其他引擎不支持的特性。有如下特點：①數(shù)據(jù)按照主鍵進(jìn)行排序；②可以使用分區(qū)（如果指定了主鍵）；③支持?jǐn)?shù)據(jù)副本；④支持?jǐn)?shù)據(jù)采樣

2.1.1 MergeTree

? MergeTree 引擎支持索引，通過主鍵和日期來構(gòu)建索引, 同時提供 數(shù)據(jù)的實時更新能力. 這是目前 ClickHouse處理能力最好的引擎。

? 注意：①不能和Merge 引擎相混淆。

? ②MergeTree雖然有主鍵索引，但是其主要作用是加速查詢，而不是類似MySQL等數(shù)據(jù)庫用來保持記錄唯一。即便在Compaction完成后，主鍵相同的數(shù)據(jù)行也仍舊共同存在。

? 建表語句如下

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1] [TTL expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2] [TTL expr2],...INDEX index_name1 expr1 TYPE type1(...) GRANULARITY value1,INDEX index_name2 expr2 TYPE type2(...) GRANULARITY value2 ) ENGINE = MergeTree() [PARTITION BY expr] [ORDER BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...] [SETTINGS name=value, ...]

? 參數(shù)說明：

參數(shù)說明

ENGINE = MergeTree()	ENGINE：引擎名和參數(shù)
PARTITION BY expr	PARTITION BY：分區(qū)鍵，按月分區(qū)采用“YYYYMM”格式，可以使用toYYYYMM (date_column)表達(dá)式，date_column類型必須是Date
ORDER BY expr	ORDER BY：排序鍵，列的元組或任意表達(dá)式 (字段的組合)， 或者單獨的表達(dá)式。如:ORDER BY (CounterID, EventDate)
PRIMARY KEY expr	PRIMARY KEY：主鍵，需要與排序鍵字段不同，默認(rèn)主鍵與排序鍵相同
SAMPLE BY expr	SAMPLE BY：抽樣表達(dá)式，要用抽樣表達(dá)式，主鍵必須包含這個表達(dá)式
TTL expr [DELETE |TO DISK ‘xxx’ TO VOLUME ‘xxx’], …	TTL：指定行存儲時間和定義磁盤與卷之間自動部件移動邏輯的規(guī)則列表。如：TTL create_time + INTERVAL 1 MONTH（表數(shù)據(jù)的生命周期為期一個月 ）它的含義是當(dāng)ClickHouse合并數(shù)據(jù)分區(qū)時, 會根據(jù)create_time這一列的時間數(shù)據(jù)以及之后一個月的這樣一周期內(nèi)的數(shù)據(jù)進(jìn)行保存，不在這一時間段內(nèi)的數(shù)據(jù)，ck就是主動刪除分區(qū)目錄下的列文件
SETTINGS name=value, …	SETTINGS：影響MergeTree性能的額外參數(shù) ①index_granularity：索引粒度，索引鍵相鄰標(biāo)記間的數(shù)據(jù)行數(shù)，默認(rèn)8192 ②use_minimalistic_part_header_in_zookeeper：在Zookeeper中的存儲方式 ③min_merge_bytes_to_use_direct_io：使用直接I/O來操作磁盤的合并操作時的最小數(shù)據(jù)量

? 測試：test_mt表的主鍵為(id, create_time)，并且按照主鍵進(jìn)行存儲排序，按照create_time進(jìn)行數(shù)據(jù)分區(qū)，根據(jù)create_time這一列的時間數(shù)據(jù)保留最近一個月。

CREATE TABLE test_mt ( \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;

? 插入數(shù)據(jù)

insert into test_mt values(0, '2020-01-01', null); insert into test_mt values(0, '2020-01-01', null); insert into test_mt values(1, '2020-01-02', null); insert into test_mt values(2, '2020-01-03', null);

? 查詢數(shù)據(jù)

SELECT count(*) FROM test_mt ┌─count()─┐ │ 4 │ └─────────┘SELECT * FROM test_mt ┌─id─┬─create_time─┬─comment─┐ │ 2 │ 2020-01-03 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 1 │ 2020-01-02 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-01-01 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-01-01 │ ???? │ └────┴─────────────┴─────────┘

? 可以發(fā)現(xiàn)雖然主鍵id、create_time相同的數(shù)據(jù)只有3條數(shù)據(jù)，但是結(jié)果卻有4行。因為MergeTree采用類似LSM tree的結(jié)構(gòu)，很多存儲層處理邏輯直到Compaction期間才會發(fā)生。因此強(qiáng)制后臺compaction執(zhí)行

optimize table test_mt final;

? 再次查詢，發(fā)現(xiàn)沒有數(shù)據(jù)了。

SELECT count(*) FROM test_mt ┌─count()─┐ │ 0 │ └─────────┘

? 是因為TTL的原因，我們在表上加了TTL當(dāng)表內(nèi)的數(shù)據(jù)過期時, ClickHouse會刪除所有對應(yīng)的行。如果是列上加TTL，當(dāng)列字段中的值過期時, ClickHouse會將它們替換成數(shù)據(jù)類型的默認(rèn)值。如果分區(qū)內(nèi)，某一列的所有值均已過期，則ClickHouse會從文件系統(tǒng)中刪除這個分區(qū)目錄下的列文件。

? 如果沒有加TTL的查詢出來應(yīng)該是如下所示

SELECT count(*) FROM test_mt ┌─count()─┐ │ 4 │ └─────────┘select * from test_mt; ┌─id─┬─create_time─┬─comment─┐ │ 2 │ 2020-01-03 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 1 │ 2020-01-02 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-01-01 │ ???? │ │ 0 │ 2020-01-01 │ ???? │ └────┴─────────────┴─────────┘

?

2.1.2 ReplacingMergeTree

? ReplacingMergeTree在MergeTree的基礎(chǔ)上，添加了處理重復(fù)數(shù)據(jù)的功能，也就是會刪除具有相同主鍵的重復(fù)項，這就是與MergeTree的不同之處。

? 注意：數(shù)據(jù)的去重是在合并的過程中出現(xiàn)的，合并會在未知的時間在后臺運行，所以無法預(yù)先做出計劃。所以可能有一些數(shù)據(jù)任未被處理，因此ReplacingMergeTree適用于在后臺清理重復(fù)數(shù)據(jù)以節(jié)省空間，但是不能保證沒有重復(fù)的數(shù)據(jù)出現(xiàn)。

? 語法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],... ) ENGINE = ReplacingMergeTree([ver]) [PARTITION BY expr] [ORDER BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]

? 參數(shù)說明：ENGINE = ReplacingMergeTree([ver])，這個ver是版本列，類型是UInt*，Date或者DateTime，合并的時候ReplacingMergeTree從具有相同主鍵的行中選擇一行留下，如果ver列沒有指定，選擇最后一條，如果ver列已指定，選擇ver最大的版本。其他的參考MergeTree的

? 測試：

CREATE TABLE test_rmt (\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;

? 插入數(shù)據(jù)：

insert into test_rmt values(0, '2020-05-01', null); insert into test_rmt values(0, '2020-05-01', null); insert into test_rmt values(1, '2020-05-02', null); insert into test_rmt values(2, '2020-05-03', null);

? 查詢結(jié)果：

SELECT count(*) FROM test_rmt ┌─count()─┐ │ 4 │ └─────────┘SELECT * FROM test_rmt ┌─id─┬─create_time─┬─comment─┐ │ 1 │ 2020-05-02 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 2 │ 2020-05-03 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-05-01 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-05-01 │ ???? │ └────┴─────────────┴─────────┘

? 可以發(fā)現(xiàn)數(shù)據(jù)還是4條，強(qiáng)制后臺compaction

optimize table test_rmt final;

? 再次查詢

SELECT count(*) FROM test_rmt ┌─count()─┐ │ 3 │ └─────────┘SELECT * FROM test_rmt ┌─id─┬─create_time─┬─comment─┐ │ 1 │ 2020-05-02 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 2 │ 2020-05-03 │ ???? │ └────┴─────────────┴─────────┘ ┌─id─┬─create_time─┬─comment─┐ │ 0 │ 2020-05-01 │ ???? │ └────┴─────────────┴─────────┘

? 總結(jié)：雖然ReplacingMergeTree提供了主鍵去重的能力，但是仍舊有以下缺點：①在沒有徹底o(hù)ptimize之前，可能無法達(dá)到主鍵去重的效果，部分?jǐn)?shù)據(jù)已經(jīng)去重，部分沒有去重②在分布式情況下，相同primary key的數(shù)據(jù)可能被sharding到不同節(jié)點上，不同shard間可能無法去重③無法預(yù)測optimize具體執(zhí)行時間點④海量數(shù)據(jù)下要手動執(zhí)行optimize需要消耗大量時間，無法滿足業(yè)務(wù)即時查詢的需求

2.1.3 SummingMergeTree

? SummingMergeTree與MergeTree不區(qū)別在與當(dāng)合并SummingMergeTree表的數(shù)據(jù)片段時，會把相同主鍵的行合并為一行，這一行包含了被合并的行中具有數(shù)值數(shù)據(jù)類型的列的匯總值，對于不可加的列會取出一個最先出現(xiàn)的值。如果相同的主鍵對應(yīng)大量的行，可以顯著減少存儲空間并加快數(shù)據(jù)查詢的速度。

? 語法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],... ) ENGINE = SummingMergeTree([columns]) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...]

? 參數(shù)說明：ENGINE = SummingMergeTree([columns])：[columns]表示將要被匯總的列的列名的元組。

? 測試：

CREATE TABLE test_smt(\date Date,\name String,\money UInt16,\not_sum UInt16\ )\ ENGINE = SummingMergeTree(money)\ PARTITION by date \ ORDER by (date,name);

? 插入數(shù)據(jù)：

insert into test_smt values ('2020-05-01', 'zs', 6, 1); insert into test_smt values ('2020-05-01', 'ls', 8, 2); insert into test_smt values ('2020-05-02', 'ww', 6, 3); insert into test_smt values ('2020-05-02', 'ww', 8, 4); insert into test_smt values ('2020-05-03', 'zl', 6, 5);

? 查詢：

SELECT * FROM test_smt ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-02 │ ww │ 8 │ 4 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-01 │ zs │ 6 │ 1 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-03 │ zl │ 6 │ 5 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-01 │ ls │ 8 │ 2 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-02 │ ww │ 6 │ 3 │ └────────────┴──────┴───────┴─────────┘

? 通過GROUP BY進(jìn)行聚合查詢

SELECT date, name, sum(money), min(not_sum) FROM test_smt GROUP BY date, name┌───────date─┬─name─┬─sum(money)─┬─min(not_sum)─┐ │ 2020-05-01 │ ls │ 8 │ 2 │ │ 2020-05-02 │ ww │ 14 │ 3 │ │ 2020-05-03 │ zl │ 6 │ 5 │ │ 2020-05-01 │ zs │ 6 │ 1 │ └────────────┴──────┴────────────┴──────────────┘

? 強(qiáng)制compaction

optimize table test_smt final;

? 再次查詢

SELECT * FROM test_smt ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-03 │ zl │ 6 │ 5 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-01 │ ls │ 8 │ 2 │ │ 2020-05-01 │ zs │ 6 │ 1 │ └────────────┴──────┴───────┴─────────┘ ┌───────date─┬─name─┬─money─┬─not_sum─┐ │ 2020-05-02 │ ww │ 14 │ 3 │ └────────────┴──────┴───────┴─────────┘

2.1.4 ClollapsingMergeTree

? ClollapsingMergeTree實現(xiàn)了對ReplacingMergeTree功能的限制，在建表語句中指定一個標(biāo)記列sign，后臺Compaction時會將主鍵相同，sign相反的行進(jìn)行刪除。ClollapsingMergeTree將行安裝sign的值分為兩類：sign=1為狀態(tài)行，sign=-1位取消行。每次需要新增狀態(tài)時，寫入一行狀態(tài)行，需要刪除狀態(tài)時，寫入一行取消行。后臺在Compaction時，狀態(tài)行與取消行會自動做折疊（刪除），而尚未進(jìn)行Compaction的數(shù)據(jù)，狀態(tài)行與取消行同時存在。

? 為了能夠達(dá)到主鍵折疊（刪除）的目的，對業(yè)務(wù)層進(jìn)行適當(dāng)改造：①執(zhí)行刪除操作需要寫入取消行，而取消行中需要包含與原始狀態(tài)行主鍵一樣的數(shù)據(jù)（Sign列除外）。所以在應(yīng)用層需要記錄原始狀態(tài)行的值，或者在執(zhí)行刪除操作前先查詢數(shù)據(jù)庫獲取原始狀態(tài)行。②由于后臺Compaction時機(jī)無法預(yù)測，在發(fā)起查詢時，狀態(tài)行和取消行可能尚未被折疊；另外，ClickHouse無法保證primary key相同的行落在同一個節(jié)點上，不在同一節(jié)點上的數(shù)據(jù)無法折疊。因此在進(jìn)行count(*)、sum(col)等聚合計算時，可能會存在數(shù)據(jù)冗余的情況。為了獲得正確結(jié)果，業(yè)務(wù)層需要改寫SQL，將count()、sum(col)分別改寫為sum(Sign)、sum(col * Sign)。

? 測試：

CREATE TABLE test_cmt1(\UserID UInt64,\PageViews UInt8,\Duration UInt8,\Sign Int8\ )\ ENGINE = CollapsingMergeTree(Sign)\ ORDER BY UserID;

? 插入狀態(tài)行，sign列的值為1

INSERT INTO test_cmt1 VALUES (123456, 6, 88, 1);

? 插入一行取消行，用于抵消上述狀態(tài)行。sign列的值為-1，其余值與狀態(tài)行一致；并且插入一行主鍵相同的新狀態(tài)行，用來將PageViews從6更新至7，將Duration從888更新為889.

INSERT INTO test_cmt1 VALUES (123456, 6, 88, -1), (123456, 7, 90, 1);

? 查詢數(shù)據(jù)：可以看到未Compaction之前，狀態(tài)行與取消行共存。

SELECT * FROM test_cmt1 ┌─UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 123456 │ 6 │ 88 │ 1 │ └────────┴───────────┴──────────┴──────┘ ┌─UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 123456 │ 6 │ 88 │ -1 │ │ 123456 │ 7 │ 90 │ 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 test_cmt1 GROUP BY UserID HAVING sum(Sign) > 0┌─UserID─┬─PageViews─┬─Duration─┐ │ 123456 │ 7 │ 90 │ └────────┴───────────┴──────────┘

? 強(qiáng)制后臺Compaction

optimize table test_cmt1 final;

? 再次查詢，可以看到狀態(tài)行、取消行已經(jīng)被折疊，只剩下最新的一行狀態(tài)行。

SELECT * FROM test_cmt1 ┌─UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 123456 │ 7 │ 90 │ 1 │ └────────┴───────────┴──────────┴──────┘

? CollapsingMergeTree雖然解決了主鍵相同的數(shù)據(jù)即時刪除的問題，但是狀態(tài)持續(xù)變化且多線程并行寫入情況下，狀態(tài)行與取消行位置可能亂序，導(dǎo)致無法正常折疊。

CREATE TABLE test_cmt2(\UserID UInt64,\PageViews UInt8,\Duration UInt8,\Sign Int8\ )\ ENGINE = CollapsingMergeTree(Sign)\ ORDER BY UserID;

? 先插入取消行，然后后插入狀態(tài)行

INSERT INTO test_cmt2 VALUES (123456, 6, 88, -1); INSERT INTO test_cmt2 VALUES (123456, 6, 88, 1);

? 強(qiáng)制Compaction

optimize table test_cmt2 final;

? 查詢

select * from test_cmt2;

? 可以看到即便Compaction之后也無法進(jìn)行主鍵折疊: 2行數(shù)據(jù)仍舊都存在。

SELECT * FROM test_cmt2 ┌─UserID─┬─PageViews─┬─Duration─┬─Sign─┐ │ 123456 │ 6 │ 88 │ -1 │ │ 123456 │ 6 │ 88 │ 1 │ └────────┴───────────┴──────────┴──────┘

? 為了解決CollapsingMergeTree亂序?qū)懭肭闆r下無法正常折疊問題，VersionedCollapsingMergeTree表引擎在建表語句中新增了一列Version，用于在亂序情況下記錄狀態(tài)行與取消行的對應(yīng)關(guān)系。主鍵相同，且Version相同、Sign相反的行，在Compaction時會被刪除。

? 與CollapsingMergeTree類似， 為了獲得正確結(jié)果，業(yè)務(wù)層需要改寫SQL，將count()、sum(col)分別改寫為sum(Sign)、sum(col * Sign)。

2.1.5 AggregatingMergeTree

? AggregatingMergeTree與MergeTree的區(qū)別在于會進(jìn)行預(yù)先的聚合，用于提升聚合計算的性能。與SummingMergeTree的區(qū)別在于SummingMergeTree對于非主鍵列進(jìn)行sum聚合，而AggregatingMergeTree則可以指定各種聚合函數(shù)。

? AggregatingMergeTree需要結(jié)合物化視圖或者ClickHouse的特殊數(shù)據(jù)類型ArrregateFunction一起使用，在insert寫入的時候需要使用-State語法，在select查詢的時候使用-Merge語法。

? 語法：

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] (name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1],name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2],... ) ENGINE = AggregatingMergeTree() [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [TTL expr] [SETTINGS name=value, ...]

? （1）結(jié)合物化視圖

CREATE TABLE test_amt1(\UserID UInt64,\CounterID UInt8,\StartDate Date,\Sign Int8\ )\ ENGINE = CollapsingMergeTree(Sign)\ ORDER BY UserID;

? 對test_amt建立物化視圖，進(jìn)行預(yù)先聚合。預(yù)先聚合使用的函數(shù)分別為： sumState, uniqState。對應(yīng)于寫入語法-State.

CREATE MATERIALIZED VIEW test_amt1_view\ ENGINE = AggregatingMergeTree() PARTITION BY toYYYYMM(StartDate) ORDER BY (CounterID, StartDate)\ AS SELECT\CounterID,\StartDate,\sumState(Sign) AS Visits,\uniqState(UserID) AS Users\ FROM test_amt1\ GROUP BY CounterID, StartDate;

? 插入數(shù)據(jù)

INSERT INTO test_amt1 VALUES(123, 0, '2020-05-01', 3); INSERT INTO test_amt1 VALUES(123, 1, '2020-05-01', 3); INSERT INTO test_amt1 VALUES(111, 2, '2020-05-02', 2);

? 對物化視圖進(jìn)行最終的聚合操作。使用的聚合函數(shù)為 sumMerge， uniqMerge。

SELECT \StartDate, \sumMerge(Visits) AS Visits, \uniqMerge(Users) AS Users\ FROM test_amt1_view \ GROUP BY StartDate\ ORDER BY StartDate ASC┌──StartDate─┬─Visits─┬─Users─┐ │ 2020-05-01 │ 6 │ 1 │ │ 2020-05-02 │ 2 │ 1 │ └────────────┴────────┴───────┘

? 普通函數(shù) sum, uniq不再可以使用,會報錯： Illegal type AggregateFunction(sum, Int8) of argument

? （2）配合特殊數(shù)據(jù)類型AggregateFunction使用

CREATE TABLE test_amt2(\CounterID UInt8,\StartDate Date,\Money UInt64,\UserID UInt64\ ) ENGINE = MergeTree() \ PARTITION BY toYYYYMM(StartDate) \ ORDER BY (CounterID, StartDate);

? 插入數(shù)據(jù)

INSERT INTO test_amt2 VALUES(111, '2020-05-01', 10, 1); INSERT INTO test_amt2 VALUES(111, '2020-05-01', 12, 5); INSERT INTO test_amt2 VALUES(122, '2020-05-02', 9, 2);

? 建立預(yù)先聚合表，其中UserID一列的類型為：AggregateFunction(uniq, UInt64)

CREATE TABLE test_amt2_agg(\CounterID UInt8,\StartDate Date,\Money AggregateFunction(sum, UInt64),\UserID AggregateFunction(uniq, UInt64)\ ) ENGINE = AggregatingMergeTree() \ PARTITION BY toYYYYMM(StartDate) \ ORDER BY (CounterID, StartDate);

? 從明細(xì)表中讀取數(shù)據(jù)，插入聚合表，子查詢中使用的聚合函數(shù)為 uniqState

INSERT INTO test_amt2_agg SELECT \CounterID, \StartDate, \sumState(Money),\uniqState(UserID)\ FROM test_amt2 \ GROUP BY \CounterID, \StartDate

? 注意：不能使用普通insert語句向AggregatingMergeTree中插入數(shù)據(jù)。會報錯：Cannot convert UInt64 to AggregateFunction(uniq, UInt64)

? 從聚合表中查詢，select中使用的聚合函數(shù)為uniqMerge

SELECT CounterID,StartDate,sumMerge(Money),uniqMerge(UserID) AS state\ FROM test_amt2_agg \ GROUP BY \CounterID, \StartDateSELECT CounterID, StartDate, sumMerge(Money), uniqMerge(UserID) AS state FROM test_amt2_agg GROUP BY CounterID, StartDate┌─CounterID─┬──StartDate─┬─sumMerge(Money)─┬─state─┐ │ 122 │ 2020-05-02 │ 9 │ 1 │ │ 111 │ 2020-05-01 │ 22 │ 2 │ └───────────┴────────────┴─────────────────┴───────┘

?

2.2 Log系列

? Log系列表引擎功能相對簡單，輕量級引擎主要用于快速寫入小表（1百萬行左右的表），然后全部讀出的場景。

? 特點：

? （1）數(shù)據(jù)被順序append寫到磁盤上。

? （2）不支持delete、update。

? （3）不支持index。

? （4）不支持原子性寫。

? （5）insert會阻塞select操作。

? TinyLog，StripLog，Log區(qū)別如下：①TinyLog：不支持并發(fā)讀取數(shù)據(jù)文件，查詢性能較差；格式簡單，適合用來暫存中間數(shù)據(jù)。②StripLog：支持并發(fā)讀取數(shù)據(jù)文件，查詢性能比TinyLog好；將所有列存儲在同一個大文件中，減少了文件個數(shù)。③Log：支持并發(fā)讀取數(shù)據(jù)文件，查詢性能比TinyLog好；每個列會單獨存儲在一個獨立文件中。

2.2.1 TinyLog

? 最簡單的表引擎，用于將數(shù)據(jù)存儲在磁盤上。每列都存儲在單獨的壓縮文件中，寫入時，數(shù)據(jù)將附加到文件末尾。該引擎沒有并發(fā)控制，如果同時從表中讀取和寫入數(shù)據(jù)，則讀取操作將拋出異常；如果同時寫入多個查詢中的表，則數(shù)據(jù)將被破壞。
不支持索引。

? 測試：

CREATE TABLE test_tl (\id UInt16,\name String)\ ENGINE=TinyLog;

? 插入數(shù)據(jù)：

INSERT INTO test_tl (id, name) values (1, 'zs');

? 進(jìn)入ClickHouse的test_tl表的數(shù)據(jù)存儲目錄

[root@ambari01 test_tl]# cd /data/clickhouse/data/test/test_tl[root@ambari01 test_tl]# ll total 12 -rw-r----- 1 clickhouse hadoop 28 May 22 18:07 id.bin -rw-r----- 1 clickhouse hadoop 29 May 22 18:07 name.bin -rw-r----- 1 clickhouse hadoop 64 May 22 18:07 sizes.json

? id.bin和name.bin是壓縮過的對應(yīng)的列的數(shù)據(jù)，sizes.json 中記錄了每個 *.bin 文件的大小。

[root@ambari01 test_tl]# cat sizes.json {"yandex":{"id%2Ebin":{"size":"28"},"name%2Ebin":{"size":"29"}}}

2.3 Integration系列

? 該系統(tǒng)表引擎主要用于將外部數(shù)據(jù)導(dǎo)入到ClickHouse中，或者在ClickHouse中直接操作外部數(shù)據(jù)源。

2.3.1 Kafka

? 將Kafka Topic中的數(shù)據(jù)直接導(dǎo)入到ClickHouse。

2.3.2 MySQL

? 將Mysql作為存儲引擎，直接在ClickHouse中對MySQL表進(jìn)行select等操作。

2.3.3 JDBC/ODBC

? 通過指定jdbc、odbc連接串讀取數(shù)據(jù)源。

2.3.4 HDFS

? 直接讀取HDFS上的特定格式的數(shù)據(jù)文件；

2.4 Special系列

? Special系列的表引擎，是為了特定場景而定制的，不做詳述。

? Memory：將數(shù)據(jù)存儲在內(nèi)存中，重啟后會導(dǎo)致數(shù)據(jù)丟失。查詢性能極好，適合于對于數(shù)據(jù)持久性沒有要求的1億一下的小表。在ClickHouse中，通常用來做臨時表。

? Buffer：為目標(biāo)表設(shè)置一個內(nèi)存buffer，當(dāng)buffer達(dá)到了一定條件之后會flush到磁盤。

? File：直接將本地文件作為數(shù)據(jù)存儲。

? Null：寫入數(shù)據(jù)被丟棄、讀取數(shù)據(jù)為空。

總結(jié)

以上是生活随笔為你收集整理的ClickHouse表引擎的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。