歡迎光臨
每天分享高質量文章

百億級資料處理優化

作者: 渡碼

鏈接:https://www.cnblogs.com/duma/p/11186279.html

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

問題

這是一個離線資料處理的問題。在這個業務中有兩張表,表結構及說明如下:

 

user_article_tb 表:

欄位解釋:


 uid: 用戶標識,itemid:文章id,dur: 閱讀文章時長,如果大於 0 代表閱讀了文章,等於 0 代表沒有點擊文章 
 dt:天分割槽,每天 55 億條記錄


user_profile_tb 表

欄位解釋:

uid:用戶標識,gender:性別,F 代表女,M 代表男,age:年齡,city:城市
 dt:天分割槽欄位,這是一張總表,每天儲存全量用戶畫像屬性,最新資料十億級別

 

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

 

select 
  itemid
  , count(if(dur > 01null)) / 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
;

 

 

那麼問題來了:

 

  • 對於 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 > 01null)) / 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 itemid
order by ctr desc
limit 50000

 

引數優化

mapreduce.map.memory.mb
mapreduce.reduce.memory.mb
mapred.reduce.tasks

這些引數設置是比較通用的選項, 當這些選項不能夠達到最優的效果時,需要從業務上進行優化。

小結

這篇文章主要介紹了在 ODPS 或 Hive 上,百億級資料規模的 join 優化。核心思想就是減少 join 的資料量,同時優化沒有放之四海而皆準的方法,一定是結合業務進行的。

已同步到看一看
赞(0)

分享創造快樂