MySQL 的數(shù)據(jù)表有 InnoDB 和 MyISAM 存儲引擎，不同的存儲引擎提供不同的存儲機(jī) 制、索引方式等功能，也可以稱之為表類型。在 ClickHouse 中也有表引擎。 表引擎在 ClickHouse 中的作用十分關(guān)鍵，直接決定了數(shù)據(jù)如何存儲和讀取、是否支 持并發(fā)讀寫、是否支持 index 索引、支持的 query 種類、是否支持主備復(fù)制等。 ClickHouse 提供了大約 28 種表引擎，各有各的用途，比如有 Log 系列用來做小表 數(shù)據(jù)分析，MergeTree 系列用來做大數(shù)據(jù)量分析，而 Integration 系列則多用于外表數(shù) 據(jù)集成。再考慮復(fù)制表 Replicated 系列，分布式表 Distributed 等 ClickHouse 表引擎一共分為四個系列，分別是 Log 系列、MergeTree 系列、Integration 系列、Special 系列。其中包含了兩種特殊的表引擎 Replicated、Distributed，功能上與其他表引擎正交，根據(jù)場景組合使用。

Log系列表引擎

Log 系列表引擎功能相對簡單，主要用于快速寫入小表（1 百萬行左右的表），然后全 部讀出的場景，即一次寫入，多次查詢。Log 系列表引擎包含：TinyLog、StripeLog、 Log 三種引擎。 ? 幾種 Log 表引擎的共性是： ? 數(shù)據(jù)被順序 append 寫到本地磁盤上。 ? 不支持 delete、update 修改數(shù)據(jù)。 ? 不支持 index（索引）。 ? 不支持原子性寫。如果某些操作(異常的服務(wù)器關(guān)閉)中斷了寫操作，則可能會獲 得帶有損壞數(shù)據(jù)的表。 ? insert 會阻塞 select 操作。當(dāng)向表中寫入數(shù)據(jù)時，針對這張表的查詢會被阻塞， 直至寫入動作結(jié)束。 ? 它們彼此之間的區(qū)別是： ? TinyLog：不支持并發(fā)讀取數(shù)據(jù)文件，查詢性能較差；格式簡單，適合用來暫存 中間數(shù)據(jù)。 ? StripLog：支持并發(fā)讀取數(shù)據(jù)文件，查詢性能比 TinyLog 好；將所有列存儲在 同一個大文件中，減少了文件個數(shù)。 ? Log：支持并發(fā)讀取數(shù)據(jù)文件，查詢性能比 TinyLog 好；每個列會單獨(dú)存儲在一 個獨(dú)立文件中

TinyLog

TinyLog 是 Log 系列引擎中功能簡單、性能較低的引擎。 它的存儲結(jié)構(gòu)由數(shù)據(jù)文件和元數(shù)據(jù)兩部分組成。其中，數(shù)據(jù)文件是按列獨(dú)立存儲的，也 就是說每一個列字段都對應(yīng)一個文件。 由于 TinyLog 數(shù)據(jù)存儲不分塊，所以不支持并發(fā)數(shù)據(jù)讀取，該引擎適合一次寫入，多 次讀取的場景，對于處理小批量中間表的數(shù)據(jù)可以使用該引擎，這種引擎會有大量小文件， 性能會低 create table t_tinylog(id UInt8,name String,age UInt8)??engine=TinyLog; insert into t_tinylog values (1,'張三',18),(2,'李四',19),(3,' 王五',20); #在表中刪除一條數(shù)據(jù)，這里是不支持 delete。 alter table t_tinylog delete where id = 1; 當(dāng) 在 newdb 庫 中 創(chuàng) 建 表 t_tinylog 后 ， 在 ClickHouse 保 存 數(shù) 據(jù) 的 目 錄 /var/lib/clickhouse/data/newdb/下會多一個 t_tinylog 目錄在向表 t_tinylog 中插入數(shù)據(jù)后，進(jìn)入“ t_tinylog”目錄，查看目錄下的文件， 如下圖所示： 表 t_tinylog 中的每個列都單獨(dú)對應(yīng)一個*.bin 文件，同時還有一 個 sizes.json 文件存儲元數(shù)據(jù)，記錄了每個 bin 文件中數(shù)據(jù)大小

StripeLog

StripeLog 數(shù)據(jù)存儲會劃分塊，每次插入對應(yīng)一個數(shù)據(jù)塊，擁 有更高的查詢性能（擁有.mrk 標(biāo)記文件，支持并行查詢）。StripeLog 引擎將所有列存 儲在一個文件中，使用了更少的文件描述符。對每一次 Insert 請求，ClickHouse 將 數(shù)據(jù)塊追加在表文件的末尾，逐列寫入。StripeLog 引擎不支持 ALTER UPDATE 和 ALTER DELETE 操作。 create table t_stripelog(id UInt8,name String,age UInt8) engine = StripeLog; #向表 t_stripelog 中插入數(shù)據(jù)，這里插入分多次插入，會將數(shù)據(jù)插入不同的數(shù)據(jù)塊中 node1 :) insert into t_stripelog values (1,'張三',18); node1 :) insert into t_stripelog values (2,'李四',19); 當(dāng) 在 newdb 庫 中創(chuàng) 建表 t_stripelog 后 ，在 ClickHouse 保 存數(shù) 據(jù)的 目錄 /var/lib/clickhouse/data/newdb/下會多一個 t_stripelog 目錄，如圖所示： ? data.bin:數(shù)據(jù)文件，所有列字段都寫入 data.bin 文件中。 ? index.mrk:數(shù)據(jù)標(biāo)記文件，保存了數(shù)據(jù)在 data.bin 文件中的位置信息，即每 個插入數(shù)據(jù)列的 offset 信息，利用數(shù)據(jù)標(biāo)記能夠使用多個線程，并行度取 data.bin 壓縮數(shù)據(jù)，提升查詢性能。 ? sizes.json:元數(shù)據(jù)文件，記錄了 data.bin 和 index.mrk 大小信息。

Log

Log 引擎表適用于臨時數(shù)據(jù)，一次性寫入、測試場景。Log 引擎結(jié)合了 TinyLog 表引 擎和 StripeLog 表引擎的長處，是 Log 系列引擎中性能最高的表引擎。 Log 表引擎會將每一列都存在一個文件中，對于每一次的 INSERT 操作，會生成數(shù)據(jù) 塊，經(jīng)測試，數(shù)據(jù)塊個數(shù)與當(dāng)前節(jié)點的 core 數(shù)一致。 create table t_log(id UInt8 ,name String ,age UInt8 ) engine = Log; #向表 t_log 中插入數(shù)據(jù)，分多次插入，插入后數(shù)據(jù)存入數(shù)據(jù)塊 node1 :) insert into t_log values (1,'張三',18); node1 :) insert into t_log values (2,'李四',19); node1 :) insert into t_log values (3,'王五',20); node1 :) insert into t_log values (4,'馬六',21); node1 :) insert into t_log values (5,'田七',22); 當(dāng) 在 newdb 庫 中 創(chuàng) 建 表 t_log 后 ， 在 ClickHouse 保 存 數(shù) 據(jù) 的 目 錄 /var/lib/clickhouse/data/newdb/下會多一個 t_log 目錄，如圖所示： 我們發(fā)現(xiàn)表 t_log 中的每個列都對應(yīng)一個*.bin 文件。其他兩個文件的解釋如下： ? __marks.mrk：數(shù)據(jù)標(biāo)記，保存了每個列文件中的數(shù)據(jù)位置信息，利用數(shù)據(jù)標(biāo)記 能夠使用多個線程，并行度取 data.bin 壓縮數(shù)據(jù)，提升查詢性能。 ? sizes.json:記錄了*.bin 和__mark.mrk 大小的信息。

Special系列引擎

Memory 表引擎直接將數(shù)據(jù)保存在內(nèi)存中，ClickHouse 中的 Memory 表引擎具有以 下特點: ? Memory 引擎以未壓縮的形式將數(shù)據(jù)存儲在 RAM 中，數(shù)據(jù)完全以讀取時獲得的形 式存儲。 ? 并發(fā)數(shù)據(jù)訪問是同步的，鎖范圍小，讀寫操作不會相互阻塞。 ? 不支持索引。 ? 查詢是并行化的，在簡單查詢上達(dá)到最大速率（超過 10 GB /秒），在相對較少 的行（最多約 100,000,000）上有高性能的查詢。 ? 沒有磁盤讀取，不需要解壓縮或反序列化數(shù)據(jù)，速度更快（在許多情況下，與 MergeTree 引擎的性能幾乎一樣高）。 ? 重新啟動服務(wù)器時，表存在，但是表中數(shù)據(jù)全部清空。 ? Memory 引擎多用于測試。

create table t_memory(id UInt8 ,name String, age UInt8) engine = Memory; 注意：”Memory”表引擎寫法固定，不能小寫。同時創(chuàng)建好表 t_memory 后，在對應(yīng) 的磁盤目錄/var/lib/clickhouse/data/newdb 下沒有“ t_memory”目錄，基于內(nèi)存 存儲，當(dāng)重啟 ClickHouse 服務(wù)后，表 t_memory 存在，但是表中數(shù)據(jù)全部清空。

Merge

Merge 引擎 (不要跟 MergeTree 引擎混淆) 本身不存儲數(shù)據(jù)，但可用于同時從任 意多個其他的表中讀取數(shù)據(jù)，這里需要多個表的結(jié)構(gòu)相同，并且創(chuàng)建的 Merge 引擎表的結(jié) 構(gòu)也需要和這些表結(jié)構(gòu)相同才能讀取。 讀是自動并行的，不支持寫入。讀取時，那些被真正讀取到數(shù)據(jù)的表如果設(shè)置了索引， 索引也會被使用。 Merge 引擎的參數(shù)：一個數(shù)據(jù)庫名和一個用于匹配表名的正則表達(dá)式： Merge(數(shù)據(jù)庫, 正則表達(dá)式) 例如：Merge(hits, '^WatchLog') 表示數(shù)據(jù)會從 hits 數(shù)據(jù)庫中表名匹配正則 ‘ ^WatchLog’ 的表中讀取。 注意:當(dāng)選擇需要讀取的表時，會匹配正則表達(dá)式匹配上的表，如果當(dāng)前 Merge 表的 名稱也符合正則表達(dá)式匹配表名，這個 Merge 表本身會自動排除，以避免進(jìn)入遞歸死循環(huán)， 當(dāng)然也可以創(chuàng)建兩個相互無限遞歸讀取對方數(shù)據(jù)的 Merge 表，但這并沒有什么意義。 例子： create table m_t1 (id UInt8 ,name String,age UInt8) engine = TinyLog; node1 :) insert into m_t1 values (1,'張三',18),(2,'李四',19） #在 newdb 庫中創(chuàng)建表 m_t2，并插入數(shù)據(jù) node1 :) create table m_t2 (id UInt8 ,name String,age UInt8) engine = TinyLog; node1 :) insert into m_t2 values (3,'王五',20),(4,'馬六',21) #在 newdb 庫中創(chuàng)建表 m_t3，并插入數(shù)據(jù) node1 :) create table m_t3 (id UInt8 ,name String,age UInt8) engine = TinyLog; node1 :) insert into m_t3 values (5,'田七',22),(6,'趙八',23) #在 newdb 庫中創(chuàng)建表 t_merge，使用 Merge 引擎，匹配 m 開頭的表 node1 :) create table t_merge (id UInt8,name String,age UInt8) engine = Merge(newdb,'^m'); #查詢 t_merge 表中的數(shù)據(jù) node1 :) select * from t_merge;

Distributed

Distributed 是 ClickHouse 中 分 布 式 引 擎 ， 之 前 所 有 的 操 作 雖 然 說 是 在 ClickHouse 集群中進(jìn)行的，但是實際上是在 node1 節(jié)點中單獨(dú)操作的，與 node2、node3 無關(guān)，使用分布式引擎聲明的表才可以在其他節(jié)點訪問與操作。 Distributed 引擎和 Merge 引擎類似，本身不存放數(shù)據(jù),功能是在不同的 server 上把多張相同結(jié)構(gòu)的物理表合并為一張邏輯表。 分布式引擎語法： Distributed(cluster_name, database_name, table_name[, sharding_key]) 對以上語法解釋： ? cluster_name：集群名稱，與集群配置中的自定義名稱相對應(yīng)。配置在 /etc/metrika.xml 文件中，如下圖：

目錄文件有的在：[root@dc-o-ch-08 dev]# cat /etc/clickhouse-server/metrika.xml

? database_name：數(shù)據(jù)庫名稱。 ? table_name：表名稱。 ? sharding_key：可選的，用于分片的 key 值，在數(shù)據(jù)寫入的過程中，分布式表 會依據(jù)分片 key 的規(guī)則，將數(shù)據(jù)分布到各個節(jié)點的本地表。 注意：創(chuàng)建分布式表是讀時檢查的機(jī)制，也就是說對創(chuàng)建分布式表和本地表的順序并沒 有強(qiáng)制要求。

例子：

#使用默認(rèn)的 default 庫，在每個節(jié)點上創(chuàng)建表 test_table node1 :) create table test_local (id UInt8,name String) engine= TinyLog node2 :) create table test_local (id UInt8,name String) engine= TinyLog node3 :) create table test_local (id UInt8,name String) engine= TinyLog #在 node1 上創(chuàng)建分布式表 t_distributed，表引擎使用 Distributed 引擎 node1 :) create table t_distributed(id UInt8,name String) engine = Distributed(clickhouse_cluster_3shards_1replicas,default,test_local,id); 注意：以上分布式表 t_distributed 只存在與 node1 #分別在 node1、node2、node3 節(jié)點上向表 test_local 中插入 2 條數(shù)據(jù) node1 :) insert into test_local values (1,'張三'),(2,'李四'); node2 :) insert into test_local values (3,'王五'),(4,'馬六'); node3 :) insert into test_local values (5,'田七'),(6,'趙八'); #查詢分布式表 t_distributed 中的數(shù)據(jù) node1 :) select * from t_distributed; #向分布式表 t_distributed 中插入一些數(shù)據(jù)，然后查詢 node1、node2、node3 節(jié) 點上的 test_local 數(shù)據(jù)，發(fā)現(xiàn)數(shù)據(jù)已經(jīng)分布式存儲在不同節(jié)點上 node1 :) insert into t_distributed values (7,'zs'),(8,'ls'),(9,'ww'),(10,'ml'),(11,'tq'),(12,'zb'); #node1 查詢本地表 test_local 以上在 node1 節(jié)點上創(chuàng)建的分布式表 t_distributed 雖然數(shù)據(jù)是分布式存儲在每 個 clickhouse 節(jié)點上的，但是只能在 node1 上查詢 t_distributed 表，其 他 clickhouse 節(jié)點查詢不到此分布式表。如果想要在每臺 clickhouse 節(jié)點上都能訪問分 布式表我們可以指定集群，創(chuàng)建分布式表：

?#創(chuàng)建分布式表 t_cluster ,引擎使用 Distributed 引擎

create table t_cluster on cluster clickhouse_cluster_3shards_1replicas (id UInt8,name String) engine =Distributed(clickhouse_cluster_3shards_1replicas,default,test_local,id); 中使用了 ON CLUSTER 分布式 DDL（數(shù)據(jù)庫定義語言），這意味著在集群 的每個分片節(jié)點上，都會創(chuàng)建一張 Distributed 表，這樣便可以從其中任意一端發(fā)起對 所有分片的讀、寫請求。

MergeTree系列表引擎

所有的表引擎中，最為核心的當(dāng)屬 MergeTree 系列表引擎，這些表引擎擁有最為強(qiáng) 大的性能和最廣泛的使用場合。對于非 MergeTree 系列的其他引擎而言，主要用于特殊用 途，場景相對有限。而 MergeTree 系列表引擎是官方主推的存儲引擎，有主鍵索引、數(shù)據(jù) 分區(qū)、數(shù)據(jù)副本、數(shù)據(jù)采樣、刪除和修改等功能，支持幾乎所有 ClickHouse 核心功能。MergeTree 系 列 表 引 擎 包 含 ： MergeTree 、 ReplacingMergeTree 、 SummingMergeTree（匯總求和功能）、AggregatingMergeTree（聚合功能）、 CollapsingMergeTree（折疊刪除功能）、VersionedCollapsingMergeTree（版本折疊功能）引擎，在這些的基礎(chǔ)上還可以疊加 Replicated 和 Distributed。MergeTree 在寫入一批數(shù)據(jù)時，數(shù)據(jù)總會以數(shù)據(jù)片段的形式寫入磁盤，且數(shù)據(jù)片段在磁盤上不可修改。為了避免片段過多，ClickHouse 會通過后臺線程，定期合并這些數(shù)據(jù)片段，屬于相同分區(qū)的數(shù)據(jù)片段會被合成一個新的片段。這種數(shù)據(jù)片段往復(fù)合并的特點，也 正是合并樹名稱的由來。 MergeTree 作為家族系列最基礎(chǔ)的表引擎，主要有以下特點： ? 存儲的數(shù)據(jù)按照主鍵排序：創(chuàng)建稀疏索引加快數(shù)據(jù)查詢速度。 ? 支持?jǐn)?shù)據(jù)分區(qū)，可以通過 PARTITION BY 語句指定分區(qū)字段。 ? 支持?jǐn)?shù)據(jù)副本。 ? 支持?jǐn)?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() ORDER BY expr [PARTITION BY expr] [PRIMARY KEY expr] [SAMPLE BY expr] [TTL expr [DELETE|TO DISK 'xxx'|TO VOLUME 'xxx'], ...] [SETTINGS name=value, ...] 關(guān)于以上建表語句的解釋如下： ? ENGINE：ENGINE = MergeTree()，MergeTree 引擎沒有參數(shù)。 ? ORDER BY：排序字段。比如 ORDER BY (Col1, Col2)，值得注意的是，如果 沒有使用 PRIMARY KEY 顯式的指定主鍵 ORDER BY 排序字段自動作為主鍵。如 果不需要排序，則可以使用 ORDER BY tuple() 語法，這樣的話，創(chuàng)建的表也 就不包含主鍵。這種情況下，ClickHouse 會按照插入的順序存儲數(shù)據(jù)。必選項。 PARTITION BY ： 分 區(qū) 字 段 ， 例 如 要 按 月 分 區(qū) ， 可 以 使 用 表 達(dá) 式 toYYYYMM(date_column)，這里的 date_column 是一個 Date 類型的列，分 區(qū)名的格式會是"YYYYMM"。可選。 PRIMARY KEY：指定主鍵，如果排序字段與主鍵不一致，可以單獨(dú)指定主鍵字段。 否則默認(rèn)主鍵是排序字段。大部分情況下不需要再專門指定一個 PRIMARY KEY 子句，注意，在 MergeTree 中主鍵并不用于去重，而是用于索引，加快查詢速度。 可選。 另外，如果指定了 PRIMARY KEY 與排序字段不一致，要保證 PRIMARY KEY 指 定的主鍵是 ORDER BY 指定字段的前綴，比如： 這種強(qiáng)制約束保障了即便在兩者定義不同的情況下，主鍵仍然是排序鍵的前綴，不 會出現(xiàn)索引與數(shù)據(jù)順序混亂的問題。 --允許 ... ... ORDER BY (A,B,C) PRIMARY KEY A --報錯 ... ... ORDER BY (A,B,C) PRIMARY KEY B DB::Exception: Primary key must be a prefix of the sorting key SAMPLE BY：采樣字段，如果指定了該字段，那么主鍵中也必須包含該字段。比 如 SAMPLE BY intHash32(UserID) ORDER BY (CounterID, EventDate,intHash32(UserID))。可選。 ? TTL：數(shù)據(jù)的存活時間。在 MergeTree 中，可以為某個列字段或整張表設(shè)置 TTL。 當(dāng)時間到達(dá)時，如果是列字段級別的 TTL，則會刪除這一列的數(shù)據(jù)；如果是表級別 的 TTL，則會刪除整張表的數(shù)據(jù)。可選。 ? SETTINGS：額外的參數(shù)配置。可選。 例子： create table t_mt(id UInt8,name String,age UInt8,birthday Date,location String) engine=MergeTree() order by (id,age) partition by toYYYYMM(birthday);insert into t_mt values(1,'張三',18,'2021-06-01','上海'),(1,'張三',18,'2021-06-01','上海'),(2,'李四',19,'2021-02-10','北京'),(3,'王五',12,'2021-06-18','天津'),(1,'馬六',10,'2021-06-18','上海')(5,'田七',22,'2021-06-09','廣州')insert into t_mt values(1,'趙八',18,'2021-06-01','北京'),(2,'李九',19,'2021-02-10','天津'),(3,'鄭十',12,'2021-07-01','北京')optimize table t_mt partition '202102' optimize table t_mt partition '202106' 新插入的數(shù)據(jù)新生成了數(shù)據(jù)塊，實際上這里在底層對應(yīng)新的分區(qū)文件片段，那 么為什么新插入的數(shù)據(jù)沒有根據(jù)日期和之前的數(shù)據(jù)放入同一個分區(qū)文件呢？MergeTree引 擎會在插入數(shù)據(jù) 15 分鐘左右，將同一個分區(qū)的各個分區(qū)文件片段合并成一整個分區(qū)文件。 這里也可以手動執(zhí)行 OPTIMIZE 語句手動觸發(fā)合并 注意：以上 optimize 操作，也可以直接寫 optimize table t_mt, 每次執(zhí)行合并一 個分區(qū)，如果有多個分區(qū)需要執(zhí)行多次。如果想一次合并所有分區(qū)，也可以寫成 optimize table t_mt final; 注意：MergeTree 引擎表中主鍵并不用于去重，而是用于索引，加快查詢速度。

MergeTree引擎表的目錄結(jié)構(gòu)

t_mt ， 當(dāng) 插 入 數(shù) 據(jù) 完 成 后 ， 在 clickhouse 節(jié) 點 /var/lib/clickhouse/data/newdb/路徑下會生成對應(yīng)目錄“t_mt”,進(jìn)入此目錄下， 可以看到對應(yīng)的分區(qū)目錄,通過： DC-pre-clickhost-01 :) select * from system.parts where table='t_mt'查詢分區(qū)信息

? table 代表當(dāng)前表。 ? partition 是當(dāng)前表的分區(qū)名稱。 ? name 是對應(yīng)到磁盤上數(shù)據(jù)所在的分區(qū)目錄片段。例如“ 202102_2_2_0”中“202102” 是分區(qū)名稱，“2”是數(shù)據(jù)塊的最小編號，“2”是數(shù)據(jù)塊的最大編號，“0”代表該塊 在 MergeTree 中第幾次合并得到。 ? active 代表當(dāng)前分區(qū)片段的狀態(tài)：1 代表激活狀態(tài)，0 代表非激活狀態(tài)，非激活片段 是那些在合并到較大片段之后剩余的源數(shù)據(jù)片段，損壞的數(shù)據(jù)片段也表示為非活動狀 態(tài)。非激活片段會在合并后的 10 分鐘左右被刪除。 數(shù)據(jù)目錄說明： 對以上目錄的解釋如下： ? checksums.txt：校驗文件，使用二進(jìn)制格式存儲。它保存了余下各類文件(primary. idx、count.txt 等)的 size 大小及 size 的哈希值，用于快速校驗文件的完整性和 正確性。 ? columns.txt： 存儲當(dāng)前分區(qū)所有列信息。使用明文格式存儲。 ? count.txt：計數(shù)文件，使用明文格式存儲。用于記錄當(dāng)前數(shù)據(jù)分區(qū)目錄下數(shù)據(jù)的總 行數(shù)。 ? primary.idx：一級索引文件，使用二進(jìn)制格式存儲。用于存放稀疏索引，一張 MergeTree 表只能聲明一次一級索引，即通過 ORDER BY 或者 PRIMARY KEY 指定字 段。借助稀疏索引，在數(shù)據(jù)查詢的時能夠排除主鍵條件范圍之外的數(shù)據(jù)文件，從而有效減少數(shù)據(jù)掃描范圍，加速查詢速度。 ? 列.bin：數(shù)據(jù)文件，使用壓縮格式存儲，默認(rèn)為 LZ4 壓縮格式，用于存儲某一列的數(shù) 據(jù)。由于 MergeTree 采用列式存儲，所以每一個列字段都擁有獨(dú)立的.bin 數(shù)據(jù)文件， 并以列字段名稱命名。 ? 列.mrk2：列字段標(biāo)記文件，使用二進(jìn)制格式存儲。標(biāo)記文件中保存了.bin 文件中數(shù) 據(jù)的偏移量信息 ? partition.dat 與 minmax_[Column].idx：如果指定了分區(qū)鍵，則會額外生成 partition.dat 與 minmax 索 引 文 件 ， 它 們 均 使 用 二 進(jìn) 制 格 式 存 儲 。 partition.dat 用于保存當(dāng)前分區(qū)下分區(qū)表達(dá)式最終生成的值，即分區(qū)字段值；而 minmax 索引用于記錄當(dāng)前分區(qū)下分區(qū)字段對應(yīng)原始數(shù)據(jù)的最小和最大值。比如當(dāng)使用 birthday 字段對應(yīng)的原始數(shù)據(jù)為 2021-02-17、2021-02-23，分區(qū)表達(dá)式為 PARTITION BY toYYYYMM(birthday)，即按月分區(qū)。partition.dat 中保存的 值將會是 202102，而 minmax 索引中保存的值將會是 2021-02-17、2021-02-23。 ClickHouse MergeTree 引擎表支持分區(qū)，索引，修改，并發(fā)查詢數(shù)據(jù)，當(dāng)查詢 MergeTree 表數(shù)據(jù)時，首先向 primary.idx 文件中獲取對應(yīng)的索引，根據(jù)索引找到 【列.mrk2】文件獲取對應(yīng)的數(shù)據(jù)塊偏移量，然后再根據(jù)偏移量從【列.bin】文件中讀取 塊數(shù)據(jù)。

MergeTree設(shè)置表引擎

給表設(shè)置分區(qū)可以在查詢過程中跳過不需要的數(shù)據(jù)目錄，提升查詢效率。在 ClickHouse 中并不是所有的表都支持分區(qū)，目前只有 MergeTree 家族系列的表引擎才 支持?jǐn)?shù)據(jù)分區(qū)。 通過前面的學(xué)習(xí)，我們知道向 MergeTree 分區(qū)表中每次插入數(shù)據(jù)時，每次都會生成對 應(yīng)的分區(qū)片段，不會立刻合并相同分區(qū)的數(shù)據(jù)，需要等待 15 分鐘左右，ClickHouse 會自 動合并相同的分區(qū)片段，并刪除合并之前的源數(shù)據(jù)片段，當(dāng)然這里我們也可以手動執(zhí)行 OPTIMIZE 語句手動觸發(fā)合并分區(qū)表中的分區(qū)片段。通過下面案例來學(xué)習(xí)分區(qū)表中分區(qū)片 段合并的規(guī)則。 create table login_info(id UInt8,name String,log_time Date)engine=MergeTree() order by (id) partition by toYYYYMM(log_time); insert into login_info values(1,'zs','2021-06-01'),(2,'ls','2021-06-01'),(3,'ww','2021-07-01'),(4,'ml','2021-07-01');

insert into login_info values(5,'zs','2021-06-01'),(6,'ls','2021-06-01'),(7,'ww','2021-07-01'),(8,'ml','2021-07-01'); 通過插入數(shù)據(jù)之后再次查詢發(fā)現(xiàn)，相同分區(qū)的數(shù)據(jù)展示在不同的數(shù)據(jù)塊中。在 clickhouse 節(jié) 點 上 再 次 查 看 表 login_info 數(shù) 據(jù) 目 錄 /var/lib/clickhouse/data/newdb/login_info，如下圖示： “ 202106_3_3_0”為例，“202006”為分區(qū)，“3”代表數(shù)據(jù)塊的最小編號，“3” 代表數(shù)據(jù)塊的最大編號，“0”代表合并的第幾次（合并樹中塊的級別）。 手動執(zhí)行 OPTIMIZE 語句手動觸發(fā)合并分區(qū)表中的分區(qū)片段: #執(zhí)行如下命令，手動合并分區(qū)片段 node1 :) optimize table login_info partition '202106' ; node1 :) optimize table login_info partition '202107' ;

?

MergeTree 分區(qū)表合并分區(qū)規(guī)則如下：獲取相同分區(qū)片段中最小編號和最大編號，組 合成新的分區(qū)片段，同時修改合并的次數(shù)（合并樹中塊的級別），合并示意圖如下：

繼續(xù)插入數(shù)據(jù)

insert into login_info values(9,'zs','2021-06-01'),(10,'ls','2021-06-01'),(11,'ww','2021-07-01'),(12,'ml','2021-07-01');

新增之后的情況：

?再次執(zhí)行合并分區(qū)命令，合并表 login_info 分區(qū)片段：

node1 :) optimize table login_info partition '202106' ; node1 :) optimize table login_info partition '202107' ; 此外，表設(shè)置分區(qū)字段時，分區(qū)健不僅可以指定成時間列，也可以是表中任意列或者列 的表達(dá)式。下面案例使用表中的地區(qū)列當(dāng)做分區(qū)： create table emp_info(id UInt8,name String,age UInt8,loc String,salary Decimal32(2))engine=MergeTree() order by (id) partition loc; insert into login_info values(1,'zs',18,'上海',10.11),(2,'zs',18,'上海',10.11),(3,'zs',18,'上海',10.11),(4,'zs',18,'上海',10.11),(5,'zs',18,'上海',10.11); 注 意 ： 如 果 按 照 字 符 串 字 段 來 進(jìn) 行 分 區(qū) ， 在 底 層 /var/lib/clickhouse/data/newdb/目錄下對應(yīng)的表 emp_info 中的分區(qū)片段名稱 是使用字符串的 hashcode+編碼的形式來命名。

ReplacingMergeTree

MergeTree 不 能 對 相 同 主 鍵 的 數(shù) 據(jù) 進(jìn) 行 去 重 ， ClickHouse 提 供 了 ReplacingMergeTree 引擎，可以針對同分區(qū)內(nèi)相同主鍵的數(shù)據(jù)進(jìn)行去重，它能夠在合并 分區(qū)時刪除重復(fù)的數(shù)據(jù)。值得注意的是，ReplacingMergeTree 只是在一定程度上解決了 數(shù)據(jù)重復(fù)問題，由于自動分區(qū)合并機(jī)制在后臺定時執(zhí)行，所以并不能完全保障數(shù)據(jù)不重復(fù)。 ReplacingMergeTree 適用于在后臺清除重復(fù)的數(shù)據(jù)以節(jié)省空間。 ? ReplaceingMergeTree 建表語句： 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] [SAMPLE BY expr] [SETTINGS name=value, ... 以上建表語句的解釋如下： ? [ver] ：可選參數(shù)，指定列的版本，可以是 UInt*、Date 或者 DateTime 類型 的字段作為版本號。該參數(shù)決定了數(shù)據(jù)去重的方式。當(dāng)沒有指定[ver]時，保留最 后插入的數(shù)據(jù)，也就是最新的數(shù)據(jù)；如果指定了具體的[ver]列，則保留最大版本 數(shù)據(jù)。 使用 ReplacingMergeTree 是需要注意以下幾點： ? 如何判斷數(shù)據(jù)重復(fù) ReplacingMergeTree 在去除重復(fù)數(shù)據(jù)時，是以 ORDERBY 排序鍵為基準(zhǔn)的，而不是 PRIMARY KEY。 ? 何時刪除重復(fù)數(shù)據(jù) 在執(zhí)行分區(qū)合并時，會觸發(fā)刪除重復(fù)數(shù)據(jù)。optimize 的合并操作是在后臺執(zhí)行的，無 法預(yù)測具體執(zhí)行時間點，除非是手動執(zhí)行。 ? 不同分區(qū)的重復(fù)數(shù)據(jù)不會被去重 ReplacingMergeTree 是以分區(qū)為單位刪除重復(fù)數(shù)據(jù)的。只有在相同的數(shù)據(jù)分區(qū)內(nèi)重 復(fù)的數(shù)據(jù)才可以被刪除，而不同數(shù)據(jù)分區(qū)之間的重復(fù)數(shù)據(jù)依然不能被剔除。 ? 數(shù)據(jù)去重的策略是什么 如果沒有設(shè)置[ver]版本號，則保留同一組重復(fù)數(shù)據(jù)中的最新插入的數(shù)據(jù)；如果設(shè)置了 [ver]版本號，則保留同一組重復(fù)數(shù)據(jù)中 ver 字段取值最大的那一行。 ? optimize 命令使用 一般在數(shù)據(jù)量比較大的情況，盡量不要使用該命令。因為在海量數(shù)據(jù)場景下，執(zhí)行 optimize 要消耗大量時間。 測試去重按照 Order by 字段進(jìn)行去重，而不是按照 primary 主鍵字段進(jìn)行去重。 #創(chuàng)建表 t_replacing_mt ,使用 ReplacingMergeTree 引擎 create table t_replacing_mt(id UInt8,name String,age UInt8,gender String)engine=ReplacingMergeTree() order by (id,age) primary key id partition by gender; insert into t_replacing_mt values(1,'張三',18,'男'),(2,'李四',19,'女'),(3,'王五',20,'男') insert into t_replacing_mt values(1,'張三',18,'10') select * from t_replacing_mt; optimize table t_replacing_mt; select * from t_replacing_mt; insert into t_replacing_mt values(1,'張三三',18,'男'); select * from t_replacing_mt; optimize table t_replacing_mt; select * from t_replacing_mt; 注意：通過以上測試發(fā)現(xiàn) ClickHouse ReplacingMergeTree 中去除重復(fù)數(shù)據(jù)時，是以 ORDERBY 排序鍵為基準(zhǔn)的，而不是 PRIMARY KEY。 ? 測試不指定[ver]列時，插入相同排序字段的數(shù)據(jù)，保留最新一條數(shù)據(jù)。 #刪除表 t_replacing_mt 重建,使用 ReplacingMergeTree 引擎 create table t_replacing_mt(id UInt8,name String,age UInt8,gender String)engine=ReplacingMergeTree() order by id primary key id partition by gender;insert into t_replacing_mt values(1,'張三',18,'男'),(2,'李四',19,'女'),(3,'王五',20,'男') select * from t_replacing_mt; insert into t_replacing_mt values(1,'張三',18,'男') select * from t_replacing_mt; optimize table t_replacing_mt 注意：通過以上測試可以發(fā)現(xiàn)，ClickHouse ReplacingMergeTree 中不指定[ver]列 時，當(dāng)插入排序字段相同的數(shù)據(jù)時，保留最新一條數(shù)據(jù) ? 測試指定[ver]列時，插入相同排序字段的數(shù)據(jù)，保留當(dāng)前[ver]列最大值。 create table t_replacing_mt(id UInt8,name String,age UInt8,gender String)engine=ReplacingMergeTree() order by id primary key id partition by gender(age);insert into t_replacing_mt values(1,'張三',18,'男'),(2,'李四',19,'女'),(3,'王五',20,'男') select * from t_replacing_mt; insert into t_replacing_mt values(1,'張三',18,'男') select * from t_replacing_mt; optimize table t_replacing_mt 注意：通過以上測試可以發(fā)現(xiàn)，在 ClickHouse 中創(chuàng)建 ReplacingMergeTree 時，如果 指定了[ver]列，當(dāng)存在 Order by 字段重復(fù)時，會保留 ver 列最大值對應(yīng)的行。

?? 測試不同分區(qū)中有相同的 Order by 字段時，不去重。

注意：通過以上測試可以發(fā)現(xiàn)，在 ClickHouse 中創(chuàng)建 ReplacingMergeTree 時，不同 分區(qū)中相同的 Order by 字段不會去重。

SummingMergeTree

該引擎繼承了 MergeTree 引擎，當(dāng)合并 SummingMergeTree 表的數(shù)據(jù)片段時， ClickHouse 會把所有具有相同主鍵的行合并為一行，該行包含了被合并的行中具有數(shù)值 數(shù)據(jù)類型的列的匯總值，即如果存在重復(fù)的數(shù)據(jù)，會對對這些重復(fù)的數(shù)據(jù)進(jìn)行合并成一條數(shù) 據(jù)，類似于 group by 的效果，可以顯著減少存儲空間并加快數(shù)據(jù)查詢速度。 如果用戶只需要查詢數(shù)據(jù)的匯總結(jié)果，不關(guān)心明細(xì)數(shù)據(jù)，并且數(shù)據(jù)的匯總條件是預(yù)先明 確的，即 GROUP BY 的分組字段是確定的，可以使用該表引擎。 ? SummingMergeTree 建表語句： 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, ...] 對以上建表語句的解釋如下： ? [columns]: 將要被匯總的列,或者多個列，多個列需要寫在元組中。可選參數(shù)。 所選的列必須是數(shù)值類型，并且不可位于主鍵中。如果沒有指定 [columns]， ClickHouse 會把所有不在主鍵中的數(shù)值類型的列都進(jìn)行匯總。 使用 SummingMergeTree 注意以下幾點： ? SummingMergeTree 是根據(jù)什么對兩條數(shù)據(jù)進(jìn)行合并的 用 ORBER BY 排序鍵作為聚合數(shù)據(jù)的條件 Key。即如果排序 key 是相同的，則會合并 成一條數(shù)據(jù)，并對指定的合并字段進(jìn)行聚合。 ? 僅對分區(qū)內(nèi)的相同排序 key 的數(shù)據(jù)行進(jìn)行合并 以數(shù)據(jù)分區(qū)為單位來聚合數(shù)據(jù)。當(dāng)分區(qū)合并時，同一數(shù)據(jù)分區(qū)內(nèi)聚合 Key 相同的數(shù)據(jù) 會被合并匯總，而不同分區(qū)之間的數(shù)據(jù)則不會被匯總。 ? 如果沒有指定聚合字段，會怎么聚合 如果沒有指定聚合字段，則會按照非主鍵的數(shù)值類型字段進(jìn)行聚合。 ? 對于非匯總字段的數(shù)據(jù)，該保留哪一條 如果兩行數(shù)據(jù)除了排序字段相同，其他的非聚合字段不相同，那么在聚合發(fā)生時，會保 留最初的那條數(shù)據(jù)，新插入的數(shù)據(jù)對應(yīng)的那個字段值會被舍棄。 ? 測試不指定聚合字段同時測試不同分區(qū)內(nèi)，相同排序 key 數(shù)據(jù)不會被合并 create table t_summing_mt(id UInt8,name String,age UInt8,loc String,dept String,workdays UInt8,salary Decimal32(2))engine=SummingMergeTree() order by (id,age) primary key id partition by loc;
insert into t_summing_mt values(1,'張三',18,'北京','大數(shù)據(jù)',24,10000),(2,'李四',19,'上海','java',22,8000),(3,'王五',20,'北京','Java',24,10000)
select * from t_summing_mt
注意：我們可以看到當(dāng)不指定 聚合字段時，有相同排序字段行進(jìn)行聚合時，會將數(shù)值類型 的字段進(jìn)行聚合合并。
#繼續(xù)向表 t_summing_mt 中插入以下數(shù)據(jù)：?
node1 :) insert into t_summing_mt values (1,' 張 三 ',18,' 南 京 ','java',18,12000);
注意：不同分區(qū)內(nèi)相同的排序 key 的數(shù)據(jù)不能被合并 測試指定一個聚合字段 create table t_suming_mt(id UInt8,name String,age UInt8,loc String,dept String,workdays UInt8,salary Decimal32(2))engine=SumingMergeTree(salary) order by (id,age) primary key id partition by loc; insert into t_suming_mt values(1,'張三',18,'北京','大數(shù)據(jù)'，24,10000),(2,'李四',19,'上海','Java'，22,8000)，,(3,'王五',20,'北京 ','Java'，26,12000) insert into t_suming_mt values(1,'馬六',18,'北京','前端'，27,15000)注意：我們可以看到當(dāng)指定一個聚合字段時，有相同排序字段行進(jìn)行聚合時，會按照這個 數(shù)值字段進(jìn)行合并，其他的保留最開始一條數(shù)據(jù)的信息。測試多字段聚合： create table t_suming_mt(id UInt8,name String,age UInt8,loc String,dept String,workdays UInt8,salary Decimal32(2))engine=SumingMergeTree((salary,workdays)) order by (id,age) primary key id partition by loc; 注意：我們可以看到當(dāng)指定多個聚合字段時，有相同排序字段行進(jìn)行聚合時，會按照指定 的多個數(shù)值字段進(jìn)行合并，其他的保留最開始一條數(shù)據(jù)的信息。

AggregatingMergeTree：

表引擎繼承自 MergeTree，可以使用 AggregatingMergeTree 表來做增量數(shù)據(jù) 統(tǒng)計聚合。如果要按一組規(guī)則來合并減少行數(shù)，則使用 AggregatingMergeTree 是合適 的。AggregatingMergeTree 是通過預(yù)先定義的聚合函數(shù)計算數(shù)據(jù)并通過二進(jìn)制的格式存入表內(nèi)。與 SummingMergeTree 的區(qū)別在于：SummingMergeTree 對非主鍵列進(jìn)行 sum 聚合，而 AggregatingMergeTree 則可以指定各種聚合函數(shù)。對某些字段需要進(jìn)行聚合時， 需要在創(chuàng)建表字段時指定成 AggregateFunction 類型： 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, ...] #創(chuàng)建表 t_aggregating_mt ，使用 AggregatingMergeTree 引擎，指定 salary 字 create table t_aggregating_mt(id UInt8,name String,age UInt8,loc String,dept String,workdays UInt8,salary AggregateFunction(sum,Decimal32(2)))engine = AggregatingMergeTree() order by (id,age) primary key id partition by loc;#對于 AggregateFunction 類型的列字段，在進(jìn)行數(shù)據(jù)的寫入和查詢時與其他的表引 擎有很大區(qū)別，在寫入數(shù)據(jù)時，需要調(diào)用 *-State 函數(shù)；而在查詢數(shù)據(jù)時，則需要調(diào)用 相應(yīng)的 *-Merge 函數(shù)。DC-pre-clickhost-01 :) insert into t_aggregating_mt select 1,'張三',18,'北京','java',18,sumState(toDecimal32(10000,2)); DC-pre-clickhost-01 :) insert into t_aggregating_mt select 1,'張三',18,'北京','java',18,sumState(toDecimal32(10000,2)); DC-pre-clickhost-01 :) insert into t_aggregating_mt select 3,'王五',20,'北京','java',25,sumState(toDecimal32(12000,2));查詢數(shù)據(jù)時，如果正常語句查詢，aggregateFunction 類型的列不會正常顯示數(shù)據(jù)， 針對以上的數(shù)據(jù)需要使用 sumMerge 來展示數(shù)據(jù)。 ##錯誤方式查詢表 t_aggregating_mt 中的數(shù)據(jù)

???????

#正確方式查詢表 t_aggregating_mt 中的數(shù)據(jù),注意需要跟上 groupBy 向表中插入排序字段相同的數(shù)據(jù)進(jìn)行分區(qū)聚合時，數(shù)據(jù)按照建表指定的聚合字段進(jìn)行合 并，其他的非聚合字段會保留最初的那條數(shù)據(jù)，新插入的數(shù)據(jù)對應(yīng)的字段值會被舍棄。

?# 向表中插入新的一條數(shù)據(jù)

?#查詢表中的數(shù)據(jù)，這里為了方便看到分區(qū)不合并，直接查詢

#使用 optimize 命令合并相同分區(qū)數(shù)據(jù)

?

以上方式使用 AggregatingMergeTree 表引擎比較不方便，更多情況下，我們將 AggregatingMergeTree 作為物化視圖的表引擎與 MergeeTree 搭配使用

?DC-pre-clickhost-01 :) create materialized view view_aggregating_mt engine=AggregatingMergeTree() order by id as select id,name,sumState(salary) as ss from t_merge_base group by id,name;

DC-pre-clickhost-01 :) insert into t_merge_base values(1,'張三',18,'北京','大數(shù)據(jù)',24,10000),(2,'李四',19,'上海','Java',22,8000),(3,'王五',20,'北京','JAVA',26,12000)

DC-pre-clickhost-01 :) select * ,sumMerge(ss) from view_aggregating_mt group by id,name,ss

insert into t_merge_base values (1,'張三三',18,'北京','前端 ',22,5000);

?#手動執(zhí)行 optimize 命令，合并物化視圖 view_aggregating_mt 相同分區(qū)數(shù)據(jù)

optimize table view_aggregating_mt;

#查詢視圖 view_aggregating_mt 數(shù)據(jù)

select *,sumMerge(ss) from view_aggregating_mt group by id,name,ss; 注 意 ： 通 過 普 通 MergeTree 表 與 AggregatingMergeTree 物 化 視 圖 結(jié) 合 使 用 ， MergeTree 中存放原子數(shù)據(jù)，物化視圖中存入聚合結(jié)果數(shù)據(jù)，可以提升數(shù)據(jù)查詢效率。

CollapsingMergeTree

CollapsingMergeTree 就是一種通過以增代刪的思路，支持行級數(shù)據(jù)修改和刪除的 表引擎。它通過定義一個 sign 標(biāo)記位字段，記錄數(shù)據(jù)行的狀態(tài)。如果 sign 標(biāo)記為 1，則 表示這是一行有效的數(shù)據(jù)；如果 sign 標(biāo)記為-1，則表示這行數(shù)據(jù)需要被刪除。當(dāng) CollapsingMergeTree 分區(qū)合并時，同一數(shù)據(jù)分區(qū)內(nèi)，sign 標(biāo)記為 1 和-1 的一組數(shù)據(jù) 會被抵消刪除。 每次需要新增數(shù)據(jù)時，寫入一行 sign 標(biāo)記為 1 的數(shù)據(jù)；需要刪除數(shù)據(jù)時，則寫入一行sign 標(biāo)記為-1 的數(shù)據(jù)。此外，只有相同分區(qū)內(nèi)的數(shù)據(jù)才有可能被折疊。 CollapsingMergeTree 建表語法如下： CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... sign Int8 ) ENGINE = CollapsingMergeTree(sign) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...] ? 存在的問題： CollapsingMergeTree對于寫入數(shù)據(jù)的順序有著嚴(yán)格要求，否則導(dǎo)致無法正常折疊。 ? 數(shù)據(jù)折疊保留規(guī)則： 在同一個分區(qū)內(nèi) order by 字段相同的數(shù)據(jù)存在多條，且 sign 值不同，數(shù)據(jù)保留規(guī) 則如下： 1) 如果 sign=1 和 sign=-1 的行數(shù)相同并且最后一行數(shù)據(jù) sign=1,則保留第一行 sign=-1 的行和最后一行 sign=1 的行。 2) 如果 sign=1 的行比 sign=-1 的行多，則保留最后一條 sign=1 的行。 3) 如果 sign=-1 的行比 sign=1 的行多，則保留第一條 sign=-1 的行。 4) 其他情況，不保留數(shù)據(jù) ? 按照順序?qū)懭胄枰禄騽h除的數(shù)據(jù)： #創(chuàng)建表 t_collapsing_mt ,使用 CollapsingMergeTree create table t_collapsing_mt(id UInt8,name String,loc String login_times UInt8,total_dur UInt8 sign Int8)engine=CollapsingMergeTree(sign) order by (id,total_dur) primary key id,partition by loc; #向表t_collapsing_mt中插入數(shù)據(jù)： insert into t_collapsing_mt values(1,'張三','北京',1,30,1),(2,'李四','上海',1,40,1) #查看數(shù)據(jù)：select * from t_collapsing_mt; #向表 t_collapsing_mt 中繼續(xù)插入一條數(shù)據(jù)，刪除“張三”數(shù)據(jù) insert into t_collapsing_mt values(1,'張三','北京',1,30,-1); #optimize合并相同分區(qū)的數(shù)據(jù) optimize table t_collapsing_mt #查詢表中的數(shù)據(jù) select * from t_collapsing_mt; #插入以下兩條數(shù)據(jù)，來更新 “李四”數(shù)據(jù) insert into t_collapsing_mt values(2,'李四','上海',1,40,-1),(2,'李四','上海'，2,100,1） #查詢表t_collasping_mt中的數(shù)據(jù) select * from t_collapsing_mt; 注意：當(dāng)數(shù)據(jù)插入到表中的順序標(biāo)記如果不是 1,-1 這種順序時，合并相同分區(qū)內(nèi)的數(shù)據(jù)不 能達(dá)到修改和更新效果。 如果數(shù)據(jù)的寫入程序是單線程執(zhí)行的，則能夠較好地控制寫入順序；如果需要處理的數(shù) 據(jù)量很大，數(shù)據(jù)的寫入程序通常是多線程執(zhí)行的，那么此時就不能保障數(shù)據(jù)的寫入順序了。 在這種情況下，CollapsingMergeTree 的工作機(jī)制就會出現(xiàn)問題。但是可以通過 VersionedCollapsingMergeTree 的表引擎得到解決。

VersionedCollapsingMergeTree

CollapsingMergeTree 表引擎對于數(shù)據(jù)寫入亂序的情況下，不能夠?qū)崿F(xiàn)數(shù) 據(jù) 折 疊 的 效 果 。 VersionedCollapsingMergeTree 表 引 擎 的 作 用 與 CollapsingMergeTree 完 全 相 同 ，它 們 的 不 同 之 處 在 于 ，VersionedCollapsingMergeTree 對數(shù)據(jù)的寫入順序沒有要求，在同一個分區(qū)內(nèi)，任意順序的數(shù)據(jù)都能夠完成折疊操作。VersionedCollapsingMergeTree 使用 version 列來實現(xiàn)亂序情況下的數(shù)據(jù)折疊，該引擎除了需要指定一個 sign 標(biāo)識之外，還需要指定一個 UInt*類型的 version 版本號。 VersionedCollapsingMergeTree 建表語句： CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster] ( name1 [type1] [DEFAULT|MATERIALIZED|ALIAS expr1], name2 [type2] [DEFAULT|MATERIALIZED|ALIAS expr2], ... sign Int8, version UInt8 ) ENGINE = VersionedCollapsingMergeTree(sign, version) [PARTITION BY expr] [ORDER BY expr] [SAMPLE BY expr] [SETTINGS name=value, ...] #創(chuàng)建t_version_collapsing_mt表使用versionedCollapsingMergeTree引擎 create table t_version_collapsing_mt( id UInt8, name String, loc String, login_times UInt8, total_dur UInt8, sign Int8, version UInt8 ) engine = VersionedCollapsingMergeTree(sign,version) order by (id,total_dur) primary key id,partition by loc;

Integration系列引擎

ClickHouse 提供了許多與外部系統(tǒng)集成的方法，包括一些表引擎。這些表引擎與其 他類型的表引擎類似，可以用于將外部數(shù)據(jù)導(dǎo)入到 ClickHouse 中，或者在 ClickHouse 中直接操作外部數(shù)據(jù)源

HDFS引擎

HDFS 引擎支持 ClickHouse 直接讀取 HDFS 中特定格式的數(shù)據(jù)文件，目前文件格式 支持 Json,Csv 文件等，ClickHouse 通過 HDFS 引擎建立的表，不會在 ClickHouse 中 產(chǎn)生數(shù)據(jù)，讀取的是 HDFS 中的數(shù)據(jù)，將 HDFS 中的數(shù)據(jù)映射成 ClickHouse 中的一張表，這樣就可以使用 SQL 操作 HDFS 中的數(shù)據(jù)。ClickHouse 并不能夠刪除 HDFS 上的數(shù)據(jù)，當(dāng)我們在 ClickHouse 客戶端中刪除了對應(yīng)的表，只是刪除了表結(jié)構(gòu)，HDFS 上的文件并沒有被刪除，這一點跟 Hive 的外部表 分相似 語法： ENGINE = HDFS(URI,format) 注意：URI 是 HDFS 文件路徑，format 指定文件格式。HDFS 文件路徑中文件為多個 時，可以指定成 some_file_?，或者當(dāng)數(shù)據(jù)映射的是 HDFS 多個文件夾下數(shù)據(jù)時，可以指 定 somepath/* 來指定 URI 其他配置： 由于 HDFS 配置了 HA 模式，有集群名稱，所以 URI 使用 mycluster HDFS 集群名稱 時，ClickHouse 不識別，這時需要做以下配置： 1） 將 hadoop 路徑下$HADOOP_HOME/etc/hadoop 下的 hdfs-site.xml 文件復(fù) 制到/etc/clickhouse-server 目錄下。 2） 修 改 /etc/init.d/clickhouse-server 文 件 ， 加 入 一 行 “ export LIBHDFS3_CONF=/etc/clickhouse-server/hdfs-site.xml” 3） 重啟 ClickHouse-server 服務(wù) serveice clickhouse-server restart 當(dāng)然，這里也可以不做以上配置，在寫 HDFS URI 時，直接寫成對應(yīng)的節(jié)點+端口即可 建表語句： create table t_hdfs(id UInt8,name String,age UInt8) engine = HDFS('hdfs://mycluster/ch/*.csv','CSV') 注意：這里表 t_hdfs 不會在 clickhouse 對應(yīng)的節(jié)點路徑下創(chuàng)建數(shù)據(jù)目錄，同時這種表 映射的是 HDFS 路徑中的 csv 文件，不能插入數(shù)據(jù)，t_hdfs 是只讀表。 create table t_hdfs2(id UInt8,name String,age UInt8) engine = HDFS('hdfs://mycluster/chdata','CSV'); #向表 t_hdfs2 中寫入數(shù)據(jù) node1 :) insert into t_hdfs2 values(5,'田七',23),(6,'趙八',24); 注意：t_hdfs2 表沒有直接映射已經(jīng)存在的 HDFS 文件，這種表允許查詢和插入數(shù)據(jù)。

MYSQL引擎

ClickHouse MySQL數(shù)據(jù)庫引擎可以將MySQL 某個庫下的表映射到 ClickHouse中， 使用 ClickHouse 對數(shù)據(jù)進(jìn)行操作。ClickHouse 同樣支持 MySQL 表引擎，即映射一張 MySQL 中的表到 ClickHouse 中，使用 ClickHouse 進(jìn)行數(shù)據(jù)操作，與 MySQL 數(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], ... ) ENGINE = MySQL('host:port', 'database', 'table', 'user', 'password'[, replace_query, 'on_duplicate_clause']); ? host:port - MySQL 服務(wù)器名稱和端口 ? database - MySQL 數(shù)據(jù)庫。 ? table - 映射的 MySQL 中的表 ? user - 登錄 mysql 的用戶名 ? password - 登錄 mysql 的密碼 replace_query - 將 INSERT INTO 查詢是否替換為 REPLACE INTO 的標(biāo) 志,默認(rèn)為 0，不替換。當(dāng)設(shè)置為 1 時，所有的 insert into 語句更改為 replace into 語句。當(dāng)插入的數(shù)據(jù)有重復(fù)主鍵數(shù)據(jù)時，此值為 0 默認(rèn)報錯，此值為 1 時， 主鍵相同這條數(shù)據(jù)，默認(rèn)替換成新插入的數(shù)據(jù) on_duplicate_clause - 默認(rèn)不使用。當(dāng)插入數(shù)據(jù)主鍵相同時，可以指定只 更新某列的數(shù)據(jù)為新插入的數(shù)據(jù)，對應(yīng)于 on duplicate key 后面的語句，其 他的值保持不變，需要 replace_query 設(shè)置為 0。 #在 mysql 中創(chuàng)建一張表 t_ch,指定 id 為主鍵 CREATE TABLE t_ch ( id INT, NAME VARCHAR (255), age INT, PRIMARY KEY (id) ) insert into t_ch values (1,"張三",18),(2,"李四",19),(3,"王五",20) create table t_mysql_engine ( id UInt8, :name String,?age UInt8)engine = MySQL('node2:3306','test','t_ch','root','123456'); 注意：在 clickhouse 中 t_mysql_engine 表不會在 ClickHouse 服務(wù)器節(jié)點上創(chuàng)建 數(shù)據(jù)目錄。 測試MYSQL的引擎： create table t_ch(id Int,name varchar(255),age int,primary key (id)) insert into t_ch values(1,'張三',18),(2,'李四',19),(3,'王五',20) create table t_mysql_engine (id UInt8, name String, age UInt8 )engine = MySQL('node2:3306','test','t_ch','root','123456',1); #在 ClickHouse 中向表 t_mysql_engine 中插入一條數(shù)據(jù)，主鍵重復(fù)。這里由于指定 了 replace_query = 1 ,所以當(dāng)前主鍵數(shù)據(jù)會被替換成新插入的數(shù)據(jù)。 node1 :) insert into t_mysql_engine values (3,'馬六','21'); node1 :) select * from t_mysql_engine;

kafka引擎

ClickHouse 中還可以創(chuàng)建表指定為 Kafka 為表引擎，這樣創(chuàng)建出的表可以查詢到 Kafka 中的流數(shù)據(jù)。對應(yīng)創(chuàng)建的表不會將數(shù)據(jù)存入 ClickHouse 中，這里這張 kafka 引 擎表相當(dāng)于一個消費(fèi)者，消費(fèi) Kafka 中的數(shù)據(jù)，數(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 = Kafka() SETTINGS kafka_broker_list = 'host:port', kafka_topic_list = 'topic1,topic2,...', kafka_group_name = 'group_name', kafka_format = 'data_format'[,] 對以上參數(shù)的解釋： ? kafka_broker_list: 以逗號分隔的 Kafka Broker 節(jié)點列表 ? kafka_topic_list ： topic 列表 ? kafka_group_name ： kafka 消費(fèi)者組名稱 ? kafka_format : Kafka 中消息的格式，例如：JSONEachRow、CSV 等等，具 體參照 https://clickhouse.tech/docs/en/interfaces/formats/。這 里一般使用 JSONEachRow 格式數(shù)據(jù)，需要注意的是，json 字段名稱需要與創(chuàng)建的 Kafka 引擎表中字段的名稱一樣，才能正確的映射數(shù)據(jù) create table t_kafka_consumer ( id UInt8, name String, age UInt8 ) engine = Kafka() settings kafka_broker_list='node1:9092,node2:9092,node3:9092', kafka_topic_list='ck-topic', kafka_group_name='group1', kafka_format='JSONEachRow'; #啟動 kafka,在 kafka 中創(chuàng)建 ck-topic topic,并向此 topic 中生產(chǎn)以下數(shù)據(jù)： 創(chuàng)建 topic: kafka-topics.sh?--zookeeper??node3:2181,node4:2181,node5:2181 --create --topic ck-topic --partitions 3 --replication-factor 3 生產(chǎn)數(shù)據(jù)： kafka-console-producer.sh? --broker-list? node1:9092,node2:9092,node3:9092 --topic ck-topic #在 ClickHouse 中查詢表 t_kafka_consumer 數(shù)據(jù)，可以看到生產(chǎn)的數(shù)據(jù) node1 :) select * from t_kafka_consumer; 注意：再次查看表 t_kafka_consumer 數(shù)據(jù) ，我們發(fā)現(xiàn)讀取不到任何數(shù)據(jù)，這里對應(yīng)的 ClikcHouse 中的 Kafka 引擎表，只是相當(dāng)于是消費(fèi)者，消費(fèi)讀取 Kafka 中的數(shù)據(jù)，數(shù) 據(jù)被消費(fèi)完成之后，不能再次查詢到對應(yīng)的數(shù)據(jù)。 在 ClickHouse 中創(chuàng)建的 Kafka 引擎表 t_kafka_consumer 只是一個數(shù)據(jù)管 道，當(dāng)查詢這張表時就是消費(fèi) Kafka 中的數(shù)據(jù)，數(shù)據(jù)被消費(fèi)完成之后，不能再次被讀取到。 如果想將 Kafka 中 topic 中的數(shù)據(jù)持久化到 ClickHouse 中，我們可以通過物化視圖方式訪 問 Kafka 中的 數(shù)據(jù)，可 以通過以 下三個步 驟完成將 Kafka 中數(shù) 據(jù)持久化到 ClickHouse 中： 1) 創(chuàng)建 Kafka 引擎表,消費(fèi) kafka 中的數(shù)據(jù)。 2) 再創(chuàng)建一張 ClickHouse 中普通引擎表，這張表面向終端用戶查詢使用。這里生 產(chǎn)環(huán)境中經(jīng)常創(chuàng)建 MergeTree 家族引擎表。 3) 創(chuàng)建物化視圖，將 Kafka 引擎表數(shù)據(jù)實時同步到終端用戶查詢表中。 #在 ClickHouse 中創(chuàng)建 t_kafka_consumer2 表，使用 Kafka 引擎 : create table t_kafka_consumer2 ( : id UInt8, : name String, : age UInt8 : ) engine = Kafka() : settings : kafka_broker_list='node1:9092,node2:9092,node3:9092', : kafka_topic_list='ck-topic', : kafka_group_name='group1', : kafka_format='JSONEachRow'; #在 ClickHouse 中創(chuàng)建一張終端用戶查詢使用的表，使用 MergeTree 引擎 create table t_kafka_mt( ?id UInt8, ?name String, ?age UInt8 ?) engine = MergeTree() ?order by id；
#創(chuàng)建物化視圖，同步表 t_kafka_consumer2 數(shù)據(jù)到 t_kafka_mt 中 create materialized view view_consumer to t_kafka_mt :?as select id,name,age from t_kafka_consumer2; 注意：物化視圖在 ClickHouse 中也是存儲數(shù)據(jù)的，create materialized view? view_consumer to t_kafka_mt 語句是將物化視圖 view_consumer 中的數(shù)據(jù)存儲到 到對應(yīng)的 t_kafka_mt 表中，這樣同步的目的是如果不想繼續(xù)同步 kafka 中的數(shù)據(jù)，可 以直接刪除物化視圖即可

總結(jié)

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

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