Hive中各种join对比案例分析

最新推荐文章于 2024-06-28 20:46:07 发布
最新推荐文章于 2024-06-28 20:46:07 发布
阅读量297 收藏 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
关注
专栏目录
【大数据Hive】hive 优化策略之job任务优化
congge_study的博客
11-22 8929
hive job优化策略
Hive Sql中六种面试题型总结
lightupworld的博客
09-14 1万+
Hive面试/经典练习题一、N日留存1.思路分析二、连续登陆1.数据准备2.思路分析三、 Top N 一、N日留存 1.思路分析 按天留存率计算 就是指用户在首日新增后，在接下来的后推第N天活跃情况，用后推第N天活跃的用户 / 首日新增用户。就得到后推第N天的新增用户留存率。 留存率计算案例 1月1日，新增用户200人; 次日留存：第2天，1月2日，这200人里面有100人活跃，则次日留存率为： 100 / 200 = 50% 2日留存：第3天，1月3日;这200名新
hive：几个join的差别汇总
Julyaaaa的博客
08-20 683
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 99
2019独角兽企业重金招聘Python工程师标准>>> ...
hive中join的实例
十色花的博客
07-06 430
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 SQL的各种join总结
biqidaoer的专栏
12-05 1557
join时的最后一个表会通过reducer流式传输，并在其中缓冲之前的其他表，因此，将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存。包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行 在功能上，它等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用。左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。（LEFT SEMI JOIN）会返回左边表的记录，前提是其记录对于右边的表满足ON语句中的判定条件。左指的是join关键字左边的表，简称左表。
hive 四种join的区别
AntKengElephant的博客
10-31 2143
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进行复杂用户行为大数据分析及优化案例
02-20
利用Hive进行复杂用户行为大数据分析及优化案例（全套视频+课件+代码+讲义+工具软件），具体内容包括： 01_自动批量加载数据到hive 02_Hive表批量加载数据的脚本实现（一） 03_Hive表批量加载数据的脚本实现（二） ...
【Hive Join性能突破】：案例分析与专业优化策略
Hive中的Join操作本质上是MapJoin或ReduceJoin的实现，它们分别在Map端和Reduce端完成数据的合并。在执行Join时，Hive通过多个阶段处理数据，包括读取数据、映射（Shuffle）、合并和写入输出。理解这些
hive调优原理详解：案例解析参数配置(第17天）
最新发布
syhiiu的博客
06-28 1477
本文主要详解hive调优，案例解析。
【hive】简单介绍hive的几种join
Mrerlou的博客
08-17 2043
common join 主要是针对数据/业务逻辑的join。Map joinSkew Join是hive 针对特殊数据、场景 进行的优化。则是Sql语句的优化，并且也可以应用上面的优化方案。MAP JION会把小表全部加载到内存中，在map阶段直接拿另外一个表的数据和内存中表数据做匹配，由于在map端是进行了join操作，省去了reduce运行的时间，算是hive中的一种优化。
Hive 中HQL中inner join，join， left join,full join区别
热门推荐
杨鑫newlife的专栏
05-31 3万+
表t1 (id , data)数据：1，112, 223, 33表t2 (id, data)数据：1，112, 2244，44---------------------------注意：join默认是inner  join，就是当被join的两个表都同时存在字段的时候才会成功t1 join t2 on t1.id = t2.idleft join 是两个被join的表，根据左侧表，这里是t1,有的...
hive join 的用法
dream__future的博客
10-07 348
该查询将返回 A 表中所有行，以及 B 表中 id 列相等的行，如果在 B 表中没有匹配的行，将返回 NULL 值。该查询将返回 B 表中所有行，以及 A 表中 id 列相等的行，如果在 A 表中没有匹配的行，将返回 NULL 值。3. Right join：返回右表中的所有行，以及左表中匹配的行。2. Left join：返回左表中的所有行，以及右表中匹配的行。1. Inner join：返回两个表中匹配的行。该查询与 inner join 相同，返回 A 和 B 表中 id 列相等的所有行。
Hive的join语句
stay hungry ! stay foolish!
10-19 1636
Hive的join语句 目录 一、INNER JOIN(Hive默认的JOIN) 二、JOIN优化 三、LEFT OUTER JOIN 四、RIGHTOUTER JOIN 五、FULL OUTER JOIN 六、LEFT SEMI-JOIN 七、笛卡尔积 JOIN 八、map-side JOIN 一、INNER JOIN(Hive...
Hive——join的使用
sunghosts的专栏
10-15 677
转：https://www.cnblogs.com/jnba/p/10673747.html hive中常用的join有：inner join、left join 、right join 、full join、left semi join、cross join、mulitiple 在hive中建立两张表，用于测试： hive> select * from rdb_a; OK 1 lucy 2 jack 3 tony hive> select * from
Hive的join
2301_77257988的博客
01-04 392
SQL中的连接查询有inner join(内连接）、left join(左连接)、right join（右连接）、full join（全连接）left semi join(左半连接)五种方式，它们之间其实并没有太大区别，仅仅是查询出来的结果有所不同。2）而union相当于把两个查询结果(左查询结果表有m列p行和右查询结果表有n列q行)的所有行进行了拼接，形成具有p+q行的查询结果。left semi join(左半连接)： 并不拼接两张表，两个表对 on 的条件字段做交集，返回前面表的记录。
Hive性能比较之in的使用和join的使用
fengkuang的一亩三分地
05-07 6940
问题：已知老用户表B，求解浏览表A里有多少用户是老用户？ 说明： A表有130万不重复浏览用户，B表也是百万级。 方法一： select count(*) from (select pin from A group by pin) a join (select pin from B group by pin) b 出现了数据倾斜，卡在一个reducer一直跑不出来。 方...
hive的几种join到底有何区别
weixin_46429290的博客
03-08 3859
hive中，几种join的区别 数据： tom,1 jey,2 lilly,7 lilly,8 tom,1 lilly,3 may,4 bob,5 以上两个为数据，没有什么意义，全是为了检测join的使用 看一下两张表，其实可以看出来，在name一行有重复的，也有不重复的，在id一行1表完全包含2表 left join left join会把左边的表所有数据列出来，当左边表有而右边表没有的时候，就会用null代替 select * from jn1 left join jn2 on jn1.name
Hive中join方法总结--有这一篇就够了
skj1995的博客
02-14 5242
1. 常见的四种join的区别 常见的join主要有下面四种，join,outer join, semi join和inner join，下面对这四个join的关系进行说明 （1.1）join等价于inner join,也就是只会将两表都存在的join在一起 （1.2）outer join分为： left outer join（等价于left join），right outer join（right join）和full outer join（full join） Left join是以左表为基准
hive中INNER JOIN如何使用
04-10
在Hive中，INNER JOIN是一种常用的关联操作，用于将两个或多个表中的数据根据指定的条件进行匹配。使用INNER JOIN可以获取满足连接条件的行，并将它们合并为一行结果。 INNER JOIN的语法如下： ``` SELECT 列名 ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值