出處:https://mp.weixin.qq.com/s?__biz=MzI1NDc5MzIxMw==&mid=2247490744&idx=1&sn=a57f105a16b6ea3e495339838fdd3f57

導讀

在資料量日益增長的當下，傳統資料庫的查詢效能已滿足不了我們的業務需求。而Clickhouse在OLAP領域的快速崛起引起了我們的注意,於是我們引入Clickhouse並不斷最佳化系統性能，提供高可用叢集環境。本文主要講述如何透過Clickhouse結合大資料生態來定製一套完善的資料分析方案、如何打造完備的運維管理平臺以降低維護成本，並結合具體案例說明Clickhouse的實踐過程。

Clickhouse簡介

1.為什麼選擇Clickhouse

目前企業使用者行為日誌每天百億量級，雖然經過數倉的分層以及資料彙總層通用維度指標的預計算，但有些個性化的分析場景還是需要直接編寫程式或sql查詢，這種情況下hive sql和spark sql的查詢效能已無法滿足使用者需求，我們迫切的需要一個OLAP引擎來支援快速的即席查詢。BI儲存庫主要採用的是Infobright，在千萬量級能很快的響應BI的查詢請求，但隨著時間推移和業務的發展，Infobright的併發量與查詢瓶頸日益凸顯，我們嘗試將大資料量級的表匯入TiDB、Hbase、ES等儲存庫，雖然對查詢有一定的提速，但是也存在著相應的問題（後續章節會詳細介紹），這時我們考慮到Clickhouse。Clickhouse社群活躍度高、版本迭代非常快,幾乎幾天到十幾天更新一個小版本，我們非常看好它以後的發展。2.Clickhouse特性

Clickhouse是俄羅斯yandex公司於2016年開源的一個列式資料庫管理系統，在OLAP領域像一匹黑馬一樣，以其超高的效能受到業界的青睞。特性：

基於shard+replica實現的線性擴充套件和高可靠採用列式儲存，資料型別一致，壓縮效能更高硬體利用率高，連續IO，提高了磁碟驅動器的效率向量化引擎與SIMD提高了CPU利用率，多核多節點並行化大查詢

不足：

1.整體架構

我們依據資料的流向將Clickhouse的應用架構劃分為4個層級。

資料接入層

提供了資料匯入相關的服務及功能，按照資料的量級和特性我們抽象出三種Clickhouse匯入資料的方式。

方式一：數倉應用層小表匯入 這類資料量級相對較小，且分佈在不同的資料來源如hdfs、es、hbase等，這時我們提供基於DataX自研的TaskPlus資料流轉+排程平臺匯入資料，單分割槽資料無併發寫入，多分割槽資料小併發寫入，且能和線上任務形成依賴關係，確保匯入程式的可靠性。方式二：離線多維明細寬表匯入 這類資料一般是彙總層的明細資料或者是使用者基於Hadoop生產的大量級資料，我們基於Spark開發了一個匯入工具包，使用者可以根據配置直接拉取hdfs或者hive上的資料到clickhouse，同時還能基於配置sql對資料進行ETL處理，工具包會根據配置叢集的節點數以及Clickhouse叢集負載情況(merges、processes)對local表進行高併發的寫入，達到快速導數的目的。方式三：實時多維明細寬表匯入 實時資料接入場景比較固定，我們封裝了通用的ClickhouseSink，將app、pc、m三端每日百億級的資料透過Flink接入clickhouse，ClickhouseSink也提供了batchSize(單次匯入資料量)及batchTime(單次匯入時間間隔)供使用者選擇。

資料儲存層

資料儲存層這裡我們採用雙副本機制來保證資料的高可靠，同時用nginx代理clickhouse叢集，透過域名的方式進行讀寫操作，實現了資料均衡及高可靠寫入，且對於域名的響應時間及流量有對應的實時監控，一旦響應速度出現波動或異常我們能在第一時間收到報警通知。

nginx_one_replication：代理叢集一半節點即一個完整副本，常用於寫操作，在每次提交資料時由nginx均衡路由到對應的shard表，當某一個節點出現異常導致寫入失敗時，nginx會暫時剔除異常節點並報警，然後另選一臺節點重新寫入。nginx_two_replication：代理叢集所有節點，一般用作查詢和無副本表資料寫入，同時也會有對於異常節點的剔除和報警機制。資料服務層對外：將叢集查詢統一封裝為scf服務(RPC)，供外部呼叫。對內：提供了客戶端工具直接供分析師及開發人員使用。資料應用層埋點系統：對接實時clickhouse叢集，提供秒級別的OLAP查詢功能。使用者分析平臺：透過標籤篩選的方式，從使用者訪問總集合中根據特定的使用者行為捕獲所需使用者集。BI：提供資料應用層的視覺化展示，對接單分片多副本Clickhouse叢集，可橫向擴充套件。Clickhouse運維管理平臺

在Clickhouse的使用過程中我們對常見的運維操作如：增刪節點、使用者管理、版本升降級等封裝了一系列的指令指令碼,再結合業務同學使用過程中的一些訴求開發了Clickhouse管理平臺,該平臺集管理、運維、監控為一體，旨在讓使用者更方便、快捷的使用Clickhouse服務，降低運維成本，提高工作效率。

配置檔案結構

在自動化運維操作時會經常修改配置檔案，而clickhouse大部分引數都是支援熱修改的，為了降低修改配置的帶來的風險和便於維護管理，我們將預設的配置檔案做了如下拆解。

users.xml預設的users.xml可分為三個部分使用者設定users：主要配置使用者資訊如賬號、密碼、訪問ip等及對應的許可權對映配額設定quotas：用於追蹤和限制使用者一段時間內的資源使用引數許可權profiles：讀寫許可權、記憶體、執行緒等大多數引數配置為了統一管理許可權我們在users.xml預定義了對應許可權及資源的quotas及profiles，例如default_profile、readwrite_profile、readonly_profile等,新增使用者無需單獨配置quotas及profiles,直接關聯預定義好的配置即可users.d/xxx.xml 按不同的使用者屬性設定user配置，每一個xml對應一組使用者,每個使用者關聯users.xml中的不同許可權quotas及profilesusers_copy/xxx.xml 每次有變更使用者操作時備份指定屬性的xml，方便回滾metrika.xml 預設情況下包含叢集的配置、zookeeper的配置、macros的配置,當有叢集節點變動時通常需要將修改後的配置檔案同步整個叢集,而macros是每個伺服器獨有的配置,如果不拆解很容易造成配置覆蓋,引起macros混亂丟失資料,所以我們在metrika.xml中只保留每臺伺服器通用的配置資訊,而將獨立的配置拆解出去conf.d/xxx.xml 儲存每臺伺服器獨立的配置,如macros.xmlconfig_copy/xxx.xml 存放每次修改主配置時的備份檔案，方便回滾元資料管理

維護各個Clickhosue叢集的元資料資訊，包含表的元資料資訊及Clickhouse服務狀態資訊，給使用者更直觀的元資料管理體驗，主要有如下功能：

查詢指定叢集和庫表資訊，同時展示該表的狀態：只讀 or 讀寫。查看錶的元資料資訊 行數、磁碟佔用、原始大小、更新時間、分割槽資訊等。設定資料生命週期，基於分割槽數對資料進行清理操作。自動化運維使用者管理

由於我們基於nginx代理的方式對Clickhouse進行均衡讀寫，同時Clickhouse的配置也是可以熱修改的，所以在使用者管理及資源控制方面我們直接透過web平臺對Clickhosue配置檔案進行修改操作。

透過web平臺展示users.xml中對應許可權的profiles 和 quotas，運維人員只需根據使用者屬性選擇對應的配置填寫對應的使用者名稱及自動生成的密文密碼即可，不會影響已配置好的許可權及資源，同時每次xml操作都會提前備份檔案，在xml修改異常時可隨時回滾。

叢集操作

clickhosue管理平臺的核心模組，依託於運維作業平臺 API封裝了一系列的運維指令碼，覆蓋了叢集管理的常用操作。

clickhouse服務的啟動、停止、重啟clickhouse的安裝、解除安裝、故障節點替換升級/降級指定Clickhouse版本動態上下線指定節點元資料維護 (cluster_name、metrik、macros)

這裡以新增節點為例展示整體的流程操作：

其中，較為核心的操作在於install作業的分發及對應的配置生成。

分發install作業 ：由Clickhouse平臺呼叫運維作業平臺服務將預定義的指令碼分發到指定節點執行，同時傳入使用者選填的配置引數。

生成配置檔案 ：通常情況下我們會在一個物理叢集分別建立單副本叢集和雙副本叢集，在為新節點生成配置檔案時由clickhouse平臺從元資料模組獲取到新增節點的叢集資訊，動態生成新增節點的macros與metrika配置，然後將metrika.xml同步到所有叢集。

監控與報警硬體指標監控 硬體指標監控主要指clickhouse服務節點的負載、記憶體、磁碟IO、網絡卡流量等，這裡我們依託於monitor監控平臺來配置各種指標，當監控指標達到一定閾值後觸發報警。叢集指標監控 我們在Clickhouse管理平臺中集成了grafana，採用Prometheus採集clickhosue叢集資訊在grafana做展現，一般的監控指標有top排名(慢查詢、記憶體佔用、查詢失敗 )、QPS、讀寫壓力、HTTP&TCP連線數、zookeeper狀態等，當這些指標出現異常時透過alertmanager外掛配置的規則觸發報警。流量指標監控 目前所有對於clickhouse的讀寫請求都是透過域名代理的方式，透過域名的各項指標能精準且實時的反映出使用者最原始的讀寫請求，當域名響應時間波動較大或者響應失敗時我們能在第一時間收到報警並檢視原始請求。

Clickhouse應用

1. BI查詢引擎核心訴求

在未接入Clickhouse之前，BI的儲存庫有Infobright、Hbase、ES、druid等，其中主要使用的是Infobright，在千萬級別以下Infobright效能出色，對於一些時間跨度較長、資料量級較大的表Infobright就有些無能為力，這種資料我們通常會存放在ES與Hbase中，這樣雖然加快了查詢速度但是也增大了系統適配不同資料來源的複雜度，同時分析師會有直接操作表的訴求，資料存入ES與Hbase會增加對應的學習成本，基於此我們的核心訴求就是：

(1) 大資料量級下高查詢效能;

(2) BI適配成本低;

(3)支援sql簡單易用。

選型對比

基於以上訴求我們拿現有的Infobright與TiDB、Doris、Clickhouse做了如下對比。

功能點InfobrightTiDBDorisClickhouseBI適配成本-低低中學習使用成本-低低低百萬級查詢(100w)84ms24ms25ms41ms千萬級查詢(1000w)1330ms332ms130ms71ms億級別查詢(1.1億)57000ms16151ms3200ms401ms

總體來看Clickhouse的查詢效能略高於Doris，而TiDB在千萬量級以上效能下降明顯，且對於大資料量級下Clickhouse相比Infobright效能提升巨大，所以最終我們選擇了Clikhouse作為BI的儲存查詢引擎。

2. 叢集構建

在評估了目前Infobright中的資料量級和Clickhouse的併發限制之後，我們決定使用單分片 多副本的方式來構建Clickhouse叢集，理由如下：

BI對接數倉應用層資料，總體來說量級較小，同時clickhouse有著高效的資料壓縮比，採用單節點能儲存當前BI的全量資料，且能滿足未來幾年的資料儲存需求。Clickhouse預設併發數為100，採用單分片每個節點都擁有全量資料，當qps過高時可橫向增加節點來增大併發數。clickhouse對Distributed 表的join支援較差，單分片不走網路，能提高join查詢速度。

伺服器配置：CPU：16 × 2 cores、記憶體：192GB、磁碟：21TB,整體的架構圖如下所示：

在寫資料時由taskplus對其中的一臺節點寫入，如果該節點異常可切換到其他副本節點寫入，由寫入副本自動同步其他副本。

查詢同樣用nginx代理三臺節點，由於是單分片叢集所以查詢視圖表和本地表效果是一樣的，不過視圖表會自動路由健康副本，所以這裡還是選擇查詢視圖表。

在透過Taskplus將BI的資料來源切換到Clickhouse後對於大量級查詢效能提升明顯。

tp99由1184ms變為739ms大於1秒的查詢總量日均減少4.5倍大於1秒的查詢總耗時日均降低6.5倍3. 問題及最佳化

在接入clickhouse之前BI的平均響應時間為187.93ms，接入clickhouse之後BI的平均響應時間為84.58ms，整體響應速度提升了2.2倍，雖然查詢速度有所提升但是我們在clickhouse監控日報郵件中仍發現了一些慢查詢，究其原因是我們對於應用層的表預設都是以日期欄位stat_date分割槽，而有一部分表資料量級非常小且分割槽較多如某產品留存表總資料量：5564行，按日期分割槽 851個分割槽，平均每天6.5條資料，以下是針對於該表執行的常規group by count查詢統計。

功能點ck日期分割槽(冷查詢)ck 日期分割槽(熱查詢)ck 無分割槽(熱查詢)Infobrightquery12000ms220ms16ms8ms

由此可見Clickhouse對於多分割槽的select的查詢效能很差，官方文件中也有對應的表述：

> A merge only works for data parts that have the same value for the partitioning expression. This means you shouldn’t make overly granular partitions (more than about a thousand partitions). Otherwise， the SELECT query performs poorly because of an unreasonably large number of files in the file system and open file descriptors

針對於這種場景我們想直接建立月或年維度的分割槽，但是對於增量資料會存在重跑歷史等問題，而delete或ReplacingMergeTree都可能造成的資料查詢不一致情況，基於此我們在mysql中做了一箇中間表，每次增量匯入或修改mysql表然後全量更新至clickhouse，不設定分割槽或不以日期為分割槽，保證查詢的效率和一致性，經過多分割槽小量級表的最佳化之後我們的平均響應時間變為到70.66ms，相比未最佳化前查詢效能提升了16%，最終BI的查詢響應時間對比如下圖所示：

實時數倉

1.分層架構

由於每日使用者行為資料量級已達百億，傳統的離線分析已不能滿足業務方的需求，因此我們基於三端資料構建了實時數倉，整體分層架構如下：

clickhouse在其中扮演的角色是秒級別的實時OLAP查詢引擎，當我們DWS層的通用維度實時指標不滿足使用者需求時，使用者可以直接透過Clickhouse編寫sql查詢實時資料，大大降低了實時資料查詢門檻。

2.資料輸入與輸出

在資料輸入層面我們將使用者的行為資料實時關聯維表寫入kafka，然後由Flink + JDBC寫入Clickhouse，為了保證實時查詢的穩定性我們採用了雙副本結構，用nginx代理其中一個完整的副本，直接對域名寫入.同時在程式中增加失敗重試機制，當有節點不可寫入時，會嘗試向其他分片寫入，保證了每條資料都能被寫入clickhouse。

在資料的輸出層面將同樣由nginx代理整個叢集，對接到客戶端工具及與SCF服務，其中客戶端工具對接到開發人員及分析師，scf對外提供查詢服務。

3.資料產品

埋點系統是我們專為埋點管理開發的系統其主要功能有：

埋點報備及校驗：新上線埋點的收錄及校驗；需求管理：針對於新埋點上線及埋點變更的需求週期監控及狀態追蹤；埋點多維分析：基於使用者上報埋點進行多維彙總，方便使用者下鑽分析定位問題；指標及看板：有單個或多個埋點按一定規則組合進行多維彙總，可直接在看板中配置對應的統計結果資料；埋點測試：實時收集測試埋點數並進行格式化校驗及解析。在未接入Clickhouse前埋線系統採用MR預計算彙總使用者配置的埋點指標，並將結果資料寫入Hbase，預計算針對於使用者側來說查詢的都是結果資料，響應速度非常快，但是同時也帶來一些問題時效性較差：新上報埋點資料或者修改後的埋點需要在T+1天才能展示，且修改埋點維度後需要重跑歷史資料。模型單一不便擴充套件：只針對埋點的事件模型做流量統計，想要支援其他分析模型必須另外開發對應的計算模型。基於此種情況我們直接將埋點系統中使用者配置的規則轉換為sql，查詢Clickhouse中接入的實時多維明細資料，同時針對於埋點系統的使用場景優化了實時明細表的索引結構，依託clickhouse極致的查詢效能保證實時埋點統計能在秒級別的響應，相當於即配即出，且能隨意修改維度及指標，大大提升了使用者體驗.由於是基於sql直接統計明細資料，所以統計模型的擴充套件性較高，能更快的支援產品迭代。

接入對比時效性時間維度計算方式擴充套件性未接入clickhouseT+1天級mr預計算低接入clickhouse秒級分鐘級實時計算高

常見問題

資料寫入一個batch內不要寫多個分割槽的資料；根據伺服器配置適當增大background_pool_size，提高merge執行緒的數量 預設值16；對於system.merges、system.processes表做好監控，可隨時感知寫入壓力情況作出預警，避免服務崩潰；索引不宜建立過多，對於大資料量高併發的寫入可以考慮先做資料編排按建表索引排序在寫入，減少merge壓力；禁止對Distributed表寫入，可透過代理方式如nginx或chproxy直接對local表寫入，而且能基於配置實現均衡寫入及動態上下線節點。JOIN操作無論什麼join小表必須放在右邊，可以用left、right調整join方式；開啟謂詞下推：enable_optimize_predicate_expression=1(部分版本預設關閉)；大量降低資料量的操作如where、group by、distinct操作優先在join之前做(需根據降低比例評估)。常用引數max_execution_time 單次查詢的最大時間：600s；max_memory_usage 單伺服器單次查詢使用的最大記憶體，設定總體記憶體的50%；max_bytes_before_external_group_by 啟動外部儲存 max_memory_usage/2；max_memory_usage_for_all_queries 單伺服器所有查詢使用的最大記憶體，設定總體記憶體的80%-90%，防止因clickhouse服務佔用過大資源導致伺服器假死。

總結與展望

目前Clickhouse主要應用於資料產品、畫像、BI等方向，日更新百億資料，每日百萬量級查詢請求，持續對外提供高效的查詢服務，我們未來將在以下兩個方面加強Clickhouse的建設：

1.完善Clickhouse管理平臺保障Clickhouse服務的穩定性：

Clickhouse在千億級資料場景下複雜查詢最佳化埋點系統基於Clickhouse統計模型拓展如訪問路徑、間隔、分佈等參考文獻：Clickhouse官網作者簡介：

楊迪，58同城分析與決策支援部資料高階開發工程師

楊琛，58同城分析與決策支援部資料高階開發工程師

曹德嵩，58同城分析與決策支援部資料資深開發工程師

出處:https://mp.weixin.qq.com/s?__biz=MzI1NDc5MzIxMw==&mid=2247490744&idx=1&sn=a57f105a16b6ea3e495339838fdd3f57