轉轉OLAP自助分析實踐

來源：轉轉技術

• 1.導讀

• 2.背景介紹

• 2.1 為什麼要做自助分析

• 2.2 核心解決的問題

• 2.3 建設初期的卡點

• 3.技術實現

• 3.1 技術架構

• 3.2 基於Quick BI+StarRocks的自助分析功能實現

• 3.3 為什麼選擇StarRocks作為OLAP引擎

• 3.4 上線效果

• 4.最佳化案例

• 4.1 記憶體超限問題最佳化

• 4.2 慢查詢問題最佳化

• 4.3 維值載入慢問題最佳化

• 4.4 高峰期查詢慢問題最佳化

• 4.5 資料寫入引數最佳化

• 5.寫在最後

1.導讀

本次分享介紹轉轉在OLAP自助分析場景的實踐。主要圍繞背景介紹、技術實現、問題最佳化展開和大家聊聊轉轉為什麼要做自助分析，以及期間踩過的一些坑，希望能給讀者朋友帶來一些參考。

2.背景介紹

這一部分先給大家交代一下轉轉為什麼要做自助分析，自助分析核心解決了什麼問題，建設過程中遇到的卡點。幫助大家對轉轉做OLAP自助分析這個事情有個基本的瞭解，以及對照自己的業務場景怎麼更好的避坑。

2.1 為什麼要做自助分析

做大資料開發的朋友是否有這樣的困擾：

隨著業務的快速發展，業務側看數的需求更是變化頻繁，很多線上的看板是改了又改，今天加個指標、明天加個維度、同樣的指標換個維度組合又是一個新的看板需求，極大的增加了數倉RD在應用層建設的工作量，分析師也成了存粹的提數工具人，很難聚焦在業務資料的分析上。

在看板開發、分析師取數的效率上也不容樂觀，從排期到上線往往都需要比較長的週期，短則幾天，長則一兩週甚至更久。

基於上述場景，我們開始籌劃建設自助分析平臺，期望將數倉RD和分析師從這種境況解脫出來去做些更有意義的事情。

2.2 核心解決的問題

• 滿足業務側靈活組合各種維度進行業務指標分析的訴求
• 提升業務側獲取資料的效率
• 減少數倉RD同學在固定看板需求開發和迭代上的時間投入
• 減少分析師在日常取數需求上的時間投入

2.3 建設初期的卡點

• 要求上手簡單易理解

因為自助分析是要直接給到業務側品類運營、供應鏈運營、產品運營等團隊使用，對於平臺的易用性和資料的可理解性就要求比較高，對於從0到1去搭建一個這樣的平臺，其實是一個蠻大的挑戰。

• 時間緊、任務重

從規劃做自助分析到一期預期上線的時間，前後就一個多月，加上前後端的開發資源比較緊張，能夠投入到這個事情上的數倉RD也只有1-2人，對於整個專案的研發來說時間是非常緊的，開發的壓力也比較大。

3.技術實現

鑑於上一部分提到的一些背景，如果採用純自研的方案，很難在那麼短的時間並且投入那麼少的研發資源的前提下取的很好的效果，結合我們本身就一直在使用Quick BI進行資料視覺化分析現狀，最終選擇了BI工具+OLAP資料庫的組合，從另一個角度解決上述提到的卡點，達到了預期的效果。

一句話概括就是：利用Quick BI靈活的托拉拽圖表配置及自動生成查詢SQL的能力 + StarRocks資料庫強大的資料計算能力，實現基於高度冗餘的業務資料明細大寬表資料集為基礎的、靈活的自助分析。

3.1 技術架構

專案建設初期是以離線作為切入點的，二期才陸續迭代了實時資料集的能力。使用到的產品及元件有Quick BI、StarRocks、Hive、Spark、Flink、kafka等。

架構圖如下：

資料鏈路如下：

3.2 基於Quick BI+StarRocks的自助分析功能實現

開始這部分介紹前，先給大家講講Quick BI是個啥。Quick BI是阿里雲旗下的智慧BI服務平臺，我們使用的是私有化部署的版本。它可以提供海量資料實時線上分析服務，支援拖拽式操作和豐富的視覺化效果，幫助使用者輕鬆自如地完成資料分析、業務資料探查、報表製作等工作。具體怎麼使用以及它的功能特性可以自行到官網檢視學習，下面是它的產品功能架構供大家瞭解：

接下來我們重點展開說說使用Quick BI進行自助分析的功能實現需要做哪些事情，總結成一句話就是：建立資料來源用於連結StarRocks資料庫；然後讀取StarRocks表建立資料集，同時進行維度和指標的定義；最後建立儀表板即可進行資料的自助分析。這裡可以看到，跟大多數BI一樣，無非就是獲取資料、建立資料集、托拉拽圖表進行資料視覺化。

重點說說，為了滿足易用和易理解的訴求，我們在資料集層面做的一些設計。

資料結構設計如下：

核心三類欄位，分別是data_type、維度欄位、原子指標欄位。data_type用於區分不同的資料，不同data_type具備不同的維度和原子指標，對不支援的維度和原子指標直接儲存為null。

這樣的結構高度冗餘，雖然不易於維護，但是好處也很明顯：一個資料集即可拿到幾乎所有業務過程的資料，以及相關聯的各個維度和指標。

對於維度和指標體系，運營同學是相對比較熟悉的，這樣做的好處就體現出來了：他們不需要去理解那麼多的資料集，不用去根據分析的場景判斷要用哪個資料集，只要知道自己想到什麼維度組合、分析什麼指標即可，極大的簡化了運營理解資料的成本，降低了使用難度。

資料集SQL示例：

select 維度1
      ...
      ,維度n
      ,case when t.data_type = 'DAU' then DAU end as DAU
      ,case when t.data_type = '曝光' then 曝光pv end as 曝光pv
      ,case when t.data_type = '曝光' then 曝光uv end as 曝光uv
      ,case when t.data_type = '商詳' then 商詳pv end as 商詳pv
      ,case when t.data_type = '商詳' then 商詳uv end as 商詳uv
      ...
      ,原子指標n
from   sr_table t

這裡再補充一點，資料集SQL定義了原子指標的邏輯，聚合的方式是可以在Quick BI的資料集裡面進行配置，包括求和、求均值、計數、去重計數等都是支援的。在維度、原子指標的基礎上，還可以進行計算欄位的加工，衍生出更多的維度和派生指標。由於時間關係，就不展開講解怎麼配置，大家感興趣可以去看一下官方文件。

最後一步，就是最終的目標自助分析了。整體流程如下：

簡單概括一下就是： 建立儀表板>新增視覺化圖表>選擇資料集>繫結維度和度量（指標）。

目前支援40餘種圖表樣式，包含了表格類、指標類、線/面圖類、柱/條圖類、餅/環類、氣泡/散點類、漏斗/轉化關係類、地理類和其他類；涵蓋了趨勢、比較、分佈、關係、空間、時序6個分析大類，同時支援自定義圖表型別，基本覆蓋了常見的視覺化分析方式。

最終呈現的形式：

3.3 為什麼選擇StarRocks作為OLAP引擎

關於這個問題，起初我們用過一段時間的ClickHouse，受限於叢集規模，在我們的資料體量和使用場景下，出現了明顯的效能瓶頸（ps:單資料集近200億行資料，300+維度和指標；長時間週期且比較多維度指標的基於明細資料的複雜查詢）。

後面經過測試，StarRocks在我們這個場景下效能要優於ClickHouse，並且在一些特性上更加友好，後面就統一將業務切到了StarRocks上。因為前後業務體量有些差異，加上叢集規模也不完全一致，就不貼具體的測試結果，避免引起不必要的誤會。但是有幾個點，在我們使用的感受上，StarRocks是要明顯優於ClickHouse的：

• StarRocks 相容 MySQL 協議，支援標準 SQL 語法，這點在自助分析的場景實在是太友好了，相比於ClickHouse來說，極大的簡化了業務側運營人員建立計算欄位的難度。
• StarRocks 在彈性擴縮容的支援上比ClickHouse要更加友好。
• StarRocks 對Join的操作支援更加友好。
• StarRocks 對多併發的場景支援更好。
• StarRocks 的資料型別跟Hive非常接近，進行資料回導的時候對映更加簡單。

（StarRocks叢集規模：3FE節點 + 14BE節點）

3.4 上線效果

• 業務側獲取資料的效率提升。原本提一個維度組合的迭代、或者探索性的業務看板需求、分析師取數需求可能需要一週以上的時間才能滿足，現在只需要一天甚至幾個小時就可以自助獲取到想要資料。
• 釋放數倉RD和分析師部分精力。由於業務側運營同學很多資料需求都可自助滿足，提到我們的需求就少了很多，釋放出來的精力可以投入到底層數倉的建設和業務資料的分析上。
• 走通了一條可以快速複製的自助分析模式。以B2C自助分析作為探索，取得一些不錯的效果之後，快速的複用到客服、上門等業務。後續有類似的場景都可以依葫蘆畫瓢快速實現。
• 查詢效能和時效效能夠滿足使用。目前叢集整體的平均查詢耗時可以做到秒級，40%左右的查詢可以在亞秒級內處理完；實時資料全鏈路的時效性大概是10S左右。

4.最佳化案例

這一部分主要介紹一下我們建設自助分析過程中遇到的一些問題，分享一下我們的解決思路。

4.1 記憶體超限問題最佳化

由於StarRocks使用的MPP架構，當查詢的資料量比較大時，就很容易觸發記憶體超限的問題：Memory of process exceed limit. Pipeline Backend: *.*.*.*, fragment: f7ee1d9e-3bde-11ee-a999-0ab213ea0003 Used: 109007523400, Limit: 109000207318. Mem usage has exceed the limit of BE

從 v3.0.1 開始，StarRocks 支援將一些大運算元的中間結果落盤。使用此功能，您可以在犧牲一部分效能的前提下，大幅降低大規模資料查詢上的記憶體消耗，進而提高整個系統的可用性。開啟方法可參考：

開啟中間結果落盤之後，一定程度上可以緩解記憶體超限的問題出現，但是隻能治標，並不能治本，從根本上還需要減少大查詢的產生，核心還是慢查詢的治理。

4.2 慢查詢問題最佳化

業務側配置圖表時不規範，很容易就會產生大量的慢查詢，經過對慢SQL的分析，發現往往都是沒有進行有效的資料裁剪導致全表去查所有資料，從而出現大量的慢查詢。

有效的資料裁剪：首先是要求業務側使用自助分析時，日期維度是必須要限制的，其次是對data_type過濾不需要檢視的資料型別；在技術層面對日期維度、和data_type維度沒有入參時，傳遞預設值查詢返回null結果；其次是在對日期和data_type進行過濾的時候，不要在這兩個欄位上套函式和處理邏輯，這樣才能夠正常命中索引。實測進行有效的資料裁剪之後，查詢效能可以得到幾十倍的提升，極大的減少了慢查詢的出現。

謂詞下推機制：謂詞下推是很多OLAP資料庫都支援的能力，這裡提一下主要是因為Quick BI的資料集是透過SQL建立的，前端托拉拽配置圖表生成查詢SQL時，是把資料集SQL作為一個子查詢去拼接的。帶來的一個問題就是如果不能合理利用謂詞下推的機制，就會導致索引失效從而全表掃描資料，影響整體的查詢體驗。實測只要最外層的維度沒有額外的轉換動作，即可觸發謂詞下推的機制，從而正常走索引查詢。

大致的效果如下：

-- 假如有一張表table
-- table表有一個欄位a，a欄位有索引

-- SQL1,常規寫法，先過濾資料再做進一步的處理
-- 這樣寫可以命中索引，可以避免載入過多的資料到記憶體
select * 
from (
    select * 
    from table
    where a = 'aaa'
) t
;

-- SQL2，當子查詢不能提前過濾，但不對維度欄位做轉換操作
-- 同樣可以命中索引，可以避免載入過多的資料到記憶體
-- 效能等同於SQL1
select * 
from (
    select * 
    from table
) t
where t.a = 'aaa'
;


-- SQL3，因為對a增加了轉換操作，不能夠開啟謂詞下推
-- 導致無法命中索引，將全表資料載入到記憶體
select * 
from (
    select * 
    from table
) t
where trim(t.a) = 'aaa'
;

如果沒有謂詞下推機制的話，SQL2也是不能夠命中索引的，會去全表掃描。這個機制利用的好可以避免Quick BI的一些坑，在日常資料查詢的時候也很有用。特別是使用Quick BI處理一些日期或時間欄位拼接SQL的時候，需要格外注意這個問題。

4.3 維值載入慢問題最佳化

我們經常還會收到過濾器、查詢控制元件中維度的列舉值載入慢的問題反饋，根本的原因是Quick BI透過distinct全表的方式去獲取列舉值。針對這種場景，我們的解法是按照使用者、訂單、商品、流量等主題拆分了若干維表，配合Quick BI的維值加速功能，使儀表板配置和使用過程統一走維表檢索列舉值，實現維值毫秒級響應。

4.4 高峰期查詢慢問題最佳化

業務使用高峰時，查詢耗時普遍會比日常要慢不少，核心原因在於扎堆使用導致StarRocks叢集的負載比較大。經過調研發現，查詢高峰主要集中在週一或者月初，出週報、月報需要自助分析一些資料，並且很多都是根據提前配置好的儀表板查詢一次對應的資料即可，但是因為查詢資料庫都是發生在訪問頁面時，所以伴隨扎堆的使用出現了該問題。基於這種場景，我們想到了一個錯峰查詢的辦法：在低峰時（9點前），透過selenium模擬訪問儀表板列表，提前將請求的SQL和結果快取起來。這樣一來，減少了高峰期時對StarRocks的查詢操作，緩解了叢集壓力，整體的查詢效能也得到保障，業務也優先透過快取獲取到對應的週報、月報資料，提升了使用者體驗。

大致的方案：部署一個定時排程的python指令碼，透過selenium遍歷提前配置好的儀表板列表，模擬使用者的訪問行為去進行翻頁，需要控制好翻頁的頻率，使頁面懶載入的內容也能夠載入出來。整個過程要控制好停留的時間以及併發，避免把StarRocks查掛了。

4.5 資料寫入引數最佳化

• 實時寫入時效性最佳化。透過Flink往StarRocks實時寫入資料時，要控制好StarRocks的batch_max_rows、batch_max_bytes，以及Flink的Checkpoint引數的大小，否則會出現寫入過慢或者叢集寫蹦的問題，具體要根據自己的資料量和叢集規模去調整。因為寫入資料較為頻繁，並且當batch_max_rows、batch_max_bytes設定太大時，資料的時效性就會變低，因此這幾個引數都會設定的較小，當前配置的為：

batch_max_rows = 10000
batch_max_bytes = 58864
Checkpoint = 1

• 離線資料寫入最佳化。離線匯入使用的Apache SeaTunnel，同樣需要控制好StarRocks的batch_max_rows、batch_max_bytes引數的大小以及SeaTunnel任務的並行度parallelism，否則也會出現過慢或者叢集寫蹦的問題。因為考慮到離線資料寫入不頻繁，一次性寫入的資料量較大，所以引數配置的會比較大，當前的配置為：

batch_max_rows = 1500000
batch_max_bytes = 335544320
parallelism = 90

5.寫在最後

本文提到的解決方案，絕不是OLAP自助分析的最優解，更不是唯一的答案，只是轉轉在業務發展過程中，結合當前現狀選擇的比較適合我們的一種實現方式，且已經在B2C、客服等多個場景中取得了一些成果。希望能夠給各位讀者帶來一些參考價值。

這個解決方案的優點是開發週期短、見效快；缺點就是需要配合比較好用的BI工具實現（業內比較好用的BI工具基本都是收費的，如果公司內部原本沒有在用的BI，可能需要額外的採購成本），另外就是資料集的維護成本較高且需要隨著業務發展持續迭代。

在OLAP和自助分析探索的道路上，我們也才剛剛開始，後續也將繼續聚焦業務痛點，嘗試更多的解法。道阻且長，行則將至，大家共勉。

如果本篇文章對大家有所幫助請幫忙點個贊，也歡迎大家在評論區交流~~

參考文件：

Quick BI官方文件:

StarRocks官方文件：

關於作者

邱狄凡，轉轉大資料開發工程師，線上業務數倉負責人，主要負責新媒體、B2C、供應鏈等業務主題數倉建設。