(08)Hive——Join连接、谓词下推

news/2024/11/29 9:46:22/

前言

  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 ； 只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

selectt1.user_id,t1.name,t1.gender,t2.subject,t2.score
from studentInfo t1inner join studentScore t2 on t1.user_id = t2.user_id

1.2 left join 左外连接

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

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

1.3 right join 右外连接

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

selectt2.user_id,t2.subject,t2.score,t1.user_id,t1.name,t1.gender
from studentInfo t1right  join studentScore t2on t1.user_id = t2.user_id;

1.4 full join 满外连接

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

selectt1.user_id,t1.name,t1.gender,t2.user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on t1.user_id = t2.user_id;

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

selectt1.user_id,t1.name,t1.gender,t2.user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on t1.user_id = t2.user_id;----- 等价于下述代码selectt1.user_id as t1_user_id ,t1.name,t1.gender,t2.user_id as  t2_user_id,t2.subject,t2.score
from studentInfo t1left  join studentScore t2on t1.user_id = t2.user_id
union
selectt1.user_id as t1_user_id ,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1right  join studentScore t2on 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条件过滤。

---举例：
selectt1.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--- 等价于：
selectt1.user_id as t1_user_id ,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1join studentScore t2---等价于：
selectt1.user_id as t1_user_id ,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1cross  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
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1inner 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
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1inner 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
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1inner 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
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1inner 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
selectt1.user_id,t1.name,t1.gender,t2.user_id,t2.subject,t2.score
from studentInfo t1left  join studentScore t2on t1.user_id = t2.user_id
where t1.user_id >2;

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

  • 对右表where过滤
explain
selectt1.user_id,t1.name,t1.gender,t2.user_id,t2.subject,t2.score
from studentInfo t1left  join studentScore t2on 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 
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1left join studentScore t2on t1.user_id = t2.user_id and t1.user_id >2

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

 

  • 对右表on过滤
explain
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1left join studentScore t2on 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 
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on t1.user_id = t2.user_id
where  t1.user_id >2 ;

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

 

  • 对右表where过滤
explain
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on 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
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on t1.user_id = t2.user_id and t1.user_id >2;

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

  • 对右表on过滤
explain
selectt1.user_id as t1_user_id,t1.name,t1.gender,t2.user_id as t2_user_id,t2.subject,t2.score
from studentInfo t1full  join studentScore t2on 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博客


http://www.ppmy.cn/news/1353624.html

相关文章

Spring Boot 笔记 010 创建接口_更新用户头像

Spring Boot 笔记 010 创建接口_更新用户头像

1.1.1 usercontroller中添加updateAvatar，校验是否为url PatchMapping("updateAvatar")public Result updateAvatar(RequestParam URL String avatarUrl) {userService.updateAvatar(avatarUrl);return Result.success();} 1.1.2 userservice //更新头像…
阅读更多...
文件系统常识

文件系统常识

文件系统常识 认识文件路径 文件类型的分类 认识文件 对于计算机来说，“文件” 是一个广义的概念，分为： 硬盘上普通的文件硬盘上的 ”目录“（即文件夹，目录是专业用语）被操作系统 “抽象” 成了文件的硬件…
阅读更多...
GPT-4带来的思想火花

GPT-4带来的思想火花

GPT-4能够以其强大的生成能力和广泛的知识储备激发出众多思想火花。它能够在不同的情境下生成新颖的观点、独特的见解和富有创意的解决方案，这不仅有助于用户突破思维定势，还能促进知识与信息在不同领域的交叉融合。 1.GPT-4出色的创新思考和知识整合能…
阅读更多...
Mysql第一关之常规用法

Mysql第一关之常规用法

简介 介绍Mysql常规概念，用法。包括DDL、DCL、DML、DQL，关键字、分组、连表、函数、排序、分页等。 一、 SQL DCMQ，分别代表DDL、DCL、DML、DQL。 模糊简记为DCMQ，看起来像一个消息队列。 D：Definition 定义语句 M…
阅读更多...
JDBC 核心 API

JDBC 核心 API

引入 mysql-jdbc 驱动 驱动 jar 版本的选择：推荐使用 8.0.25，省略时区设置java 工程导入依赖 项目创建 lib 文件夹导入驱动依赖 jar 包jar 包右键 - 添加为库 JDBC 基本使用步骤 注册驱动获取连接创建发送 sql 语句对象发送 sql 语句，并获…
阅读更多...
【天幕系列 02】开源力量：揭示开源软件如何成为技术演进与社会发展的引擎

【天幕系列 02】开源力量：揭示开源软件如何成为技术演进与社会发展的引擎

文章目录 导言01 开源软件如何推动技术创新1.1 开放的创新模式1.2 快速迭代和反馈循环1.3 共享知识和资源1.4 生态系统的建设和扩展1.5 开放标准和互操作性 02 开源软件的商业模式2.1 支持和服务模式2.2 基于订阅的模式2.3 专有附加组件模式2.4 开源软件作为平台模式2.5 双重许…
阅读更多...
相机图像质量研究(22)常见问题总结：CMOS期间对成像的影响--光学串扰

相机图像质量研究(22)常见问题总结：CMOS期间对成像的影响--光学串扰

系列文章目录 相机图像质量研究(1)Camera成像流程介绍 相机图像质量研究(2)ISP专用平台调优介绍 相机图像质量研究(3)图像质量测试介绍 相机图像质量研究(4)常见问题总结：光学结构对成像的影响--焦距 相机图像质量研究(5)常见问题总结：光学结构对成…
阅读更多...
洛谷：P1331 海战

洛谷：P1331 海战

题目描述 在一个方形的盘上，放置了固定数量和形状的船只，每只船却不能碰到其它的船。在本题中，我们认为船是方形的，所有的船只都是由图形组成的方形。 求出该棋盘上放置的船只的总数。 输入格式 第一行为两个整数 R 和 C&…
阅读更多...
最新文章

Copyright @ 2022~2023