Hive中各种join对比案例分析

最新推荐文章于 2024-05-30 14:52:09 发布
最新推荐文章于 2024-05-30 14:52:09 发布
阅读量251 收藏 1
点赞数 1
分类专栏： hive 文章标签： sql hive
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_45971146/article/details/108447786
版权

SQL语句执行顺序

在这里插入图片描述
很多SQL的优化可以根据SQL语句的执行顺序来处理。

创建表和插入数据

将建表和插入数据语句写到一个join.sql SQL文件中

-- 使用db2数据库
use db2;
-- 如果存在，则删除表
drop table if exists stu;
drop table if exists score;

-- 创建stu表
create table if not exists stu(
id int,
name string
)
row format delimited fields terminated by '\t'
;

-- 创建score表
create table if not exists score(
id int,
name string,
score int
)
row format delimited fields terminated by '\t'
;

-- 向表中插入数据
insert into table stu values (1,'dh'),(2,'xd'),(3,'xe'),(4,'ch'),(5,'lj');
insert into table score values (1,'dh',100),(2,'xd','86'),(3,'xe',null),(4,'ch',94),(6,'xx',26);

将join.sql文件上传到服务器：

[root@mini001 join]# ll
total 4
-rw-r--r--. 1 root root 593 Sep  7 22:30 join.sql

执行hive语句：

[root@mini001 join]# hive -f join.sql

查看表中数据：

0: jdbc:hive2://mini001:10000> select * from stu;select * from score;
+---------+-----------+--+
| stu.id  | stu.name  |
+---------+-----------+--+
| 1       | dh        |
| 2       | xd        |
| 3       | xe        |
| 4       | ch        |
| 5       | lj        |
+---------+-----------+--+
5 rows selected (2.057 seconds)
+-----------+-------------+--------------+--+
| score.id  | score.name  | score.score  |
+-----------+-------------+--------------+--+
| 1         | dh          | 100          |
| 2         | xd          | 86           |
| 3         | xe          | NULL         |
| 4         | ch          | 94           |
| 6         | xx          | 26           |
+-----------+-------------+--------------+--+
5 rows selected (0.196 seconds)

内连接

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

select
	st.id,
	st.name,
	sc.id,
	sc.name,
	sc.score
from stu st
inner join score sc on st.id=sc.id;

结果：

+--------+----------+--------+----------+-----------+--+
| st.id  | st.name  | sc.id  | sc.name  | sc.score  |
+--------+----------+--------+----------+-----------+--+
| 1      | dh       | 1      | dh       | 100       |
| 2      | xd       | 2      | xd       | 86        |
| 3      | xe       | 3      | xe       | NULL      |
| 4      | ch       | 4      | ch       | 94        |
+--------+----------+--------+----------+-----------+--+

默认情况下，join即为inner join，所以inner可以省略不写。

左外连接

左外连接：JOIN 操作符左边表中符合 WHERE 子句的所有记录将会被返回。

select
	st.id,
	st.name,
	sc.id,
	sc.name,
	sc.score
from stu st
left join score sc on st.id=sc.id
;

结果：

+--------+----------+--------+----------+-----------+--+
| st.id  | st.name  | sc.id  | sc.name  | sc.score  |
+--------+----------+--------+----------+-----------+--+
| 1      | dh       | 1      | dh       | 100       |
| 2      | xd       | 2      | xd       | 86        |
| 3      | xe       | 3      | xe       | NULL      |
| 4      | ch       | 4      | ch       | 94        |
| 5      | lj       | NULL   | NULL     | NULL      |
+--------+----------+--------+----------+-----------+--+

select 
	sc.id,
	sc.name,
	sc.score,
	st.id,
	st.name
from score sc
left join stu st on sc.id=st.id
;

结果：

+--------+----------+-----------+--------+----------+--+
| sc.id  | sc.name  | sc.score  | st.id  | st.name  |
+--------+----------+-----------+--------+----------+--+
| 1      | dh       | 100       | 1      | dh       |
| 2      | xd       | 86        | 2      | xd       |
| 3      | xe       | NULL      | 3      | xe       |
| 4      | ch       | 94        | 4      | ch       |
| 6      | xx       | 26        | NULL   | NULL     |
+--------+----------+-----------+--------+----------+--+

右外连接

右外连接：JOIN 操作符右边表中符合 WHERE 子句的所有记录将会被返回。

select
	st.id,
	st.name,
	sc.id,
	sc.name,
	sc.score
from stu st
right join score sc on st.id=sc.id
;

结果：

+--------+----------+--------+----------+-----------+--+
| st.id  | st.name  | sc.id  | sc.name  | sc.score  |
+--------+----------+--------+----------+-----------+--+
| 1      | dh       | 1      | dh       | 100       |
| 2      | xd       | 2      | xd       | 86        |
| 3      | xe       | 3      | xe       | NULL      |
| 4      | ch       | 4      | ch       | 94        |
| NULL   | NULL     | 6      | xx       | 26        |
+--------+----------+--------+----------+-----------+--+

select 
	sc.id,
	sc.name,
	sc.score,
	st.id,
	st.name
from score sc
right join stu st on sc.id=st.id
;

结果：

+--------+----------+-----------+--------+----------+--+
| sc.id  | sc.name  | sc.score  | st.id  | st.name  |
+--------+----------+-----------+--------+----------+--+
| 1      | dh       | 100       | 1      | dh       |
| 2      | xd       | 86        | 2      | xd       |
| 3      | xe       | NULL      | 3      | xe       |
| 4      | ch       | 94        | 4      | ch       |
| NULL   | NULL     | NULL      | 5      | lj       |
+--------+----------+-----------+--------+----------+--+

满外连接

满外连接：将会返回所有表中符合 WHERE 语句条件的所有记录。如果任一表的指定字 段没有符合条件的值的话，那么就使用 NULL 值替代。

select
	st.id,
	st.name,
	sc.id,
	sc.name,
	sc.score
from stu st
full join score sc on st.id=sc.id
;

结果：

+--------+----------+--------+----------+-----------+--+
| st.id  | st.name  | sc.id  | sc.name  | sc.score  |
+--------+----------+--------+----------+-----------+--+
| 1      | dh       | 1      | dh       | 100       |
| 2      | xd       | 2      | xd       | 86        |
| 3      | xe       | 3      | xe       | NULL      |
| 4      | ch       | 4      | ch       | 94        |
| 5      | lj       | NULL   | NULL     | NULL      |
| NULL   | NULL     | 6      | xx       | 26        |
+--------+----------+--------+----------+-----------+--+

笛卡尔积

笛卡尔集会在下面条件下产生 ：
（1）省略连接条件 ；
（2）连接条件无效 ；
（3）所有表中的所有行互相连接；

select
	st.id,
	st.name,
	sc.id,
	sc.name,
	sc.score
from stu st,score sc
;

结果：

+--------+----------+--------+----------+-----------+--+
| st.id  | st.name  | sc.id  | sc.name  | sc.score  |
+--------+----------+--------+----------+-----------+--+
| 1      | dh       | 1      | dh       | 100       |
| 2      | xd       | 1      | dh       | 100       |
| 3      | xe       | 1      | dh       | 100       |
| 4      | ch       | 1      | dh       | 100       |
| 5      | lj       | 1      | dh       | 100       |
| 1      | dh       | 2      | xd       | 86        |
| 2      | xd       | 2      | xd       | 86        |
| 3      | xe       | 2      | xd       | 86        |
| 4      | ch       | 2      | xd       | 86        |
| 5      | lj       | 2      | xd       | 86        |
| 1      | dh       | 3      | xe       | NULL      |
| 2      | xd       | 3      | xe       | NULL      |
| 3      | xe       | 3      | xe       | NULL      |
| 4      | ch       | 3      | xe       | NULL      |
| 5      | lj       | 3      | xe       | NULL      |
| 1      | dh       | 4      | ch       | 94        |
| 2      | xd       | 4      | ch       | 94        |
| 3      | xe       | 4      | ch       | 94        |
| 4      | ch       | 4      | ch       | 94        |
| 5      | lj       | 4      | ch       | 94        |
| 1      | dh       | 6      | xx       | 26        |
| 2      | xd       | 6      | xx       | 26        |
| 3      | xe       | 6      | xx       | 26        |
| 4      | ch       | 6      | xx       | 26        |
| 5      | lj       | 6      | xx       | 26        |
+--------+----------+--------+----------+-----------+--+

select 
	sc.*,
	st.*
from score sc,stu st
;

结果：

+--------+----------+-----------+--------+----------+--+
| sc.id  | sc.name  | sc.score  | st.id  | st.name  |
+--------+----------+-----------+--------+----------+--+
| 1      | dh       | 100       | 1      | dh       |
| 1      | dh       | 100       | 2      | xd       |
| 1      | dh       | 100       | 3      | xe       |
| 1      | dh       | 100       | 4      | ch       |
| 1      | dh       | 100       | 5      | lj       |
| 2      | xd       | 86        | 1      | dh       |
| 2      | xd       | 86        | 2      | xd       |
| 2      | xd       | 86        | 3      | xe       |
| 2      | xd       | 86        | 4      | ch       |
| 2      | xd       | 86        | 5      | lj       |
| 3      | xe       | NULL      | 1      | dh       |
| 3      | xe       | NULL      | 2      | xd       |
| 3      | xe       | NULL      | 3      | xe       |
| 3      | xe       | NULL      | 4      | ch       |
| 3      | xe       | NULL      | 5      | lj       |
| 4      | ch       | 94        | 1      | dh       |
| 4      | ch       | 94        | 2      | xd       |
| 4      | ch       | 94        | 3      | xe       |
| 4      | ch       | 94        | 4      | ch       |
| 4      | ch       | 94        | 5      | lj       |
| 6      | xx       | 26        | 1      | dh       |
| 6      | xx       | 26        | 2      | xd       |
| 6      | xx       | 26        | 3      | xe       |
| 6      | xx       | 26        | 4      | ch       |
| 6      | xx       | 26        | 5      | lj       |
+--------+----------+-----------+--------+----------+--+
The Great Bear
关注
  • 1
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
hive：几个join的差别汇总
Julyaaaa的博客
08-20 619
employ表 department表 1.笛卡尔积 select * from employee a ,department b; 2.inner join select * from employee a inner join department b on a.DepartmentId=b.id; 等价于 select * from employee a ,department...
hive 常用的 join 操作 实例
weixin_33939380的博客
04-09 80
2019独角兽企业重金招聘Python工程师标准>>> ...
hive中join的实例
十色花的博客
07-06 386
hive> create table a1(id int,name string)    row format delimited    fields terminated by '\t'    stored as textfile;hive> create table a2(id int,city string)    row format delimited    fields t...
hive 四种join的区别
AntKengElephant的博客
10-31 2111
tableA的结构及数据： id   name       grade     dept 1    lijie1   100.0   10 2    lijie2   90.0    20 3    lijie3   60.0    10 4    lijie4   80.0    10 5    lijie5   70.0    20 tableB的结构及数据： id    name 10...
Hive 常用案例分析
MusicDancing的博客
12-01 5338
得到一个不重复的数据样本 1. rand() 产生一个介于0和1之间的随机数 select rand(); # 0.5523740163966064 # 指定随机种子，以后使用这个种子每次产生的随机数都是一样的。 select rand(1234); # 0.6465821602909256 2. order by rand() 对结果进行随机排序 # 数据随机排序后取前100条 select uid from tablename order by rand(1234) limit 10.
hive join 数据倾斜 真实案例
Yan_Programer 的专栏
01-23 240
hive或者MR处理数据，不怕数据量大，就怕倾斜。hive里大表join的时候，数据倾斜就是个很头疼的问题。本博主就遇到了一个真实案例，特意记录下来，有需要的同学可以参考 1.查了5个小时还没结束的sql语句 set mapred.reduce.tasks = 30; insert overwrite directory 'xxx' select cus.idA,cus.name,addr.bb from tableA as cus join tableB as addr on cus.idA = a
【Hive】Hive中的MapJoin，CommonJoin，SMBJoin详细介绍
一个写湿的程序猿
08-04 2294
Hive-窗口函数，详细分析，案例实践 文章目录Hive-窗口函数，详细分析，案例实践窗口函数介绍窗口函数分析案例实践一案例实践二 窗口函数介绍 LEAD(col,n, default_val)：表示往后第n行数据，col是字段，n表示第几行，default_val 如果没有往后第n行则用默认值代替 LAG(col,n,default_val)：表示往前第n行数据，col是字段，n表示第几行，default_val 如果没有往前第n行则用默认值代替 FIRST_VALUE (col,true/false)：
大数据开发——Hive实战案例
weixin_44606952的博客
11-25 5372
1. 创建表结构 1.1 视频表结构 1.2 用户表结构 2. 准备工作 2.1 创建临时表 2.2 创建最终使用表 2.3 对创建表进行解读 3. 业务分析
Hive笔记——Join、group by、排序、窗口函数
m0_57479841的博客
12-01 336
非全局排序，如果reducer数量设置为2，即有两个reduce task，数据再进入reduce之前会进行排序，即每个reduce会输出一个文件，文件中会进行排序，但只是局部有序；③task通常表示一个过程(进程)，job通常表示用户提交的一个任务(目标)，期望明确的结果，job可以包含多个task，可以认为task是一个JobHandler。③maptask运行的时候：会把小表中的数据读取到内存中，然后maptask会读取block块中大表的数据，读完后和小表中的内存中的数据join，最后对外输出。
利用Hive进行复杂用户行为大数据分析及优化案例
02-20
利用Hive进行复杂用户行为大数据分析及优化案例（全套视频+课件+代码+讲义+工具软件），具体内容包括： 01_自动批量加载数据到hive 02_Hive表批量加载数据的脚本实现（一） 03_Hive表批量加载数据的脚本实现（二） ...
基于 Hive的数据分析案例 -MM聊天软件数据分析
06-16
临时文件链接
hive 中join和Group的优化
01-07
set hive.map.aggr = true; //是否在 Map 端进行聚合，默认为 True ；该设置会消耗更多的内存。 set hive.groupby.mapaggr.checkinterval = 100000000; //在 Map 端进行聚合操作的条目数目 set hive.groupby....
hive sql + left join 数据缺失
02-29
hive sql + left join 数据缺失
hive综合应用案例-用户搜索日志分析.docx
05-15
在这个综合应用案例中，我们将使用Hive来分析和处理用户搜索日志数据。搜索日志数据通常包含用户搜索的关键词、搜索时间、用户ID等信息，通过分析这些数据，我们可以了解用户的搜索习惯、热门搜索词、搜索趋势等，...
基础—SQL—DQL（数据查询语言）聚合函数
最新发布
m0_74363339的博客
05-30 437
这篇博客主要讲到了SQL分类中的DQL（数据查询语言）—聚合函数的讲解，聚合函数比较简单，但它主要配合着分组查询进行数据查询的操作，注意NULL的字段值不参与聚合函数的运算......
postgressql——Tuple学习（2）
Re_view的博客
05-27 328
postgressql——Tuple学习（2）
PostgreSQL发展史
PGCCC的博客
05-28 1001
PostgreSQL 的发展史反映了其从学术研究项目到全球领先的开源数据库系统的演变过程。通过不断的创新和社区支持，PostgreSQL 已经成为一个功能强大、性能优越的数据库管理系统，广泛应用于各个行业的关键业务场景中。#postgresql培训。
SQL开窗函数
zwldn的博客
05-21 1565
数据分析必备函数：开窗函数提供了在查询结果中进行排序、排名、聚合和分析的灵活性，在数据分析和报表生成中非常有用，可以实现更复杂的计算和分析需求。
hive中的join和inner join有什么区别
04-05
在Hive中，join和inner join有以下区别： 1. join可以包含多种类型：inner join、left join、right join和full outer join，而inner join只是其中一种类型。 2. join的语法格式比inner join复杂，需要指定两个表之间的连接条件和连接方式。而inner join只需要指定连接条件即可。 3. 在执行inner join时，Hive会自动过滤掉不满足连接条件的行，而在执行join时，需要根据连接方式来决定是否过滤掉不满足连接条件的行。 4. 在执行inner join时，如果连接条件中有重复的列名，需要使用别名来区分不同的列。而在执行join时，不需要使用别名来区分不同的列。 综上所述，join是一种更灵活、更复杂的连接方式，而inner join更为简单、直接，适用于只需要连接两个表且不需要考虑连接方式的情况。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值