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/386470.html

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

相关文章

课时27：内容格式化_输入格式化_EOF原理

课时27：内容格式化_输入格式化_EOF原理

3.2.1 EOF原理 学习目标 这一节，我们从 基础知识、简单实践、小结 三个方面来学习。 基础知识 场景需求 在运维岗位中，有非常多的场景需要我们在脚本中编写应用软件的配置文件。在这些应用软件的配置文件中，经常携带大量的格式&#xff0…
阅读更多...
第四篇【传奇开心果微博系列】Python微项目技术点案例示例：美女颜值判官

第四篇【传奇开心果微博系列】Python微项目技术点案例示例：美女颜值判官

传奇开心果微博系列 系列微博目录Python微项目技术点案例示例系列 微博目录一、微项目目标二、雏形示例代码三、扩展思路四、添加不同类型的美女示例代码五、增加难度等级示例代码六、添加特殊道具示例代码七、设计关卡系统示例代码八、添加音效和背景音乐示例代码九、多人游戏…
阅读更多...
一文看懂春晚刘谦魔术

一文看懂春晚刘谦魔术

魔术步骤 step1: 准备4张牌，跟随魔术步骤，见证奇迹 step2: 将4张牌平均斯成两份，并叠在一起 step3: 将牌堆顶数量为名字字数的牌移到牌堆底 step4: 将前三张牌放在牌堆中间并取出牌堆顶的一张牌放到屁股下 step5: 南方人、北方人、不确定分别取顶上的1/2/3张牌插入牌堆…
阅读更多...
Stable Diffusion主流UI详细介绍

Stable Diffusion主流UI详细介绍

Stable Diffusion目前主流的操作界面有WebUI、ComfyUI以及Fooocus 这里webui和fooocus在人机交互上的逻辑是一样的，fooocus界面更加简洁。 comfyui是在人机交互上是采用流程节点的交互逻辑，和上面略有区别。 界面分别如下： WebUI界面如下 we…
阅读更多...
C/C++内存管理：new、delete功能及原理实现

C/C++内存管理：new、delete功能及原理实现

目录 一、C/C内存分布 二、C中内存管理方式 2.1new/delete操作内置类型 2.2 new和delete操作自定义类型 三、operator new与operator delete函数 四、new和delete的实现原理 4.1内置类型 4.2自定义类型 五、定位new 一、C/C内存分布 int globalVar 1; static int sta…
阅读更多...
HarmonyOS鸿蒙学习基础篇 - Column/Row 组件

HarmonyOS鸿蒙学习基础篇 - Column/Row 组件

前言 Row和Column组件是线性布局容器，用于按照垂直或水平方向排列子组件。Row表示沿水平方向布局的容器，而Column表示沿垂直方向布局的容器。这些容器具有许多属性和方法，可以方便地管理子组件的位置、大小、间距和对齐方式。例如&#xff0c…
阅读更多...
四、案例 - Oracle数据迁移至MySQL

四、案例 - Oracle数据迁移至MySQL

Oracle数据迁移至MySQL 一、生成测试数据表和数据1.在Oracle创建数据表和数据2.在MySQL创建数据表 二、生成模板文件1.模板文件内容2.模板文件参数详解2.1 全局设置2.2 数据读取（Reader）2.3 数据写入（Writer）2.4 性能设置 三、案例…
阅读更多...
博主用树莓派绕过 Windows Bitlocker 加密，用时不到一分钟

博主用树莓派绕过 Windows Bitlocker 加密，用时不到一分钟

近日 YouTube 博主 stacksmashing 发现 Bitlocker 存在一个巨大的安全漏洞，他利用价值不到 10 美元的树莓派 Pico 在不到一分钟内成功绕过了该加密。 2 月 7 日消息，微软 Windows 10 和 11 专业版内置的 Bitlocker 加密功能一直被认为是方便易用的安全解…
阅读更多...
伦敦金是现货黄金吗？

伦敦金是现货黄金吗？

伦敦金属现货黄金交易的一种形式。投资者可以通过伦敦金市场直接买卖黄金，以实现投资收益。伦敦金市场具有高度流动性和透明度，是全球投资者广泛参与的贵金属交易市场之一。 什么是现货黄金？ 现货黄金是指实物黄金的交易，投资者可…
阅读更多...
编辑器的新选择（基本不用配置）

编辑器的新选择（基本不用配置）

Cline 不用看网上那些教程Cline几乎不用配置。 点击设置直接选择Chinese, C直接在选择就行了。 Cline是一个很好的编辑器，有很多懒人必备的功能。 Lightly 这是一个根本不用配置的C编辑器。 旁边有目录，而且配色也很好，语言标准可以自己…
阅读更多...
优先级队列(堆)_PriorityQueue

优先级队列(堆)_PriorityQueue

前言 想要看如何使用可以通过目录跳转到 PriorityQueue的使用 优先级队列 概念 队列是一种先进先出(FIFO)的数据结构，但有些情况下，操作的数据可能带有优先级，一般出队 列时，可能需要优先级高的元素先出队列，该中场…
阅读更多...
【Vue】工程化开发脚手架Vue CLI

【Vue】工程化开发脚手架Vue CLI

📝个人主页：五敷有你 🔥系列专栏：Vue⛺️稳重求进，晒太阳 工程化开发&脚手架Vue CLI 基本介绍 Vue Cli是Vue官方提供的一个全局命令工具 可以帮助我们快速创建一个开发Vue项目的标准化基础架子【集成了we…
阅读更多...
鼠标和键盘

鼠标和键盘

阅读更多...
【教程】MySQL数据库学习笔记（二）——数据类型（持续更新）

【教程】MySQL数据库学习笔记（二）——数据类型（持续更新）

写在前面： 如果文章对你有帮助，记得点赞关注加收藏一波，利于以后需要的时候复习，多谢支持！ 【MySQL数据库学习】系列文章 第一章 《认识与环境搭建》 第二章 《数据类型》 文章目录 【MySQL数据库学习】系列文章一、整…
阅读更多...
MySQL表的基础操作

MySQL表的基础操作

创建表 create table 表名（列名 类型，列名 类型……） 注意 1.在进行表操作之前都必须选中数据库 2.表名，列名等一般不可以与关键字相同，如果确定相同，就必须用反引号引住 3.可以使用comment来增加字段说…
阅读更多...
【Langchain Agent研究】SalesGPT项目介绍（三）

【Langchain Agent研究】SalesGPT项目介绍（三）

【Langchain Agent研究】SalesGPT项目介绍（二）-CSDN博客 上节课，我们介绍了salesGPT项目的初步的整体结构，poetry脚手架工具和里面的run.py。在run.py这个运行文件里，引用的最主要的类就是SalesGPT类，今天我…
阅读更多...
云原生容器化-4 Docker仓库

云原生容器化-4 Docker仓库

1.Docker仓库 1.1 Docker Hub docker仓库用于存放docker镜像，可以分为公用和私有两种。Docker Hub是全球公用的仓库，因服务器在国外，国内基本不可以；一般需要配置阿里、腾讯等加速器。公司内部而言，可以搭建私有的Do…
阅读更多...
【牛客面试必刷TOP101】Day19.BM24 二叉树的中序遍历和BM26 求二叉树的层序遍历

【牛客面试必刷TOP101】Day19.BM24 二叉树的中序遍历和BM26 求二叉树的层序遍历

作者简介：大家好，我是未央； 博客首页：未央.303 系列专栏：牛客面试必刷TOP101 每日一句：人的一生，可以有所作为的时机只有一次，那就是现在！！！&…
阅读更多...
寒假作业-day11

寒假作业-day11

1>编程实现二维数组的杨辉三角 2>编程实现二维数组计算每一行的和以及列和 3>编程实现二维数计算第二大值 代码&#xff1a; #include<stdio.h> #include<stdlib.h> #include<string.h>void yanghui(int n){int arr[n][n];for (int i 0; i <…
阅读更多...
【力扣】5.最长回文子串

【力扣】5.最长回文子串

这道题我主要是通过动态规划来进行解题&#xff0c;看了我好久&#xff08;解析&#xff09;&#xff0c;生疏了呀。 首先就是判断一个字符串是不是回文&#xff0c;我们可以设置两个指针&#xff0c;从前往后进行判断即可&#xff0c;运用暴力解题法&#xff0c;这里运用的动…
阅读更多...
最新文章

Copyright @ 2022~2023