• 1.導(dǎo)讀
• 2.背景介紹

• 2.1 為什么要做自助分析
• 2.2 核心解決的問題
• 2.3 建設(shè)初期的卡點

• 3.技術(shù)實現(xiàn)
• 3.1 技術(shù)架構(gòu)
• 3.2 基于Quick BI+StarRocks的自助分析功能實現(xiàn)
• 3.3 為什么選擇StarRocks作為OLAP引擎
• 3.4 上線效果
• 4.優(yōu)化案例
• 4.1 內(nèi)存超限問題優(yōu)化
• 4.2 慢查詢問題優(yōu)化
• 4.3 維值加載慢問題優(yōu)化
• 4.4 高峰期查詢慢問題優(yōu)化
• 4.5 數(shù)據(jù)寫入?yún)?shù)優(yōu)化
• 5.寫在最后

一.導(dǎo)讀

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

二.背景介紹

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

2.1 為什么要做自助分析

做大數(shù)據(jù)開發(fā)的朋友是否有這樣的困擾：

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

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

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

2.2 核心解決的問題

• 滿足業(yè)務(wù)側(cè)靈活組合各種維度進(jìn)行業(yè)務(wù)指標(biāo)分析的訴求
• 提升業(yè)務(wù)側(cè)獲取數(shù)據(jù)的效率
• 減少數(shù)倉RD同學(xué)在固定看板需求開發(fā)和迭代上的時間投入
• 減少分析師在日常取數(shù)需求上的時間投入

2.3 建設(shè)初期的卡點

• 要求上手簡單易理解

因為自助分析是要直接給到業(yè)務(wù)側(cè)品類運營、供應(yīng)鏈運營、產(chǎn)品運營等團(tuán)隊使用，對于平臺的易用性和數(shù)據(jù)的可理解性就要求比較高，對于從0到1去搭建一個這樣的平臺，其實是一個蠻大的挑戰(zhàn)。

• 時間緊、任務(wù)重

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

三.技術(shù)實現(xiàn)

鑒于上一部分提到的一些背景，如果采用純自研的方案，很難在那么短的時間并且投入那么少的研發(fā)資源的前提下取的很好的效果，結(jié)合我們本身就一直在使用Quick BI進(jìn)行數(shù)據(jù)可視化分析現(xiàn)狀，最終選擇了BI工具+OLAP數(shù)據(jù)庫的組合，從另一個角度解決上述提到的卡點，達(dá)到了預(yù)期的效果。

一句話概括就是：利用Quick BI靈活的托拉拽圖表配置及自動生成查詢SQL的能力 + StarRocks數(shù)據(jù)庫強(qiáng)大的數(shù)據(jù)計算能力，實現(xiàn)基于高度冗余的業(yè)務(wù)數(shù)據(jù)明細(xì)大寬表數(shù)據(jù)集為基礎(chǔ)的、靈活的自助分析。

3.1 技術(shù)架構(gòu)

項目建設(shè)初期是以離線作為切入點的，二期才陸續(xù)迭代了實時數(shù)據(jù)集的能力。使用到的產(chǎn)品及組件有Quick BI、StarRocks、Hive、Spark、Flink、kafka等。

架構(gòu)圖如下：

圖片

數(shù)據(jù)鏈路如下：

圖片

3.2 基于Quick BI+StarRocks的自助分析功能實現(xiàn)

開始這部分介紹前，先給大家講講Quick BI是個啥。Quick BI是阿里云旗下的智能BI服務(wù)平臺，我們使用的是私有化部署的版本。它可以提供海量數(shù)據(jù)實時在線分析服務(wù)，支持拖拽式操作和豐富的可視化效果，幫助用戶輕松自如地完成數(shù)據(jù)分析、業(yè)務(wù)數(shù)據(jù)探查、報表制作等工作。具體怎么使用以及它的功能特性可以自行到官網(wǎng)查看學(xué)習(xí)，下面是它的產(chǎn)品功能架構(gòu)供大家了解：

圖片

接下來我們重點展開說說使用Quick BI進(jìn)行自助分析的功能實現(xiàn)需要做哪些事情，總結(jié)成一句話就是：創(chuàng)建數(shù)據(jù)源用于鏈接StarRocks數(shù)據(jù)庫；然后讀取StarRocks表創(chuàng)建數(shù)據(jù)集，同時進(jìn)行維度和指標(biāo)的定義；最后創(chuàng)建儀表板即可進(jìn)行數(shù)據(jù)的自助分析。這里可以看到，跟大多數(shù)BI一樣，無非就是獲取數(shù)據(jù)、創(chuàng)建數(shù)據(jù)集、托拉拽圖表進(jìn)行數(shù)據(jù)可視化。

重點說說，為了滿足易用和易理解的訴求，我們在數(shù)據(jù)集層面做的一些設(shè)計。

數(shù)據(jù)結(jié)構(gòu)設(shè)計如下：

核心三類字段，分別是data_type、維度字段、原子指標(biāo)字段。data_type用于區(qū)分不同的數(shù)據(jù)，不同data_type具備不同的維度和原子指標(biāo)，對不支持的維度和原子指標(biāo)直接存儲為null。

這樣的結(jié)構(gòu)高度冗余，雖然不易于維護(hù)，但是好處也很明顯：一個數(shù)據(jù)集即可拿到幾乎所有業(yè)務(wù)過程的數(shù)據(jù)，以及相關(guān)聯(lián)的各個維度和指標(biāo)。

對于維度和指標(biāo)體系，運營同學(xué)是相對比較熟悉的，這樣做的好處就體現(xiàn)出來了：他們不需要去理解那么多的數(shù)據(jù)集，不用去根據(jù)分析的場景判斷要用哪個數(shù)據(jù)集，只要知道自己想到什么維度組合、分析什么指標(biāo)即可，極大的簡化了運營理解數(shù)據(jù)的成本，降低了使用難度。

數(shù)據(jù)集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
      ...
      ,原子指標(biāo)n
from   sr_table t

這里再補(bǔ)充一點，數(shù)據(jù)集SQL定義了原子指標(biāo)的邏輯，聚合的方式是可以在Quick BI的數(shù)據(jù)集里面進(jìn)行配置，包括求和、求均值、計數(shù)、去重計數(shù)等都是支持的。在維度、原子指標(biāo)的基礎(chǔ)上，還可以進(jìn)行計算字段的加工，衍生出更多的維度和派生指標(biāo)。由于時間關(guān)系，就不展開講解怎么配置，大家感興趣可以去看一下官方文檔。

最后一步，就是最終的目標(biāo)自助分析了。整體流程如下：

圖片

簡單概括一下就是： 創(chuàng)建儀表板>添加可視化圖表>選擇數(shù)據(jù)集>綁定維度和度量（指標(biāo)）。

目前支持40余種圖表樣式，包含了表格類、指標(biāo)類、線/面圖類、柱/條圖類、餅/環(huán)類、氣泡/散點類、漏斗/轉(zhuǎn)化關(guān)系類、地理類和其他類；涵蓋了趨勢、比較、分布、關(guān)系、空間、時序6個分析大類，同時支持自定義圖表類型，基本覆蓋了常見的可視化分析方式。

最終呈現(xiàn)的形式：

圖片

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

關(guān)于這個問題，起初我們用過一段時間的ClickHouse，受限于集群規(guī)模，在我們的數(shù)據(jù)體量和使用場景下，出現(xiàn)了明顯的性能瓶頸（ps:單數(shù)據(jù)集近200億行數(shù)據(jù)，300+維度和指標(biāo)；長時間周期且比較多維度指標(biāo)的基于明細(xì)數(shù)據(jù)的復(fù)雜查詢）。

后面經(jīng)過測試，StarRocks在我們這個場景下性能要優(yōu)于ClickHouse，并且在一些特性上更加友好，后面就統(tǒng)一將業(yè)務(wù)切到了StarRocks上。因為前后業(yè)務(wù)體量有些差異，加上集群規(guī)模也不完全一致，就不貼具體的測試結(jié)果，避免引起不必要的誤會。但是有幾個點，在我們使用的感受上，StarRocks是要明顯優(yōu)于ClickHouse的：

• StarRocks 兼容 MySQL 協(xié)議，支持標(biāo)準(zhǔn) SQL 語法，這點在自助分析的場景實在是太友好了，相比于ClickHouse來說，極大的簡化了業(yè)務(wù)側(cè)運營人員創(chuàng)建計算字段的難度。
• StarRocks 在彈性擴(kuò)縮容的支持上比ClickHouse要更加友好。
• StarRocks 對Join的操作支持更加友好。
• StarRocks 對多并發(fā)的場景支持更好。
• StarRocks 的數(shù)據(jù)類型跟Hive非常接近，進(jìn)行數(shù)據(jù)回導(dǎo)的時候映射更加簡單。

（StarRocks集群規(guī)模：3FE節(jié)點 + 14BE節(jié)點）

3.4 上線效果

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

四.優(yōu)化案例

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

4.1 內(nèi)存超限問題優(yōu)化

由于StarRocks使用的MPP架構(gòu)，當(dāng)查詢的數(shù)據(jù)量比較大時，就很容易觸發(fā)內(nèi)存超限的問題：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 支持將一些大算子的中間結(jié)果落盤。使用此功能，您可以在犧牲一部分性能的前提下，大幅降低大規(guī)模數(shù)據(jù)查詢上的內(nèi)存消耗，進(jìn)而提高整個系統(tǒng)的可用性。開啟方法可參考：https://docs.starrocks.io/zh/docs/administration/spill_to_disk/

開啟中間結(jié)果落盤之后，一定程度上可以緩解內(nèi)存超限的問題出現(xiàn)，但是只能治標(biāo)，并不能治本，從根本上還需要減少大查詢的產(chǎn)生，核心還是慢查詢的治理。

4.2 慢查詢問題優(yōu)化

業(yè)務(wù)側(cè)配置圖表時不規(guī)范，很容易就會產(chǎn)生大量的慢查詢，經(jīng)過對慢SQL的分析，發(fā)現(xiàn)往往都是沒有進(jìn)行有效的數(shù)據(jù)裁剪導(dǎo)致全表去查所有數(shù)據(jù)，從而出現(xiàn)大量的慢查詢。

有效的數(shù)據(jù)裁剪：首先是要求業(yè)務(wù)側(cè)使用自助分析時，日期維度是必須要限制的，其次是對data_type過濾不需要查看的數(shù)據(jù)類型；在技術(shù)層面對日期維度、和data_type維度沒有入?yún)r，傳遞默認(rèn)值查詢返回null結(jié)果；其次是在對日期和data_type進(jìn)行過濾的時候，不要在這兩個字段上套函數(shù)和處理邏輯，這樣才能夠正常命中索引。實測進(jìn)行有效的數(shù)據(jù)裁剪之后，查詢性能可以得到幾十倍的提升，極大的減少了慢查詢的出現(xiàn)。

謂詞下推機(jī)制：謂詞下推是很多OLAP數(shù)據(jù)庫都支持的能力，這里提一下主要是因為Quick BI的數(shù)據(jù)集是通過SQL創(chuàng)建的，前端托拉拽配置圖表生成查詢SQL時，是把數(shù)據(jù)集SQL作為一個子查詢?nèi)テ唇拥摹淼囊粋€問題就是如果不能合理利用謂詞下推的機(jī)制，就會導(dǎo)致索引失效從而全表掃描數(shù)據(jù)，影響整體的查詢體驗。實測只要最外層的維度沒有額外的轉(zhuǎn)換動作，即可觸發(fā)謂詞下推的機(jī)制，從而正常走索引查詢。

大致的效果如下：

-- 假如有一張表table
-- table表有一個字段a，a字段有索引
-- SQL1,常規(guī)寫法，先過濾數(shù)據(jù)再做進(jìn)一步的處理
-- 這樣寫可以命中索引，可以避免加載過多的數(shù)據(jù)到內(nèi)存
select * 
from (
    select * 
    from table
    where a = 'aaa'
) t
;
-- SQL2，當(dāng)子查詢不能提前過濾，但不對維度字段做轉(zhuǎn)換操作
-- 同樣可以命中索引，可以避免加載過多的數(shù)據(jù)到內(nèi)存
-- 性能等同于SQL1
select * 
from (
    select * 
    from table
) t
where t.a = 'aaa'
;
-- SQL3，因為對a增加了轉(zhuǎn)換操作，不能夠開啟謂詞下推
-- 導(dǎo)致無法命中索引，將全表數(shù)據(jù)加載到內(nèi)存
select * 
from (
    select * 
    from table
) t
where trim(t.a) = 'aaa'
;

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

4.3 維值加載慢問題優(yōu)化

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

4.4 高峰期查詢慢問題優(yōu)化

業(yè)務(wù)使用高峰時，查詢耗時普遍會比日常要慢不少，核心原因在于扎堆使用導(dǎo)致StarRocks集群的負(fù)載比較大。經(jīng)過調(diào)研發(fā)現(xiàn)，查詢高峰主要集中在周一或者月初，出周報、月報需要自助分析一些數(shù)據(jù)，并且很多都是根據(jù)提前配置好的儀表板查詢一次對應(yīng)的數(shù)據(jù)即可，但是因為查詢數(shù)據(jù)庫都是發(fā)生在訪問頁面時，所以伴隨扎堆的使用出現(xiàn)了該問題?；谶@種場景，我們想到了一個錯峰查詢的辦法：在低峰時（9點前），通過selenium模擬訪問儀表板列表，提前將請求的SQL和結(jié)果緩存起來。這樣一來，減少了高峰期時對StarRocks的查詢操作，緩解了集群壓力，整體的查詢性能也得到保障，業(yè)務(wù)也優(yōu)先通過緩存獲取到對應(yīng)的周報、月報數(shù)據(jù)，提升了用戶體驗。

大致的方案：部署一個定時調(diào)度的python腳本，通過selenium遍歷提前配置好的儀表板列表，模擬用戶的訪問行為去進(jìn)行翻頁，需要控制好翻頁的頻率，使頁面懶加載的內(nèi)容也能夠加載出來。整個過程要控制好停留的時間以及并發(fā)，避免把StarRocks查掛了。

4.5 數(shù)據(jù)寫入?yún)?shù)優(yōu)化

• 實時寫入時效性優(yōu)化。通過Flink往StarRocks實時寫入數(shù)據(jù)時，要控制好StarRocks的batch_max_rows、batch_max_bytes，以及Flink的Checkpoint參數(shù)的大小，否則會出現(xiàn)寫入過慢或者集群寫蹦的問題，具體要根據(jù)自己的數(shù)據(jù)量和集群規(guī)模去調(diào)整。因為寫入數(shù)據(jù)較為頻繁，并且當(dāng)batch_max_rows、batch_max_bytes設(shè)置太大時，數(shù)據(jù)的時效性就會變低，因此這幾個參數(shù)都會設(shè)置的較小，當(dāng)前配置的為：

batch_max_rows = 10000
batch_max_bytes = 58864
Checkpoint = 1

• 離線數(shù)據(jù)寫入優(yōu)化。離線導(dǎo)入使用的Apache SeaTunnel，同樣需要控制好StarRocks的batch_max_rows、batch_max_bytes參數(shù)的大小以及SeaTunnel任務(wù)的并行度parallelism，否則也會出現(xiàn)過慢或者集群寫蹦的問題。因為考慮到離線數(shù)據(jù)寫入不頻繁，一次性寫入的數(shù)據(jù)量較大，所以參數(shù)配置的會比較大，當(dāng)前的配置為：

batch_max_rows = 1500000
batch_max_bytes = 335544320
parallelism = 90

五.寫在最后

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

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

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

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