背景

在數(shù)據(jù)量日益增長的當下，傳統(tǒng)數(shù)據(jù)庫的查詢性能已滿足不了我們的業(yè)務需求。而Clickhouse在OLAP領域的快速崛起引起了我們的注意,于是我們引入Clickhouse并不斷優(yōu)化系統(tǒng)性能，提供高可用集群環(huán)境。本文主要講述如何通過Clickhouse結(jié)合大數(shù)據(jù)生態(tài)來定制一套完善的數(shù)據(jù)分析方案、如何打造完備的運維管理平臺以降低維護成本，并結(jié)合具體案例說明Clickhouse的實踐過程。

Clickhouse簡介

為什么選擇Clickhouse

1. 目前企業(yè)用戶行為日志每天百億量級，雖然經(jīng)過數(shù)倉的分層以及數(shù)據(jù)匯總層通用維度指標的預計算，但有些個性化的分析場景還是需要直接編寫程序或sql查詢，這種情況下hive sql和spark sql的查詢性能已無法滿足用戶需求，我們迫切的需要一個OLAP引擎來支持快速的即席查詢。
2. BI存儲庫主要采用的是Infobright，在千萬量級能很快的響應BI的查詢請求，但隨著時間推移和業(yè)務的發(fā)展，Infobright的并發(fā)量與查詢瓶頸日益凸顯，我們嘗試將大數(shù)據(jù)量級的表導入TiDB、Hbase、ES等存儲庫，雖然對查詢有一定的提速，但是也存在著相應的問題（后續(xù)章節(jié)會詳細介紹），這時我們考慮到Clickhouse。
3. Clickhouse社區(qū)活躍度高、版本迭代非?？?幾乎幾天到十幾天更新一個小版本，我們非?？春盟院蟮陌l(fā)展。

Clickhouse特性

Clickhouse是俄羅斯yandex公司于2016年開源的一個列式數(shù)據(jù)庫管理系統(tǒng)，在OLAP領域像一匹黑馬一樣，以其超高的性能受到業(yè)界的青睞。
特性：

• 基于shard+replica實現(xiàn)的線性擴展和高可靠
• 采用列式存儲，數(shù)據(jù)類型一致，壓縮性能更高
• 硬件利用率高，連續(xù)IO，提高了磁盤驅(qū)動器的效率
• 向量化引擎與SIMD提高了CPU利用率，多核多節(jié)點并行化大查詢

不足：

• 不支持事務、異步刪除與更新
• 不適用高并發(fā)場景

Clickhouse建設

整體架構(gòu)

clickhouse整體架構(gòu)

我們依據(jù)數(shù)據(jù)的流向?qū)lickhouse的應用架構(gòu)劃分為4個層級。

數(shù)據(jù)接入層

提供了數(shù)據(jù)導入相關的服務及功能，按照數(shù)據(jù)的量級和特性我們抽象出三種Clickhouse導入數(shù)據(jù)的方式。

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

數(shù)據(jù)存儲層

數(shù)據(jù)存儲層這里我們采用雙副本機制來保證數(shù)據(jù)的高可靠，同時用nginx代理clickhouse集群，通過域名的方式進行讀寫操作，實現(xiàn)了數(shù)據(jù)均衡及高可靠寫入，且對于域名的響應時間及流量有對應的實時監(jiān)控，一旦響應速度出現(xiàn)波動或異常我們能在第一時間收到報警通知。

• nginx_one_replication：代理集群一半節(jié)點即一個完整副本，常用于寫操作，在每次提交數(shù)據(jù)時由nginx均衡路由到對應的shard表，當某一個節(jié)點出現(xiàn)異常導致寫入失敗時，nginx會暫時剔除異常節(jié)點并報警，然后另選一臺節(jié)點重新寫入。
• nginx_two_replication：代理集群所有節(jié)點，一般用作查詢和無副本表數(shù)據(jù)寫入，同時也會有對于異常節(jié)點的剔除和報警機制。

數(shù)據(jù)服務層

• 對外：將集群查詢統(tǒng)一封裝為scf服務(RPC)，供外部調(diào)用。
• 對內(nèi)：提供了客戶端工具直接供分析師及開發(fā)人員使用。

數(shù)據(jù)應用層

• 埋點系統(tǒng)：對接實時clickhouse集群，提供秒級別的OLAP查詢功能。
• 用戶分析平臺：通過標簽篩選的方式，從用戶訪問總集合中根據(jù)特定的用戶行為捕獲所需用戶集。
• BI：提供數(shù)據(jù)應用層的可視化展示，對接單分片多副本Clickhouse集群，可橫向擴展。

Clickhouse運維管理平臺

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

clickhouse運維管理平臺首頁

配置文件結(jié)構(gòu)

在自動化運維操作時會經(jīng)常修改配置文件，而clickhouse大部分參數(shù)都是支持熱修改的，為了降低修改配置的帶來的風險和便于維護管理，我們將默認的配置文件做了如下拆解。

配置文件拆解

• users.xml
  默認的users.xml可分為三個部分
  用戶設置users：主要配置用戶信息如賬號、密碼、訪問ip等及對應的權限映射
  配額設置quotas：用于追蹤和限制用戶一段時間內(nèi)的資源使用
  參數(shù)權限profiles：讀寫權限、內(nèi)存、線程等大多數(shù)參數(shù)配置
  為了統(tǒng)一管理權限我們在users.xml預定義了對應權限及資源的quotas及profiles，例如default_profile、readwrite_profile、readonly_profile等,新增用戶無需單獨配置quotas及profiles,直接關聯(lián)預定義好的配置即可

• users.d/xxx.xml
  按不同的用戶屬性設置user配置，每一個xml對應一組用戶,每個用戶關聯(lián)users.xml中的不同權限quotas及profiles

• users_copy/xxx.xml
  每次有變更用戶操作時備份指定屬性的xml，方便回滾

• metrika.xml
  默認情況下包含集群的配置、zookeeper的配置、macros的配置,當有集群節(jié)點變動時通常需要將修改后的配置文件同步整個集群,而macros是每個服務器獨有的配置,如果不拆解很容易造成配置覆蓋,引起macros混亂丟失數(shù)據(jù),所以我們在metrika.xml中只保留每臺服務器通用的配置信息,而將獨立的配置拆解出去

• conf.d/xxx.xml
  保存每臺服務器獨立的配置,如macros.xml

• config_copy/xxx.xml
  存放每次修改主配置時的備份文件，方便回滾

元數(shù)據(jù)管理

維護各個Clickhosue集群的元數(shù)據(jù)信息，包含表的元數(shù)據(jù)信息及Clickhouse服務狀態(tài)信息，給用戶更直觀的元數(shù)據(jù)管理體驗，主要有如下功能

1. 查詢指定集群和庫表信息，同時展示該表的狀態(tài)：只讀 or 讀寫。
2. 查看表的元數(shù)據(jù)信息 行數(shù)、磁盤占用、原始大小、更新時間、分區(qū)信息等。
3. 設定數(shù)據(jù)生命周期，基于分區(qū)數(shù)對數(shù)據(jù)進行清理操作。

生命周期

自動化運維

用戶管理

由于我們基于nginx代理的方式對Clickhouse進行均衡讀寫，同時Clickhouse的配置也是可以熱修改的，所以在用戶管理及資源控制方面我們直接通過web平臺對Clickhosue配置文件進行修改操作。
通過web平臺展示users.xml中對應權限的profiles 和 quotas，運維人員只需根據(jù)用戶屬性選擇對應的配置填寫對應的用戶名及自動生成的密文密碼即可，不會影響已配置好的權限及資源，同時每次xml操作都會提前備份文件，在xml修改異常時可隨時回滾。

用戶管理

集群操作

clickhosue管理平臺的核心模塊，依托于運維作業(yè)平臺 API封裝了一系列的運維腳本，覆蓋了集群管理的常用操作。

1. clickhouse服務的啟動、停止、重啟
2. clickhouse的安裝、卸載、故障節(jié)點替換
3. 升級/降級指定Clickhouse版本
4. 動態(tài)上下線指定節(jié)點
5. 元數(shù)據(jù)維護 (cluster_name、metrik、macros)

集群管理

新增節(jié)點流程圖

作業(yè)分片install腳本

生成配置文件

監(jiān)控與報警

1. 硬件指標監(jiān)控
   硬件指標監(jiān)控主要指clickhouse服務節(jié)點的負載、內(nèi)存、磁盤IO、網(wǎng)卡流量等，這里我們依托于monitor監(jiān)控平臺來配置各種指標，當監(jiān)控指標達到一定閾值后觸發(fā)報警。

2. 集群指標監(jiān)控
   我們在Clickhouse管理平臺中集成了grafana，采用Prometheus采集clickhosue集群信息在grafana做展現(xiàn)，一般的監(jiān)控指標有top排名(慢查詢、內(nèi)存占用、查詢失敗 )、QPS、讀寫壓力、HTTP&TCP連接數(shù)、zookeeper狀態(tài)等，當這些指標出現(xiàn)異常時通過alertmanager插件配置的規(guī)則觸發(fā)報警。

   
   
   grafana監(jiān)控圖

3. 流量指標監(jiān)控
   目前所有對于clickhouse的讀寫請求都是通過域名代理的方式，通過域名的各項指標能精準且實時的反映出用戶最原始的讀寫請求，當域名響應時間波動較大或者響應失敗時我們能在第一時間收到報警并查看原始請求。

Clickhouse應用

BI查詢引擎

核心訴求

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

• 大數(shù)據(jù)量級下高查詢性能
• BI適配成本低
• 支持sql簡單易用

選型對比

基于以上訴求我們拿現(xiàn)有的Infobright與TiDB、Doris、Clickhouse做了如下對比。

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

集群構(gòu)建

在評估了目前Infobright中的數(shù)據(jù)量級和Clickhouse的并發(fā)限制之后，我們決定使用單分片 多副本的方式來構(gòu)建Clickhouse集群，理由如下：

• BI對接數(shù)倉應用層數(shù)據(jù)，總體來說量級較小，同時clickhouse有著高效的數(shù)據(jù)壓縮比，采用單節(jié)點能存儲當前BI的全量數(shù)據(jù)，且能滿足未來幾年的數(shù)據(jù)存儲需求。
• Clickhouse默認并發(fā)數(shù)為100，采用單分片每個節(jié)點都擁有全量數(shù)據(jù)，當qps過高時可橫向增加節(jié)點來增大并發(fā)數(shù)。
• clickhouse對Distributed 表的join支持較差，單分片不走網(wǎng)絡，能提高join查詢速度。

服務器配置：CPU：16 × 2 cores、內(nèi)存：192GB、磁盤：21TB,整體的架構(gòu)圖如下所示：

BI_Clickhouse應用架構(gòu)圖

在寫數(shù)據(jù)時由taskplus對其中的一臺節(jié)點寫入，如果該節(jié)點異?？汕袚Q到其他副本節(jié)點寫入，由寫入副本自動同步其他副本。
查詢同樣用nginx代理三臺節(jié)點，由于是單分片集群所以查詢視圖表和本地表效果是一樣的，不過視圖表會自動路由健康副本，所以這里還是選擇查詢視圖表。
在通過Taskplus將BI的數(shù)據(jù)源切換到Clickhouse后對于大量級查詢性能提升明顯

• tp99由1184ms變?yōu)?39ms
• 大于1秒的查詢總量日均減少4.5倍

• 大于1秒的查詢總耗時日均降低6.5倍

  
  
  Clickhouse接入前后對比

問題及優(yōu)化

在接入clickhouse之前BI的平均響應時間為187.93ms，接入clickhouse之后BI的平均響應時間為84.58ms，整體響應速度提升了2.2倍，雖然查詢速度有所提升但是我們在clickhouse監(jiān)控日報郵件中仍發(fā)現(xiàn)了一些慢查詢，究其原因是我們對于應用層的表默認都是以日期字段stat_date分區(qū)，而有一部分表數(shù)據(jù)量級非常小且分區(qū)較多如某產(chǎn)品留存表總數(shù)據(jù)量：5564行，按日期分區(qū) 851個分區(qū)，平均每天6.5條數(shù)據(jù)，以下是針對于該表執(zhí)行的常規(guī)group by count查詢統(tǒng)計。

由此可見Clickhouse對于多分區(qū)的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

針對于這種場景我們想直接創(chuàng)建月或年維度的分區(qū)，但是對于增量數(shù)據(jù)會存在重跑歷史等問題，而delete或ReplacingMergeTree都可能造成的數(shù)據(jù)查詢不一致情況，基于此我們在mysql中做了一個中間表，每次增量導入或修改mysql表然后全量更新至clickhouse，不設置分區(qū)或不以日期為分區(qū)，保證查詢的效率和一致性，經(jīng)過多分區(qū)小量級表的優(yōu)化之后我們的平均響應時間變?yōu)榈?0.66ms，相比未優(yōu)化前查詢性能提升了16%，最終BI的查詢響應時間對比如下圖所示

BI響應時間對比

實時數(shù)倉

分層架構(gòu)

由于每日用戶行為數(shù)據(jù)量級已達百億，傳統(tǒng)的離線分析已不能滿足業(yè)務方的需求，因此我們基于三端數(shù)據(jù)構(gòu)建了實時數(shù)倉，整體分層架構(gòu)如下

實時數(shù)倉分層架構(gòu)

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

數(shù)據(jù)輸入與輸出

實時數(shù)倉_Clickhouse應用架構(gòu)圖

在數(shù)據(jù)輸入層面我們將用戶的行為數(shù)據(jù)實時關聯(lián)維表寫入kafka，然后由Flink + JDBC寫入Clickhouse，為了保證實時查詢的穩(wěn)定性我們采用了雙副本結(jié)構(gòu)，用nginx代理其中一個完整的副本，直接對域名寫入.同時在程序中增加失敗重試機制，當有節(jié)點不可寫入時，會嘗試向其他分片寫入，保證了每條數(shù)據(jù)都能被寫入clickhouse。
在數(shù)據(jù)的輸出層面將同樣由nginx代理整個集群，對接到客戶端工具及與SCF服務，其中客戶端工具對接到開發(fā)人員及分析師，scf對外提供查詢服務。

數(shù)據(jù)產(chǎn)品

埋點系統(tǒng)是我們專為埋點管理開發(fā)的系統(tǒng)其主要功能有

1. 埋點報備及校驗：新上線埋點的收錄及校驗
2. 需求管理：針對于新埋點上線及埋點變更的需求周期監(jiān)控及狀態(tài)追蹤
3. 埋點多維分析：基于用戶上報埋點進行多維匯總，方便用戶下鉆分析定位問題
4. 指標及看板：有單個或多個埋點按一定規(guī)則組合進行多維匯總，可直接在看板中配置對應的統(tǒng)計結(jié)果數(shù)據(jù)

5. 埋點測試：實時收集測試埋點數(shù)并進行格式化校驗及解析

   
   
   埋點系統(tǒng)
   
   

   在未接入Clickhouse前埋線系統(tǒng)采用MR預計算匯總用戶配置的埋點指標，并將結(jié)果數(shù)據(jù)寫入Hbase，預計算針對于用戶側(cè)來說查詢的都是結(jié)果數(shù)據(jù)，響應速度非?？?，但是同時也帶來一些問題

• 時效性較差：新上報埋點數(shù)據(jù)或者修改后的埋點需要在T+1天才能展示，且修改埋點維度后需要重跑歷史數(shù)據(jù)。

• 模型單一不便擴展：只針對埋點的事件模型做流量統(tǒng)計，想要支持其他分析模型必須另外開發(fā)對應的計算模型。

  
  
  埋點系統(tǒng)新建指標
  
  

  基于此種情況我們直接將埋點系統(tǒng)中用戶配置的規(guī)則轉(zhuǎn)換為sql，查詢Clickhouse中接入的實時多維明細數(shù)據(jù)，同時針對于埋點系統(tǒng)的使用場景優(yōu)化了實時明細表的索引結(jié)構(gòu)，依托clickhouse極致的查詢性能保證實時埋點統(tǒng)計能在秒級別的響應，相當于即配即出，且能隨意修改維度及指標，大大提升了用戶體驗.由于是基于sql直接統(tǒng)計明細數(shù)據(jù)，所以統(tǒng)計模型的擴展性較高，能更快的支持產(chǎn)品迭代。

埋點系統(tǒng)看板

常見問題

1. 數(shù)據(jù)寫入

• 一個batch內(nèi)不要寫多個分區(qū)的數(shù)據(jù)
• 根據(jù)服務器配置適當增大background_pool_size，提高merge線程的數(shù)量 默認值16。
• 對于system.merges、system.processes表做好監(jiān)控，可隨時感知寫入壓力情況作出預警，避免服務崩潰
• 索引不宜建立過多，對于大數(shù)據(jù)量高并發(fā)的寫入可以考慮先做數(shù)據(jù)編排按建表索引排序在寫入，減少merge壓力
• 禁止對Distributed表寫入，可通過代理方式如nginx或chproxy直接對local表寫入，而且能基于配置實現(xiàn)均衡寫入及動態(tài)上下線節(jié)點

2. JOIN操作

• 無論什么join小表必須放在右邊，可以用left、right調(diào)整join方式
• 開啟謂詞下推：enable_optimize_predicate_expression=1(部分版本默認關閉)
• 大量降低數(shù)據(jù)量的操作如where、group by、distinct操作優(yōu)先在join之前做(需根據(jù)降低比例評估)

3. 常用參數(shù)

• max_execution_time 單次查詢的最大時間：600s
• max_memory_usage 單服務器單次查詢使用的最大內(nèi)存，設置總體內(nèi)存的50%
• max_bytes_before_external_group_by 啟動外部存儲 max_memory_usage/2
• max_memory_usage_for_all_queries 單服務器所有查詢使用的最大內(nèi)存，設置總體內(nèi)存的80%-90%，防止因clickhouse服務占用過大資源導致服務器假死

總結(jié)及展望

目前Clickhouse主要應用于數(shù)據(jù)產(chǎn)品、畫像、BI等方向，日更新百億數(shù)據(jù)，每日百萬量級查詢請求，持續(xù)對外提供高效的查詢服務，我們未來將在以下兩個方面加強Clickhouse的建設：
1.完善Clickhouse管理平臺保障Clickhouse服務的穩(wěn)定性：

• 目前在刪除節(jié)點時會啟動一個Rebalance腳本將被刪除節(jié)點上的數(shù)據(jù)重新寫入其他節(jié)點,在此過程中會造成數(shù)據(jù)查詢不一致的問題,我們希望能提供更高效無感的Rebalance操作方案
• 更精細化的權限控制及管理,目前最新版本中已有此實現(xiàn)(Role及Privileges),后續(xù)我們將嘗試使用該功能并適配到Clickhouse管理平臺
• 實時數(shù)據(jù)寫入Clickhouse的一致性保證

2.優(yōu)化Clickhouse性能,拓展Clickhouse使用場景：

• Clickhouse在千億級數(shù)據(jù)場景下復雜查詢優(yōu)化
• 埋點系統(tǒng)基于Clickhouse統(tǒng)計模型拓展如訪問路徑、間隔、分布等