AI 助理
备案控制台
开发者社区 开发与运维 文章 正文

Hive的JOIN连接

2024-09-13 15
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： Hive的JOIN连接

1，现有数据

现在有两个表scores和part_student。

代码语言：javascript

复制

hive (demo)> select * from scores;
OK
scores.student_id scores.scores
1 {"Chinese":91,"mathematics":100,"Physics":87,"Chemistry":97,"English":93}
2 {"Chinese":62,"mathematics":93,"Physics":77,"Chemistry":96,"English":97}
3 {"Chinese":43,"mathematics":73,"Physics":87,"Chemistry":66,"English":97}
4 {"Chinese":97,"mathematics":97,"Physics":63,"Chemistry":97,"English":96}
5 {"Chinese":96,"mathematics":99,"Physics":87,"Chemistry":99,"English":95}
6 {"Chinese":93,"mathematics":67,"Physics":89,"Chemistry":95,"English":84}
7 {"Chinese":96,"mathematics":53,"Physics":86,"Chemistry":80,"English":92}
8 {"Chinese":87,"mathematics":71,"Physics":84,"Chemistry":76,"English":93}
9 {"Chinese":77,"mathematics":73,"Physics":93,"Chemistry":91,"English":91}
10 {"Chinese":94,"mathematics":81,"Physics":81,"Chemistry":93,"English":90}
11 {"Chinese":91,"mathematics":90,"Physics":87,"Chemistry":97,"English":93}
12 {"Chinese":97,"mathematics":84,"Physics":87,"Chemistry":78,"English":90}
13 {"Chinese":92,"mathematics":83,"Physics":87,"Chemistry":96,"English":97}
14 {"Chinese":97,"mathematics":97,"Physics":63,"Chemistry":97,"English":96}
15 {"Chinese":96,"mathematics":99,"Physics":87,"Chemistry":99,"English":95}
16 {"Chinese":93,"mathematics":67,"Physics":89,"Chemistry":95,"English":94}
17 {"Chinese":96,"mathematics":83,"Physics":86,"Chemistry":90,"English":92}
18 {"Chinese":87,"mathematics":71,"Physics":84,"Chemistry":76,"English":93}
19 {"Chinese":87,"mathematics":73,"Physics":83,"Chemistry":91,"English":91}
20 {"Chinese":84,"mathematics":71,"Physics":81,"Chemistry":93,"English":90}
21 {"Chinese":91,"mathematics":100,"Physics":87,"Chemistry":97,"English":93}
22 {"Chinese":87,"mathematics":94,"Physics":87,"Chemistry":78,"English":90}
23 {"Chinese":92,"mathematics":93,"Physics":77,"Chemistry":86,"English":97}
24 {"Chinese":97,"mathematics":87,"Physics":83,"Chemistry":87,"English":86}
25 {"Chinese":96,"mathematics":99,"Physics":87,"Chemistry":99,"English":95}
26 {"Chinese":93,"mathematics":87,"Physics":89,"Chemistry":95,"English":84}
27 {"Chinese":86,"mathematics":53,"Physics":86,"Chemistry":90,"English":92}
28 {"Chinese":87,"mathematics":71,"Physics":84,"Chemistry":76,"English":93}
29 {"Chinese":87,"mathematics":73,"Physics":83,"Chemistry":91,"English":91}
30 {"Chinese":94,"mathematics":71,"Physics":81,"Chemistry":93,"English":90}
31 {"Chinese":98,"mathematics":83,"Physics":93,"Chemistry":91,"English":81}
32 {"Chinese":84,"mathematics":81,"Physics":91,"Chemistry":93,"English":90}
Time taken: 0.087 seconds, Fetched: 32 row(s)

代码语言：javascript

复制

hive (demo)> select * from part_student;
OK
part_student.id part_student.info  part_student.year part_student.month       part_student.day
1 {"name":"Jerry","age":"19"}      2022      2     1
2 {"name":"Tom","age":"19"}        2022      2     1
3 {"name":"Peter","age":"19"}      2022      2     1
4 {"name":"Jessca","age":"19"}     2022      2     1
5 {"name":"White","age":"18"}      2022      2     1
6 {"name":"Terry","age":"19"}      2022      9     1
7 {"name":"Zhi","age":"19"}        2022      9     1
8 {"name":"Cindy","age":"19"}      2022      9     1
9 {"name":"Smith","age":"19"}      2022      9     1
10 {"name":"Toy","age":"19"}       2022      9     1
11 {"name":"Kerry","age":"19"}     2023      2     1
12 {"name":"Linda","age":"19"}     2023      2     1
13 {"name":"Susan","age":"19"}     2023      2     1
14 {"name":"Yao","age":"19"}       2023      2     1
15 {"name":"Jack","age":"19"}      2023      2     1
16 {"name":"Mokey","age":"20"}     2023      9     1
17 {"name":"Mouse","age":"20"}     2023      9     1
18 {"name":"Cat","age":"20"}       2023      9     1
19 {"name":"Dog","age":"20"}       2023      9     1
20 {"name":"Snack","age":"20"}     2023      9     1
21 {"name":"Qian","age":"20"}      2024      2     1
22 {"name":"Yong","age":"20"}      2024      2     1
23 {"name":"Xiang","age":"20"}     2024      2     1
24 {"name":"Kun","age":"20"}       2024      2     1
25 {"name":"Hunter","age":"20"}    2024      2     1
26 {"name":"Qian","age":"20"}      2024      9     1
27 {"name":"Yong","age":"20"}      2024      9     1
28 {"name":"Xiang","age":"20"}     2024      9     1
29 {"name":"Kun","age":"20"}       2024      9     1
30 {"name":"Hunter","age":"20"}    2024      9     1
36 {"name":"Harry","age":"19"}     2025      2     1
37 {"name":"Jeckson","age":"19"}   2025      2     1
38 {"name":"JiM","age":"19"}       2025      2     1
39 {"name":"White","age":"18"}     2025      2     1
40 {"name":"BaiDEn","age":"18"}    2025      2     1
Time taken: 0.091 seconds, Fetched: 35row(s)

2，Hive的JOIN连接语法

Hive JOIN的语法如下：

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

代码语言：javascript

复制

hive> SELECT * FROM teacher t INNER JOIN
course c ON t.t_id = c.t_id; -- inner可省略

LEFT OUTER JOIN左外连接：左边所有数据会被返回，右边符合条件的被返回

代码语言：javascript

复制

hive> SELECT * FROM teacher t LEFT JOIN
course c ON t.t_id = c.t_id; -- outer可省略

RIGHT OUTER JOIN右外连接：右边所有数据会被返回，左边符合条件的被返回

代码语言：javascript

复制

hive> SELECT * FROM teacher t RIGHT JOIN
course c ON t.t_id = c.t_id;

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

代码语言：javascript

复制

SELECT * FROM teacher t FULL JOIN course c
ON t.t_id = c.t_id ;

注意：

  • Hive2版本已经支持不等值连接，就是 JOIN ON条件后面可以使用大于小于符号了;并且也支持 JOIN ON 条件后跟or (早前版本 ON 后只支持 = 和 and，不支持 > < 和OR)
  • 如hive执行引擎使用MapReduce，一个JOIN就会启动一个job，一条SQL语句中如有多个JOIN，则会启动多个job。

另外

表之间用逗号(,)连接和 INNER JOIN 是一样的

代码语言：javascript

复制

hive> SELECT * FROM table_a,table_b
WHERE table_a.id=table_b.id;

等同于

代码语言：javascript

复制

hive> SELECT * FROM table_a INNER JOIN table_b
ON table_a.id=table_b.id;

它们的执行效率没有区别，只是书写方式不同，用逗号是SQL 89标准，JOIN 是SQL 92标准。用逗号连接后面过滤条件用 WHERE ，用 JOIN 连接后面过滤条件是ON。

3，实例

1）[INNER] JOIN…ON

代码语言：javascript

复制

hive> SELECT sc.student_id,
get_json_object(st.info, '$.name') AS name,  
get_json_object(st.info, '$.age') AS age,
get_json_object(sc.scores, '$.Chinese') AS Chinese,  
get_json_object(sc.scores, '$.mathematics') AS mathematics,  
get_json_object(sc.scores, '$.Physics') AS Physics,  
get_json_object(sc.scores, '$.Chemistry') AS Chemistry,  
get_json_object(sc.scores, '$.English') AS English
FROM scores sc JOIN part_student st ON
sc.student_id=st.id;
OK
sc.student_id name age chinese   mathematics physics   chemistry      english
1 Jerry            19   91   100 87   97   93
2 Tom              19   62   93   77   96   97
3 Peter            19   43   73   87   66   97
4 Jessca           19   97   97   63   97   96
5 White            18   96   99   87   99   95
6 Terry            19   93   67   89   95   84
7 Zhi              19   96   53   86   80   92
8 Cindy            19   87   71   84   76   93
9 Smith            19   77   73   93   91   91
10 Toy             19   94   81   81   93   90
11 Kerry           19   91   90   87   97   93
12 Linda           19   97   84   87   78   90
13 Susan           19   92   83   87   96   97
14 Yao             19   97   97   63   97   96
15 Jack            19   96   99   87   99   95
16 Mokey           20   93   67   89   95   94
17 Mouse           20   96   83   86   90   92
18 Cat             20   87   71   84   76   93
19 Dog             20   87   73   83   91   91
20 Snack           20   84   71   81   93   90
21 Qian            20   91   100  87   97   93
22 Yong            20   87   94   87   78   90
23 Xiang           20   92   93   77   86   97
24 Kun             20   97   87   83   87   86
25 Hunter          20   96   99   87   99   95
26 Qian            20   93   87   89   95   84
27 Yong            20   86   53   86   90   92
28 Xiang           20   87   71   84   76   93
29 Kun             20   87   73   83   91   91
30 Hunter          20   94   71   81   93   90
Time taken: 9.961 seconds, Fetched: 30
row(s)

2) LEFT [OUTER] JOIN…ON

代码语言：javascript

复制

hive> SELECT sc.student_id,
get_json_object(st.info, '$.name') AS name,  
get_json_object(st.info, '$.age') AS age,
get_json_object(sc.scores, '$.Chinese') AS Chinese,  
get_json_object(sc.scores, '$.mathematics') AS mathematics,  
get_json_object(sc.scores, '$.Physics') AS Physics,  
get_json_object(sc.scores, '$.Chemistry') AS Chemistry,  
get_json_object(sc.scores, '$.English') AS English
FROM scores sc LEFT JOIN part_student st ON
sc.student_id=st.id;
sc.student_id name age chinese   mathematics physics   chemistry      english
1 Jerry       19   91   100  87   97   93
2 Tom         19   62   93   77   96   97
3 Peter       19   43   73   87   66   97
4 Jessca      19   97   97   63   97   96
5 White       18   96   99   87   99   95
6 Terry       19   93   67   89   95   84
7 Zhi         19   96   53   86   80   92
8 Cindy       19   87   71   84   76   93
9 Smith       19   77   73   93   91   91
10 Toy        19   94   81   81   93   90
11 Kerry      19   91   90   87   97   93
12 Linda      19   97   84   87   78   90
13 Susan      19   92   83   87   96   97
14 Yao        19   97   97   63   97   96
15 Jack       19   96   99   87   99   95
16 Mokey      20   93   67   89   95   94
17 Mouse      20   96   83   86   90   92
18 Cat        20   87   71   84   76   93
19 Dog        20   87   73   83   91   91
20 Snack      20   84   71   81   93   90
21 Qian       20   91   100  87   97   93
22 Yong       20   87   94   87   78   90
23 Xiang      20   92   93   77   86   97
24 Kun        20   97   87   83   87   86
25 Hunter     20   96   99   87   99   95
26 Qian       20   93   87   89   95   84
27 Yong       20   86   53   86   90   92
28 Xiang      20   87   71   84   76   93
29 Kun        20   87   73   83   91   91
30 Hunter     20   94   71   81   93   90
31 NULL       NULL 98   83   93   91   81
32 NULL       NULL 84   81   91   93   90

显示左表scores sc所有记录，右表part_student st没有的记录以NULL表示

3）RIGHT [OUTER] JOIN…ON

代码语言：javascript

复制

hive> SELECT sc.student_id,
get_json_object(st.info, '$.name') AS name,  
get_json_object(st.info, '$.age') AS age,
get_json_object(sc.scores, '$.Chinese') AS Chinese,  
get_json_object(sc.scores, '$.mathematics') AS mathematics,  
get_json_object(sc.scores, '$.Physics') AS Physics,  
get_json_object(sc.scores, '$.Chemistry') AS Chemistry,  
get_json_object(sc.scores, '$.English') AS English
FROM scores sc RIGHT JOIN part_student st
ON sc.student_id=st.id;
.student_id name     age chinese   mathematics physics   chemistry      english
1 Jerry         19   91   100 87   97   93
2 Tom           19   62   93   77   96   97
3 Peter         19   43   73   87   66   97
4 Jessca        19   97   97   63   97   96
5 White         18   96   99   87   99   95
6 Terry         19   93   67   89   95   84
7 Zhi           19   96   53   86   80   92
8 Cindy         19   87   71   84   76   93
9 Smith         19   77   73   93   91   91
10 Toy          19   94   81   81   93   90
11 Kerry        19   91   90   87   97   93
12 Linda        19   97   84   87   78   90
13 Susan        19   92   83   87   96   97
14 Yao          19   97   97   63   97   96
15 Jack         19   96   99   87   99   95
16 Mokey        20   93   67   89   95   94
17 Mouse        20   96   83   86   90   92
18 Cat          20   87   71   84   76   93
19 Dog          20   87   73   83   91   91
20 Snack        20   84   71   81   93   90
21 Qian         20   91   100 87   97   93
22 Yong         20   87   94   87   78   90
23 Xiang        20   92   93   77   86   97
24 Kun          20   97   87   83   87   86
25 Hunter       20   96   99   87   99   95
26 Qian         20   93   87   89   95   84
27 Yong         20   86   53   86   90   92
28 Xiang        20   87   71   84   76   93
29 Kun          20   87   73   83   91   91
30 Hunter       20   94   71   81   93   90
NULL Harry      19   NULL      NULL      NULL      NULL      NULL
NULL Jeckson    19   NULL      NULL      NULL      NULL      NULL
NULL JiM        19   NULL      NULL      NULL      NULL      NULL
NULL White      18   NULL      NULL      NULL      NULL      NULL
NULL BaiDEn     18   NULL      NULL      NULL      NULL      NULL
Time taken: 8.651 seconds, Fetched: 35
row(s)

显示右表part_student st所有记录，左表scores sc没有的记录以NULL表示。

4）FULL [OUTER] JOIN…ON

代码语言：javascript

复制

hive> SELECT sc.student_id,
get_json_object(st.info, '$.name') AS name,  
get_json_object(st.info, '$.age') AS age,
get_json_object(sc.scores, '$.Chinese') AS Chinese,  
get_json_object(sc.scores, '$.mathematics') AS mathematics,  
get_json_object(sc.scores, '$.Physics') AS Physics,  
get_json_object(sc.scores, '$.Chemistry') AS Chemistry,  
get_json_object(sc.scores, '$.English') AS English
FROM scores sc FULL JOIN part_student st ON
sc.student_id=st.id; sc.student_id name       age chinese   mathematics physics   chemistry      english
1 Jerry                 19   91   100 87    97   93
2 Tom                   19   62   93   77   96   97
3 Peter                 19   43   73   87   66   97
4 Jessca                19   97   97   63   97   96
5 White                 18   96   99   87   99   95
6 Terry                 19   93   67   89   95   84
7 Zhi                   19   96   53   86   80   92
8 Cindy                 19   87   71   84   76   93
9 Smith                 19   77   73   93   91   91
10 Toy                  19   94   81   81   93   90
11 Kerry                19   91   90   87   97   93
12 Linda                19   97   84   87   78   90
13 Susan                19   92   83   87   96   97
14 Yao                  19   97   97   63   97   96
15 Jack                 19   96   99   87   99   95
16 Mokey                20   93   67   89   95   94
17 Mouse                20   96   83   86   90   92
18 Cat                  20   87   71   84   76   93
19 Dog                  20   87   73   83   91   91
20 Snack                20   84   71   81   93   90
21 Qian                 20   91   100  87   97   93
22 Yong                 20   87   94   87   78   90
23 Xiang                20   92   93   77   86   97
24 Kun                  20  97   87   83   87   86
25 Hunter               20   96   99   87   99   95
26 Qian                 20   93   87   89   95   84
27 Yong                 20   86   53   86   90   92
28 Xiang                20   87   71   84   76   93
29 Kun                  20   87   73   83   91   91
30 Hunter               20   94   71   81   93   90
31                      NULL      NULL      98   83   93   91   81
32                      NULL      NULL      84   81   91   93   90
NULL Harry              19   NULL      NULL      NULL      NULL      NULL
NULL Jeckson            19   NULL      NULL      NULL      NULL      NULL
NULL JiM                19   NULL      NULL      NULL      NULL      NULL
NULL White              18   NULL      NULL      NULL      NULL      NULL
NULL BaiDEn             18   NULL      NULL      NULL      NULL      NULL
Time taken: 1.374 seconds, Fetched: 37
row(s)

显示左右表所有记录，对应不能存在的记录以NULL表示。

文章标签：
JavaScript
前端开发
SQL
HIVE
分布式计算
关键词：
Hive join
Hive join连接
顾翔
目录
相关文章
阿甘兄
|
10月前
|
SQL 缓存 分布式计算
54 Hive的Join操作
54 Hive的Join操作
阿甘兄
114 0
WHYBIGDATA
|
SQL HIVE
DataGrip连接Hive执行DDL操作报错：「FAILED: ParseException line 1:5 cannot recognize input near 'show' 'indexes' 'on' in ddl statement」
DataGrip连接Hive执行DDL操作报错：「FAILED: ParseException line 1:5 cannot recognize input near 'show' 'indexes' 'on' in ddl statement」
WHYBIGDATA
562 0
DataGrip连接Hive执行DDL操作报错：「FAILED: ParseException line 1:5 cannot recognize input near 'show' 'indexes' 'on' in ddl statement」
三分钟热度的鱼
|
2月前
|
SQL 关系型数据库 MySQL
实时计算 Flink版产品使用问题之如何使用Flink SQL连接带有Kerberos认证的Hive
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架，广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器，提供了一套全面的解决方案，以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点，使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
三分钟热度的鱼
76 0
真的很搞笑
|
4月前
|
SQL DataWorks 网络安全
DataWorks操作报错合集之DataWorks连接Hive数据库时出现连接超时的问题如何解决
DataWorks是阿里云提供的一站式大数据开发与治理平台，支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中，可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
真的很搞笑
72 2
ha_lydms
|
11月前
|
SQL 关系型数据库 MySQL
Mybatis-Plus(连接Hive)
Mybatis-Plus(连接Hive)
ha_lydms
212 0
赵广陆
|
SQL 存储 大数据
大数据Hive Join连接查询
大数据Hive Join连接查询
赵广陆
77 0
二哥不像程序员
|
SQL 数据库 HIVE
Python连接Hive数据库
Python连接Hive数据库
二哥不像程序员
179 0
晓之以理的喵~~
|
SQL HIVE
Hive中的in、exists和left semi join
Hive中的in、exists和left semi join
晓之以理的喵~~
657 0
Hive中的in、exists和left semi join
冲冲冲冲
|
SQL 分布式计算 网络协议
hive连接被拒
connection refused
冲冲冲冲
207 0
Maynor
|
4月前
|
SQL 数据采集 数据挖掘
大数据行业应用之Hive数据分析航班线路相关的各项指标
大数据行业应用之Hive数据分析航班线路相关的各项指标
Maynor
166 1

热门文章

最新文章

  • 1
    Hive之数据倾斜的原因和解决方法
  • 2
    Hive架构优点及使用场景
  • 3
    原来MaxCompute还能这么玩系列（2）—— 利用HiveServer2 Proxy实现MaxCompute与Hive生态工具的互通
  • 4
    HIVE的安装配置、mysql的安装、hive创建表、创建分区、修改表等内容、hive beeline使用、HIVE的四种数据导入方式、使用Java代码执行hive的sql命令
  • 5
    记一次hive里面统计数据报错
  • 6
    【阿里云 CDP 公开课】 第八讲：Hive 迁移到 CDP
  • 7
    hadoop hive 手记
  • 8
    CDP中的Hive3系列之Apache Hive3的特性
  • 9
    HIVE 基本查询操作（二）——第2关：Hive数据类型和类型转换
  • 10
    Hive动态分区详解
  • 1
    DataWorks操作报错合集之DataWorks连接Hive数据库时出现连接超时的问题如何解决
    72
  • 2
    [AIGC 大数据基础]hive浅谈
    59
  • 3
    基于Hadoop数据仓库Hive1.2部署及使用
    58
  • 4
    Hive的安装
    42
  • 5
    【Hive】所有的Hive任务都会有MapReduce的执行吗？
    251
  • 6
    【Hive】Hive的函数：UDF、UDAF、UDTF的区别？
    112
  • 7
    【Hive】Hive有哪些方式保存元数据，各有哪些特点？
    412
  • 8
    【Hive】写出Hive中split、coalesce及collect_list函数的用法？
    735
  • 9
    【Hive】请说明hive中 Sort By，Order By，Cluster By，Distrbute By各代表什么意思？
    354
  • 10
    【Hive】请谈一下Hive的特点，Hive和RDBMS有什么异同？
    240

    • 相关课程

    更多
  • 大数据Hive教程精讲

    • 相关电子书

    更多
  • Comparison of Spark SQL with Hive
  • Hive Bucketing in Apache Spark
  • 2019大数据技术公开课第五季—Hive迁移到MaxCompute最佳实践

    • 相关实验场景

    更多
  • 使用SQL语句管理索引
    • 下一篇
    基于LNMP搭建WordPress