作者簡介

Kane，攜程高級數(shù)倉經(jīng)理，專注數(shù)倉建設、數(shù)據(jù)應用和分析；

Wn，大數(shù)據(jù)平臺開發(fā)專家，專注大數(shù)據(jù)領(lǐng)域。

攜程火車票事業(yè)群運營著鐵友、攜程火車票和去哪兒火車票等重要的業(yè)務和品牌，目前正在積極地拓展海外市場?；疖嚻钡闹笜似脚_旨在為業(yè)務人員提供便捷的指標查詢服務，讓業(yè)務人員能夠快速靈活地獲得這些業(yè)務和品牌相關(guān)的指標數(shù)據(jù)。

一、早期 OLAP 架構(gòu)與痛點

火車票事業(yè)群的業(yè)務涵蓋了火車票、國際火車票、汽車票（含船票）等產(chǎn)品，錯綜復雜的業(yè)務也產(chǎn)生了多種多樣訂單和行為數(shù)據(jù)，通過對這些數(shù)據(jù)的分析可以揭示當前業(yè)務的發(fā)展現(xiàn)狀，也可以為未來的發(fā)展提供方向指引。

早些時候事業(yè)群開發(fā)過一套指標平臺，根據(jù)不同的指標類型使用了 3 套數(shù)據(jù)庫引擎，分別是 ClickHouse，Apache Kylin （以下簡稱 Kylin）和 Presto，如下圖所示。

圖片

圖 1

在舊版的指標平臺中，為了提升查詢性能使用了 ClickHouse、 Kylin 和 Presto 等多種存儲和查詢引擎，數(shù)據(jù)層混合使用了明細層和輕度匯總層，由此帶來的問題有：

• 指標數(shù)據(jù)源混亂，容易造成口徑不一致，維護成本大。
• 學習成本高，BI 同學錄入指標不僅需要了解不同存儲的區(qū)別，還需要掌握不同引擎的數(shù)據(jù)同步方法。
• 架構(gòu)不合理，指標平臺將查詢的中間結(jié)果通過 jdbc 寫入 mysql 后再到服務端用 java 做匯總計算，處理鏈路過長，整體性能非常差，導致部分指標查詢需要半小時以上的等待時間。

鑒于這些原因，無論是用戶（運營人員）還是指標開發(fā)人員，都面臨著使用極差的問題。在這種情況下，我們決定使用基于一種查詢速度快和使用簡單的分布式數(shù)據(jù)庫來重構(gòu)指標平臺。

二、指標平臺重構(gòu)整體設計

首先，重構(gòu)指標平臺我們首先考慮的是將多套存儲合并成一套，雖說 ClickHouse 和 Kylin 已經(jīng)足夠強大，但是不足也很明顯。比如 ClickHouse 的 join 性能不盡如人意，并發(fā)性能差，SQL 語法是非標準的，使用起來不方便，大量的查詢很容易將 CPU 打滿；Kylin 是一個分析引擎，不支持增刪改操作，修改數(shù)據(jù)需要重新導入，修改 schema 需要重建 Cube（ETL成本很高），其次 Kylin 需要預先創(chuàng)建模型加載數(shù)據(jù)到 Cube 后才可進行查詢，使用上需要具備一定的數(shù)倉知識。

于是我們將目光投向 StarRocks，StarRocks 是一款全場景的 MPP 數(shù)據(jù)庫，相比 ClickHouse 等具有以下優(yōu)點：

• 性能強悍：查詢速度快，多張億級表 join 也能秒級響應；
• 使用簡單：兼容 MySQL 協(xié)議，用戶使用門檻低；
• 支持高并發(fā)：滿足大量用戶同時查詢；
• 支持多種數(shù)據(jù)模型：明細、聚合、更新和主鍵模型，可靈活配置 ETL 任務；
• 支持物化視圖：可以自動路由到命中的物化視圖，用戶無感知；
• 支持多種導入方式：StreamLoad、SparkLoad、RoutineLoad，便于實時離線快速導入 StarRocks，流批一體。

圖 2

因此，重構(gòu)后的結(jié)構(gòu)如下：

圖 3

重構(gòu)后的指標平臺只有一個數(shù)據(jù)庫，查詢時利用 StarRocks 內(nèi)部 ETL 將明細數(shù)據(jù)轉(zhuǎn)存到臨時表，后續(xù)的匯總從臨時表查詢，避免了反復掃描大表。

2.1 指標查詢過程

當一個指標查詢請求發(fā)起時，由于指標屬性和用戶想查看的信息不同，我們根據(jù)查詢參數(shù)將查詢拆解成若干子查詢，子查詢分為明細和匯總兩類。

1）明細類子查詢

a. 可累加的指標查詢時間范圍內(nèi)的明細數(shù)據(jù)，以及去年和 2019 年同期的明細數(shù)據(jù)，這部分的明細會存儲到臨時表，后續(xù)查詢都從這張表掃描，以避免對大表的頻繁掃描；該表每天生成 T+1 分區(qū)，防止增加分區(qū)失敗導致當天的指標查詢無法進行。

-tarpresqls "
ALTER TABLE ${table} ADD PARTITION if not exists p${partition}
VALUES [('${zdt.addDay(1).format("yyyy-MM-dd")}'),('${zdt.addDay(2).format("yyyy-MM-dd")}'));
" \

b. 如果指標不可累加或 count（distinct）類，僅存儲查詢時間范圍內(nèi)的明細,不存儲用戶計算同環(huán)比的明細；

c. 當多個指標同時對相同維度進行查詢時，將多個指標的數(shù)據(jù) join 后以寬表模式存儲。

2）匯總類子查詢

這一類 sql 主要在明細的基礎上根據(jù)用戶的需要做相應的計算，相比舊版本在服務內(nèi)部用 java 做匯總計算，這里全部借助了 StarRocks，主要的匯總功能有：

a. 指標卡匯總和同環(huán)比；

b. 折線圖和維度下鉆。

3） “緩存”

多維度特別是包含出發(fā)/到達城市組合的查詢數(shù)據(jù)量非常大，耗時較長，同時避免相同的查詢反復訪問大表，我們增加了“緩存”功能，實現(xiàn)原理如下：

a. 記錄初次查詢的指標信息，主要包括維度和維度值，時間范圍，指標原始計算 sql 的 MD5 值，以及是否查詢成功；

b. 新的查詢進入后，我們會在當天的記錄中查找是否存在相同的查詢。如果存在相同的查詢，我們使用唯一的查詢標識（groupkey）將當前查詢指向上次已經(jīng)執(zhí)行過的查詢。這樣，我們可以直接讀取上次查詢的詳細數(shù)據(jù)和匯總結(jié)果，從而提高查詢效率。

因此這里的緩存非真實意義上的緩存，而是直接調(diào)用相同查詢的結(jié)果。

2.2 數(shù)據(jù)同步

首先我們梳理了舊平臺的數(shù)據(jù)源，從 300+ 指標的邏輯 sql 中提取了公共的 dwd 和 dim 表 51 張，并將這些數(shù)據(jù)統(tǒng)一同步至 StarRocks，但是對于一些指標使用的 dwd 表只出現(xiàn)一次的，依然將 dws 同步過來。

對于不同的 hive 表，我們使用了不同的 StarRocks 建表模型和同步方式，有以下幾種：

a. 全量同步：主要針對一些數(shù)據(jù)量小的表，例如 shareout_trn.dim_ibu_alliance，大小為 608k；

b. 增量分區(qū)同步：每天同步 hive 表中 T-1 的分區(qū)，各分區(qū)之間獨立；

c. 更新同步：火車票 BU 的一些訂單數(shù)據(jù)由于涉及到預售和訂單狀態(tài)的變更，變更的數(shù)據(jù)時間跨度比較大，將跨度范圍內(nèi)的數(shù)據(jù)全部更新代價比較高，因此使用更新模型。

數(shù)據(jù)導入更新模型直接需要計算 T-1 和 T-2 分區(qū)有差異的數(shù)據(jù)，這里將所有字段使用 concat_ws（'|',***）拼接后取 hash 值，之后 join 找到 hash 值不一致的數(shù)據(jù)。

模型KEY設置：
UNIQUE KEY(`order_id`)
取兩天有差異的數(shù)據(jù)：
select
t1.* 
from
(select … where d='${cur_day}') as t1
left join
(select … where d=’${pre_day}’) as t2
on t1.business_pk_id=t2.business_pk_id
where t1.hash_code!=t2.hash_code or t2.order_id is null

d. 每天同步當月數(shù)據(jù)：如國際火車的訪問數(shù)據(jù)量較小，每天一個分區(qū)會導致 StarRocks 集群有很多小的 bucket，分桶數(shù)太多會導致元數(shù)據(jù)壓力比較大，數(shù)據(jù)導入導出時也會受到一些影響，因此我們按月設置分區(qū)，每天同步當月的數(shù)據(jù)。

時間范圍：
startdate='${zdt.format("yyyy-MM-01")}'
endDate='${zdt.add(2,1).format("yyyy-MM-01")}'
表設計：
PARTITION BY RANGE(dt)(Start("2019-01-01") End("2023-03-01") Every(Interval 1 month))
DISTRIBUTED BY HASH(分桶字段) BUCKETS 桶的數(shù)量
PROPERTIES (
"dynamic_partition.enable" = "true",
"dynamic_partition.prefix" = "p",
"dynamic_partition.time_unit" = "month",
"dynamic_partition.end" = "1");
datax配置：
-temporary_partitions "tp${partition}" \
-tarpresqls "
ALTER TABLE ${table} DROP TEMPORARY PARTITION if exists tp${partition};
ALTER TABLE ${table} ADD PARTITION if not exists p${partition} VALUES [('${startdate}'),('${endDate}'));
ALTER TABLE ${table} ADD TEMPORARY PARTITION tp${partition} VALUES [('${startdate}'),('${endDate}'));
" \
-tarpostsqls "
ALTER TABLE ${table} REPLACE PARTITION (p${partition}) WITH TEMPORARY PARTITION (tp${partition});"

此外，對于 UBT 類數(shù)據(jù)，數(shù)據(jù)量級非常大，并且常見用于查詢 PV，UV 和停留時長等比較固定的場景，于是我們從中抽取出三張表：

ubt_for_pv: 每天按維度匯總 count（uid），每天數(shù)據(jù)大小只有幾十 K；

ubt_for_duration: 每天按維度匯總 sum（duration），如需要計算平均停留時長除以對應的 pv 即可；

ubt_for_uv: 每天按維度去重，盡最大可能減少數(shù)據(jù)量。

最后，鑒于上游表的迭代可能帶來的數(shù)據(jù)的不穩(wěn)定，我們對需要同步的表的數(shù)據(jù)量做了監(jiān)控，若發(fā)現(xiàn)當天的數(shù)據(jù)量波動超過 3sigma，監(jiān)控任務自動發(fā)出郵件告警，這些 job 的同步都在 15 分鐘內(nèi)完成。

三、Starrocks使用經(jīng)驗分享

在指標平臺重構(gòu)的過程中我們也遇到了一些問題，與數(shù)據(jù)和查詢相關(guān)的有以下幾個：

3.1 建表經(jīng)驗

首先是 buckets 設置不合理，多數(shù)是設置過多，通常一個桶的數(shù)據(jù)量在 500MB~1GB 為好，個別表設置的桶數(shù)量太少，導致查詢時間長；其次是分區(qū)不合理，有些表沒有設置分區(qū)，有些設置的分區(qū)后每個分區(qū)數(shù)據(jù)量很小，優(yōu)化建議是將不常訪問的數(shù)據(jù)按月分區(qū)，經(jīng)常訪問的數(shù)據(jù)按日分區(qū)。

3.2 數(shù)據(jù)查詢

由于指標的查詢sql之前是針對不同引擎編寫，很多引擎是沒有索引的，比如 Presto。StarRocks 有豐富的索引功能，統(tǒng)一至 StarRocks 希望利用索引加速查詢，因此過濾條件中最好不要加函數(shù)，比如 select c1 from t1 where upper(employeeid) = upper(' s1')修改成select c1 from t1 where employeeid in(upper(' s1'), lower(' s1'))。

另外很多 sql 沒有使用分區(qū)，在 StarRocks 中將會全表掃描造成資源浪費。

3.3 函數(shù)問題

StarRocks 的 split 函數(shù)結(jié)果的下標從 1 開始，而 sparksql 等引擎對應的是從 0 開始，導致 sql 在 StarRocks 執(zhí)行查詢的時候不報錯但是結(jié)果錯誤。

select split('a,b,c',',')[0] StarRocks查詢結(jié)果為空，其他引擎查詢結(jié)果為‘a(chǎn)’
select split('a,b,c',',')[1] StarRocks查詢結(jié)果為‘a(chǎn)’，其他引擎查詢結(jié)果為‘b’

四、查詢性能大幅提升

指標平臺的重構(gòu)主要是為了解決查詢性能的問題，并且重構(gòu)后也基本達到了預期。重構(gòu)之前，復雜查詢需要數(shù)分鐘的時間才能完成。特別對于火車票相關(guān)指標，諸如出票票量指標，如果帶上出發(fā)和到達城市查詢，可能需要等待 30 分鐘以上，并且查詢失敗率較高。而在重構(gòu)后，查詢時間大大縮短，復雜查詢在 10s 左右，并且 P99 在 2 秒之內(nèi)，因此整體體驗得到顯著提升，用戶查詢次數(shù)相比改造前也有了翻倍的增長。

此外，現(xiàn)在新指標系統(tǒng)還豐富了更多功能，比如同環(huán)比和維度下鉆計算。得益于 StarRocks 的并發(fā)能力，我們可以在生成子查詢 SQL 后并發(fā)提交，從而大幅度減少響應時間，使得用戶在進行維度下鉆時幾乎無需等待即可快速獲取所需數(shù)據(jù)。

五、 后續(xù)優(yōu)化方向

a. 目前，UV 類的 Count Distinct 查詢是基于存儲了大量明細數(shù)據(jù)的方式進行的。然而，對于部分指標，我們可以嘗試使用 Bitmap 來減少不必要的明細數(shù)據(jù)存儲空間，并且更重要的是可以提高查詢速度。在接下來的工作中，我們計劃嘗試這種方案，以進一步優(yōu)化 UV 類指標的查詢性能。

b. 對于全量或增量更新的表使用聚合模型，聚合模型會對導入后具有相同維度的數(shù)據(jù)做預聚合，查詢的時候減少掃描數(shù)據(jù)的行數(shù)達到提升查詢速度的目的。

c. 當前的指標平臺計算過程將所需的數(shù)據(jù)寫入臨時表，后續(xù)改成使用物化視圖，在達到同樣效果的情況下減少了復雜度。