本文主要分享了作者在螞蟻集團(tuán)高管數(shù)據(jù)鏈路改造升級(jí)過(guò)程中，針對(duì)去重Cube的優(yōu)化實(shí)踐。

引言

SQL作為目前最通用的數(shù)據(jù)庫(kù)查詢(xún)語(yǔ)言，其功能和特性復(fù)雜度遠(yuǎn)不止大家常用的“SELECT * FROM tbl”這樣簡(jiǎn)單，一段好的SQL和差的SQL，其性能可能有幾十乃至上千倍的差距。而寫(xiě)出一個(gè)好的能兼顧性能和易用性的SQL，考驗(yàn)的不僅僅是了解到多少新特性新寫(xiě)法，而是要深入理解數(shù)據(jù)的處理過(guò)程，然后設(shè)計(jì)好數(shù)據(jù)的處理過(guò)程。

一、場(chǎng)景描述

在做數(shù)據(jù)匯總計(jì)算和統(tǒng)計(jì)分析時(shí)，最頭疼的就是去重類(lèi)指標(biāo)計(jì)算（比如用戶(hù)數(shù)、商家數(shù)等），尤其還要帶多種維度的下鉆分析，由于其不可累加的特性，幾乎每換一種統(tǒng)計(jì)維度組合，都得重新計(jì)算。數(shù)據(jù)量小時(shí)可以暴力的用明細(xì)數(shù)據(jù)直接即時(shí)統(tǒng)計(jì)，但當(dāng)數(shù)據(jù)量大時(shí)就不得不考慮提前進(jìn)行計(jì)算了。

典型場(chǎng)景如下：省、市、區(qū)等維度下的支付寶客戶(hù)端的日支付用戶(hù)數(shù)（其中省、市、區(qū)為用戶(hù)支付時(shí)所在的位置，表格中指標(biāo)數(shù)據(jù)均為虛構(gòu)的）。

存在一個(gè)情況，某用戶(hù)早上在杭州市使用支付寶支付了一次，下午跑到紹興市時(shí)又使用支付寶線(xiàn)下支付了一次。那么在統(tǒng)計(jì)省+市維度的日支付用戶(hù)數(shù)，需要為杭州市、紹興市各計(jì)1；但在省維度下，需要按用戶(hù)去重，只能為浙江省計(jì)1。針對(duì)這種情況，通常就需要以Cube的方式完成數(shù)據(jù)預(yù)計(jì)算，同時(shí)每個(gè)維度組合都需要進(jìn)行去重操作，因?yàn)椴豢衫奂印１疚膶⒋朔N場(chǎng)景簡(jiǎn)稱(chēng)為去重Cube。

二、常見(jiàn)的實(shí)現(xiàn)方法

直接計(jì)算，每個(gè)維度組合單獨(dú)計(jì)算。比如單獨(dú)生成省、省+市、省+市+區(qū)等維度組合的多張表。每個(gè)表只計(jì)算固定的維度。然后是數(shù)據(jù)膨脹再計(jì)算，如Union All或者Lateral View Explode或者M(jìn)axCompute的 Cube計(jì)算功能，通過(guò)數(shù)據(jù)膨脹實(shí)現(xiàn)一行數(shù)據(jù)滿(mǎn)足多種維度組合的數(shù)據(jù)計(jì)算方法，如下圖所示。

這三種寫(xiě)法其實(shí)都類(lèi)似，重點(diǎn)都在于對(duì)數(shù)據(jù)進(jìn)行膨脹，再進(jìn)行去重統(tǒng)計(jì)。其執(zhí)行流程如下圖所示，核心思路都是先把數(shù)據(jù)"膨脹"拆為多行，再按照“普通”的Distinct去重統(tǒng)計(jì)，因此性能上本身無(wú)太大差異，主要在于代碼可維護(hù)性上。

三、性能分析

上述方法核心都是先把數(shù)據(jù)"膨脹"拆為多行，再按照“普通”的Distinct去重統(tǒng)計(jì)，本身性能無(wú)太大差異，主要在于代碼可維護(hù)性上。這幾種方案計(jì)算消耗會(huì)隨著所需維度組合線(xiàn)性增加，同時(shí)還要疊加Distinct本身的計(jì)算性能差的影響。

在實(shí)際實(shí)驗(yàn)中，我們發(fā)現(xiàn)，去重Cube的計(jì)算過(guò)程中，80%+的計(jì)算成本消耗在數(shù)據(jù)膨脹和數(shù)據(jù)傳輸上。比如高管核心指標(biāo)場(chǎng)景，需要計(jì)算各種組合維度下的支付用戶(hù)數(shù)以支持分析。實(shí)際實(shí)驗(yàn)中，選取100億數(shù)據(jù)x25種維度組合進(jìn)行測(cè)試，實(shí)際執(zhí)行任務(wù)如下圖所示，其中R3_2為核心的數(shù)據(jù)膨脹過(guò)程，數(shù)據(jù)膨脹近10倍，中間結(jié)果數(shù)據(jù)大小由100GB膨脹至1TB、數(shù)據(jù)量由100億膨脹至近1300億，大部分計(jì)算資源和計(jì)算耗時(shí)都花在數(shù)據(jù)膨脹和傳輸上了。若實(shí)際的組合維度進(jìn)一步增加的話(huà)，數(shù)據(jù)膨脹大小也將進(jìn)一步增加。

四、一種新的思路

首先對(duì)問(wèn)題進(jìn)行拆解下，去重Cube的計(jì)算過(guò)程核心分為兩個(gè)部分，數(shù)據(jù)膨脹+數(shù)據(jù)去重。數(shù)據(jù)膨脹解決的是一行數(shù)據(jù)同時(shí)滿(mǎn)足多種維度組合的計(jì)算，數(shù)據(jù)去重則是完成最終的去重統(tǒng)計(jì)，核心思路還是在于原始數(shù)據(jù)去匹配結(jié)果數(shù)據(jù)的需要。其中數(shù)據(jù)去重本身的計(jì)算量就較大，而數(shù)據(jù)膨脹會(huì)導(dǎo)致這一情況加劇，因?yàn)橛?jì)算過(guò)程中需要拆解和在shuffle過(guò)程中傳輸大量的數(shù)據(jù)。數(shù)據(jù)計(jì)算過(guò)程中是先膨脹再聚合，加上本身數(shù)據(jù)內(nèi)容的中英文字符串內(nèi)容較大，所以才導(dǎo)致了大量的數(shù)據(jù)計(jì)算和傳輸成本。

而我們的核心想法是能否避免數(shù)據(jù)膨脹，同時(shí)進(jìn)一步減少數(shù)據(jù)傳輸大小。因此我們聯(lián)想到，是否可以采用類(lèi)似于用戶(hù)打標(biāo)簽的數(shù)據(jù)打標(biāo)方案，先進(jìn)行數(shù)據(jù)去重生成UID粒度的中間數(shù)據(jù)，同時(shí)讓需要的結(jié)果維度組合反向附加到UID粒度的數(shù)據(jù)上，在此過(guò)程中并對(duì)結(jié)果維度進(jìn)行編號(hào)，用更小的數(shù)據(jù)結(jié)構(gòu)去存儲(chǔ)，避免數(shù)據(jù)計(jì)算過(guò)程中的大量數(shù)據(jù)傳輸。整個(gè)數(shù)據(jù)計(jì)算過(guò)程中，數(shù)據(jù)量理論上是逐漸收斂的，不會(huì)因?yàn)榻y(tǒng)計(jì)維度組合的增加而增加。

4.1.核心思路

核心計(jì)算思路如上圖，普通的數(shù)據(jù)膨脹計(jì)算cube的方法，中間需要對(duì)數(shù)據(jù)進(jìn)行膨脹，再聚合，其中結(jié)果統(tǒng)計(jì)需要的組合維度數(shù)就是數(shù)據(jù)膨脹的倍數(shù)，比如上述的“省、省+市”共計(jì)兩種維度組合，數(shù)據(jù)預(yù)計(jì)要膨脹2倍。

而新的數(shù)據(jù)聚合方法，通過(guò)一定的策略方法將維度組合拆解為維度小表并進(jìn)行編號(hào)，然后將原本的訂單明細(xì)數(shù)據(jù)聚合至用戶(hù)粒度的中間過(guò)程數(shù)據(jù)，其中各類(lèi)組合維度轉(zhuǎn)換為數(shù)字標(biāo)記錄至用戶(hù)維度的數(shù)據(jù)記錄上，整個(gè)計(jì)算過(guò)程數(shù)據(jù)量是呈收斂聚合的，不會(huì)膨脹。

4.2.邏輯實(shí)現(xiàn)

明細(xì)數(shù)據(jù)準(zhǔn)備：以用戶(hù)線(xiàn)下支付數(shù)據(jù)為例，明細(xì)記錄包含訂單編號(hào)、用戶(hù)ID、支付日期、所在省、所在市、支付金額。最終指標(biāo)統(tǒng)計(jì)需求為統(tǒng)計(jì)包含省、市組合維度+支付用戶(hù)數(shù)的多維Cube。

整體方案流程如下圖。

STEP1：對(duì)明細(xì)數(shù)據(jù)進(jìn)行所需的維度提取（即Group By對(duì)應(yīng)字段），得到維度集合。

STEP2：對(duì)得到的維度集合生成Cube，并對(duì)Cube的行進(jìn)行編碼 （假設(shè)最終需要所在省、所在省+所在市2種組合維度），可以用ODPS的Cube功能實(shí)現(xiàn)，再根據(jù)生成的Cube維度組合進(jìn)行排序生成唯一編碼。

STEP3：將Cube的行編碼，根據(jù)映射關(guān)系回寫(xiě)到用戶(hù)明細(xì)上，可用Mapjoin的方式實(shí)現(xiàn)。

STEP4：匯總到用戶(hù)維度，并對(duì) Cube ID集合字段進(jìn)行去重 （可以用ARRAY 的DISTINCT）

STEP5：按照Cube ID進(jìn)行計(jì)數(shù)計(jì)算（由于STEP4已經(jīng)去重啦，因此這里不需要再進(jìn)行去重）；然后按照映射關(guān)系進(jìn)行維度還原。

Over～

4.3.代碼實(shí)現(xiàn)

WITH 
-- 基本的明細(xì)數(shù)據(jù)表準(zhǔn)備
base_dwd AS (
  SELECT   pay_no
          ,user_id
          ,gmt_pay
          ,pay_amt
          ,prov_name
          ,prov_code
          ,city_name
          ,city_code
  FROM tmp_user_pay_order_detail
)
-- 生成多維Cube,并進(jìn)行編碼
,dim_cube AS (
  -- Step02:CUbe生成
  SELECT *,DENSE_RANK() OVER(PARTITION BY 1 ORDER BY cube_prov_name,cube_city_name) AS cube_id 
  FROM (
    SELECT dim_key
          ,COALESCE(IF(GROUPING(prov_name) = 0,prov_name,'ALL'),'na') AS cube_prov_name             
          ,COALESCE(IF(GROUPING(city_name) = 0,city_name,'ALL'),'na') AS cube_city_name 
    FROM (
      -- Step01:維度統(tǒng)計(jì)
        SELECT  CONCAT(''
                       ,COALESCE(prov_name ,''),'#' 
                       ,COALESCE(city_name     ,''),'#' 
                ) AS dim_key
                ,prov_name
                ,city_name
        FROM base_dwd
        GROUP BY prov_name
        ,city_name
    ) base
    GROUP BY dim_key
            ,prov_name
              ,city_name
    GROUPING SETS (
           (dim_key,prov_name)
          ,(dim_key,prov_name,city_name)
    )
  )
)
-- 將CubeID回寫(xiě)到明細(xì)記錄上,并生成UID粒度的中間過(guò)程數(shù)據(jù)
,detail_ext AS (
  -- Step04:指標(biāo)統(tǒng)計(jì)
  SELECT   user_id
          ,ARRAY_DISTINCT(SPLIT(WM_CONCAT(';',cube_ids),';')) AS cube_id_arry
  FROM (
    -- Step03:CubeID回寫(xiě)明細(xì)
    SELECT  /*+ MAPJOIN(dim_cube) */ 
          user_id
        ,cube_ids
    FROM (
        SELECT   user_id
                ,CONCAT(''
                       ,COALESCE(prov_name,''),'#' 
                      ,COALESCE(city_name,''),'#' 
                 ) AS dim_key
        FROM base_dwd
    ) dwd_detail
    JOIN (
        SELECT dim_key,WM_CONCAT(';',cube_id) AS cube_ids
        FROM dim_cube 
        GROUP BY dim_key
    ) dim_cube
    ON dwd_detail.dim_key = dim_cube.dim_key
  ) base
  GROUP BY user_id
)
-- 指標(biāo)匯總并將CubeID翻譯回可理解的維度
,base_dws AS (
  -- Step05:CubeID翻譯
  SELECT cube_id
        ,MAX(prov_name) AS prov_name
        ,MAX(city_name    ) AS city_name
        ,MAX(uid_cnt      ) AS user_cnt
  FROM (
      SELECT cube_id              AS cube_id
            ,COUNT(1)             AS uid_cnt
            ,CAST(NULL AS STRING) AS prov_name
            ,CAST(NULL AS STRING) AS city_name
      FROM detail_ext
      LATERAL VIEW EXPLODE(cube_id_arry) arr AS cube_id
      GROUP BY cube_id
      UNION ALL 
      SELECT CAST(cube_id AS STRING) AS cube_id
            ,CAST(NULL AS BIGINT) AS uid_cnt
            ,cube_prov_name       AS prov_name
            ,cube_city_name       AS city_name    
      FROM dim_cube
  ) base 
  GROUP BY cube_id
)
-- 大功告成,輸出結(jié)果！！！
SELECT   prov_name
        ,city_name
        ,user_cnt
FROM base_dws
;

實(shí)際的執(zhí)行過(guò)程（ODPS的Logview）如下圖。

4.4.實(shí)驗(yàn)效果

左邊是基于Cube打標(biāo)方案的新鏈路。實(shí)驗(yàn)過(guò)程中將實(shí)驗(yàn)數(shù)據(jù)由100億增加至200億，組合維度數(shù)由原來(lái)的25個(gè)增加至50種組合維度，整體耗時(shí)在18分鐘，若只計(jì)算和原始數(shù)據(jù)量、組合維度均相同的數(shù)據(jù)，整體計(jì)算耗時(shí)可控制在10分鐘內(nèi)。

右邊是基于數(shù)據(jù)膨脹計(jì)算的老鏈路。實(shí)驗(yàn)數(shù)據(jù)設(shè)定為100億，組合維度數(shù)為25種，中間過(guò)數(shù)據(jù)將膨脹至1300億+，數(shù)據(jù)大小更是膨脹至1TB+，整體耗時(shí)47分鐘。若此方案擴(kuò)展至新方法的200億數(shù)據(jù)x50種組合維度，中間過(guò)程數(shù)據(jù)將膨脹至4000億+，數(shù)據(jù)大小增加將膨脹至3TB+，整體計(jì)算耗時(shí)預(yù)估將達(dá)到2.5小時(shí)+。

新方法目前已經(jīng)在業(yè)務(wù)核心高管鏈路上線(xiàn)，在數(shù)據(jù)統(tǒng)計(jì)維度組合、數(shù)據(jù)計(jì)算量都大幅增加的情況下，整體核心指標(biāo)產(chǎn)出相較于以往，進(jìn)一步提前1小時(shí)以上，有效的保障了相關(guān)核心指標(biāo)數(shù)據(jù)的穩(wěn)定性。

4.5.方案總結(jié)

常見(jiàn)的基于數(shù)據(jù)膨脹的Cube計(jì)算方法，數(shù)據(jù)計(jì)算大小和過(guò)程數(shù)據(jù)傳輸量將隨著組合維度的數(shù)量呈線(xiàn)性增長(zhǎng)，組合維度數(shù)越多，花費(fèi)在數(shù)據(jù)膨脹與Shuffle傳輸?shù)馁Y源和耗時(shí)占比越高。在實(shí)驗(yàn)過(guò)程中，100億實(shí)驗(yàn)數(shù)據(jù)x25種維度組合場(chǎng)景，過(guò)程數(shù)據(jù)已經(jīng)膨脹至1300億+，數(shù)據(jù)大小由100GB膨脹至1TB，當(dāng)數(shù)據(jù)量和維度組合數(shù)進(jìn)一步增加時(shí)，整個(gè)計(jì)算過(guò)程基本上難以完成。

為了解決數(shù)據(jù)膨脹過(guò)程中產(chǎn)生的大量過(guò)程數(shù)據(jù)，我們基于數(shù)據(jù)打標(biāo)的思路反向操作，先對(duì)數(shù)據(jù)聚合為UID粒度，過(guò)程中將需要的維度組合轉(zhuǎn)化編碼數(shù)字并賦予明細(xì)數(shù)據(jù)上，整個(gè)計(jì)算過(guò)程數(shù)據(jù)呈收斂聚合狀，數(shù)據(jù)計(jì)算過(guò)程較為穩(wěn)定，不會(huì)隨著維度組合的進(jìn)一步增加而大幅增加。在實(shí)驗(yàn)中，將實(shí)驗(yàn)數(shù)據(jù)由100億增加至200億+，組合維度數(shù)由原來(lái)的25個(gè)增加至50種組合維度，整體耗時(shí)控制在18分鐘左右。若同等的數(shù)據(jù)量，采用老的數(shù)據(jù)膨脹方案，中間過(guò)程數(shù)據(jù)將膨脹至4000億+，數(shù)據(jù)大小將增加至3TB+，整體計(jì)算耗時(shí)將達(dá)到2.5小時(shí)+。

綜上，當(dāng)前的方案整體性能相較于以往有大幅度的提升，并且不會(huì)隨著維度組合的增加而有明顯的增加。但當(dāng)前的方案也有不足之處，即代碼的可理解性和可維護(hù)性，過(guò)程中的打標(biāo)計(jì)算過(guò)程雖然流程較為固定，但整體上需要有個(gè)初始化理解的過(guò)程，目前尚無(wú)法做到普通UnionAll/Cube等方案的易讀和易寫(xiě)。另外，當(dāng)組合維度數(shù)較少（即數(shù)據(jù)膨脹倍數(shù)不高）時(shí)，兩者的性能差異不大，此時(shí)建議還是用原始普通的Cube計(jì)算方案；但當(dāng)組合維度數(shù)達(dá)幾十倍時(shí)，可以改用這種數(shù)據(jù)打標(biāo)的思路進(jìn)行壓縮，畢竟此時(shí)的性能優(yōu)勢(shì)開(kāi)始凸顯，并且維度組合數(shù)越高，此方案的性能優(yōu)勢(shì)越大。

五、其他方案

BitMap方案。核心思路在于將不可累計(jì)的數(shù)據(jù)指標(biāo)，通過(guò)可累加計(jì)算的數(shù)據(jù)結(jié)構(gòu)，近似實(shí)現(xiàn)可累加指標(biāo)的效果。具體實(shí)現(xiàn)過(guò)程方案是對(duì)用戶(hù)ID進(jìn)行編碼，存入BitMap結(jié)構(gòu)中，比如一個(gè)二進(jìn)制位表示一個(gè)用戶(hù)是否存在，消耗1個(gè)Bit。維度統(tǒng)計(jì)上卷時(shí)，再對(duì)BitMap的數(shù)據(jù)結(jié)構(gòu)進(jìn)行合并和計(jì)數(shù)統(tǒng)計(jì)。

HyperLogLog方案。非精確數(shù)據(jù)去重，相對(duì)于Distinct的精確去重，性能提升明顯。

這兩種方案，性能上相對(duì)于普通的Cube計(jì)算有巨大的提升，但BitMap方案需要對(duì)去重統(tǒng)計(jì)用的UID進(jìn)行編碼存儲(chǔ)，對(duì)一般用戶(hù)的理解和實(shí)操成本較高，除非系統(tǒng)級(jí)集成此功能，不然通常需要額外的代碼開(kāi)發(fā)實(shí)現(xiàn)。而HyperLogLog方案的一大弊端就是數(shù)據(jù)的非精確統(tǒng)計(jì)。

審核編輯：黃飛