百億級資料處理最佳化

作者： 渡碼

連結：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 > 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
;

 

 

那麼問題來了：

 

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

 

引數最佳化

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

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

小結

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