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

Hive的Join的文档说明地址：
https://cwiki.apache.org/conflue ... guageManual%2BJoins
以下为两个测试数据表建表语句：
use test;DROP TABLE IF EXISTS table1;create table table1(    student_no      bigint  comment '学号',    student_name    string  comment '姓名')COMMENT 'test 学生信息'ROW FORMAT DELIMITEDFIELDS 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 DELIMITEDFIELDS 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;

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;

测试数据为：

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

再来个 left semi join 例子：
SELECT
        产品类型,
        COUNT(DISTINCT t1.用户ID) AS KEEP_UV
FROM
(
        SELECT
                产品类型,
                用户ID
        FROM 事实表
        WHERE (`DATE` >= 20140201 AND `DATE` <= 20140228)
) t1
LEFT SEMI JOIN
(
        SELECT
                产品类型,
                用户ID
        FROM 事实表
        WHERE (`DATE` >= 20140101 AND `DATE` <= 20140131)
) t2 ON (t1.产品类型 = t2.产品类型 AND t1.用户ID = t2.用户ID)
GROUP BY 产品类型

0.12版本的hive可以用left jion

学习了。。。。。。。。。mark

谢谢楼主分享