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

引言

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

一、場景描述

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

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

存在一個情況，某用戶早上在杭州市使用支付寶支付了一次，下午跑到紹興市時又使用支付寶線下支付了一次。那么在統(tǒng)計省+市維度的日支付用戶數(shù)，需要為杭州市、紹興市各計1；但在省維度下，需要按用戶去重，只能為浙江省計1。針對這種情況，通常就需要以Cube的方式完成數(shù)據(jù)預(yù)計算，同時每個維度組合都需要進(jìn)行去重操作，因為不可累加。本文將此種場景簡稱為去重Cube。

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

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

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

三、性能分析

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

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

四、一種新的思路

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

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

4.1.核心思路

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

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

4.2.邏輯實現(xiàn)

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

整體方案流程如下圖。

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

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

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

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

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

Over～

4.3.代碼實現(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)計
        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回寫到明細(xì)記錄上,并生成UID粒度的中間過程數(shù)據(jù)
,detail_ext AS (
  -- Step04:指標(biāo)統(tǒng)計
  SELECT   user_id
          ,ARRAY_DISTINCT(SPLIT(WM_CONCAT(';',cube_ids),';')) AS cube_id_arry
  FROM (
    -- Step03:CubeID回寫明細(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
;

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

4.4.實驗效果

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

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

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

4.5.方案總結(jié)

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

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

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

五、其他方案

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

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

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

審核編輯：黃飛