SQL 百億級數據處理優化 效率/速度 [ 利用暫存表(臨時表)把資料筆數降低,使用虛擬表+子查詢+INNER JOIN ]

SQL 百億級數據處理優化 效率/速度 [ 利用暫存表(臨時表)把資料筆數降低,使用虛擬表+子查詢+INNER JOIN ]

SQL 百億級數據處理優化 效率/速度 [ 利用暫存表(臨時表)把資料筆數降低,使用虛擬表+子查詢+INNER JOIN ] 



資料來源:https://mp.weixin.qq.com/s?__biz=MzI3NDA4OTk1OQ==&mid=2649902730&idx=2&sn=9c6faf81fb65c9a19abad9ac7a8ea332&chksm=f31fbc02c468351472291f5544c409ca1f8bc325489b3823b33ac88495fd275282801da923b0&scene=0&xtrack=1&key=d9588cb87fa8bcaf22c354e29eeb87df548fc508290797ee2dc1d9dd76ed1c682b41908e53c8c437a512990710c658be28da39938fbaa8322c1c0181980fadb818f7d53d4edda626aa0fb757bf13278e&ascene=1&uin=MjIwODk2NDgxNw%3D%3D&devicetype=Windows+10&versiON=62060833&lang=zh_TW&pass_ticket=dbZkB%2F4JLMf4Rs%2B5lHPciWLvs06vLL3mKHHsB5hlpQGZ3JdrcJwgovy%2FN8oPOeqH


user_article_tb 表:

    uid: 用戶ID
    itemid:文章ID
    dur: 閱讀文章時長,如果大於0代表閱讀了文章,等於0代表沒有點擊文章
    dt:每天的日期(EX:2019/0707)
    PS.每天 55 億筆資料

user_profile_tb 表:
    uid: 用戶ID
    gender:性别,F 代表女,M 代表男
    age:年龄
    city:城市
    dt:每天的日期(EX:2019/0707)
    PS.這是一張總表,每天存儲全量用戶畫像屬性,最新數據十億級別

需求是這樣的:計算7天中,女性用戶在每篇文章上的ctr (最終會按照降序進行截斷)。直接寫SQL很容易

--原解 start--
SELECT itemid , count(if(dur > 0, 1, null)) / count(1) ctr
FROM
  (
      SELECT uid, itemid, dur
      FROM user_article_tb
      WHERE dt>='20190701' AND dt<='20190707'
  ) data_tb
  JOIN
  (
    SELECT *
    FROM user_profile_tb
    WHERE dt='20190707' AND gender='F'
  ) profile_tb
  ON data_tb.uid = profile_tb.uid
GROUP BY itemid
ORDER BY ctr DESC
LIMIT 50000;

--原解 end--

那麼問題來了:


對於user_article_tb來說,7天的數據量將近400億條記錄,還需要JOIN一張十億級別的畫像表。這個數據量基本上就跑不出來了


像這種探索性質的需求,經常會變化。假設需求變成計算男性或者計算一二線城市用戶的呢?可能又需要重跑整個數據,既要付出時間成本又要付出高昂的資源成本


我們一一解決上面提到的兩個問題。先考慮第一個,既然JOIN的兩張表太大了,我們能不能嘗試把表變小呢。答案是肯定的,對於畫像表來說顯然是沒辦法縮小了,但是對於user_artitle_tb是可以的。我們可以按照表的分區字段dt用每天的數據分別JOIN畫像表,將結果再按天存儲在一張臨時表裡面。這樣每天就是十億級別的數據JOIN,基本可以解決問題。但是每天的數據仍有多餘的JOIN,比如:某天的數據中uid = 00001的用戶,一天看了1000篇文章,那這個用戶就需要多JOIN 999次。在我們的業務中一個用戶一天看文章的數量> 10是很普遍的,因此多餘JOIN的情況還是比較嚴重的。

針對上面提到的多餘JOIN的情況,最徹底的解決方法就是把user_article_tb表變成uid粒度的,跟畫像表一樣。我們將7天的數據轉換成uid粒度的SQL如下:

INSERT OVERWRITE TABLE user_article_uid_tb AS 
SELECT uid, wm_cONcat(':', cONcat_ws(',', itemid, dur)) item_infos
FROM 
  (
     SELECT *
    FROM user_article_tb
     WHERE dt >= '20190701' and dt <= '20190707'   
  ) tmp
GROUP BY uid


從上面SQL可以看到,我們首先將7天的數據按照uid做group by操作,構造item_infos。因為我們的是計算ctr,所以我們可以按照uid粒度對錶做轉換,並且item_infos字段包含什麼是要根據業務需求做選擇。每天不到1億uid,7天匯總的uid不到10億,兩張uid粒度的表進行JOIN就會快很多。

至此,多餘JOIN的問題得到了解決,再來看看第二個問題。這個問題其實就是我們維度建模理論中所說的寬表,為了避免統計不同維度時頻繁JOIN維表,我們可以在上游數據將常用的維度提前關聯起來,形成一張大寬表。下游數據可以直接用從而減少JOIN。以我們的問題為例,SQL如下:


CREATE table user_profile_article_uid_tb AS
SELECT 
    data_tb.uid
    , item_infos
    , gender
    , age
    , city
  -- 其他维度字段
FROM
  (
      SELECT uid, item_infos
      FROM user_article_uid_tb 
  ) data_tb
  JOIN
  (
      SELECT uid, gender, age, city
    FROM user_profile_tb
    WHERE dt='20190707'
  ) profile_tb
  ON data_tb.uid = profile_tb.uid;

這樣,上面提到的兩個問題就都解決了。最終我們的需求:女性用戶每篇文章的ctr計算如下:

SELECT 
    itemid
    , count(if(dur > 0, 1, null)) / count(1) ctr
FROM 
  (
    SELECT 
      split(item_info, ',')[0] itemid
        , split(item_info, ',')[1] dur
    FROM user_profile_article_uid_tb 
    lateral view explode(split(item_infos, ':')) item_tb as item_info
  ) tmp
GROUP BY itemid
ORDER BY ctr DESC
LIMIT 50000

4 thoughts on “SQL 百億級數據處理優化 效率/速度 [ 利用暫存表(臨時表)把資料筆數降低,使用虛擬表+子查詢+INNER JOIN ]

  1. PS:
    最近在做大數據處理時,遇到兩個大表join導致數據處理太慢(甚至算不出來)的問題。我們的數倉基於阿里的ODPS,它與Hive類似,所以這篇文章也適用於使用Hive優化。處理優化問題,一般是先指定一些常用的優化參數,但是當設置參數仍然不奏效的時候,我們就要結合具體的業務,在SQL上做優化了。為了不增加大家的閱讀負擔,我會簡化這篇文章的業務描述。

    Apache Hive SQL [HiveQL] – https://ithelp.ithome.com.tw/articles/10194547

    阿里的ODPS – ODPS(Open Data Processing Service),是阿里巴巴通用計算平台提供的一種快速、完全託管的GB/TB/PB 級數據倉庫解決方案,現在已更名為MaxCompute,MaxCompute 向用戶提供了完善的數據導入方案以及多種經典的分佈式計算模型,能夠更快速的解決用戶海量數據計算問題,有效降低企業成本,並保障數據安全。

    MaxCompute SQL與標準SQL的主要區別及解決方法 ~ https://yq.aliyun.com/articles/66611

發表迴響

你的電子郵件位址並不會被公開。 必要欄位標記為 *