B站基於ClickHouse的海量使用者行為分析應用實踐

資料驅動理念已被各行各業所熟知，核心環節包括資料採集、埋點規劃、資料建模、資料分析和指標體系構建。在使用者行為資料領域，對常見的多維資料模型進行資訊提煉和模型整合，可以形成一套常見的資料分析方法來發現使用者行為的內在聯絡，能更好洞察使用者的行為習慣和行為規律，幫助企業挖掘使用者資料的商業價值。

行業內最早可追溯到Google Analytics埋點分析工具，國內較早開始這方面研究的是百度大資料分析平臺；隨著15年後國內大資料興起，神策的使用者行為分析平臺、GrowthingIO的增長平臺等獨立資料分析平臺公司相繼成立；18年後一些發展較快的大廠經過幾年資料積累也有了自己的分析平臺，例如美團點評的Ocean行為分析平臺、位元組的火山引擎增長分析平臺等等。

只有當資料達到一定規模才更適合用科學化的方法來提升資料分析效率，如前面所述，雖然Google和百度在這塊最早探索，但後面一些網際網路公司也是過幾年才有自己的產品，即資料產品的發展需要與實際資料規模和業務發展相符。B站最早從19年開始關注大資料建設，到現在已經有一套較為成熟的資料產品——北極星，可以實現對使用者行為資料進行埋點採集、埋點測試、埋點管理、行為資料分析等功能。行為資料分析平臺主要包括下圖所列功能模組，本文介紹主要模組原理和相關技術實現。

北極星使用者行為分析（User Behavior Analysis, UBA）模組自19年以來主要有三波迭代。

2019年~2020年中：【部分模型化聚合+Spark Jar任務】

這個階段主要任務是功能實現，根據使用者前端查詢引數，提交Spark Jar作業等待返回結果。不同的分析模組對應不同的Spark Jar作業，也對應不同加工好的使用者行為模型。資料架構如下圖所示:

雖然在一定程度上可以完成功能實現，但存在明顯弊端：

• 部分模型化：使用者維度資訊需要提前加工到模型表中，後面不易變更和運維，且早期分析模型設計不支援私參查詢，即明細資料資訊只保留了一部分；

• 資源自適應問題：Spark Jar任務每次啟動都需要透過YARN單獨申請資源，不同查詢條件對應的任務計算複雜度不同，但任務資源引數固定，一方面資源的申請和調配就需要花費較長時間，另一方面不能動態適應任務複雜度，就算維護一個常駐記憶體的 SparkSession供查詢任務呼叫，也沒法解決根據查詢任務的資源自適應問題；

• 併發受限：同一時間段查詢的請求太多，後面請求一直會等待前面請求對應Spark任務釋放所佔用資源，且資源未隔離，會影響其他正常ADHOC查詢。

在實際使用中計算時間太長，單事件分析需要超過3分鐘返回結果，漏斗和路徑分析需要超過30分鐘返回結果，導致產品可用性極低，查詢穩定性和成功率不是很高，使用人數不是很多。這個階段的埋點管理和上報格式未完全規範化，所以重點還是做後者。

2020年中~2021年中：【無模型化明細+Flink+ClickHouse】

ClickHouse是Yandex公司於2016年開源的一個列式資料庫管理系統。Yandex的核心產品是搜尋引擎，非常依賴流量和線上廣告業務，因此ClickHouse天生就適合使用者流量分析。B站於2020年開始引入ClickHouse，結合北極星行為分析場景進行重構，如下圖所示：

這裡直接從原始資料開始消費，透過Flink清洗任務將資料直接洗入ClickHouse生成使用者行為明細，可以稱作無模型化明細資料。Redis維表被用來做實時使用者屬性關聯，字典服務被用於把String型別的實體ID轉成Bigint，利用ClickHouse原生的RoaringBitMap函式對參與計算的行為人群交併差集計算。這一代實現了實時埋點效果檢視，上線以來北極星產品周活人數提升了300%以上，相對於前代，效能有較大提升：

• 查詢速度極大提升：90%事件分析查詢可以在5秒內返回查詢結果，90%的漏斗查詢可以在30S內返回查詢結果，速度提升達到98%以上；

• 實時性查詢：可以對當天實時的使用者行為資料進行分析，極大的增加了使用者獲取分析結果的及時性

但本身這種效能提升是以資源消耗為前提的。以移動端日誌為例，FLink消費任務峰值可以達到百萬條每秒，對Redis維表關聯和字典服務處理挑戰很大，計算併發度甚至達到1200core，遇到特殊流量事件往往出現堆積、延遲、斷流，對人工運維成本消耗也較大。此外這種Lambda資料流架構，實時和離線清洗邏輯需要保持一致，否則很容易導致資料解釋成本提升。另外本身實時+離線維護兩套對儲存上也是極大浪費，即Kafka、Hive、CK都需要儲存同一份資料。到21年底，隨著業務發展CK儲存幾經橫向擴充剩下不到10%，而叢集的擴充套件和資料遷移也需要較大精力，本文後面小節會詳細介紹。功能方面，直接對明細資料應用原生CK函式查詢的跨天留存分析、路徑分析需要用時分鐘級，體驗不是很好。

2021年中~今：【Iceberg全模型化聚合+ClickHouse】

22年開始公司大力推動降本增效，這就要求以儘可能少的資源最大化行為分析產品效能。整體核心思路是全模型化聚合加速，底層流量資料鏈路走kappa架構，不會再用北極星應用資料和流量表不一致的情況，資料小時級產出。這次改造實時資源可以節約1400core，節省Redis記憶體400G、節省Kafka300 Partiton。每天資料量由千億資料降低為百億，透過特定的sharding方式配合下推引數，利用分割槽、主鍵、索引等手段支援事件分析(平均查詢耗時2.77s)、事件合併去重分析(平均查詢耗時1.65s)、單使用者細查(平均查詢耗時16.2s)、漏斗分析(平均查詢耗時0.58s)，留存分析和路徑分析從分鐘級查詢到10s內相應。資料架構如圖所示：

擁有以下特點：

• 全模型聚合：21年中開始我們就設計了一款通用流量聚合模型，可以認為是全資訊的hive流量模型結構，除了把時間維度退化外其餘資訊基本能完整保留，原來千億級的量級可以壓縮為百億內

• BulkLoad出倉：資料按檔案批次從HDFS匯入到ClickHouse，千億級別的資料一小時內可以導完，其原理後文會有介紹

• 字典服務升級：我們透過加強版的snowflake+redis+公司自研rockdbKV儲存，大大增強了字典服務效能，壓測可支援40萬QPS

• 使用者屬性現算模式：不再採用預計算模式，而是透過我們另一套基於CK的標籤平臺所生成的指定使用者標籤人群跨叢集關聯現算，這樣可以靈活指定想要分析的使用者屬性

到22年中，隨著資料湖的興起，我們將hive流量聚合模型遷移到Iceberg上，日常事件查詢可以在10s內完成，可以作為CK資料的備用鏈路。這條鏈路不光降低了緊急事件運維成本，提升資料可用性保障，還可以支援使用者日常流量關聯其他業務定製化查詢取數。通用的模型結構除了支援流量行為日誌外，透過對映管理可以快速接入其他服務端日誌，擴充套件其使用的場景。下圖為22年12月份最近一週各功能模組使用情況：

從發展歷程來看，使用者行為資料分析經歷了從強離線引擎驅動到強OLAP驅動，離不開業界大資料技術不斷髮展和進步，北極星行為資料底層明細後面也會切換到Hudi，可以滿足更加實時的資料消費，讓專業的工具做專業的事。

事件分析是指對具體的行為事件進行相關指標統計、屬性分組、運算、條件篩選等操作，本質上是分析埋點事件的使用者觸發情況以及埋點事件的分析統計情況。留存分析可以根據業務場景以及產品階段的不同，自定義起始行為和後續行為做留存計算，協助分析使用者使用產品的粘性，根據留存分析結果有針對性地調整策略，引導使用者發現產品價值，留住使用者，實現使用者真實的增長。

過去北極星分析平臺的分析模組大多以B站的千億明細行為資料為基礎，透過ClickHouse查詢引擎的指標函式例如uniq()，可以支援單個事件分析、多個事件的對比分析以及多個事件的複合指標運算，支援指定時間內的行為留存分析(參與後續行為的使用者佔參與初始行為使用者的比值)，透過篩選、分組等元件滿足多樣化分析需求。但是過去的北極星事件分析是基於明細資料，B站行為資料每天增量千億級別，儲存日增10T以上資源消耗巨大，明細資料分析查詢比較慢，每天使用者慢查詢平均30s～50s體驗較差，而且其功能比較單薄，只能支援30天的查詢視窗，使用者留存、使用者分群等複雜分析模組很難實現。而且海量行為資料分析也面臨許多挑戰，每天千億行為資料，高峰期寫入QPS百萬以上。如何實現既滿足時效性又滿足海量資料壓力的計算方式？如何滿足複雜分析場景的同時，壓縮儲存提升查詢效率？如何簡化資料鏈路，模組化外掛化降低接入成本，提升擴充套件性？如何打通標籤、ABTest等其他業務系統將北極星的行為分析能力標準化？

北極星事件分析：

為了解決以上痛點和海量資料分析的挑戰，新的事件、留存分析透過準實時方式建模分層，使用者、事件、時間等粒度的預聚合壓縮，不僅統一了離線口徑，而且自研拉寬匯聚spark指令碼可以承載千億資料壓力，搭配多種聚合模型實現豐富的分析模組。同時釋放實時資源離線小時任務保證時效性，維表壓力採用join離線維表+屬性字典維度服務的方式解決，並且早於平臺自研可指定shard的BulkLoad出倉工具，配合下推引數可加速查詢，資料鏈路可擴充套件易運維。相比較以往的處理千億明細資料，準實時在DWB層實現了對資料的壓縮，將每天千億資料壓縮到每天百億級別。OLAP層也透過彙總後的資料替代了原先的明細資料，大大縮小儲存的同時也提高了查詢效能，每天使用者慢查詢可降到10s以內，時間視窗可擴大到45天甚至更長。並且對高複雜的查詢比如使用者留存，使用者分群等分析場景可以更好的支援。

事件分析資料開發流程

具體實現包括以下核心部分：
1. 流量聚合模型建立。首先準實時清洗DWD層B站千億明細行為資料，流量資料都是分為私有引數和公有引數，其中公有引數在使用者粒度下是不會經常改變的，我們會用一般聚合函式取一定時間內指定裝置和行為事件下最新保留的不變公有引數，而將同等粒度下變化比較頻繁的私有引數維度名寫入Array結構，利用map索引原理，把私參維度值組合透過spark自定義邏輯計數併入map的key中，map的value則用來寫入各種公共指標聚合結果，整個過程均透過spark指令碼實現，最終寫入到Iceberg引擎中。因為Iceberg可以關聯其他任何已有hive表，透過快速業務表關聯也可以支援到其他多項業務應用，也可以作為不出倉的北極星降級備用方案支援大部分查詢分析功能。

流量聚合模型資料方案

2.流量聚合模型在iceberg下查詢。 如下圖所示，聚合之後的資料形成DWB層落地到iceberg表(即圖中iceberg_bdp.dwb_flow_ubt_app_group_buvid_eventid_v1_l_hr)，可以在hive和spark上計算大部分查詢維度下的指標。利用Trino基於聯結器實現了儲存與計算分離，透過map_filter、array_position等trino條件函式和map_values、reduce等trino指標函式可以實現一系列複雜事件分析，當然我們也配套開發了一些簡單易用的UDF可以繞開較複雜的trino函式組合供使用者查詢使用，效能上相差不大。

3.公參和私參篩選器建立。接下來我們利用BulkLoad出倉指令碼將iceberg資料匯入ClickHouse表(即圖中polagrou.polaris_dwb_flow_ubt_group_buvid_eventid_pro_i_d_v1)，即保證了時效性又相容了特殊的資料結構。從ClickHouse表結構設計上支援了SAMPLE BY murmurHash3_64(buvid)的抽樣功能，由於buvid(裝置id)分shard寫入可以保證單節點的資料隨機分配，只要在單節點上做抽樣配合ReplicatedReplacingMergeTree引擎就可以實現了ck to ck的物化篩選器，直接為北極星分析平臺提供公參維度聚合、私參列舉排序的維度篩選功能。整個過程直接在可支援排程的python指令碼上實現，可支援到近小時更新。

4.流量聚合模型在ClickHouse下查詢。在ClickHouse查詢上設計特定的CK-UDF來解析巢狀map結構，保證複雜分析場景的同時用於加速了查詢，相比用ClickHouse原生多個函式組合解析要快30%左右，比原先明細模型的查詢要快更多。而且透過指令碼實現了多維度的ClickHouse小時級別的機器人監控告警，早於平臺對此定製化監控告警的支援。

目前北極星分析平臺平均查詢耗時3.4s，透過通用聚合模型，下游可以對行為人群進行交併計算實現標籤畫像和人群圈選等轉化分析功能，也可以利用Retention函式實現了N日的事件留存分析。最終相比前代方案節省計算資源1400C、節省儲存資源40%，提升查詢效率60%以上，利用RBM實現了北極星、標籤、ABTest等多業務打通。

流量業務分析場景上會檢視一群使用者在客戶端或者網頁上的路徑流轉資訊，路徑分析將使用者在產品中的使用路徑用桑吉圖呈現，展現使用者在頁面與頁面流轉中的流量走向。透過路徑分析可以幫助驗證產品運營策略，最佳化產品設計思路。漏斗是使用者在產品使用中完成的一系列行為轉化。漏斗分析可以幫助瞭解使用者在行為步驟中的轉化或流失情況，進而透過最佳化產品或者開展運營活動提升轉化率，達成業務目標。

在業務日益增長的情況下，對使用者漏斗、路徑精細化分析訴求逐漸增加，為此北極星分析平臺增加此型別支援，用於分析一群使用者在某一頁面、某一模組前後的流量流轉變化。漏斗分析業界常見解決此類場景利用ClickHouse提供了一個名叫windowFunnel的函式來實現對明細資料的漏斗分析。而路徑分析技術一般分為兩種，一種為明細資料結合sequenceCount(pattern)(timestamp, cond1, cond2, ...)做簡單的路徑分析，而複雜的路徑分析又叫智慧路徑分析可以透過ClickHouse提供的高階陣列函式進行曲線救國。

路徑分析背景挑戰：

但是過去的流量漏斗、路徑分析都是基於明細資料進行的。儲存資源消耗大、分析查詢慢、功能比較單薄等。為了解決以上痛點，新的漏斗、路徑分析透過離線方式的建模分層、使用者路徑粒度的預聚合、儲存引擎ClickHouse的RBM物化檢視等技術，將每天千億資料壓縮到每天幾十億。查詢效率也從分鐘級最佳化到秒級，更是透過關聯標籤和人群支援到了各種轉化查詢分析。大大縮小儲存的同時查詢效能大大提升，最終實現了關聯標籤和人群圈選等功能。

具體實現包括以下核心部分：
1.路徑聚合DWB模型建立。首先離線處理B站的千億明細行為資料，經過維度裁剪變化比較頻繁的私有引數，保留使用者粒度下的公有引數，並且透過buvid(裝置id)粒度進行聚合，將同一個buvid的所有事件根據時間線串聯聚合到一個欄位中，聚合之後的資料形成DWB層落地到hive表。

路徑分析資料方案：

2.路徑聚合DWS模型建立。在上一步的基礎上，對DWB層的資料進行路徑的彙總，將同一個路徑的buvid(裝置id)彙總聚合到陣列結構中，這個過程出現很多干擾事件，比如某些路徑會頻繁出現，會亂序而干擾真正的使用者行為，所以我們會透過去重等手段進行干擾事件過濾路徑補位拼接形成桑基圖節點，當然我們還引入了RBM資料結構儲存聚合後的裝置編碼，最終落到hive表。整個過程都是透過spark指令碼利用程式碼和演算法實現的。

漏斗分析查詢方案：

3.路徑聚合模型Clickhouse表設計。接下來我們利用平臺工具將hive資料出倉到ClickHouse，在ClickHouse表結構設計上，採用了ClickHouse的物化檢視技術和RBM資料結構，進一步壓縮buvid(裝置id)集合為RBM編碼，利用陣列物化RBM的方式大大壓縮了儲存，可透過Bitmap交併計算路徑相關指標，千億資料壓縮到幾十億做到了秒級查詢。

4.路徑聚合模型漏斗分析查詢。在功能上漏斗分析透過windowFunnel函式進行計算，將計算週期內每個使用者的行為明細按時間順序聚合為對應事件鏈，然後搜尋滑動時間視窗滿足漏斗條件的事件鏈，並計算從鏈中發生的最大事件數level，最後對各級level計算uv獲得結果。

5.路徑聚合模型路徑分析查詢。同理路徑分析在ClickHouse資料基礎上利用資料協議和複雜sql繪製出路徑樹狀圖進而拼接出桑基圖，可直觀的展現使用者主流流程，幫助確定轉化漏斗中的關鍵步驟，迅速發現被使用者忽略的產品價值點，修正價值點曝光方式並發現使用者的流失點，同時透過Bitmap的交併計算實現了標籤畫像和人群圈選等轉化分析功能。

B站的北極星行為分析平臺、標籤畫像平臺、AB實驗人群包都是基於ClickHouse的RBM(RoaringBitMap)實現，此外RBM還有其他多項應用，比如事件分析標籤人群圈選、預計算的路徑分析、建立使用者行為的使用者分群等，具體可檢視之前文章^[1]。

RBM固然好用，但是隻支援int或者long型別，如果去重欄位不是int或者long怎麼辦呢？海量資料應用層的維度服務如何做到高可用高併發？依賴的鏈路出問題如何快速恢復，資料如何保障？

屬性字典維度服務就是可解碼編碼多業務屬性、可輸出管理多業務維度，具有分散式、高可用、高併發等特性的服務系統，透過屬性字典維度服務可實現多維度管理多業務打通，為海量資料應用層定製化提供技術支援。

屬性字典維度服務架構設計：

高可用方面Grpc+LoadCache+Redis+公司自研rockdbKV儲存，多級快取分散式架構支援平滑擴容和滾動釋出，可做到日常快取命中率70%以上，底層ID生成演算法基於Leaf-SnowFlake快速生成，壓測可支援50w以上QPS高併發。所有請求透過公司的日誌傳輸通道可以小時級同步到hive做備份，事故情況下配合BulkLoad讀寫分離可40分鐘內恢復20億+屬性字典。

最終利用屬性字典對buvid(裝置id)等業務屬性編碼和解碼，對使用者標籤和AB人群進行建立，並且透過RBM交併計算實現了北極星分析平臺、使用者畫像平臺、AB實驗平臺的多業務打通。

人群圈選sql示例：

如上文所述，北極星是基於ClickHouse構建的一套海量UBA技術解決方案，底層ClickHouse叢集的穩定性 、讀寫效能、資源使用率均會影響上層業務的使用體驗。與此同時，海量資料如何匯入ClickHouse，以及資料匯入過程的穩定性、匯入效率、資源消耗在很大程度上決定了ClickHouse叢集的整體穩定性和使用效率。所以，一個穩定高效的資料匯入方案對於一套UBA解決方案來說是必不可少的。

在B站，UBA場景的資料匯入方案大致經歷了三個階段的演進：

在B站內部，針對資料寫入到各個資料庫/引擎主要有兩套pipeline：一套是基於Spark的離線匯入鏈路，大部分資料來源於Hive；另一套是基於FLink的實時匯入鏈路，大部分資料來源來源於kafka。這兩套鏈路都支援clickhouse作為data sink，UBA場景最開始也是基於這兩套鏈路來做資料匯入的，主要使用的是實時匯入鏈路，在歷史資料初始匯入和故障補數等少數情況下也用到離線匯入鏈路。

如上圖所示，離線和實時匯入最終都使用ClickHouse JDBC向ClickHouse傳送資料，這種寫入方式實現起來比較簡單，使用開源的ClickHouse JDBC Driver就可以使用標準JDBC介面向ClickHouse寫入資料。同時，flink實時寫入的資料延遲比較低，端到端延遲可控制在秒級。但這個方案存在以下問題：

1. ClickHouse Server端的資源消耗比較大（因為資料的排序，索引生成，資料壓縮等步驟均是在server端完成），在高峰時會影響查詢效能。

2. 實時任務寫入頻次較高，資料會在寫入後觸發大量merge操作，造成“寫放大”，消耗更多的磁碟IO和CPU資源，可能導致too many parts錯誤。

3. 實時Flink任務需要長時間佔用大量資源，且在故障情況下容易出現資料堆積、延遲、斷流等問題，運維成本較高。

以上問題在資源充沛的情況下不會影響業務使用，但當叢集資源接近瓶頸時，查詢效能受寫入影響，寫入效能和穩定性受merge影響，最終導致叢集整體穩定性下降，影響業務使用。

UBA場景的多個分析模組對資料延遲要求不盡相同，大部分資料實時性要求並不高，小時級延遲在大部分模組下是可接受的。因此，為了解決上述JDBC寫入方案的問題，我們針對大部分對時效性要求不高的資料匯入需求，構建了一套基於中間儲存的BulkLoad匯入方案：

1. 首先，將clickhouse格式的data part檔案的生成過程轉移到Spark Application中完成，這樣就可以利用Yarn叢集的資源來完成資料排序，索引生成，資料壓縮等步驟。

2. data part檔案的生成我們藉助clickhouse-local工具實現，在Spark Executor中呼叫clickhouse-local寫入資料到本地磁碟，生成clickhouse data part檔案。

3. 然後，將Spark Executor生成的data part檔案上傳到HDFS檔案系統的特定目錄中。

4. 接著，從Spark Executor端傳送 "ALTER TABLE ... FETCH PART/PARTITION" SQL語句到clickhouse server執行。

5. 最後，ClickHouse Server執行 "ALTER TABLE ... FETCH PART/PARTITION"，從HDFS拉取data part檔案並完成attach操作。其中，我們對ClickHouse程式碼做了一些改造，使得FETCH語句支援從HDFS拉取檔案。

由於Bulkload匯入將資料寫入data part檔案這個過程移到了Spark端執行，大大降低了ClickHouse Server資料寫入對資源的消耗。與此同時，由於在Spark端資料批次寫入之前已經完成了repartition和攢批，到達ClickHouse Server的data part數量相較JDBC寫入要少很多，所以clickhouse的merge壓力也大幅降低。該方案上線後，資料寫入對clickhouse查詢的影響基本消除，叢集穩定性得到大幅提升。

但這個方案依然存在一些問題：

1. 以HDFS作為檔案傳輸的中間儲存，增加了資料傳輸的耗時和網路開銷，同時會佔用HDFS的儲存資源。

2. HDFS的負載情況可能影響ClickHouse Bulkload資料匯入的效能與穩定性。

為了進一步最佳化資料匯入的效能和穩定性，我們參照ClickHouse副本間資料同步的DataExchange服務，開發了ClickHouse的DataReceive服務，以支援Spark Executor直接將data part檔案傳輸到ClickHouse Server，繞開HDFS中間儲存。

DataReceive服務允許使用HTTP客戶端直接將資料檔案傳送到ClickHouse，ClickHouse端會進行鑑權、資料校驗、流量控制、併發控制、磁碟負載均衡等操作。該方案相較於基於HDFS中間儲存的Bulkload方案，大致有一倍的效能提升。

B站每天的使用者行為資料量達數千億行，UBA場景需要分析最近半年以上的歷史資料，所以底層ClickHouse需要儲存PB級的已壓縮資料。同時，隨著B站活躍使用者日益增長，需要儲存的資料量也在不斷增長，所以叢集擴容的需求是必不可少的。

然而，由於受限於存算一體的架構設計，ClickHouse叢集目前無法做到彈性擴容，資料需要在新叢集中完成重分配。因此，ClickHouse如何高效穩定地完成資料重平衡（Data Rebalance）是ClickHouse叢集管理人員必須面對和解決的的問題。

我們在UBA場景叢集擴容的準備和實施過程中，經歷了從手動化，到半自動化，再到服務化的演進。在此期間，我們將在海量資料重平衡實踐過程中遇到的問題與解決方法轉化成為了一套自動化工具服務。下面，我們就來介紹一下這套工具服務的功能與實現原理。

叢集中表的大小差異很大，有些達到幾百TB, 有些只有幾GB，如何度量資料的平衡程度，篩選出需要平衡的表？我們引入了一些數學公式來解決這個問題。

變異係數：當需要比較兩組資料離散程度大小的時候，如果兩組資料的測量尺度相差太大，或者資料量綱的不同，直接使用標準差來進行比較不合適，此時就應當消除測量尺度和量綱的影響，而變異係數可以做到這一點，它是原始資料標準差與原始資料平均數的比，取值範圍0~1，值越小，離散程度越小。

表的平衡度 = 變異係數(取值範圍0~1，值越大，表越不平衡)

舉例:  表A的平衡度

叢集共有4個節點，表A在不同節點的大小分別為4GB, 10GB, 5GB, 3GB

平均值:  (4 + 10 + 5 + 3) / 4 = 5.5
方差:  (x - 平均值) ^ 2 / 4 = 7.25
標準差:  root(方差) = 2.69
變異係數: 標準差 / 平均值 = 0.49

表A的平衡度 = 0.49

對於待平衡的表，有些業務期望最大程度的平衡，提升並行度，發揮叢集的最大算力，而有些表容量過大，業務期望以最小的遷移成本，快速平衡資料，達到相對較優的平衡。

對於不同的業務需求，提供了兩種平衡演算法，裝箱演算法和貪心演算法。

期望達到極致的均衡，資料量較小時，推薦使用裝箱演算法。期望以最小的遷移成本，達到較優的均衡，推薦使用貪心演算法。

裝箱演算法

演算法整體採用Best Fit(最優裝箱演算法) + AVL樹的設計。每個ClickHouse節點即為一個Node，每個Node有初始閾值capacity，代表ClickHouse節點的容納量。將準備平衡的part按照大小順序排序，並根據Best Fit演算法依次填充到Node中，Node根據remain_capacity(剩餘容量)，左旋右旋組成一棵AVL樹，以此提升查詢效率，方便快速完成平衡。

設計如下圖所示。

裝箱演算法細節在此不做贅述，感興趣的讀者可參考這裡^[2]。

貪心演算法

演算法整體採用不斷輪詢 + 區域性最優的設計。將ClickHouse節點按照大小排序，找出最大和最小的節點，如果將某個part從最大的節點搬遷至最小的節點，遷出的節點仍然大於遷入節點，則搬遷該part，直到最大節點無法遷出。依此類推，繼續按照大小排序ClickHouse節點，每次找到最大最小節點，平衡part至區域性最優，直到輪詢ClickHouse節點結束。

設計如下圖所示：

根據平衡演算法，可以得出叢集中節點計劃的遷入、遷出情況。平衡單位為表級別，遷移粒度到part，可以理解為表內部part平衡。

如下圖所示，可以看到表平衡前後的平衡度，以及節點1計劃的遷入、遷出情況。平衡計劃生成完成後，可以根據需要選擇執行特定的平衡計劃。

在執行平衡計劃的過程中，如何準確、高效地將part遷入和遷出？如何保證原子性，避免資料出現丟失或重複的問題？如何限流，避免因平衡佔用過多的資源，影響叢集的穩定性？

• 經過不斷的測試、調整，最終制定了一套比較健壯的平衡方案，整體流程為：預判斷(是否merge) + fetch(遷入節點) + detach(遷出節點) + attach(遷入節點) +  detached(遷出節點) + drop detached(遷出節點)。

• 平衡期間對於不同階段的異常，新增了相應的重試和回滾機制，以此來覆蓋網路抖動、zookeeper重連線等問題，從而保證了平衡的原子性，資料一致性。

• 平衡期間透過限流配置(max_replicated_fetches_network_bandwidth)，來控制平衡速度，保障了叢集的穩定性，避免影響其他業務的正常查詢。

整體設計如下圖所示。

在支援UBA場景各項功能模組的過程中，我們針對ClickHouse的查詢，儲存等方面做了大量應用最佳化工作。下面選取其中幾個最佳化點做簡單介紹。

ClickHouse中的針對分散式表的查詢會被改寫成對local表的查詢併傳送到叢集各個shard執行，然後將各個shard的中間計算結果收集到查詢節點做合併。當中間計算結果很大時，比如countDistinct、 windowFunnel函式等，查詢節點的資料收集和資料合併可能成為整個查詢的效能瓶頸。

查詢下推的思路就是儘量將計算都下推到各個shard執行，查詢節點僅收集合並少量的最終計算結果。不過，也不是所有查詢都適合做下推最佳化，滿足以下兩個條件的查詢可以考慮做下推最佳化：

• 資料已經按照計算需求做好sharding：比如，UBA場景的資料已按user id做好了sharding，所以針對使用者的漏斗分析，UV等計算可以下推到各個shard執行。否則，下推後的計算結果是不準確的。

• 計算的中間結果較大：sum，count等計算是無需下推的，因為其中間結果很小，合併計算很簡單，下推並不能帶來效能提升。

下面，我們以上文中提到的漏斗分析為例，闡述一下如何做查詢下推。

上圖是用windowFunnel函式實現漏斗分析的一個SQL，如圖中“執行步驟”所示，該查詢需要從各shard收集大量資料並在查詢節點完成計算，會產生大量資料傳輸和單點計算量。

我們先使用配置distributed_group_by_no_merge做了一版下推最佳化：

最佳化SQL-V1將windowFunnel的計算下推到各個shard執行，僅在查詢節點對windowFunnel的最終結果做聚合計算。在我們的場景下，該版本較上一版本效能提升了5倍以上。

為了更進一步做查詢下推，我們利用cluster + view的函式組合，將聚合查詢進一步下推：

最佳化SQL-V2的效能較最佳化SQL-V1進一步提升30+%.

UBA場景中的事件資料有很多公共屬性和私有屬性，公共屬性被設計為表的固定欄位，而私有屬性因為各個事件不盡相同，所以採用Array/Map來儲存。最初的設計是採用兩個陣列分別儲存屬性名和屬性值，ClickHouse支援Map結構後，則在後續模組中採用Map來滿足類似需求。無論是Array還是Map，最初都不支援建立跳數索引，所以在其他索引欄位過濾效果有限的情況下，針對Array和Map的操作可能會成為查詢的效能瓶頸。

針對這個問題，我們給Array和Map加上了Bloom filter等跳數索引支援，針對Map僅對其key構建索引。在某些出現頻率較低的私有屬性過濾場景下，Array/Map的跳數索引可以收穫數倍的效能提升。

ClickHouse常用的資料壓縮方式有三種，分別為LZ4、LZ4HC以及ZSTD。針對不同的資料型別，資料分佈方式來使用特定的編碼方式可以大大提高資料壓縮率，以減少儲存成本。

針對UBA場景，我們測試了不同壓縮演算法的壓縮率，寫入效能，查詢效能。相較預設的LZ4，ZSTD(1)在壓縮率上普遍可以節省30%以上的儲存空間，查詢效能方面未見明顯差異，不過寫入效能在某些場景下有20%左右的下降。由於UBA場景資料儲存壓力較大，同時對資料時效性要求不是很高，因此我們最終選擇了ZSTD(1)作為主要的壓縮方式。

UBA場景的泛化形態實際是人+內容+行為，例如使用者可以在觀看場景產出彈幕行為或者點贊行為，這類資料不同於傳統的SDK日誌資料具有通用的埋點格式，但我們可以透過抽象對映到通用行為聚合模型上來，來實現對服務端日誌的行為分析。目前我們正在對社群服務端日誌和其他非埋點規範的業務SDK日誌進行泛化支援，儘可能複用已有能力提高使用者查詢和分析效率。

在UBA場景下，同一張表可能在多個模組中使用到，比如，使用者行為事件資料在事件分析等分析模組中使用，同時在單使用者行為明細查詢中會使用到。這兩種使用場景下對錶的查詢是基於不同過濾維度的，但clickhouse目前的主鍵索引很難同時對多個維度過濾都有較好過濾效果，因此很難同時滿足多個場景下的查詢效能要求。我們已經完成了ZOrder索引的開發，目前正在開發相應的編碼型別，使得UBA場景下的資料可以使用ZOrder index同時支援多個維度的高效查詢。

參考連結：

[1]https://mp.weixin.qq.com/s/hZsZoaMfEo3G51OLv2keyQ

[2]~goodrich/teach/cs165/notes/BinPacking.pdf