HIVE中MAPJOIN可以使用的場景分析

最近開發中遇到幾種應用，剛好使用MAPJOIN來解決實際的問題。

應用共同點如下：

1: 有一個極小的表<1000行

2: 需要做不等值join操作（a.x < b.y 或者 a.x like b.y等）

這種操作如果直接使用join的話語法不支援不等於操作，hive語法解析會直接丟擲錯誤

如果把不等於寫到where裡會造成笛卡爾積，資料異常增大，速度會很慢。甚至會任務無法跑成功~

根據mapjoin的計算原理，MAPJION會把小表全部讀入記憶體中，在map階段直接拿另外一個表的資料和記憶體中表資料做匹配。這種情況下即使笛卡爾積也不會對任務執行速度造成太大的效率影響。

而且hive的where條件本身就是在map階段進行的操作，所以在where裡寫入不等值比對的話，也不會造成額外負擔。

如此看來，使用MAPJOIN開發的程式僅僅使用map一個過程就可以完成不等值join操作，效率還會有很大的提升。

問題解決~~

示例程式碼如下：

 select /*+ MAPJOIN(a) */

 a.start_level, b.*

 from dim_level a

 join (select * from test) b

 where b.xx>=a.start_level and b.xx<end_level;

3 MAPJOIN 結合 UNIONALL

 

原始sql：

select a.*,coalesce(c.categoryid,’NA’) as app_category

from (select * from t_aa_pvid_ctr_hour_js_mes1

) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

) c

on a.app_id=c.book_id;

 

速度很慢，老辦法，先查下資料分佈。

select *

from

(selectapp_id,count(1) cnt

fromt_aa_pvid_ctr_hour_js_mes1

group by app_id) t

order by cnt DESC

limit 50;

 

資料分佈如下：

NA      617370129

2       118293314

1       40673814

d       20151236

b       1846306

s       1124246

5       675240

8       642231

6       611104

t       596973

4       579473

3       489516

7       475999

9       373395

107580  10508

 

我們可以看到除了NA是有問題的異常值，還有appid=1~9的資料也很多，而這些資料是可以關聯到的，所以這裡不能簡單的隨機函式了。而t_qd_cmfu_book_info_mes這張app庫表，又有幾百萬資料，太大以致不能放入記憶體使用mapjoin。

 

解決方案：

select a.*,coalesce(c.categoryid,’NA’) as app_category

from –if app_id isnot number value or <=9,then not join

(select * fromt_aa_pvid_ctr_hour_js_mes1

where cast(app_id asint)>9

) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

where cast(book_id asint)>9) c

on a.app_id=c.book_id

union all

select /*+ MAPJOIN(c)*/

a.*,coalesce(c.categoryid,’NA’) as app_category

from –if app_id<=9,use map join

(select * fromt_aa_pvid_ctr_hour_js_mes1

wherecoalesce(cast(app_id as int),-999)<=9) a

left outer join

(select * fromt_qd_cmfu_book_info_mes

where cast(book_id asint)<=9) c

–if app_id is notnumber value,then not join

on a.app_id=c.book_id

 

首先將appid=NA和1~9的資料存入一組，並使用mapjoin與維表（維表也限定appid=1~9，這樣記憶體就放得下了）關聯，而除此之外的資料存入另一組，使用普通的join，最後使用union all 放到一起。