02-Hive连接JOIN用例详解

最新推荐文章于 2024-09-18 10:25:57 发布
最新推荐文章于 2024-09-18 10:25:57 发布
阅读量378 收藏
点赞数
分类专栏： hadoop hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/wshsdm/article/details/80672508
版权
hadoop 同时被 2 个专栏收录
33 篇文章 0 订阅
订阅专栏
hive
8 篇文章 0 订阅
订阅专栏

数据准备：创建数据-->创建表-->导入数据

首先创建两个原始数据的文件，这两个文件分别有三列，第一列是id、第二列是名称、第三列是另外一个表的id。通过第二列可以明显的看到两个表做连接查询的结果：

[xingoo@localhost tmp]$ cat aa.txt 
1 a 3
2 b 4
3 c 1
[xingoo@localhost tmp]$ cat bb.txt 
1 xxx 2
2 yyy 3
3 zzz 5

接下来创建两个表，需要注意的是表的字段分隔符为空格，另一个表可以直接基于当前的表创建。

hive> create table aa
    > (a string,b string,c string)
    > row format delimited
    > fields terminated by ' ';
OK
Time taken: 0.19 seconds
hive> create table bb like aa;
OK
Time taken: 0.188 seconds

查看两个表的结构：

hive> describe aa;
OK
a                       string                                      
b                       string                                      
c                       string                                      
Time taken: 0.068 seconds, Fetched: 3 row(s)
hive> describe bb;
OK
a                       string                                      
b                       string                                      
c                       string                                      
Time taken: 0.045 seconds, Fetched: 3 row(s)

下面可以基于本地的文件，导入数据

hive> load data local inpath '/usr/tmp/aa.txt' overwrite into table aa;
Loading data to table test.aa
OK
Time taken: 0.519 seconds
hive> load data local inpath '/usr/tmp/bb.txt' overwrite into table bb;
Loading data to table test.bb
OK
Time taken: 0.321 seconds

内连接

内连接即基于on语句，仅列出表1和表2符合连接条件的数据。

hive> select * from aa a join bb b on a.c=b.a;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824161233_f9ecefa2-e5d7-416d-8d90-e191937e7313
Total jobs = 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hadoop/hadoop-2.6.4/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2016-08-24 16:12:44 Starting to launch local task to process map join;  maximum memory = 518979584
2016-08-24 16:12:47 Dump the side-table for tag: 0 with group count: 3 into file: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-12-33_145_337836390845333215-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile00--.hashtable
2016-08-24 16:12:47 Uploaded 1 File to: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-12-33_145_337836390845333215-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile00--.hashtable (332 bytes)
2016-08-24 16:12:47 End of local task; Time Taken: 3.425 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-24 16:12:50,222 Stage-3 map = 100%,  reduce = 0%
Ended Job = job_local944389202_0007
MapReduce Jobs Launched: 
Stage-Stage-3:  HDFS Read: 1264 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
3   c   1   1   xxx 2
1   a   3   3   zzz 5
Time taken: 17.083 seconds, Fetched: 2 row(s)

左连接

左连接是显示左边的表的所有数据，如果有右边表与之对应，则显示；否则显示null

ive> select * from aa a left outer join bb b on a.c=b.a;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824161637_6d540592-13fd-4f59-a2cf-0a91c0fc9533
Total jobs = 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hadoop/hadoop-2.6.4/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2016-08-24 16:16:48 Starting to launch local task to process map join;  maximum memory = 518979584
2016-08-24 16:16:51 Dump the side-table for tag: 1 with group count: 3 into file: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-16-37_813_4572869866822819707-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile11--.hashtable
2016-08-24 16:16:51 Uploaded 1 File to: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-16-37_813_4572869866822819707-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile11--.hashtable (338 bytes)
2016-08-24 16:16:51 End of local task; Time Taken: 2.634 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-24 16:16:53,843 Stage-3 map = 100%,  reduce = 0%
Ended Job = job_local1670258961_0008
MapReduce Jobs Launched: 
Stage-Stage-3:  HDFS Read: 1282 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
1   a   3   3   zzz 5
2   b   4   NULL    NULL    NULL
3   c   1   1   xxx 2
Time taken: 16.048 seconds, Fetched: 3 row(s)

右连接

类似左连接，同理。

hive> select * from aa a right outer join bb b on a.c=b.a;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824162227_5d0f0090-1a9b-4a3f-9e82-e93c4d180f4b
Total jobs = 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hadoop/hadoop-2.6.4/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2016-08-24 16:22:37 Starting to launch local task to process map join;  maximum memory = 518979584
2016-08-24 16:22:40 Dump the side-table for tag: 0 with group count: 3 into file: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-22-27_619_7820027359528638029-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile20--.hashtable
2016-08-24 16:22:40 Uploaded 1 File to: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-22-27_619_7820027359528638029-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile20--.hashtable (332 bytes)
2016-08-24 16:22:40 End of local task; Time Taken: 2.368 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-24 16:22:43,060 Stage-3 map = 100%,  reduce = 0%
Ended Job = job_local2001415675_0009
MapReduce Jobs Launched: 
Stage-Stage-3:  HDFS Read: 1306 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
3   c   1   1   xxx 2
NULL    NULL    NULL    2   yyy 3
1   a   3   3   zzz 5
Time taken: 15.483 seconds, Fetched: 3 row(s)

全连接

相当于表1和表2的数据都显示，如果没有对应的数据，则显示Null.

hive> select * from aa a full outer join bb b on a.c=b.a;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824162252_c71b2fae-9768-4b9a-b5ad-c06d7cdb60fb
Total jobs = 1
Launching Job 1 out of 1
Number of reduce tasks not specified. Estimated from input data size: 1
In order to change the average load for a reducer (in bytes):
  set hive.exec.reducers.bytes.per.reducer=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Job running in-process (local Hadoop)
2016-08-24 16:22:54,111 Stage-1 map = 100%,  reduce = 100%
Ended Job = job_local1766586034_0010
MapReduce Jobs Launched: 
Stage-Stage-1:  HDFS Read: 4026 HDFS Write: 270 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
3   c   1   1   xxx 2
NULL    NULL    NULL    2   yyy 3
1   a   3   3   zzz 5
2   b   4   NULL    NULL    NULL
Time taken: 1.689 seconds, Fetched: 4 row(s)

左半开连接

这个比较特殊，SEMI-JOIN仅仅会显示表1的数据，即左边表的数据。但是效率会比左连接快，因为他会先拿到表1的数据，然后在表2中查找，只要查找到结果立马就返回数据。

hive> select * from aa a left semi join bb b on a.c=b.a;
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824162327_e7fc72a7-ef91-4d39-83bc-ff8159ea8816
Total jobs = 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hadoop/hadoop-2.6.4/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2016-08-24 16:23:37 Starting to launch local task to process map join;  maximum memory = 518979584
2016-08-24 16:23:41 Dump the side-table for tag: 1 with group count: 3 into file: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-23-27_008_3026796648107813784-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile31--.hashtable
2016-08-24 16:23:41 Uploaded 1 File to: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-23-27_008_3026796648107813784-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile31--.hashtable (317 bytes)
2016-08-24 16:23:41 End of local task; Time Taken: 3.586 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-24 16:23:43,798 Stage-3 map = 100%,  reduce = 0%
Ended Job = job_local521961878_0011
MapReduce Jobs Launched: 
Stage-Stage-3:  HDFS Read: 1366 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
1   a   3
3   c   1
Time taken: 16.811 seconds, Fetched: 2 row(s)

笛卡尔积

笛卡尔积会针对表1和表2的每条数据做连接...

hive> select * from aa join bb;
Warning: Map Join MAPJOIN[9][bigTable=?] in task 'Stage-3:MAPRED' is a cross product
WARNING: Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
Query ID = root_20160824162449_20e4b5ec-768f-48cf-a840-7d9ff360975f
Total jobs = 1
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/usr/apache-hive-2.1.0-bin/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/usr/hadoop/hadoop-2.6.4/share/hadoop/common/lib/slf4j-log4j12-1.7.5.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
2016-08-24 16:25:00 Starting to launch local task to process map join;  maximum memory = 518979584
2016-08-24 16:25:02 Dump the side-table for tag: 0 with group count: 1 into file: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-24-49_294_2706432574075169306-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile40--.hashtable
2016-08-24 16:25:02 Uploaded 1 File to: file:/usr/hive/tmp/xingoo/a69078ea-b7d5-4a78-9342-05a1695e9f98/hive_2016-08-24_16-24-49_294_2706432574075169306-1/-local-10004/HashTable-Stage-3/MapJoin-mapfile40--.hashtable (305 bytes)
2016-08-24 16:25:02 End of local task; Time Taken: 2.892 sec.
Execution completed successfully
MapredLocal task succeeded
Launching Job 1 out of 1
Number of reduce tasks is set to 0 since there's no reduce operator
Job running in-process (local Hadoop)
2016-08-24 16:25:05,677 Stage-3 map = 100%,  reduce = 0%
Ended Job = job_local2068422373_0012
MapReduce Jobs Launched: 
Stage-Stage-3:  HDFS Read: 1390 HDFS Write: 90 SUCCESS
Total MapReduce CPU Time Spent: 0 msec
OK
1   a   3   1   xxx 2
2   b   4   1   xxx 2
3   c   1   1   xxx 2
1   a   3   2   yyy 3
2   b   4   2   yyy 3
3   c   1   2   yyy 3
1   a   3   3   zzz 5
2   b   4   3   zzz 5
3   c   1   3   zzz 5

上面就是hive中的连接查询，其实与SQL一样的。

阿豪963
关注
专栏目录
spark sql hive sql join 之 using语法
yy的博客
05-09 667
sql join using语法.
hive+hbase学习手册
尬聊码农
05-13 879
hive学习手册 一、hive入门手册 1.什么是数据仓库 1.1数据仓库概念 对历史数据变化的统计，从而支撑企业的决策。比如：某个商品最近一个月的销量，预判下个月应该销售多少，从而补充多少货源。 1.2传统数据仓库面临的挑战 （1）无法满足快速增长的海量数据存储需求 （2）无法有效处理不同类型的数据 （3）计算和处理能力不足 1.3 Hive介绍 Hbase支持快速的交互式的大数据应用 ...
Hive的连接
u012474716的博客
01-03 451
Hive的表连接 -等值连接 -不等值连接 -外连接 -自连接等值连接/不等值连接:就看连接是不是用=--等值连接 查询员工信息：员工号，姓名，月薪，部门名称 select e.empno,e.ename,e.sal,d.dname from emp e,dept d where e.deptno=d.deptno; --不等值连接：用>,<,between ..and等 查询员工信息：员工
【Hive】Hive 常见连接方式介绍
最新发布
在路上的专栏
09-18 1166
hive 连接方式大全
Hive之常用join
freeRoad
07-24 519
Hive 有很多join，这里主要讲述常用的三种join。 0 Map Reduce 1 Common Join 最为普通的join策略，不受数据量的大小影响，也可以叫做reduce side join ,最没效率的一种join 方式. 它由一个mapreduce job 完成. 首先将大表和小表分别进行map 操作, 在map shuffle 的阶段每一个map output key 变成了table_name_tag_prefix + join_column_value ,..
Hive 表的连接
humanity11的博客
01-26 1055
Hive表常用连接 对于直接在mapReduce中用join相比，hive的好处是简化了繁琐的处理工作，hive表的连接操作就是如此，本文主要讲解hive的4中主要连接：内连接、外连接、半连接、map连接。 我们用如下的sales,things表的数据来举例说明各种连接的作用，方便大家理解。                               （图1 sales表）        ...
Hive连接查询
qq_41666983的博客
03-26 499
Hive连接查询 需求1：某年度对用户满意度进行调研分析，找出目标人群。 参考实现：2019年购买又退款的客户 select a.user_name from (select distinct user_name from user_trade where year(dt)=2019) as a join (select distinct user_name from user...
Hive数据定义语言（DDL）概述
weixin_48626604的博客
03-25 2163
Hive数据定义语言（DDL）概述 1.什么是DDL语言？ 数据定义语言 (Data Definition Language, DDL) 是SQL语言集中对数据库内部的对象结构进行创建，删除，修改等的操作语言，这些数据库对象包括database（schema）、table、view、index等。核心语法由CREATE、ALTER与DROP三个所组成。DDL并不涉及表内部数据的操作。 完整建表语法树 CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXIS
ClickHouse详解
热门推荐
我思，顾我在
10-27 3万+
一 clickhouse-简介 ​ ClickHouse是俄罗斯的Yandex于2016年开源的一个用于联机分析(OLAP:Online Analytical Processing)的列式数据库管理系统(DBMS:Database Management System) , 主要用于在线分析处理查询（OLAP），能够使用SQL查询实时生成分析数据报告。 ClickHouse的全称是Click Stream，Data WareHouse，简称ClickHouse 实时数据 ,事件,快 [外链图片转存失
数据分析大数据面试题大杂烩02
GavinKai
03-09 2451
Map端会处理输入数据并产生中间结果,这个中间结果会写到本地磁盘,每个Map的输出会先写到内存缓冲区中,当写入的数据达到设定的阈值时,系统将会启动一个线程将缓冲区的数据写到磁盘,这个过程叫做spill(spill写入之前,会先进行二次排序,首先根据数据所属的partition进行排序,然后每个partition中的数据再按key来排序 . partition的目是将记录划分到不同的Reducer上去,以期望能够达到负载均衡,以后的Reducer就会根据partition来读取自己对应的数据 . 接着运行co
2024年06月数据月报
SmartSi
07-06 839
2024年06月数据月报，为您准备了阿里、字节、百度等大厂130+篇实践案例
HiveJoin操作
Piepis的博客
09-23 395
HiveJoin join_table: table_reference JOIN table_factor [join_condition] | table_reference {LEFT|RIGHT|FULL} [OUTER] JOIN table_reference join_condition | table_reference LEFT SEMI JOIN table_reference join_condition | table_reference CROSS JOIN t
Hive 连接及使用
hj1993的博客
02-11 2890
有三种方式连接 hive： cli：直接输入 bin/hive 就可以进入 cli hiveserver2、beeline webui
Hive——连接方式
weixin_30586257的博客
04-08 128
Hive——连接方式 一.CLI连接 直接通过CLI连接hive,进行相关hive sql 操作。 直接使用hive-1.1.0-cdh5.7.0/bin/hive 命令即可 hive> show databases; OK default hive1 Time taken: 0.535 seconds, Fetched: 2 row(s) hive> ...
Hive第三天——Hive使用（二）（join语句）
你怎么回事
09-25 428
文章部分参考自：https://blog.csdn.net/qq_23897391/article/details/92636509 Hive第三天——Hive使用 自己的话：黑发不知勤学早，白首方悔读书迟 每天都要保持前进，我势必要有强劲的实力，再跟明天的自己问好。 一、DML 1.插入单条数据到表中 hive> insert into table t_seq values('10','...
Hive连接方式
xiaoyi52的专栏
04-28 1842
环境准备 安装 hadoop，参照：https://www.jianshu.com/p/9c8a0f7b98cf 安装hive，参照：https://www.jianshu.com/p/ed4c2852754c 说明：本文测试环境为单机，而非集群环境。 CLI连接 安装好之后，可通过CLI直接连接，并进行相关操作: HiveServer2/beeline连接 CLI连接时，输入hive实际上...
hive的连接方式
weixin_45067310的博客
09-28 273
（1）bin/hive 交互式查询 #使用hive方式查询的时候显示字段信息 <property> <name>hive.cli.print.header</name> <value>true</value> <description>Whether to print the names of the columns in query output.</description> </property> #使
hive连接方式
03-22 628
一、shell 直接连接 bin/hive create database test; show databases; 有的话，实现数据库共享 use test; 二、hive启动为一个服务器，用另一台服务器远程连接 1.将hadoop01上的hive发送到hadoop02服务器上： scp -r /opt/hive/ hadoop02:/opt 2.在hadoop01下启动hive: c...
Hive连接方式（本文梳理与datagrip连接）
房东的猫的博客
04-01 1687
1.确保HIve已经正确安装完毕，如已正确成功安装Hive，随时随地输入Hive启动即可首先要确保集群成功启动，否则hive会failed；2.启动成功之后，就可以做Hive的相关操作了，出现以下操作 -------成功！
flink-sql-connector-hive和flink-connector-hive区别
03-29
flink-sql-connector-hive是一个Flink SQL连接器，用于将Flink与Hive集成，可以在Flink SQL中使用Hive表进行数据处理。它提供了一个Flink TableSource和Flink TableSink，可以将Hive表作为输入源或输出目标。 flink...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值