(1) Hive JOIN语句详解

概  述：hive支持通常的SQL JOIN语句，但是只支持等值连接。

 

为形象说明各种JOIN语句的作用，创建两张表来进行测试，其Hql语句如下：

1 2 3 4 5

CREATE TABLE testA(person_id int, person_name string)  row format delimited fields terminated by '\t' stored as textfile;   CREATE TABLE testB(city_id int, city_name string)  row format delimited fields terminated by '\t' stored as textfile;

其具体的数据如下：

1 2 3 4 5 6

hive> select * from testA; OK 1       zhangsan 2       lisi 3       wangwu Time taken: 0.673 seconds, Fetched: 3 row(s)

 

1 2 3 4 5 6 7 8 9

hive> select * from testB; OK 1       shanghai 1       beijing 2       tianjin 4       qingdao 5       wuxi 6       changzhou Time taken: 0.191 seconds, Fetched: 6 row(s)

 

1 INNER JOIN

INNER JOIN，即内连接，只有进行连接的两个表中都存在与连接相匹配的数据才会被保存下来。

内连接的语句形式如下:

1 2 3 4 5

SELECT 字段名 FROM 表A INNER JOIN 表B ON 表A.字段 = 表B.字段 WHERE 过滤条件

 

举例:

1 2 3 4

SELECT * FROM testA a JOIN testB b ON a.person_id = b.city_id;

 

运行结果：

1 2 3 4 5 6

Total MapReduce CPU Time Spent: 3 seconds 430 msec OK 1       zhangsan        1       shanghai 1       zhangsan        1       beijing 2       lisi    2       tianjin Time taken: 34.496 seconds, Fetched: 3 row(s)

 

从结果中可以看出，只有符合a.person_id = b.city_id的数据被保存了下来。

说明，表A和表B可以换成合法的Hive查询语句，其语句形式如下，并且，该形式也符合外连接的语句形式。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SELECT 字段名 FROM (   SELECT 字段名   FROM 表A   WHERE 过滤条件 )a INNER JOIN ON (   SELECT 字段名   FROM 表B   WHERE 过滤条件 )b ON a.字段名 = b.字段名 WHERE 过滤条件

其中，a和b是查询语句块的别名。

 

2 LEFT OUTER JOIN

左外连接通过关键字LEFT OUTER进行标示。在这种JOIN操作连接中，JOIN操作符左边表中符合WHERE子句的所有记录将会被返回。JOIN操作符右边表中如果没有符合ON后面连接条件的记录时，那么右边表指定选择的列的值将会是NULL。

左外连接的语句形式如下:

1 2 3 4 5

SELECT 字段名 FROM LEFT OUTER JOIN ON WHERE

 

 

1 2 3 4

SELECT * FROM testa a LEFT OUTER JOIN testb b ON a.person_id = b.city_id

 

运行结果:

1 2 3 4 5 6 7

Total MapReduce CPU Time Spent: 2 seconds 870 msec OK 1       zhangsan        1       shanghai 1       zhangsan        1       beijing 2       lisi    2       tianjin 3       wangwu  NULL    NULL Time taken: 34.498 seconds, Fetched: 4 row(s)

 

2 RIGHT OUTER JOIN

右外连接通过RIGHT OUTER 进行标示。这种连接会返回右边表所有符合WHERE语句的记录。左表中匹配不上的字段值用NULL代替。

1 2 3 4

SELECT * FROM testa a RIGHT OUTER JOIN testb b ON a.person_id = b.city_id

 

运行结果:

1 2 3 4 5 6 7 8 9

Total MapReduce CPU Time Spent: 2 seconds 850 msec OK 1       zhangsan        1       shanghai 1       zhangsan        1       beijing 2       lisi    2       tianjin NULL    NULL    4       qingdao NULL    NULL    5       wuxi NULL    NULL    6       changzhou Time taken: 31.169 seconds, Fetched: 6 row(s)

 

3 FULL OUTER JOIN

完全外连接(FULL OUTER JOIN)将会返回所有表中符合WHERE语句条件的所有记录。如果任一表的字段没有符合条件的值的话，那么就使用NULL值代替。

1 2 3 4

SELECT * FROM testa a FULL OUTER JOIN testb b ON a.person_id = b.city_id

 

运行结果:

1 2 3 4 5 6 7 8 9 10

Total MapReduce CPU Time Spent: 8 seconds 80 msec OK 1       zhangsan        1       beijing 1       zhangsan        1       shanghai 2       lisi    2       tianjin 3       wangwu  NULL    NULL NULL    NULL    4       qingdao NULL    NULL    5       wuxi NULL    NULL    6       changzhou Time taken: 35.262 seconds, Fetched: 7 row(s)

 

4 LEFT SEMI-JOIN

    左半开连接(LEFT SEMI-JOIN)会返回左边表的记录，其前提是对于右边表满足ON语句中的判定条件。

首先，在Hive中，是不支持IN...EXISTS结构的，如下语句形式是错误的。

1 2 3 4 5 6 7 8 9

SELECT a.字段1, a.字段2, … , a.字段n FROM 表a WHERE a.字段m IN (   SELECT b.字段m   FROM 表b   WHERE 过滤条件 )

此时，可用LEFT SEMI JOIN 达到同样的目的。其表示形式如下:

1 2 3 4 5

SELECT a.字段1, a.字段2, … , a.字段n FROM表a LEFT SEMI JOIN表b ON a.字段 = b.字段 WHERE 表a的过滤条件

 

举例:

1 2 3 4

SELECT * FROM testa LEFT SEMI JOIN testb ON testa.person_id = testb.city_id

 

运行结果:

1 2 3 4 5

Total MapReduce CPU Time Spent: 3 seconds 750 msec OK 1       zhangsan 2       lisi Time taken: 39.613 seconds, Fetched: 2 row(s)

 

注意，SELECT和WHERE语句中不能引用到右边表中的字段。

SEMI-JOIN 比通常的INNER JOIN要更高效，原因如下:对于左边表中的一条指定的记录，在右边表中一旦找到匹配的记录，Hive就会立即停止扫描。从这点看，左边表中选择的列是可以预测的。