Hive 关于JOIN

 一.  inner join/ left join/ right join/ full join/ left semi join/ cross join

 这里主要说一下 left semi join 和 cross join:

1. 左半连接（LEFT SEMI JOIN）

   IN/EXISTS 子查询的一种更高效的实现。

   与JOIN不同的是, 若右表有重复记录, JOIN会出现多条, 而LEFT SEMI JOIN则不会

   

SELECT a.id
      ,a.name
  FROM data_A a
       LEFT SEMI JOIN 
       data_B b
       ON (a.id = b.id)
;

-----------
-- 相当于：
-----------
SELECT a.id
      ,a.name
  FROM data_A a
 WHERE a.id IN (SELECT id FROM data_B)
;

 

2. 笛卡尔连接 （Cross JOIN）

    返回两个表的笛卡尔积结果, 无需指定关联键.

    假设集合A={a, b}，集合B={0, 1, 2}，则两个集合的笛卡尔积为{(a, 0), (a, 1), (a, 2), (b, 0), (b, 1), (b, 2)}

SELECT a.id
      ,a.name
      ,b.age
  FROM data_A a
       CROSS JOIN 
       data_B b;

 

二. Common Join (Reduce Join) & Map Join

1. Common Join (Reduce Join)

    不指定MapJoin或者不符合MapJoin的条件时, Join的默认形式.     在Reduce阶段完成join.   

   Map > Shuffle > Reduce

   

 

 

Map阶段 :      读取源表数据，Map输出时以Join on条件中的列为key (如果Join有多个关联键，则以这些关联键的组合作为key);

                      Map输出的value为join之后select或where中需要用到的列，同时在value中还会包含表的Tag信息，用于标明此value对应哪个表。

Shuffle阶段:   根据key的值进行hash，并将key/value按照hash值推送至不同的reduce中，这样确保两个表中相同的key位于同一个reduce中。 

Reduce阶段:  根据key的值完成join操作，期间通过Tag来识别不同表中的数据。

 

2. Map Join 

    原理&使用场景:

    MapJoin通常用于一个小表和一个大表进行join的场景, 小表可以存放在内存中而不影响性能.

    通常，要连接的各表数据会分布在不同的Map中进行处理  (同一个Key对应的Value在不同的Map中) ,  这样就必须等到Reduce阶段才能去连接.

    要使MapJoin顺利进行，要满足：除了一份表的数据分布在不同的Map中外，其他连接的表的数据必须在每个Map中也有完整的拷贝。

    即把小表文件复制到每一个Map任务的本地，再让Map把文件读到内存中, 直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多.

    小表在Map 阶段它会将自己转化成MapReduce Local Task ，然后从HDFS 取小表的所有数据，将自己转化成Hashtable file 并压缩打包放入DistributedCache 里面.

    如何开启使用:

    Hive提供一个参数 hive.mapjoin.smalltable.filesize=25000000 (即默认25M) , 判断是否符合小表, 来作为开启/关闭MapJoin的阈值.

    满足条件的话, Hive v0.7后的版本可以通过设定参数, 自动转化为MapJoin.    hive> set hive.auto.convert.join=true;

    而Hive v0.7之前版本的, 需要使用hint提示 /*+ mapjoin(table) */ 才会执行MapJoin.

 

在spark的sql中开启mapjoin / BROADCAST

 

-- spark sql
select /*+ BROADCAST(b)*/ a.* from big_table a join small_table b on a.id=b.id

 

    * 注意：Map JOIN也可以用于非等值连接, 但不适合FULL/RIGHT OUTER JOIN

select /*+ mapjoin(a)*/ a.id,b.name
  from data_A a 
       join 
       data_B b
       on a.id=b.id

mapjoin还有一个很大的好处是能够进行不等连接的join操作，如果将不等条件写在where中，那么mapreduce过程中会进行笛卡尔积，运行效率特别低，

  如果使用mapjoin操作，在map的过程中就完成了不等值的join操作，效率会高很多。  

select A.a ,A.b from A join B where A.a>B.a

 

Map Join

1） 大小表连接：

如果一张表的数据很大，另外一张表很少(<1000行)，那么我们可以将数据量少的那张表放到内存里面，在map端做join。
Hive支持Map Join，用法如下

select /*+ MAPJOIN(time_dim) */ count(1) from
store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

 

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

这种操作如果直接使用join的话语法不支持不等于操作，hive语法解析会直接抛出错误
如果把不等于写到where里会造成笛卡尔积，数据异常增大，速度会很慢。甚至会任务无法跑成功~
根据mapjoin的计算原理，MapJoin会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配。这种情况下即使笛卡尔积也不会对任务运行速度造成太大的效率影响。
而且hive的where条件本身就是在map阶段进行的操作，所以在where里写入不等值比对的话，也不会造成额外负担。

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。

解决方：首先将appid=NA和1到9的数据存入一组，并使用mapjoin与维表（维表也限定appid=1~9，这样内存就放得下了）关联，而除此之外的数据存入另一组，使用普通的join，最后使用union all 放到一起。

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
where coalesce(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

 

设置：

当然也可以让hive自动识别，把join变成合适的Map Join如下所示
注：当设置为true的时候，hive会自动获取两张表的数据，判定哪个是小表，然后放在内存中

set hive.auto.convert.join=true;
select count(*) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk)