作者簡介

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

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

(相關(guān)資料圖)

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

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

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

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

圖片

圖 1

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

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

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

二、指標(biāo)平臺(tái)重構(gòu)整體設(shè)計(jì)

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

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

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

圖 2

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

圖 3

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

2.1 指標(biāo)查詢過程

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

1）明細(xì)類子查詢

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

-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. 如果指標(biāo)不可累加或 count（distinct）類，僅存儲(chǔ)查詢時(shí)間范圍內(nèi)的明細(xì),不存儲(chǔ)用戶計(jì)算同環(huán)比的明細(xì)；

c. 當(dāng)多個(gè)指標(biāo)同時(shí)對相同維度進(jìn)行查詢時(shí)，將多個(gè)指標(biāo)的數(shù)據(jù) join 后以寬表模式存儲(chǔ)。

2）匯總類子查詢

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

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

b. 折線圖和維度下鉆。

3）“緩存”

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

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

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

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

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

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

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

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

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

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

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

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

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

時(shí)間范圍：startdate="${zdt.format("yyyy-MM-01")}"endDate="${zdt.add(2,1).format("yyyy-MM-01")}"表設(shè)計(jì)：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 和停留時(shí)長等比較固定的場景，于是我們從中抽取出三張表：

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

ubt_for_duration: 每天按維度匯總 sum（duration），如需要計(jì)算平均停留時(shí)長除以對應(yīng)的 pv 即可；

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

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

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

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

3.1 建表經(jīng)驗(yàn)

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

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

由于指標(biāo)的查詢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 中將會(huì)全表掃描造成資源浪費(fèi)。

3.3 函數(shù)問題

StarRocks 的 split 函數(shù)結(jié)果的下標(biāo)從 1 開始，而 sparksql 等引擎對應(yīng)的是從 0 開始，導(dǎo)致 sql 在 StarRocks 執(zhí)行查詢的時(shí)候不報(bào)錯(cuò)但是結(jié)果錯(cuò)誤。

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’

四、查詢性能大幅提升

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

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

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

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

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

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

關(guān)鍵詞：