• 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.寫在最后

一.導讀

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

二.背景介紹

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

2.1 為什么要做自助分析

做大數據開發的朋友是否有這樣的困擾：

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

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

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

2.2 核心解決的問題

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

2.3 建設初期的卡點

• 要求上手簡單易理解

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

• 時間緊、任務重

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

三.技術實現

鑒于上一部分提到的一些背景，如果采用純自研的方案，很難在那么短的時間并且投入那么少的研發資源的前提下取的很好的效果，結合我們本身就一直在使用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.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 支持將一些大算子的中間結果落盤。使用此功能，您可以在犧牲一部分性能的前提下，大幅降低大規模數據查詢上的內存消耗，進而提高整個系統的可用性。開啟方法可參考：https://docs.starrocks.io/zh/docs/administration/spill_to_disk/

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

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

五.寫在最后

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

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

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

如果本篇文章對大家有所幫助請幫忙點個贊。