LanguageManualJoins

Join Syntax

Hive支持下面的表join语法结构：

join_table:

table_reference JOIN table_factor[join_condition]

| table_reference {LEFT|RIGHT|FULL} [OUTER]JOIN table_reference join_condition

| table_reference LEFT SEMI JOINtable_reference join_condition

| table_reference CROSS JOIN table_reference[join_condition] (as of Hive 0.10)

?

table_reference:

table_factor

| join_table

?

table_factor:

tbl_name [alias]

| table_subquery alias

| ( table_references )

?

join_condition:

ON equality_expression ( ANDequality_expression )*

?

equality_expression:

expression = expression

注释：在Hive的joins，outerjoins和left semi joins只支持等式连接，不支持不等式连接，因为不等式连接很难转化成map/reduce的job。

?

Version 0.13.0+: Implicit joinnotation（隐式连接符号）

从Hive0.13.0开始支持Implicit join notation，允许from子句去join以逗号分隔的表，省略掉join关键字，如下：

SELECT *
FROM table1t1, table2 t2, table3 t3
WHERE t1.id= t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

?

Version 0.13.0+: Unqualified columnreferences

从Hive0.13.0开始支持非指定字段的引用，如下：

CREATE TABLE a (k1 string, v1 string);
CREATE TABLE b (k2 string, v2 string);

SELECT k1, v1, k2, v2
FROM a JOIN b ON k1 = k2;

如果一个字段在多个表中出现，则Hive会指出它是一个歧义的引用。

Examples

下面有几点关于Hive的join连接重要的地方：

1） 只支持等式join

SELECTa.* FROM a JOIN b ON (a.id = b.id);

SELECTa.* FROM a JOIN b ON (a.id = b.id AND a.department = b.department);

2） 支持多张表join

SELECTa.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =b.key2);

3） 生成一个MRJob：多表连接，如果多个表中每个表都使用同一个列进行连接（出现在JOIN子句中），则只会生成一个MR（map/reduce）Job比如：

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1);

三个表a、b、c都分别使用了同一个字段进行连接，亦即同一个字段同时出现在两个JOIN子句中，从而只生成一个MRJob。

生成多个MRJob：多表连接，如果多表中，其中存在一个表使用了至少2个字段进行连接（同一个表的至少2个列出现在JOIN子句中），则会至少生成2个MRJob，如下的sql将转化为两个map/reduce任务：

SELECTa.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =b.key2);

三个表基于2个字段进行连接，这两个字段b.key1和b.key2同时出现在b表中。连接的过程是这样的：首先a和b表基于a.key和b.key1进行连接，对应着第一个MRJob；表a和b连接的结果，再和c进行连接，对应着第二个MRJob。

4） 表连接顺序优化

多表连接，会转换成多个MRJob，每一个MR Job在Hive中称为JOIN阶段（Stage）。在每一个Stage，按照JOIN顺序中的最后一个表应该尽量是大表，因为JOIN前一阶段生成的数据会存在于Reducer的buffer中，通过stream最后面的表，直接从Reducer的buffer中读取已经缓冲的中间结果数据（这个中间结果数据可能是JOIN顺序中，前面表连接的结果的Key，数据量相对较小，内存开销就小），这样，与后面的大表进行连接时，只需要从buffer中读取缓存的Key，与大表中的指定Key进行连接，速度会更快，也可能避免内存缓冲区溢出。例如：

SELECTa.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key =b.key1);

这个JOIN语句，会生成一个MRJob，在选择JOIN顺序的时候，数据量相比应该是b< c，表a和b基于a.key= b.key1进行连接，得到的结果（基于a和b进行连接的Key）会在Reducer上缓存在buffer中，在与c进行连接时，从buffer中读取Key（a.key=b.key1）来与表c的c.key进行连接。
另外，也可以通过给出一些Hint信息来启发JOIN操作，这指定了将哪个表作为大表，从而得到优化。例如：

SELECT/*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOINc ON (c.key = b.key1);

上述JOIN语句中，a表被视为大表，则首先会对表b和c进行JOIN，然后再将得到的结果与表a进行JOIN。

如果STREAMTABLE省略掉了，那么Hive会在join中streams最右边的表。

5） LEFT，RIGHT和FULLOUTER joins的存在是为了提供更多对on语句中没有匹配的控制。

SELECT a.val, b.val FROM a LEFT OUTER JOIN b ON (a.key=b.key);

6） 基于条件的LEFTOUTER JOIN优化(逻辑同样适合RIGHTand FULL joins)

表的join操作是在where语句之前执行的。

左连接时，左表中出现的字段值都会保留，右表没有连接上的字段值都为空。

例如：

SELECT a.val, b.val FROM a LEFT OUTERJOIN b ON (a.key=b.key)

WHERE a.ds='2015-06-21' ANDb.ds='2015-06-21';

执行顺序为：首先a和b表join，然后结果再通过where条件过滤，这样我们会发现在join过程中可能会输出大量结果，再对这些结果进行过滤操作，比较耗时。

进行优化时，可以将where条件放在on语句中，如下：

SELECT a.val, b.val FROM a LEFT OUTERJOIN b

ON (a.key=b.key AND b.ds='2015-06-21' ANDa.ds='2015-06-21');

?

?

Join 是不能交换位置的。无论是LEFT 还是 RIGHT join，都是左连接的，例如：

?