關于ClickHouse的基礎概念這里就不做太多的贅述了，ClickHouse官網都有很詳細說明。結合供應鏈數字化團隊在使用ClickHouse時總結出的一些注意事項，尤其在命名方面要求研發嚴格遵守約定，對日常運維有很大的幫助，也希望對讀者有啟發。

目前供應鏈數字化ck集群用來存儲實時數據，先通過下面這張圖表了解下ClickHouse數據來源。

圖中標注1和2的位置是供應鏈數字化研發在開發業務功能時改動量比較多的部分，隨著需求變多，DTS任務和數據庫表也越來越多。 通過定義研發使用約定，使我們的DTS任務、表、表字段看起來很整潔。

有哪些好處呢？

1、根據ck表名快速找到對應的DTS任務及消費jdq topic / 當然通過jdq也可快速找到對應的ck表（對不了解業務的人幫助很大）。

2、通過ck表字段即可知道該字段來自哪個業務表哪個字段（字段數據不對，聯系業務值班先排查業務庫的字段是否正確）。

3、快速統計到團隊 或 某個ck集群有多少DTS任務（運維時不會遺漏，一目了然）

一 建表約定

1.1 表命名約定

表命名要求： 1、本地表命名必須_local結尾 2、分布式表命名必須以_all結尾；

--創建本地表， 使用on cluster default 在每個節點上都創建一張本地表
CREATETABLE本地表名 on cluster 集群名稱
(
...
    ts                   DateTime Default now() COMMENT '時間搓',
    version              UInt64 COMMENT '版本號'
) ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/test_local', 
    '{replica}',version)
[PARTITIONBYexpr]-- 數據分區規則
[ORDERBYexpr]-- 排序鍵
[SAMPLEBYexpr]-- 采樣鍵
[SETTINGS index_granularity = 8192, ...]-- 額外參數

參數解釋說明：
PARTITION BY toYYYYMM(tmsCreateTime) 按照月份分
ReplicatedReplacingMergeTree(參數1，參數2，參數3)
ORDER BY (參數1,參數2, ....); 參數1,參數2，參數3....等組成業務主鍵 

----創建分布式表
CREATE TABLE IF NOT EXISTS 庫名.分布式表名 on cluster default AS 本地表名 
ENGINE = Distributed (default,庫名,本地表名,sipHash64(分片鍵));

建表示例腳本：

CREATE TABLE reports_prestore_outbound_fulltrace_local on cluster `default`
(
    `sm_so_no` String COMMENT '訂單號',
    `sm_waybill_code` String COMMENT '青龍運單號',
    `sm_so_type` Int64 DEFAULT 0 COMMENT '訂單類型',
    `st_so_status` Int64 DEFAULT 0 COMMENT '訂單狀態(1初始、2定位完成、3定位失敗、5揀貨中、6出庫完成、7取消、8轉病單、9站點已收貨、10已妥投、11再投中、12已拒收)',
        ?
  	`st_delivery_time` DateTime COMMENT '妥投時間',
   	`st_redeliver_time` DateTime COMMENT '再投時間',
   	`st_reject_time` DateTime COMMENT '拒收時間',
   	`version` UInt64 COMMENT '更新版本號',
    `ts` DateTime DEFAULT now() COMMENT '時間戳'
)
ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/{shard}/waybill_data_center/reports_prestore_outbound_fulltrace_local',
 '{replica}', version)
PARTITION BY toYYYYMM(so_create_time)
ORDER BY (sm_so_no,sm_waybill_code)
TTL so_create_time + toIntervalMonth(1)
SETTINGS index_granularity = 8192;

CREATE     TABLE     IF     NOT     EXISTS   `reports_prestore_outbound_fulltrace_all`     on     cluster     default     AS     `reports_prestore_outbound_fulltrace_local`
ENGINE     =     Distributed     (default,waybill_data_center,reports_prestore_outbound_fulltrace_local,sipHash64(sm_so_no));	

1.2 分區鍵設置

視情況大表按天分區，小表按月分區。分區鍵盡量使用date和datetime字段，避免string類型的分區鍵

分區粒度根據業務特點決定，不宜過粗或過細。 建議使用toYYYYMMDD()按天分區，如果數據量很少，100w左右，建議使用toYYYYMM()按月分區，過多的分區會占用大量的資源，會因為文件系統中的文件數量過多和需要打開的文件描述符過多，導致 SELECT 查詢效率不佳。

1.3 分片鍵設置

分布式表分片鍵需要采用hash函數，應避免數據熱點，集中寫入某個分片；

分片鍵盡量使用表中區分粒度較細的字段,可以時多個字段的組合，如：id / order_no

1.4 排序鍵/主鍵設置

1、有序可以保證很高的壓縮比及加速查詢，寫入數據建議提前排序再寫入數據；

2、若未指定默認為排序建，主鍵不保證唯一性。主鍵過長會拖慢寫入性能，并且會造成過多的內存占用（主鍵常駐內存）。

1.5 字段使用約定

1、字段類型能用數字型的字段盡量用數字型，避免使用string

2、表字段命名：${業務表縮寫}_${業務表字段}，如: 業務表ob_shipment_m 縮寫：osm， ck表字段則以osm_開頭。 注意：業務表的縮寫需要使用字典方式管理

3、日期字段建議默認值為1970-01-01，時間字段默認值為1970-01-01 08:00:00，使用到的地方排除掉默認值即可

4、表中必須包含：ts（時間搓）、version（flink寫入jdq的時間，單位：）字段。

version— 版本列。類型為 UInt*, Date 或 DateTime。可選參數。 在數據合并的時候，ReplacingMergeTree 從所有具有相同排序鍵的行中選擇一行留下： 如果 version 列未指定，保留最后一條。 如果 version 列已指定，保留 version 值最大的版本

5、盡量不使用Nullable類型

可以非NUll的盡量非NUll并在代碼中賦予默認值,數量字段默認值為0，狀態字段默認值建議使用有符號int時為-127、無符號int時為0，字符串建議默認值為空字符串。

設置成Nullable對性能影響也沒有多大，可能是因為我們數據量比較小。不過官方已經明確指出盡量不要使用Nullable類型，因為Nullable字段不能被索引，而且Nullable列除了有一個存儲正常值的文件，還會有一個額外的文件來存儲Null標記。

1.6 新增列操作方式

本地表的修改直接執行即可。如果要對分布式表進行修改，需分如下情況進行：

?如果沒有數據寫入，您可以先修改本地表，然后修改分布式表。

?如果數據正在寫入，您需要區分不同的類型進行操作。

添加表字段SQL示例參考：

ALTER TABLE reports_prestore_outbound_fulltrace_local on  cluster default ADD COLUMN  st_redeliver_time DateTime COMMENT '再投時間';
ALTER TABLE reports_prestore_outbound_fulltrace_all on  cluster default ADD COLUMN  st_redeliver_time DateTime COMMENT '再投時間';

1.7 DDL執行注意事項

1.mutation（delete，update)操作比較重，盡量避免執行此類操作；

2.清理過期數據，應使用TTL，或者drop partition；

3.分布式DDL，分片副本節點串行執行，出現阻塞會導致后面所有DDL無法正常執行，建議輪詢各分片執行DDL，尤其是變更字段類型，不建議直接on cluster default進行變更；

4.optimize table table_name final手動觸發合并慎用，盡量按分區操作。

二 數據寫入約定

結合供應鏈的使用場景，這里在flink層加工完數據后沒有直接將數據寫入ClickHouse集群，而是發送到JDQ隊列中。這樣做的優勢 1、加工和存儲解藕 2、JDQ消息共享；

若數據需要做主備存儲，我們只需要創建新的DTS任務訂閱JDQ消息，將消息寫入到備用的ClickHouse集群即可。

2.1 DTS任務命名約定

任務名規則：sc_digital_${集群ID}_${分布式表名}

使用"sc_digital_"前綴加分布式模型名稱，如：sc_digital_c4omjd8fl7_reports_prestore_outbound_fulltrace_all

2.2 DTS所屬項目空間

創建DTS任務時，任務需要放在《數字化-DTS任務空間》下。

2.3 DTS寫入批次設置

DTS任務批次寫入默認值“40W/1分鐘”。這里需要根據實際情況適當調下。

建議：每次插入50W行左右數據, 最多不可超過100W行. 總之CK不像MySQL要小事務. 
比如1000W行數據, MySQL建議一次插入1W左右, 使用小事務, 執行1000次. CK建議20次,每次50W. 
這是MergeTree引擎原理決定的, 頻繁少量插入會導致data part過多, 合并不過來.

2.4 DTS消費JDQ的等級

默認消費JDQ的等級為L3。消費等級要根據業務實際使用場景做相應調整。以下等級劃分標準（來源于JDQ等級調整說明）：

三 數據查詢約定

針對易出問題的flink-CK鏈路進行雙流，物理隔離，遇到問題可將查詢請求一鍵切換至備用CK集群。

3.1 盡量prewhere替代where

值不變得字段必須使用prewhere特性提升查詢性能

注意：prewhere目前只能用于MergeTree系列的表引擎

3.2 where條件，盡量包含分區鍵，和主鍵索引前綴字段

盡量遵循最左原則，如果跳過最左前綴字段，使用其他字段查詢，也會走索引過濾一些數據，但是效果不好；

3.3 避免使用Select *

避免使用 SELECT * 操作，這是一個非常影響的操作。應當對列進行裁剪，只選擇你需要的列，因為字段越少，消耗的 IO 資源就越少，從而性能就越高。

3.4 where、group by 順序

where和group by中的列順序，要和建表語句中order by的列順序統一，并且放在最前面使得它們有連續不間斷的公共前綴，否則會影響查詢性能。

3.5 JOIN 性能不是很好，應避免使用

替代方案：業務設計使用大寬表，或使用in替代多變關聯，或使用字典，但需注意內存占用；如必須使用join，右表選小表（hash join 右表會全部加載到內存）;

3.6 使用final去重

使用final去重查詢，盡量不要用argMax

3.7 二級索引

1、可變值字段不能添加二級索引。按此字段做條件查詢會先走索引在合并數據，查出而外的中間態數據。

2、 增加二級索引只對后續新增數據生效。如需對歷史數據也走索引，需要按分區刷新數據

創建二級索引示例

Alter table reports_prestore_outbound_fulltrace_local ON cluster default 
    ADD INDEX idx_belong_province_code belong_province_code TYPE set(0) GRANULARITY 5;
Alter table reports_prestore_outbound_fulltrace_local ON cluster default 
    ADD INDEX idx_st_delivery_time st_delivery_time TYPE minmax GRANULARITY 5;

審核編輯 黃宇