FROM a

JOIN b ON (a.key = b.key)

LEFT OUTER JOIN c ON (a.key = c.key);

?

先join a表和b表，丢掉所有joinkey中不匹配的记录，然后用这一中间结果和c表做join。当一个key在a表和c表都存在，但是b表中不存在的时候：整个记录在第一次join，即ajoin b的时候都被丢掉了(包括a.val1,a.val2和a.key)，然后我们再和c表join的时候，就会得到这样的结果：a.val1,a.val2, b.val, null。

如果使用RIGHT OUTERJOIN代替LEFT，那么我们将得到这样的结果：

NULL,NULL,NULL,c.val

?

示例如下：

hive (hive)> select * from a;

a.id a.name

1 jiangshouzhuang

2 zhangyun

?

hive (hive)> select * from b;

b.id b.name

1 jiangshouzhuang

3 baobao

?

hive (hive)> select * from c;

c.id c.name

2 zhangyun

4 xiaosan

?

hive (hive)> SELECT a.name, b.name, c.name

> FROM a

> JOIN bON (a.id = b.id)

> LEFTOUTER JOIN c ON (a.id = c.id);

jiangshouzhuang jiangshouzhuang NULL

?

hive (hive)> SELECT a.name, b.name, c.name

> FROM a

> JOIN bON (a.id = b.id)

> rightOUTER JOIN c ON (a.id = c.id);

a.name b.name c.name

NULL NULL zhangyun

NULL NULL xiaosan

?

hive (hive)> SELECT a.name, b.name, c.name

> FROM cLEFT OUTER JOIN a ON (c.id = a.id) LEFT OUTER JOIN b

a.name b.name c.name

zhangyun NULL zhangyun

NULL NULL xiaosan

7） 左半连接(leftsemi join)

左半连接可以更加有效地实现了类似in/exists的查询语义，例如：

SELECTa.key, a.value

FROMa

WHEREa.key in

(SELECT b.key FROM B);

可以用下面的语句替换：

SELECTa.key, a.val

FROMa LEFT SEMI JOIN b ON (a.key = b.key);

需要注意的是，在leftsemi join中，b表只能出现在on子句的后面，不能出现在select和where子句中。

关于子查询，Hive支持情况如下：

· 在0.12版本，只支持FROM子句中的子查询；

· 在0.13版本，也支持WHERE子句中的子查询；

· 在0.13不包，IN/NOTIN/EXISTS/NOT EXISTS支持使用子查询。

8） MapSide Join

MapSide Join优化的出发点是，Map任务的输出，不需要将数据拷贝到Reduce节点，从而降低了数据在网络节点之间传输的开销。

对于多表连接，如果只有一个表比较大，其他表都很小，则join操作会转化为一个只包含Map的job任务，例如：

SELECT/*+ MAPJOIN(b) */ a.key, a.value

FROMa JOIN b ON a.key = b.key;

对于a表数据的每一个map，都很够完全读取b表的数据。

注意：这里，表a和表b不允许执行FULL/RIGHTOUTER JOIN操作。

?

补充：

Hive内置提供的优化机制之一就包括MapJoin:

在Hivev0.7之前，需要给出MapJoin的指示(hint)，Hive才会提供MapJoin的优化。

Hivev0.7之后的版本已经不需要给出MapJoin的指示就进行优化。

它是通过如下配置参数来控制的：

hive>set hive.auto.convert.join=true;

hive0.11之后，在表的大小符合设置时

hive.auto.convert.join.noconditionaltask=true

hive.auto.convert.join.noconditionaltask.size=10000000

hive.mapjoin.smalltable.filesize=25000000

默认会把join转换为mapjoin(hive.ignore.mapjoin.hint=true,hive.auto.convert.join=true)

Hivev0.12.0版本，缺省状况下MapJoin优化是打开的。

也就是hive.auto.convert.join=true。

Hive还提供另外一个参数--表文件的大小作为开启和关闭MapJoin的阈值。

hive.mapjoin.smalltable.filesize=25000000

?

9） BucketMap Side Join

如果表进行join，同时join的列也是bucket列，并且一张表的bucket数是另外一张表的bucket数的倍数，那么表之间的buckets可以进行join。

如果表A有4个buckets，表B也有4个buckets，那么下面的join

SELECT/*+ MAPJOIN(b) */ a.key, a.value

FROMa JOIN b ON a.key = b.key;

只需在mapper阶段完成。默认情况下，对于表a的每一个bucket，都会去获取表b中的每一个bucket来进行join，这回造成一定的开销，因为只有表b中满足join条件的bucket才会真正与表a的bucket进行连接。

可以设置如下参数进行优化：

sethive.optimize.bucketmapjoin=true;

这样，join的过程是：表b的bucket1只会与表b的bucket1进行join，而不再考虑表b中的其他bucket2~4。

?

示例：

创建表a：

CREATE TABLE a(key INT, value STRING)

CLUSTERED BY(key) INTO 6 BUCKETS

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

STORED AS SEQUENCEFILE;

创建表b：

CREATE TABLE b(key INT, value STRING)

CLUSTERED BY(key) INTO 36 BUCKETS

ROW FORMAT DELIMITED

FIELDS TERMINATED BY '\001'

COLLECTION ITEMS TERMINATED BY '\002'

MAP KEYS TERMINATED BY '\003'

STORED AS SEQUENCEFILE;