Hive的left join、left outer join和left semi join三者的区别

爱被打了一巴掌 2022-08-07 10:55 243阅读 0赞

# Hive的left join、left outer join和left semi join三者的区别 #

Hive的Join的文档说明地址：

https://cwiki.apache.org/confluence/display/Hive/LanguageManual%2BJoins

以下为两个测试数据表建表语句：

use test;
    
    DROP TABLE IF EXISTS table1;
    create table table1( student_no bigint comment '学号', student_name string comment '姓名' ) COMMENT 'test 学生信息' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;
    
    
    DROP TABLE IF EXISTS table2;
    create table table2( student_no bigint comment '学号', class_no bigint comment '课程号' ) COMMENT 'test 学生选课信息' ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' STORED AS TEXTFILE;
    
    load data local inpath 'data_table1.txt' overwrite into table table1;
    load data local inpath 'data_table2.txt' overwrite into table table2;

<table style="max-width:100%; background-color:transparent; border-collapse:collapse; border-spacing:0px; font-size:14px; line-height:1.3em; width:606px"> 
 <tbody> 
 <tr style="line-height:1.3em"> 
 <td style="font-size:14px; line-height:1.3em"> 
 <div style="font-size:16px; line-height:1.7em"> 
 use&nbsp;test; 
 DROP&nbsp;TABLE&nbsp;IF EXISTS&nbsp;table1; 
 create&nbsp;table&nbsp;table1( 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;student_no&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bigint&nbsp;&nbsp;&nbsp;&nbsp;comment&nbsp;'学号'&nbsp;, 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;student_name&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;string&nbsp;&nbsp;&nbsp;&nbsp;comment&nbsp;'姓名' 
 ) 
 COMMENT&nbsp;'test 学生信息' 
 ROW&nbsp;FORMAT&nbsp;DELIMITED 
 FIELDS&nbsp;TERMINATED BY&nbsp;'\t' 
 LINES&nbsp;TERMINATED BY&nbsp;'\n' 
 STORED&nbsp;AS&nbsp;TEXTFILE; 
 DROP&nbsp;TABLE&nbsp;IF EXISTS&nbsp;table2; 
 create&nbsp;table&nbsp;table2( 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;student_no&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bigint&nbsp;&nbsp;&nbsp;&nbsp;comment&nbsp;'学号'&nbsp;, 
 &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;class_no&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;bigint&nbsp;&nbsp;&nbsp;&nbsp;comment&nbsp;'课程号' 
 ) 
 COMMENT&nbsp;'test 学生选课信息' 
 ROW&nbsp;FORMAT&nbsp;DELIMITED 
 FIELDS&nbsp;TERMINATED BY&nbsp;'\t' 
 LINES&nbsp;TERMINATED BY&nbsp;'\n' 
 STORED&nbsp;AS&nbsp;TEXTFILE; 
 load data&nbsp;local&nbsp;inpath&nbsp;'data_table1.txt'&nbsp;overwrite&nbsp;into&nbsp;table&nbsp;table1; 
 load data&nbsp;local&nbsp;inpath&nbsp;'data_table2.txt'&nbsp;overwrite&nbsp;into&nbsp;table&nbsp;table2; 
 </div> </td> 
 </tr> 
 </tbody> 
</table>

测试数据为：

![hive left join测试数据][hive left join]

hive left join测试数据

#### 测试1：left join ####

语句：

select \* from table1 left outer join table2 on(table1.student\_no=table2.student\_no);

结果：

FAILED: Parse Error: line 1:22 cannot recognize input near ‘left’ ‘join’ ‘table2′ in join type specifier

我用的HIVE版本是0.8，不支持直接的left join写法；

#### 测试2：left outer join ####

语句：

select \* from table1 left outer join table2 on(table1.student\_no=table2.student\_no);

结果：

1 name1 1 11

1 name1 1 12

1 name1 1 13

2 name2 2 11

2 name2 2 14

3 name3 3 15

3 name3 3 12

4 name4 4 13

4 name4 4 12

5 name5 5 14

5 name5 5 16

6 name6 NULL NULL

可以看到left outer join左边表的数据都列出来了，如果右边表没有对应的列，则写成了NULL值。

同时注意到，如果左边的主键在右边找到了N条，那么结果也是会叉乘得到N条的，比如这里主键为1的显示了右边的3条。

#### 测试3：left semi join ####

语句：

select \* from table1 left semi join table2 on(table1.student\_no=table2.student\_no);

结果：

1 name1

2 name2

3 name3

4 name4

5 name5

可以看到，只打印出了左边的表中的列，规律是如果主键在右边表中存在，则打印，否则过滤掉了。

结论：

*  hive不支持’left join’的写法；
 *  hive的left outer join：如果右边有多行和左边表对应，就每一行都映射输出；如果右边没有行与左边行对应，就输出左边行，右边表字段为NULL；
 *  hive的left semi join：相当于SQL的in语句，比如上面测试3的语句相当于“select \* from table1 where table1.student\_no in (table2.student\_no)”，注意，结果中是没有B表的字段的。

本文地址：http://www.crazyant.net/1470.html

[hive left join]: /images/20220805/0620f823d68b443c97ba804f3be2910d.png