ClickHouse 使用實踐與規範

ClickHouse 作爲一款開源列式數據庫管理系統(DBMS)近年來備受關注,主要用於數據分析(OLAP)領域。作者根據以往經驗和遇到的問題,總結出一些基本的開發和使用規範,以供使用者參考。

隨着公司業務數據量日益增長,數據處理場景日趨複雜,急需一種具有高可用性和高性能的數據庫來支持業務發展,ClickHouse 是俄羅斯的搜索公司 Yandex 開源的 MPP 架構的分析引擎,號稱比事務數據庫快 100-1000 倍,最大的特色是高性能的向量化執行引擎,而且功能豐富、可靠性高。

在過去的一年中,杭研 DBA 團隊已經支撐網易集團內部多個事業部上線使用,集羣規模共計十幾套,CPU 近 3000 核,每日近千億數據入庫,千億級別表查詢可在秒級完成,大大提升了業務原有 OLAP 架構的效能,覆蓋的業務場景包括:用戶行爲日誌分析,進行 PV、UV、留存、轉化漏斗和操作,包括遊戲反外掛數據統計分析;用戶畫像,人羣圈定和問卷投放;AB 實驗數據的實時計算與分析;機器和業務日誌的分析、監控、查詢等。

ClickHouse 應用場景

1. 寫在前面

(1)如果你的業務預算或機器資源有限,強烈不推薦使用 clickhouse,因爲這套架構成本比較高。

(2)最小集羣部署所需機器:ck 節點需要 2 臺 256G 內存 / 40c cpu 物理機,磁盤使用 SSD,加上 3 臺 zookeeper 和 2 臺 chproxy 應用主機或者雲主機。

(3)Clickhouse 自帶了豐富的功能來應對複雜的業務場景和大數據量,所以在使用期間需要運維和開發側都投入人力對這些功能 (表引擎類型) 學習和掌握。

2. 業務在數據層的表現

(1)業務大多數是讀請求,存儲寬表,無大字段,較少的併發 (單臺 100-200qps 左右)。

(2)數據批寫入(1000 條以上,線上業務建議 5w-10w),不修改或少修改已添加的數據。

(3)無事務要求,對數據一致性要求低。

(4)對於簡單查詢,允許延遲大約 50 毫秒,每一個查詢除了一個大表外都很小。

(5)處理單個查詢時需要高吞吐量(每個服務器每秒高達數十億行)。

3. 具體業務場景

(1)用戶行爲分析,精細化運營分析:日活,留存率分析,路徑分析,有序漏斗轉化率分析,Session 分析等;

(2)實時日誌分析,監控分析;

(3)實時數倉。

表引擎選擇

ClickHouse 表引擎一共分爲四個系列,分別是 Log、MergeTree、Integration、Special。其中包含了兩種特殊的表引擎 Replicated、Distributed,功能上與其他表引擎正交,目前業務上主要使用 MergeTree 系列,配合使用 Mview 和 Distributed 引擎。

ClickHouse 包含以下幾種常用的引擎類型:

在所有的表引擎中,最爲核心的當屬 MergeTree 系列表引擎,這些表引擎擁有最爲強大的性能和最廣泛的使用場合。對於非 MergeTree 系列的其他引擎而言,主要用於特殊用途,場景相對有限。而 MergeTree 系列表引擎是官方主推的存儲引擎,支持幾乎所有 ClickHouse 核心功能,下面主要介紹 MergeTree 系列表引擎:

1. MergeTree 表引擎

MergeTree 在寫入一批數據時,數據總會以數據片段的形式寫入磁盤,且數據片段不可修改。爲了避免片段過多,ClickHouse 會通過後臺線程,定期合併這些數據片段,屬於相同分區的數據片段會被合成一個新的片段。這種數據片段往復合併的特點,也正是合併樹名稱的由來。

 MergeTree 作爲家族系列最基礎的表引擎,主要有以下特點:

 建表語法:

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, ...]

2. ReplicatedMergeTree 表引

ReplicatedMergeTree 使得以上 MergeTree 家族擁有副本機制,保證高可用,用於生產環境,對於大數據量的表來說不推薦使用,因爲副本是基於 zk 做數據同步的,大數據量會對 zk 造成巨大壓力,成爲整個 ck 整個集羣瓶頸。業務可以根據數據重要程度在性能和數據副本之間做選擇。

建表示例:

CREATE TABLE [IF NOT EXISTS] [db.]table_name [ON CLUSTER cluster]
(`id` Int64, `ymd` Int64)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/replicated/{shard}/test''{replica}')
PARTITION BY ymd
ORDER BY id

3. ReplacingMergeTree 表引

上文提到 MergeTree 表引擎無法對相同主鍵的數據進行去重,ClickHouse 提供了 ReplacingMergeTree 引擎,可以針對相同主鍵的數據進行去重,它能夠在合併分區時刪除重複的數據。值得注意的是,ReplacingMergeTree 只是在一定程度上解決了數據重複問題,但是並不能完全保障數據不重複。

建表語法:

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, ...]

[ver]:可選參數,列的版本,可以是 UInt、Date 或者 DateTime 類型的字段作爲版本號。該參數決定了數據去重的方式。

當沒有指定 [ver] 參數時,保留最新的數據;如果指定了具體的值,保留最大的版本數據。

注意點:

(1)去重規則

ReplacingMergeTree 是支持對數據去重的,去除重複數據時,是以 ORDERBY 排序鍵爲基準的,而不是 PRIMARY KEY。

(2)何時刪除重複數據

在執行分區合併時,會觸發刪除重複數據。optimize 的合併操作是在後臺執行的,無法預測具體執行時間點,除非是手動執行。

(3)不同分區的重複數據不會被去重

ReplacingMergeTree 是以分區爲單位刪除重複數據的。只有在相同的數據分區內重複的數據纔可以被刪除,而不同數據分區之間的重複數據依然不能被剔除。

4. SummingMergeTree 表引

該引擎繼承了 MergeTree 引擎,當合並 SummingMergeTree 表的數據片段時,ClickHouse 會把所有具有相同主鍵的行合併爲一行,該行包含了被合併的行中具有數值數據類型的列的彙總值,即如果存在重複的數據,會對對這些重複的數據進行合併成一條數據,類似於 group by 的效果。

推薦將該引擎和 MergeTree 一起使用。例如,將完整的數據存儲在 MergeTree 表中,並且使用 SummingMergeTree 來存儲聚合數據。這種方法可以避免因爲使用不正確的主鍵組合方式而丟失數據。

如果用戶只需要查詢數據的彙總結果,不關心明細數據,並且數據的彙總條件是預先明確的,即 GROUP BY 的分組字段是確定的,可以使用該表引擎。

建表語法:

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, ...]

 注意點:

 要保證 PRIMARY KEY expr 指定的主鍵是 ORDER BY expr 指定字段的前綴,比如

 -- 如下情況是允許的:

ORDER BY (A,B,C)
PRIMARY KEY A

-- 如下情況會報錯:

DB::Exception: Primary key must be a prefix of the sorting key
ORDER BY (A,B,C)
PRIMARY KEY B

這種強制約束保障了即便在兩者定義不同的情況下,主鍵仍然是排序鍵的前綴,不會出現索引與數據順序混亂的問題。

總結:

5. Aggregatingmergetree 表引

該表引擎繼承自 MergeTree,可以使用 AggregatingMergeTree 表來做增量數據統計聚合。如果要按一組規則來合併減少行數,則使用 AggregatingMergeTree 是合適的。

AggregatingMergeTree 是通過預先定義的聚合函數計算數據並通過二進制的格式存入表內。與 SummingMergeTree 的區別在於:SummingMergeTree 對非主鍵列進行 sum 聚合,而 AggregatingMergeTree 則可以指定各種聚合函數。

建表語法:

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]
[SETTINGS name=value, ...]

** 6. 其他特殊的表引**

Distributed 表引擎

Distributed 表引擎是分佈式表的代名詞,它自身不存儲任何數據,數據都分散存儲在某一個分片上,能夠自動路由數據至集羣中的各個節點,所以 Distributed 表引擎需要和其他數據表引擎一起協同工作。

所以,一張分佈式表底層會對應多個本地分片數據表,由具體的分片表存儲數據,分佈式表與本地分片數據表是一對多的關係。

Distributed 表引擎的定義形式如下所示:

Distributed(cluster_name, database_name, table_name[, sharding_key])

各個參數的含義分別如下:

創建分佈式表是讀時檢查的機制,也就是說對創建分佈式表和本地表的順序並沒有強制要求。

同樣值得注意的是,在上面的語句中使用了 ON CLUSTER 分佈式 DDL,這意味着在集羣的每個分片節點上,都會創建一張 Distributed 表,這樣便可以從其中任意一端發起對所有分片的讀、寫請求。

開發規範

** 1. 查詢 sql 編寫規範**

(1)當多表聯查時,查詢的數據僅從其中一張表出時,可考慮使用 IN 操作而不是 JOIN。

(2)多表查詢性能較差,多表 Join 時要滿足小表在右的原則,右表關聯時被加載到內存中與左表進行比較,ClickHouse 中無論是 Left Join 、Right Join 還是 Inner Join 永遠都是拿着右表中的每一條記錄到左表中查找該記錄是否存在,所以右表必須是小表。

(3)將一些需要關聯分析的業務創建成字典表進行 join 操作,前提是字典表不宜太大,因爲字典表會常駐內存。

(4)禁⽌業務 select * ,列存數據, 每減少一個字段會減少大量的數據掃描, 提升查詢效率。

(5)建議使用 limit 限制返回數據條數使用 limit 返回指定的結果集數量,不會進行向下掃描,大大提升了查詢效率。

(6)查詢時如果可以建議帶上分區鍵查詢, 可以有效減少數據掃描量, 提升查詢效率。

(7)CK 的稀疏索引使得點查詢 (即 kv 類型的查詢) 性能不佳,千萬不要把它簡單當做關係型數據庫進行查詢。

(8)使用 Global 優化分佈式子查詢,避免出現查詢指數級放大。

(9)使用 uniqCombined 替代 distinctuniqCombined 對去重進行了優化,通過近似去重提升十倍查詢性能。

(10)儘量不去使用字符串類型,時間類型最終會轉換成數值類型進行處理,數值類型在執行效率和存儲上遠好過字符串。

(11)ClickHouse 的分佈式表性能性價比不如物理表高,建表分區字段值不宜過多,防止數據導入過程磁盤可能會被打滿。

(12)不要在唯一列或大基數列上進行分組或去重操作,基數太大會消耗過多的 io 和內存。

(13)CPU 一般在 50% 左右會出現查詢波動,達到 70% 會出現大範圍的查詢超時,CPU 是最關鍵的指標,要非常關注。

** 2. 數據寫入注意事項**

(1)不適合高併發寫入,最好還是從異步化隊列寫入,batch insert 5w-10w 起步,儘量不要執行單條或插入操作,會產生大量小分區文件,給後臺 merge 任務帶來巨大壓力。

(2)幾乎完全不支持 update/delete,也不支持事務。

(3)建議表要指定分區鍵, 尤其是數據量大的表,插入 / 查詢 / 合併都是以分區爲單位,合理的分區可以提升整體性能。

(4)分區不建議太多,如果分區太多,會因需要打開的文件描述符過多導致查詢效率不佳。

(5)數據在寫入 ClickHouse 前預先的對數據進行分組,避免一次插入的數據屬於多個分區。

(6)注意 MerTree 主鍵允許存在重複數據 (ReplacingMergeTree 可以在分區內去重)。

** 3. 建表規範**

(1)本地表命名格式:{tab_name}_local,分佈式表命名格式:{tab_name}_shard 。

(2)物化視圖命名規範:{tabl_name_xxx}_mv 。

(3)儘量不要使用 Nullable 類型, 該類型對性能有一定影響, 且不能包含在索引中。

(4)合理設置分區,所有本地表使用 order by 關鍵字指定分區字段,建議採用日期作爲一級分區。默認 order by 字段作爲主鍵。

(5)如果表中不是必須保留全量歷史數據,建議指定 TTL,可以免去手動過期歷史數據的麻煩。 

(6)所有複製引擎表建表指定 use_minimalistic_part_header_in_zookeeper=1。

 本地 ReplicatedMergeTree 表建表模板如下所示:

CREATE TABLE IF NOT EXISTS ads. ads_af_city_complaint_1d _local ON cluster ycdata_3shards_3replicas
(`id` UInt64 COMMENT '序號',
`order_id` UInt64 COMMENT '訂單號',
`gross_weight` UInt64  COMMENT '權重',
`create_time` Date COMMENT '創建時間',
`event` String COMMENT '事件')
ENGINE = ReplicatedMergeTree('/clickhouse/table/{shared}/ads_af_city_complaint_1d _local', '{replica}')
PARTITION BY create_time
ORDER BY id
TTL create_time + toIntervalDay(90)
SETTINGS index_granularity = 8192, use_minimalistic_part_header_in_zookeeper = 1;

解釋:

集羣架構

** 1. 常用架構**

爲簡化業務使用方式,降低業務使用成本。對 clickhouse 集羣的使用做一些約束,能夠提升交付速度,提高標準化程度,降低使用成本。

以 4 臺機器爲例,集羣模式固定爲 2 分片 2 副本模式,若數據量較大 4 臺機器不夠時,可以增加 2 臺機器,集羣模式未 3 分片每個分片 2 副本形式,另外需要 3 臺 zookeeper 和 2 臺 chproxy 應用主機或者雲主機,兩臺 chproxy 使用 NLB 管理,程序直連 NLB IP。

對於單表數據量超過 100 億數據的表不建議使用副本表,建議採用 4 分片 0 副本架構。(具體架構可以和 DBA 溝通後確定)

總體上講,一句話總結:業務訪問統一入口,讀分佈式表,寫本地表。

 優勢:

限制:

(1)業務寫入本地表 (以_local 結尾), 讀分佈式表 (以_shard 結尾表)

(2)業務寫入時需要批量寫入, 需要業務去保證每批次數據量大小盡量一致, 以保證數據儘量均勻分佈。

(3)業務每批次寫入時都要重新獲取連接, 禁止使用長連接否則無法使用負載均衡能力, 會導致數據分佈不均衡。

(4)不支持跨集羣訪問

問題:

因業務每批次寫入數據量的不同,會導致數據分佈的不均勻。

運維注意點:

2. zookeeper 的關鍵作用

ClickHouse 中依賴 Zookeeper 解決的問題可以分爲兩大類:分佈式 DDL 執行、ReplicatedMergeTree 表主備節點之間的狀態同步。zk 的性能會影響整個集羣的性能表現。使用複製表之後,隨着數據量的增加,zookeeper 可能成爲集羣瓶頸,zk 集羣建議機器配置如下:3 臺 32G/4c 機器,萬兆網卡,磁盤 80G-200G。

可以看作 ck 把 zookeeper 用成了目錄服務,日誌服務和協調服務,當 znode 達到幾百萬後,zk 出現異常,常見是連接失敗,此時有些表會出現 readonly 模式。頭條對這個問題的處理方式是改寫源碼調整 ck 對 zk 的使用方式,爲 zk 減重。

如果業務上單表數據量較大並且希望使用複製表,務必在建表時指定 use_minimalistic_part_header_in_zookeeper 參數爲 1,達到壓縮 zk 數據的目的。

3. chproxy

chproxy 官方推薦的是專用於 ClickHouse 數據庫的 HTTP 代理和負載均衡器,使用 go 語言實現,目前僅支持 http 協議。在 Clickhouse 集羣中,每一臺機器都是單獨的實例,我們可以使用其中的一臺作爲查詢機器。此時如何更好的完成負載均衡是我們所關注的,chproxy 即是這麼一個工具。

特性:

chroxy 連接測試:

echo 'show databases;' | curl 'http://10.200.161.49:9009/?user=writeuser&password=xxxx' --data-binary @-

關於 chroxy 參數配置可參照如下文檔:

https://github.com/ContentSquare/chproxy

客戶端工具選擇

1. DBeave

DBeaver 是免費和開源(GPL)爲開發人員和數據庫管理員通用數據庫工具。易用性是該項目的主要目標,是經過精心設計和開發的數據庫管理工具。免費、跨平臺、基於開源框架和允許各種擴展寫作(插件)。

2. Superse

Superset 是一款由 Airbnb 開源的 “現代化的企業級 BI(商業智能) Web 應用程序”,其通過創建和分享 dashboard,爲數據分析提供了輕量級的數據查詢和可視化方案。

3. Tabi

功能和部署方式與 Superset 相似,可參考如下文檔:

https://github.com/smi2/tabix.ui/releases

可用性說明

根據選擇的集羣架構不同, clickhouse 集羣表現出的可用性也不同。

(1)數據的讀寫高可用就是依賴複製表引擎創建多副本機制保證。如果 Clickhouse 集羣使用是多分片多副本架構,當一個副本所在的機器宕機後,chproxy 層會自動路由到可用的副本讀寫數據;

(2)如果 Clickhouse 集羣只用了 sharding 分片,沒有用到複製表作爲數據副本,那麼單臺機器宕機只會影響到單個數據分片的讀寫;

(3)當 zk 集羣不可用時,整個集羣的寫入會都會受影響,不管有沒有使用複製表。

總結:

數據可用性要求越高,意味着投入更多的資源,單臺機器的資源利用率越低,業務可根據數據重要程度靈活選擇,不過 Clickhouse 的定位是在線分析 olap 系統,建議業務方將 ck 裏的數據也定義爲二級數據,數據丟失後是可以再生成的,從而控制整體架構的成本,提高單臺機器的資源利用率。同時強烈建議業務不要強依賴 Clickhouse,要有一定的兜底和熔斷機制。

集羣配置參數調優

1. max_concurrent_querie

最大併發處理的請求數 (包含 select,insert 等),默認值 100,推薦 150(不夠再加),在我們的集羣中出現過”max concurrent queries” 的問題。

2. max_bytes_before_external_sor

當 order by 已使用 max_bytes_before_external_sort 內存就進行溢寫磁盤 (基於磁盤排序),如果不設置該值,那麼當內存不夠時直接拋錯,設置了該值 order by 可以正常完成,但是速度相對內存來說肯定要慢點 (實測慢的非常多,無法接受)。

3. background_pool_size

後臺線程池的大小,merge 線程就是在該線程池中執行,當然該線程池不僅僅是給 merge 線程用的,默認值 16,推薦 32 提升 merge 的速度 (CPU 允許的前提下)。

4. max_memory_usag

單個 SQL 在單臺機器最大內存使用量,該值可以設置的比較大,這樣可以提升集羣查詢的上限。

5. max_memory_usage_for_all_querie

單機最大的內存使用量可以設置略小於機器的物理內存 (留一點內操作系統)。

6. max_bytes_before_external_group_b

在進行 group by 的時候,內存使用量已經達到了 max_bytes_before_external_group_by 的時候就進行寫磁盤 (基於磁盤的 group by 相對於基於磁盤的 order by 性能損耗要好很多的),一般 max_bytes_before_external_group_by 設置爲 max_memory_usage / 2,原因是在 clickhouse 中聚合分兩個階段:

這些內存參數強烈推薦配置上,增強集羣的穩定性避免在使用過程中出現莫名其妙的異常。

學習資料:

作者簡介

劉彥鵬,網易杭州研究院數據庫工程師。

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