編者按：在資料大爆發的時代裡，資料分析和應用領域對資料即查即得的需求越來越迫切，ClickHouse憑藉無與倫比的查詢速度脫穎而出，被廣泛應用於眾多領域和方案中，是優秀的OLAP代表者。但是實踐應用中，尤其是需要程式碼操作時會遇到一定的效能問題，尤其在資料量大的情況下表現更為突出。本文針對實踐場景中遇到的問題，結合Spark技術與叢集資源對ClickHouse進行解剖和分析，並藉助百分點科技在某資料中臺專案中的案例，逐層分析並給出解決方案，文章偏向技術實踐和應用，通用性較強。
一、概覽
百分點科技是國內最早探索資料價值落地的公司之一，早在2017年，百分點大資料技術團隊就開始深入探索和研究ClickHouse，並在國家級專案中得到最佳實踐，獲得客戶一致好評。憑藉雄厚的技術實力和成熟的解決方案，百分點科技已經完成上萬家客戶服務，依靠強大的團隊力量、多年的專案實戰經驗和技術積累，在眾多領域和行業沉澱出優秀的解決方案，並積累了夯實的實戰經驗。
在此引用早在2019年百分點大資料技術團隊對CK的實踐總結：
- 百分點科技使用規約：禁止採用CK分散式寫入，透過本地表寫入。
- 充分利用SparkStreaming的流量控制和反壓機制。
- 在寫入ClickHouse時合理控制時間頻率。
作為服務全球企業和政府的資料智慧公司，百分點科技擁有成熟、完善的資料倉儲理論和資料治理方案。本次某集團資料中臺專案也同樣運用本套解決方案，從不同系統中進行資料接入，歷經ODS、DWD、DWS等各個層次的資料處理，最終產出完美型資料結果，並集中分佈在DM集市層。其實資料集市層已經具備對外提供訪問和資料呈現的能力，例如對接BI系統、對接WEB頁面、對接上下系統互動、對接多個第三方系統檢索、對接資料置換等。
但Hive受限於Hadopp生態圈，無法做到快速既查即得的效果，尤其這種結果型資料的使用，頻繁的查詢和調取，幾乎不可能滿足業界對Hive的期待，在資料倉儲和資料應用方之間亟待需要一種既查即得、滿足各種嚴格的高效能資料庫系統。在眾多OLAP領域，ClickHouse憑藉其無與倫比的查詢速度和諸多特性脫穎而出，成為了OLAP使用場景中優秀的代表者。
ClickHouse特性：
- 列式儲存資料庫，資料壓縮；
- 關係型、支援SQL；
- 分散式平行計算，把單機效能壓榨到極限；
- 高可用；資料量級在PB級別。
選擇ClickHouse，就不能逾越各種資料的接入和各種介質的資料輸出。從Hive到ClickHouse，從ClickHouse到其他儲存介質的需求也常常存在。那麼，如何做到彼此之間更好地銜接和更高效地傳輸，本文將作為專題進行詳細講解，希望能夠跟大家一起討論、學習和進步。
二、案例分析
可能有的同學會有疑問，使用CK查資料已經很快了，為啥還要需要這麼折騰呢？
用過ClickHouse的同學們都清楚，CK分兩種表，一種是分散式和一種是本地表，分散式表是做查詢用的，本地表是做儲存用的。一張本地表等同於一份資料的分片，通常一張表會分成多個本地表分佈儲存，從而達到海量儲存和負責均衡的叢集效應。寫入ClickHouse的時候會按照一定的均衡方式，均勻地落在不同本地表中，假如100萬資料需要寫入CK，假如CK叢集有三個主節點，則每個主節點的本地表會存33w左右，假如CK叢集有四個節點，則每個節點存25w左右，以此類推。
為方便大家更好的思考和理解，後邊會以三主三備的ClickHouse叢集為案例進行講解，首先先了解一下ck分散式表查詢的過程圖解：
圖一：ClickHouse分散式表查詢過程
如上圖所示，在每個節點執行的語句都一樣，操作也一樣，只是查詢的資料不同。同理儲存過程也非常類似，其實寫入資料時也可以寫入分散式表，讓其均勻落到不同節點上，但是這樣的寫入方式會存在諸多問題，如：資料的一致性問題、合併速度與寫入速度不匹配問題，zk壓力問題等，因此一般是禁止寫入分散式表的，所以選擇寫入本地表是一種不錯的方案。
CK的3個主節點儲存的資料是不重複，所以在準備Hive資料的時候可以將Hive資料分成3份資料，與CK的3個本地表形成對應關係。這裡引入Hive分桶的概念，每個桶對應CK的一個本地表，從Hive匯入ClickHouse的時候分別對應匯入（3個桶對應3個本地表），執行3次就能完成全部資料的匯入。
透過JBDC操作ClickHouse一般都是單執行緒的，從Hive的一個桶讀完資料後再寫入CK，有的同學會問，可不可以搞成多執行緒的？答案是可以的（實踐證明這種思路是正確的）。
但無論是單執行緒還是多執行緒都存在兩個問題，一個是效能問題，一個是資源問題，僅限於執行伺服器上資源，即使這臺伺服器有128G記憶體、32cores,我也只能用這麼多。
所以可不可以發揮叢集的作用呢？答案也是可以的，利用大資料叢集的資源管理系統Yarn，就可以解決資源的問題；利用分散式計算框架Spark技術可以解決併發的問題。
綜合上述產出我們的最終方案（也是本篇文章的亮點，概覽已提到）：合理整合Spark技術框架，充分發揮Yarn資源管理機制，實現多執行緒併發操作ClickHouse的架構設計和案例分析。
三、專案實踐
以三主三備的ClickHouse叢集為例，以用的最多的MergeTree+Distributed的分散式架構方案為例，逐步進行方案的分解和分析。
業務需求：經過資料倉儲建設和資料加工最終產出資料集市DM層中的一張1億條*400欄位體量的客戶資訊標籤大寬表（全中國14億人中就有1個人在裡面）,該表資料需要同步到ClickHouse中，以滿足BI展示、WEB頁面資料查詢、第三方系統資料檢索和資料輸出（資料輸出多為MySQL等）的需求，同時也滿足旁臨系統的使用。如下圖所示：
本次主要分析圖中橙色字型部分，總結為如下3個步驟：
- Hive集市資料準備
- Hive資料同步到ClickHouse
- ClickHouse資料同步到MySQL
接下來會按框內步驟逐一進行詳細分解。
1. Hive集市資料準備
Hive產出一張表很簡單，但如果對接ClickHouse，如何更合理地去組裝資料，可以達到更好的效果呢？其實在第三節，案例分析階段已經給出了答案，根據ClickHouse三主三備的特性，將Hive表生成3個同樣邏輯上的桶與CK中的本地表--對應，如果很抽象的話，你可以理解為做成了3條一樣的流水線管道，我們負責建成管道，只待水來、只待資料來。
在HiveSQL中distribute by就是分桶的概念，sort by指定每個bucket的檔案內部資料排序欄位，如果distribute by和sort by 欄位相同可以cluster by 統一代替，分桶的欄位一定是原表中存在的真實欄位。
在我們需要確保reduce的數量與表中的bucket數量一致，需要設定幾個引數：
（1）讓Hive強制分桶，自動按照分桶表的bucket進行分桶。（推薦）
（2）手動指定reduce數量。
我們的桶數量為3，所以這裡的值也為3。
（3）採用insert overwrite重新組裝新表資料，完成Hive資料的準備任務。
2. Hive資料寫入ClickHouse
資料已經按照3桶分的形式準備好了，那麼，如何更快速高效的完成資料匯入呢？Spark技術又如何使用的呢？
如果說第一節準備的資料是水的話，那該章節就是要建立從Hive到CK的第一個管道--引水管道。
建立引水管道大概分為3個步驟，如下：
- 建立ClickHouse所有主節JBDC點連線
- Spark分別讀取Hive，按3取模，分3次讀取
- 按3取模，分3次單獨寫入CK主節點資料
注：2和3在同一個執行緒中前後順序執行。
請看如下示意圖（3條線--3個管道）：
第一步：建立CK多節點連線
首先需要知道ClickHouse的所有連線，可以透過CK的後設資料得到，即使CK叢集發生了變化我們在使用前獲取最新的叢集資訊，以保障資料一致。
如上圖所示，我們可以看到所有叢集對應的hostname列表，透過圖內容我們可以看到該ClickHouse擁有3個資料叢集，叢集名字為write、read、meta_sync，分別部署在6個節點，其中read和write為3主3備模式，meta_sync為6主模式沒有備份，一般後設資料資訊的建表語句或者更新語句都採用meta_sync，表建立肯定都會在每個節點上都建立，一般資料表採用write或者read，三個備節點會定時同步主節點資料，即使一臺節點掛掉了也不影響整個叢集使用，所以本次資料寫入我們使用read叢集，三主三備，所以我們寫入的主機名為db3、db5、db7，db4、db6、db8會自動同步主節點資料完成資料備份。
第二步：Hive資料讀取
參考程式碼：
select*from xxxxx where l_date='2021-10-16'and tablesample(bucket %s out of 3 on uid)
說明：%s是標示從第幾個桶讀取資料，是動態引數，根據程式碼迴圈動態拼接Hive SQL，利用Spark特性分散式並行執行，加快資料讀取速度（因為資料表資料量很大，資料量超過hdfs block塊預設值大小，就會分成N多個block塊儲存在不同的節點上，Spark就會發出N個並行執行緒同時進行資料讀取），資料量大的這種場景使用Spark讀取Hive資料是最合適的方式。
總結：每個block塊都會有一個執行緒進行資料讀取，N個block塊就會相當於N個管道同時引水，這就是Spark的優勢。
第三步：多執行緒併發讀取和寫入
如果第二步是把一條管道建立好了，那第三步就是建立多條這樣的管道同時引水。具體多少條管道，與ClickHouse的節點個數和Hive的桶數量有著直接的關係。
本案例我們建立3個併發3條管道（因為CK節點和Hive桶都是3個），每個管道都獨立抽水並引入ck中。3個管道，互不影響，相互獨立，收發統一。
每條管道就是個執行緒任務，負責吸水和引水。先透過Spark執行HiveSQL讀取資料生產DataFrame，然後DataFrame寫入CK，讀Hive的連線和CK的連線都是動態拼接的，然後一起啟動執行緒，並透過join()函式監測執行緒任務，最終完成整體任務。
3. ClickHouse資料到MySQL
透過上一章節的管道建立，資料已經寫入到CK之中，CK的資料可以對外提供訪問和檢索。上一章節的建立的是資料引入管道，那本章節建立就是第二道管道--資料流出管道，即從CK到MySQL。
那從ClickHouse資料同步到MySQL這條管道如何實現呢？如何更高效的實現呢？Spark技術又如何利用？帶著問題且聽下面講解，正如圖中藍色部分所示：
從ClickHouse到MySQL的步驟與之前從Hive到ClickHouse的過程恰恰相反，Hive到ClickHouse是流入管道，這次是流出管道（相當於從CK抽水的動作），這種場景也很常見，例如資料交換、資料同步、第三方需求等，不要求太高的更新頻率，只需要資料輸出即可。眾多資料庫中，MySQL用的是最多的，所以本次以MySQL作為案例場景進行分解；雖然與Hive到CK資料流程相反，但建立管道的方案和技術都觸類旁通，此次架構設計也是基於ClickHouse的儲存特性而出發的，整合Spark框架技術，充分利用大資料叢集資源而作出的資料輸出架構設計和案例分析。
從ClickHouse輸出到MySQL，前後共嘗試四種方案進行逐階段嘗試，分析利弊。
第一種：JBDC讀取分散式表
採用JBDC讀取分散式表的形式，在某一個節點上建立連線和讀取資料，其實在底層做的也是任務分發查詢，然後彙總在執行節點上統一返回。CK是多主節點共同存在的，可以在不同的主節點提交任務，但無論在哪個節點，都會受資源的限制，因為執行僅限於本臺伺服器上。查詢資料量小還可以，但如果資料量大就會造成伺服器CPU爆滿、記憶體吃緊，如果該節點部署其他元件或應用，會嚴重影響他們的使用，如果影響到zookeeper、kafka、redis等叢集節點，可能整個叢集都會受到影響，所有這種方法酌情使用。
總結：抽水的只有一條管、一個水泵（一個伺服器可以形象化為一個水泵）。
第二種：多執行緒併發讀取本地表
多執行緒JBDC同時讀取本地表的形式，呈現出多執行緒同步執行的盛景（較第一種有了很大的進步，起碼有3個執行緒3條管道在並行操作），但如果表很大，資料量很多，同樣會受到資源的限制。因為這3個執行緒都集中在一臺伺服器上，同樣也面臨更嚴峻的CPU、記憶體爆滿，其槽點依然是未能更好地使用叢集資源去解決問題，我們需要亟待挖掘出更好的方法。
總結：雖然有三條管一起抽水，但是都擠在一個水泵裡，總體還是受這一個水泵的限制。
第三種：Spark讀取分散式表
充分利用叢集的資源，那嘗試Spark讀取ClickHouse，雖然Spark和ClickHouse都給對方做了整合，但並不是非常的好用的那種，Spark讀取分散式表時只有一個執行緒在執行（也只建立了一個管道），雖然寫MySQL呈現出多執行緒並行執行的現象，但是讀資料卻讓人大跌眼鏡，整體效果跟JBDC的形式也相差不了太多，效率和速度並未達到預期的效果，所以Spark讀分散式表的形式也不是最佳的選擇。
總結：本方案雖然寫MySQL是多條管道，但是抽水的依然是一個水管（槽點）、一個水泵，無奈抽水慢，總體也不會快到哪裡。
第四種：多執行緒Spark讀取本地表
基於第三種方案的槽點，改造最佳化和改造。Spark讀分散式表只有一個執行緒很慢，可不可以改成讀本地表和多執行緒的形式？答案是可以的，結合第二種和第三種方案的優點，從ClickHouse儲存特點出發，將Spark讀分散式表改造成多執行緒讀本地表形式進行嘗試，形成第四種方案的基本方針。
既然ClickHouse的資料都均勻分佈在各個主節點上，建立每個執行緒用Spark並行讀取本地表形成DataFrame資料集，利用DataFrame資料可分割槽的特性，將資料重新分成多個資料分割槽，每個資料分割槽都會寫入MySQL，這也充分發揮Spark分散式計算引擎的特性，形成多執行緒並行讀取，多執行緒並行寫入的壯景。
總結：本方案建立起來有三個水泵三條管道一起抽水，寫MySQL的有了3*N個執行緒（3*N條管道），相比之前的方案，本方案抽水是最快的。
現將這種方案進行拆分講解，執行步驟如下：
第一步：獲取各本地表連線資訊
建立ClickHouse主節點的連線，從Hive到CK是一樣的，見第一節描述，這裡不再重複描述。
第二步：動態拼裝本地表連線
在第一步的基礎上動態拼裝本地表連線，Spark根據JDBC連線讀取ClickHouse本地表資料。三個連線，三次並行讀取，每個連線負責讀取各個節點上的資料，Spark根據讀取ClickHouse的SQL形成DataFrame資料集合（CKSQL語句，需要哪些列就讀哪些列，充分發揮列式儲存的優勢），見下圖描述：
DataFrame資料集合可以根據資料量大小重新進行repartitions，也在一定程度上避免資料傾斜的效能問題。重新分成幾個partitions也就會有幾個執行緒共同寫入MySQL，如上圖所示，Spark寫MySQL的partitions是四個，三個執行緒就會有 4*3=12個執行緒並行寫入；在寫最佳化方面採用批次寫入形式，每3000條做一次提交，這樣進一步提高寫入效能，效果也非常明顯。
同樣舉一反三，MySQL方案可行，換成其他資料庫或者其他儲存介質也都觸類旁通，都可以模仿參考，專案實踐也證明效率非常明顯，也足以證明該方案是最好的方案。
效果對比
第三種形式，Spark讀取分散式表截圖。
只有一個job在執行，執行效率跟JBDC形式類似，並未提高多少。
第四種形式，該圖是第四種形式的執行效果圖。
看1和2標識，表明有3個並行執行的job；
看3標識，這三個任務是在幾乎同一個時間內任務提交的，可以聯想到for迴圈中的start()方法，是證實正在執行的3個任務；
看4標識，0/41說明是有41個partition，也就共有41*3=123個執行緒共同寫入MySQL中。
對比總結：表體總量都是1億條，第三種方法需要1.7小時~=110分鐘，第四種方法僅需要：16分鐘執行完，並且資料結果都一樣，這也證明了第四種形式是可靠的、是高效的、也是最好的方法。
至此，從ClickHouse到MySQL的資料輸出管道就建立完成了。
結束語
本文從“寫”和“讀”兩個模組出發，就如何更快地操作ClickHouse進行了詳細分析，兩個模組中都用到了Spark技術和多執行緒並行執行。在“寫”的過程中，對Hive資料採用分桶操作；在“讀”的過程中，透過四種不同的方案進行分析和對比，逐步獲得最佳方案。
歷經眾多專案，服務上萬家客戶，百分點大資料技術團隊在技術路線上積累了豐富的經驗，沉澱出越來越完善的解決方案和技術架構。未來，我們將繼續探索實踐，不斷創新發展，更好地為客戶提供服務。
注：關於本篇文章的細節和難點，歡迎來諮詢，同步學習、共同進步。
參考資料
[1] ClickHouse官網：https://clickhouse.tech/docs/en/
[2] Spark官網：http://spark.apache.org/
[3] Hive官網：https://hive.apache.org/
[4]《Spark快速大資料分析》圖靈出品–人民郵電出版社
[5]《Spark高階資料分析》圖靈程式設計–人民郵電出版社
[6]《ClickHouse原理解析與開發實戰》朱凱