Hive的Join连接

前言

  Hive-3.1.2版本支持6种join语法。分别是：inner join（内连接）、left join（左连接）、right join（右连接）、full outer join（全外连接）、left semi join（左半开连接）、cross join（交叉连接，也叫做笛卡尔乘积）。

一、Hive的Join连接

数据准备： 有两张表studentInfo、studentScore

create table if not exists studentInfo
(
    user_id   int comment '学生id',
    name      string comment '学生姓名',
    gender    string comment '学生性别'
)
    comment '学生信息表';
INSERT overwrite table studentInfo
VALUES (1, '吱吱', '男'),
       (2, '格格', '男'),
       (3, '纷纷', '女'),
       (4, '嘻嘻', '女'),
       (5, '安娜', '女');


create table if not exists studentScore
(
    user_id   int comment '学生id',
    subject   string comment '学科',
    score     int comment '分数'
)
    comment '学生分数表';

INSERT overwrite table studentScore
VALUES (1, '生物', 78),
       (2, '生物', 88),
       (3, '生物', 34),
       (4, '数学', 98),
       (null, '数学', 64);

1.1 inner join 内连接

       内连接是最常见的一种连接，其中inner可以省略：inner join == join ； 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.subject,
    t2.score
from studentInfo t1
        inner join studentScore t2 on t1.user_id = t2.user_id

1.2 left join 左外连接

    join时以左表的全部数据为准，右边与之关联；左表数据全部返回，右表关联上的显示返回，关联不上的显示null返回。

select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.user_id,
    t2.subject,
    t2.score
from studentInfo t1
 left  join studentScore t2 
   on t1.user_id = t2.user_id;

1.3 right join 右外连接

       join时以右表的全部数据为准，左边与之关联；右表数据全部返回，左表关联上的显示返回，关联不上的显示null返回。

select
    t2.user_id,
    t2.subject,
    t2.score,
    t1.user_id,
    t1.name,
    t1.gender
from studentInfo t1
 right  join studentScore t2
   on t1.user_id = t2.user_id;

1.4 full join 满外连接

  包含左、右两个表的全部行，不管另外一边的表中是否存在与它们匹配的行；在功能上等价于对这两个数据集合分别进行左外连接和右外连接，然后再使用消去重复行的操作将上述两个结果集合并为一个结果集。full join 本质等价于 left join  union   right join; 

select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.user_id,
    t2.subject,
    t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id;

ps：full join 本质等价于 left join union  right join; 

select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.user_id,
    t2.subject,
    t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id;

----- 等价于下述代码

select
    t1.user_id as t1_user_id ,
    t1.name,
    t1.gender,
    t2.user_id as  t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
 left  join studentScore t2
   on t1.user_id = t2.user_id
union
select
    t1.user_id as t1_user_id ,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
 right  join studentScore t2
   on t1.user_id = t2.user_id

1.5 多表连接

      注意：连接 n 个表，至少需要 n-1 个连接条件。例如：连接三个表，至少需要两个连接
条件。 join on使用的key有几组就会被转化为几个MR任务，使用相 同的key来连接，则只会被转化为1个MR任务。

1.6 cross join 交叉连接

    交叉连接cross join，将会返回被连接的两个表的笛卡尔积，返回结果的行数等于两个表行数的乘积 N*M。对于大表来说，cross join慎用（笛卡尔积可能会造成数据膨胀）

    在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联 键。
  在HiveSQL语法中，cross join 后面可以跟where子句进行过滤，或者on条件过滤。

     
---举例：
select
    t1.user_id as t1_user_id ,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1, studentScore t2

--- 等价于：
select
     t1.user_id as t1_user_id ,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 join studentScore t2

---等价于：
select
     t1.user_id as t1_user_id ,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 cross  join studentScore t2

1.7 join on和where条件区别

       两者之间的区别见文章：
Hive中left join 中的where 和 on的区别-CSDN博客文章浏览阅读1.2k次，点赞21次，收藏23次。Hive中left join 中的where 和 on的区别https://blog.csdn.net/SHWAITME/article/details/135892183?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522170780016016800197016026%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=170780016016800197016026&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_ecpm_v1~rank_v31_ecpm-1-135892183-null-null.nonecase&utm_term=where&spm=1018.2226.3001.4450

1.8 join中不能有null

  • group by字段为null，会导致结果不正确（null值也会参与group by 分组）

group by column1
  • join字段为null会导致结果不正确(例如：下述 t2.b字段是null值)
t1 left join t2 on t1.a=t2.a and t1.b=t2.b

1.9 join操作导致数据膨胀

select *
from a 
left join b 
on a.id = b.id

     如果主表a的id是唯一的，副表b的id有重复值，非唯一，那当on a.id = b.id 时，就会导致数据膨胀（一条变多条）。因此两表或多表join的时候，需保证join的字段唯一性，否则会出现一对多的数据膨胀现象。

二、Hive的谓词下推

2.1 谓词下推概念

      在不影响结果的情况下，尽量将过滤条件提前执行。谓词下推后，过滤条件在map端执行，减少了map端的输出，降低了数据在集群上传输的量，提升任务性能。

     在hive生成的物理执行计划中，有一个配置项用于管理谓词下推是否开启。

set hive.optimize.ppd=true; 默认是true

   疑问：如果hive谓词下推的功能与join同时存在，那下推功能可以在哪些场景下生效？

2.2 谓词下推场景分析

     数据准备：以上述两张表studentInfo、studentScore为例

    查看谓词下推是否开启：set hive.optimize.ppd;

(1) inner join 内连接

  • 对左表where过滤
 explain
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
    inner join studentScore t2 on t1.user_id = t2.user_id
where t1.user_id >2

     explain查看执行计划，在对t2表进行scan后，优先对t1表进行filter，过滤t1.user_id >2，即谓词下推生效。

  • 对右表where过滤
 explain
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
    inner join studentScore t2 on t1.user_id = t2.user_id
where t2.user_id is not null

    explain查看执行计划，在对t2表进行scan后，优先进行filter，过滤t2.user_id is not null，即谓词下推生效。

 

  • 对左表on过滤
explain
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
    inner join studentScore t2 on t1.user_id = t2.user_id and t1.user_id >2

    explain查看执行计划，在对t2表进行scan后，优先对t1表进行filter，过滤t1.user_id >2，即谓词下推生效。

  • 对右表on过滤
 explain
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
    inner join studentScore t2 on t1.user_id = t2.user_id and t2.user_id is not null

    explain查看执行计划，在对t2表进行scan后，优先进行filter，过滤t2.user_id is not null，即谓词下推生效。 

 (2) left join（right join 同理）

  • 对左表where过滤
explain
select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.user_id,
    t2.subject,
    t2.score
from studentInfo t1
 left  join studentScore t2
   on t1.user_id = t2.user_id
where t1.user_id >2;

    explain查看执行计划，在对t2表进行scan后，优先对t1表进行filter，过滤t1.user_id >2，即谓词下推生效。

  • 对右表where过滤
explain
select
    t1.user_id,
    t1.name,
    t1.gender,
    t2.user_id,
    t2.subject,
    t2.score
from studentInfo t1
 left  join studentScore t2
   on t1.user_id = t2.user_id
where t2.user_id is not null;

     explain查看执行计划，在对t2表进行scan后，优先进行filter，过滤t2.user_id is not null，即谓词下推生效。 

 

  • 对左表on过滤
explain 
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
   left join studentScore t2
     on t1.user_id = t2.user_id and t1.user_id >2

      explain查看执行计划，在对t2表进行scan后，在对t1表未进行filter，即谓词下推不生效。

 

  • 对右表on过滤
explain
select
    t1.user_id as t1_user_id,
    t1.name,
    t1.gender,
    t2.user_id as t2_user_id,
    t2.subject,
    t2.score
from studentInfo t1
   left join studentScore t2
     on t1.user_id = t2.user_id and t2.user_id is not null;

      explain查看执行计划，在对t2表进行scan后，优先进行filter，过滤t2.user_id is not null，即谓词下推生效。 

 (3) full join

  • 对左表where过滤
explain 
select
     t1.user_id as t1_user_id,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id
where  t1.user_id >2 ;

     explain查看执行计划，在对t2表进行scan后，优先对t1表进行filter，过滤t1.user_id >2，即谓词下推生效。

 

  • 对右表where过滤
explain
select
     t1.user_id as t1_user_id,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id
where  t2.user_id is not null

     explain查看执行计划，在对t1 表进行scan后，优先进行filter，过滤t2.user_id is not null，即谓词下推生效。 

  • 对左表on过滤
explain
select
     t1.user_id as t1_user_id,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id and t1.user_id >2;

       explain查看执行计划，在对t1表进行scan后，未对t1表进行filter，即谓词下推不生效。

  • 对右表on过滤
explain
select
     t1.user_id as t1_user_id,
     t1.name,
     t1.gender,
     t2.user_id as t2_user_id,
     t2.subject,
     t2.score
from studentInfo t1
 full  join studentScore t2
   on t1.user_id = t2.user_id and t2.user_id is not null;

     explain查看执行计划，在对t1表进行scan后，未对t2表未进行filter，即谓词下推不生效。

总结：

hive中谓词下推的各种场景下的生效情况如下表：

inner joinleft joinright joinfull join
左表右表左表右表左表右表左表右表
where条件√√√√√√√√
on条件√√×√√×××

三、Hive Join的数据倾斜

          待补充

参考文章：

Hive的Join操作_hive join-CSDN博客

《Hive用户指南》- Hive的连接join与排序_hive 对主表排序后连接查询能保持顺序吗-CSDN博客

Hive 中的join和谓词下推_hive谓词下推-CSDN博客

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/385744.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

耳机壳UV树脂制作私模定制耳塞适合什么样的人使用呢？

耳机壳UV树脂制作私模定制耳塞适合什么样的人使用呢？

耳机壳UV树脂制作私模定制耳塞适合以下人群使用： 对音质要求高的人：私模定制耳塞能够完美契合用户的耳朵形状，减少漏音和外部噪音的干扰，提供更好的音质体验。需要长时间佩戴耳机的人：私模定制耳塞能够提高佩戴舒适度…
阅读更多...
【Django】Django内建用户系统

【Django】Django内建用户系统

Django内建用户系统 14.1 Django中的用户认证 Django带有一个用户认证系统系统，它处理用户用户账号、组、权限以及基于cookie的用户会话。 用户可以直接使用Django自带的用户表。 官方文档：https://docs.djangoproject.com/zh-hans/2.2/topics/auth/ …
阅读更多...
C语言—基础数据类型(含进制转换)

C语言—基础数据类型(含进制转换)

进制转换不多，但我觉得适合小白(我爱夸自己嘿嘿) 练习 1. 确认基础类型所占用的内存空间(提示：使用sizeof 运算符)： 在这里我说一下，long 类型通常占用 4 字节。在 64 位系统上，long 类型通常也可为 8 字节。 格式…
阅读更多...
部署JForum2过程。

部署JForum2过程。

居然用了两天时间，才把JForum2部署成功。走了一些弯路，主要原因是想当然，以为jdk和tomcat，应该向下兼容，所以就随便选的版本。 一、jdk-17、apache-tomcat-10.1.18-windows-x64，部署完jforum-2.8.2.war,改…
阅读更多...
海量数据处理商用短链接生成器平台 - 4

海量数据处理商用短链接生成器平台 - 4

第六章 架构核心技术-池化思想-异步结合 性能优化最佳实践 第1集 RestTemplate里面的存在的问题你知道多少- Broken pipe错误 项目就更新到第六章了，剩下的内容 放百度网盘里面了，需要的来取。 链接：https://pan.baidu.com/s/19LHPw36dsxPB7…
阅读更多...
LeetCode、901. 股票价格跨度【中等，单调栈】

LeetCode、901. 股票价格跨度【中等，单调栈】

文章目录 前言LeetCode、901. 股票价格跨度【中等，单调栈】题目链接及分类思路思路1：暴力思路2：单调栈写法优化：单调栈简化写法(数组替代栈集合) 资料获取 前言 博主介绍：✌目前全网粉丝2W，csdn博客专家、…
阅读更多...
LeetCode、1143. 最长公共子序列【中等，二维DP】

LeetCode、1143. 最长公共子序列【中等，二维DP】

文章目录 前言LeetCode、1143. 最长公共子序列【中等，二维DP】题目链接与分类思路2022年暑假学习思路及题解二维DP解决 资料获取 前言 博主介绍：✌目前全网粉丝2W，csdn博客专家、Java领域优质创作者，博客之星、阿里云平台优质作者…
阅读更多...
如何才能学好JVM？——零基础入门篇

如何才能学好JVM？——零基础入门篇

1. JVM是什么？ JVM是Java Virtual Machine的简称，它是一个虚拟的计算机，专门为执行Java程序而设计。 你可以想象它是一个能够运行Java字节码的平台，无论你的程序在Windows、Mac还是Linux上，它们都能通过JVM在这些系统…
阅读更多...
P1928 外星密码

P1928 外星密码

网址如下&#xff1a; P1928 外星密码 - 洛谷 | 计算机科学教育新生态 (luogu.com.cn) C的string真的是太好用辣&#xff01; 思路就是用一个函数来递归翻译 代码如下&#xff1a; #include<iostream> #include<string> #include<cctype> using namespace…
阅读更多...
springboot183基于java的公寓报修管理系统

springboot183基于java的公寓报修管理系统

简介 【毕设源码推荐 javaweb 项目】基于springbootvue 的 适用于计算机类毕业设计&#xff0c;课程设计参考与学习用途。仅供学习参考&#xff0c; 不得用于商业或者非法用途&#xff0c;否则&#xff0c;一切后果请用户自负。 看运行截图看 第五章 第四章 获取资料方式 **项…
阅读更多...
Microsoft OneNote 图片文字提取

Microsoft OneNote 图片文字提取

Microsoft OneNote 图片文字提取 1. 文件 -> 新建 -> 我的电脑 -> 名称 -> 位置 -> 创建笔记本2. 插入图片​​​3. 复制图片中的文本References 1. 文件 -> 新建 -> 我的电脑 -> 名称 -> 位置 -> 创建笔记本 ​ 2. 插入图片 ​​​3. 复制图片…
阅读更多...
Linux-进程信号

Linux-进程信号

Linux进程信号 初步认识信号信号的存储结构信号的处理方式信号的产生硬件异常产生的信号核心转储sigset_t信号集信号集的操作函数对block表的操作对pending表的操作对handler表的操作信号的捕捉用户态和内核态 信号的处理过程可重入函数volatile关键字 初步认识信号 生活中有哪…
阅读更多...
C# CAD2016 多边形顶点按方向重新排序

C# CAD2016 多边形顶点按方向重新排序

多边形顶点按方向重新排序 初始化多边形顶点集合 outerPoints 创建一个名为 outerPoints 的 List<Point2d>&#xff0c;用于存储多边形的所有顶点坐标。 计算多边形顶点集合的边界框&#xff08;BoundingBox&#xff09; 使用LINQ的Aggregate方法遍历整个outerPoints列表…
阅读更多...
基于Zigbee的智能温室大棚系统（附详细使用教程+完整代码+原理图+完整课设报告）

基于Zigbee的智能温室大棚系统（附详细使用教程+完整代码+原理图+完整课设报告）

🎊项目专栏：【Zigbee课程设计系列文章】（附详细使用教程+完整代码+原理图+完整课设报告） 前言 👑由于无线传感器网络（也即是Zigbee）作为🌐物联网工程的一门必修专业课，具有很强的实用性，因此很多院校都开设了zigbee的实训课程；👑同时最近很多使用了我的单片机课…
阅读更多...
计算机网络——10FTP

计算机网络——10FTP

FTP FTP&#xff1a;文件传输协议 向远程主机上传输文件或从远程主机接收文件客户/服务器模式 客户端&#xff1a;发起传输的一方服务器&#xff1a;远程主机 ftp:RFC 959ftp服务器&#xff1a;端口号为21 FTP&#xff1a;控制连接与数据连接分开 控制连接 FTP客户端与FTP服…
阅读更多...
[FFmpeg学习]从视频中获取图片

[FFmpeg学习]从视频中获取图片

从视频中获取图片是一个比较直观的例子&#xff0c;这里从一个基础的例子来查看FFmpeg相关api的使用&#xff0c;从mp4文件中获取一帧图像&#xff0c;保存为jpeg格式图片&#xff0c;mp4文件比较好准备&#xff0c;一般手机录屏文件就是mp4格式。 原理还是比较清楚&#xff0…
阅读更多...
【杂谈】扣子(Coze) 初体验

【杂谈】扣子(Coze) 初体验

扣子(Coze)是什么 官方原文如下&#xff1a; 扣子&#xff08;coze.cn&#xff09;是一款用来开发新一代 AI Chat Bot 的应用编辑平台&#xff0c;无论你是否有编程基础&#xff0c;都可以通过这个平台来快速创建各种类型的 Chat Bot&#xff0c;并将其发布到各类社交平台和通…
阅读更多...
B2084 质因数分解

B2084 质因数分解

题目描述 已知正整数 n 是两个不同的质数的乘积&#xff0c;试求出较大的那个质数。 输入格式 输入只有一行&#xff0c;包含一个正整数 n&#xff08;6<n<&#xff09;。 输出格式 输出只有一行&#xff0c;包含一个正整数 p&#xff0c;即较大的那个质数。 输入输…
阅读更多...
Java 基于 SpringBoot+Vue 的社区医院系统

Java 基于 SpringBoot+Vue 的社区医院系统

博主介绍&#xff1a;✌程序员徐师兄、7年大厂程序员经历。全网粉丝12w、csdn博客专家、掘金/华为云/阿里云/InfoQ等平台优质作者、专注于Java技术领域和毕业项目实战✌ &#x1f345;文末获取源码联系&#x1f345; &#x1f447;&#x1f3fb; 精彩专栏推荐订阅&#x1f447;…
阅读更多...
《Java 简易速速上手小册》第8章：Java 性能优化（2024 最新版）

《Java 简易速速上手小册》第8章：Java 性能优化（2024 最新版）

文章目录 8.1 性能评估工具 - 你的性能探测仪8.1.1 基础知识8.1.2 重点案例&#xff1a;使用 VisualVM 监控应用性能8.1.3 拓展案例 1&#xff1a;使用 JProfiler 分析内存泄漏8.1.4 拓展案例 2&#xff1a;使用 Gatling 进行 Web 应用压力测试 8.2 JVM 调优 - 魔法引擎的调校8…
阅读更多...
最新文章

Copyright @ 2022~2023