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

Hive的Join的文档说明地址:

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

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

MySQL

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;

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测试数据

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 specifie

我用的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表的字段的。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏用户2442861的专栏

JdbcTemplate学习笔记(更新插入删除等)

4、使用JdbcTemplate进行查询时,使用queryForXXX()等方法 

2201
来自专栏数据之美

Hive 中的复合数据结构简介以及一些函数的用法说明

目前 hive 支持的复合数据类型有以下几种: map (key1, value1, key2, value2, ...) Creates a map wit...

7375
来自专栏编码小白

ofbiz实体引擎(八) 创建表

/** * @author 郑小康 * * 1.检验实体是否为空 * * 2.检验视图实体是否为空 ...

2813
来自专栏芋道源码1024

Sharding-JDBC 源码分析 —— SQL 解析(二)之SQL解析

1. 概述 2. SQLParsingEngine 3. SQLParser SQL解析器 3.2.1 #parseExpression() 和 SQLExp...

4745
来自专栏绿巨人专栏

TypeScript: Week Reflection

1523
来自专栏绿巨人专栏

TypeScript: Week Reflection

3257
来自专栏c#开发者

Oracle 最常用功能函数经典汇总

Oracle 最常用功能函数经典汇总 SQL中的单记录函数 1.ASCII 返回与指定的字符对应的十进制数; SQL> select ascii(...

4287
来自专栏绿巨人专栏

Database Design Guidelines

1264
来自专栏Hongten

java的poi技术写Excel的Sheet

那么在Excel里面什么叫做Sheet呢?如下图红色框里面的内容就是Excel的Sheet了。

913
来自专栏杂烩

hadoop MapReduce编写一个分组统计并排序查询-分组

说一下需求,有一张销售统计表,记录每个销售员每天的销售情况,现在要统计出某一月的每个销售员的销售情况并且按照销售额从高往低排序(hadoop默认是升序)。

1112

扫码关注云+社区

领取腾讯云代金券