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 放到一起。