hive 中的map join 操作

今天遇到一个Hive的问题，如下hive sql：

select f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802) 

该语句中B表有30亿行记录，A表只有100行记录，而且B表中数据倾斜特别严重，有一个key上有15亿行记录，在运行过程中特别的慢，而且在reduece的过程中遇有内存不够而报错。

为了解决用户的这个问题，考虑使用mapjoin,mapjoin的原理：

MAPJION会把小表全部读入内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map是进行了join操作，省去了reduce运行的效率也会高很多

这样就不会由于数据倾斜导致某个reduce上落数据太多而失败。于是原来的sql可以通过使用hint的方式指定join时使用mapjoin。

select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)

再运行发现执行的效率比以前的写法高了好多。

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

例子：

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

简单总结一下，mapjoin的使用场景：

1. 关联操作中有一张表非常小

2.不等值的链接操作

HIVE中MAP JOIN的原理机制

Hive中的Join可分为Common Join（Reduce阶段完成join）和Map Join（Map阶段完成join）。

Hive Common Join
如果不指定MapJoin或者不符合MapJoin的条件，那么Hive解析器会默认把执行Common 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来识别不同表中的数据。

以下面的HQL为例，图解其过程：

?

1 2 3

SELECT a.id,a.dept,b.age FROM a join b ON (a.id = b.id);

Hive Map Join
MapJoin通常用于一个很小的表和一个大表进行join的场景，具体小表有多小，由参数hive.mapjoin.smalltable.filesize来决定，默认值为25M。满足条件的话Hive在执行时候会自动转化为MapJoin，或使用hint提示 /*+ mapjoin(table) */执行MapJoin。

如上图中的流程，首先Task A在客户端本地执行，负责扫描小表b的数据，将其转换成一个HashTable的数据结构，并写入本地的文件中，之后将该文件加载到DistributeCache中。
接下来的Task B任务是一个没有Reduce的MapReduce，启动MapTasks扫描大表a，在Map阶段，根据a的每一条记录去和DistributeCache中b表对应的HashTable关联，并直接输出结果，因为没有Reduce，所以有多少个Map Task，就有多少个结果文件。
注意：Map JOIN不适合FULL/RIGHT OUTER JOIN。

二，join的几种类型

Hive中除了支持和传统数据库中一样的内关联（JOIN）、左关联（LEFT JOIN）、右关联（RIGHT JOIN）、全关联（FULL JOIN），还支持左半关联（LEFT SEMI JOIN）。
注意：Hive中Join的关联键必须在ON()中指定，不能在Where中指定。
数据准备：

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

hive> desc lxw1234_a; OK id                      string                                      name                    string                                      Time taken: 0.094 seconds, Fetched: 2 row(s) hive> select * from lxw1234_a; OK 1       zhangsan 2       lisi 3       wangwu Time taken: 0.116 seconds, Fetched: 3 row(s) hive> desc lxw1234_b; OK id                      string                                      age                     int                                         Time taken: 0.159 seconds, Fetched: 2 row(s) hive> select * from lxw1234_b; OK 1       30 2       29 4       21 Time taken: 0.09 seconds, Fetched: 3 row(s)

内关联（JOIN）
常规join，类似交集，只显示关联成功的行。

?

1 2 3 4 5 6 7

SELECT a.id, a. name , b.age FROM lxw1234_a a join lxw1234_b b ON (a.id = b.id); --执行结果 1       zhangsan        30 2       lisi        29

左外关联（LEFT [OUTER] JOIN）
以LEFT [OUTER] JOIN关键字前面的表作为主表，和其他表进行关联，返回记录和主表的记录数一致，关联不上的字段置为NULL。
是否指定OUTER关键字，貌似对查询结果无影响。

?

1 2 3 4 5 6 7 8

SELECT a.id, a. name , b.age FROM lxw1234_a a left join lxw1234_b b ON (a.id = b.id); --执行结果： 1   zhangsan   30 2   lisi        29 3   wangwu    NULL

右外关联（RIGHT [OUTER] JOIN）
和左外关联相反，以RIGTH [OUTER] JOIN关键词后面的表作为主表，和前面的表做关联，返回记录数和主表一致，关联不上的字段为NULL。
是否指定OUTER关键字，貌似对查询结果无影响。

?

1 2 3 4 5 6 7 8

SELECT a.id, a. name , b.age FROM lxw1234_a a RIGHT OUTER JOIN lxw1234_b b ON (a.id = b.id); --执行结果： 1          zhangsan        30 2          lisi        29 NULL       NULL        21

全外关联（FULL [OUTER] JOIN）
以两个表的记录为基准，返回两个表的所有记录，类似并集，关联不上的字段为NULL。
是否指定OUTER关键字，貌似对查询结果无影响。

?

1 2 3 4 5 6 7 8 9

SELECT a.id, a. name , b.age FROM lxw1234_a a FULL OUTER JOIN lxw1234_b b ON (a.id = b.id); --执行结果： 1       zhangsan            30 2       lisi            29 3       wangwu          NULL NULL    NULL            21

左半连接（LEFT SEMI JOIN）
以LEFT SEMI JOIN关键字前面的表为主表，返回主表的KEY也在副表中的记录。相当于IN或EXISTS的作用。左半连接的限制是右侧的表只能出现在ON子句中，不能出现在WHERE或者SELECT子句中。
注意，left semi join和join并不是等价的，比如当左表1条数据可以关联右表2条数据时，此时left semi join只显示1条数据（因为只要左表的key在右表存在就行，不用管几条），join却显示两条数据（关联到的都显示），好好体会一下。

?

1 2 3 4 5 6 7 8 9 10 11

SELECT a.id, a. name FROM lxw1234_a a LEFT SEMI JOIN lxw1234_b b ON (a.id = b.id); --执行结果： 1       zhangsan 2       lisi --等价于： SELECT a.id, a. name FROM lxw1234_a a WHERE a.id IN ( SELECT id FROM lxw1234_b);

三，join语句需要注意的地方

首先是Hive中的连接查询只支持相等连接而不支持不等连接查询：

?

1 2 3 4

//有效的连接查询，相等连接查询  SELECT a.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)  //无效的连接查询，Hive不支持不等连接查询  SELECT a.* FROM a JOIN b ON (a.id <> b.id)

如果每个表都只使用相同的列join连接，Hive将只生成一个map/reduce作业；如果一个表使用了两个以上的字段，则会生成2个以上的mr任务：

?

1 2 3 4 5

//由于 join 子句中只使用了表b的key1列，该查询转换为一个作业  SELECT a.val, b.val, c.val FROM a JOIN b ON (a. key = b.key1) JOIN c ON (c. key = b.key1)  //由于表b的key1列用在第一个 join 子句中，key2列用在第二个 join 子句中，该查询被转换为两个作业， //第一个作业执行表a和b的连接查询，第二个作业将第一个作业的结果与第二个 join 子句进行连接查询  SELECT a.val, b.val, c.val FROM a JOIN b ON (a. key = b.key1) JOIN c ON (c. key = b.key2)

Join连接的顺序是不可以交换的，无论是LEFT还是RIGHT连接都是左结合的。