(08)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/390844.html

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

相关文章

电商小程序08调用缓存

电商小程序08调用缓存

目录 1 将信息存入缓存中2 获取登录信息3 退出登录4 发布预览总结 小程序的登录功能里，如果只是将登录信息保存到全局变量中，存在的问题是如果小程序重新打开，用户的登录状态就丢失了。为了解决这个问题，我们需要用到微搭的缓存的…
阅读更多...
初识最短路径

初识最短路径

一.最短路径的介绍 最短路径是图论和网络分析中一个重要的概念，它指的是在一个图或网络中连接两个节点或顶点的路径中，具有最小权重总和的路径。这个权重可以表示为路径上边或弧的长度、耗费、时间等，具体取决于问题的背景和应用场景。 如果你…
阅读更多...
基于Spring Boot的古典舞在线交流平台设计与实现，计算机毕业设计(带源码+论文)

基于Spring Boot的古典舞在线交流平台设计与实现，计算机毕业设计(带源码+论文)

源码获取地址： 码呢-一个专注于技术分享的博客平台一个专注于技术分享的博客平台,大家以共同学习,乐于分享,拥抱开源的价值观进行学习交流http://www.xmbiao.cn/resource-details/1758349555560165377
阅读更多...
Compose 自定义 - 数据转UI的三阶段（组合、布局、绘制）

Compose 自定义 - 数据转UI的三阶段（组合、布局、绘制）

一、概念 Compose 通过三个阶段把数据转化为UI：组合（要显示什么）、布局（要显示在哪里）、绘制（如何渲染）。 组合阶段 Compisition 界面首次渲染时会将可组合函数转化为一个个布局节点 Layout Nod…
阅读更多...
NVIDIA Chat with RTX

NVIDIA Chat with RTX

NVIDIA在2月13日发布了Chat With RTX，这是一款类似于ChatGPT的免费个性化 AI 聊天机器人，可以在配备 Nvidia RTX 显卡的 PC 上本地运行。它使用Mistral或Llama开放权重LLM，可以搜索本地文件并回答有关它们的问题。本文中我们一起来了解一下Ch…
阅读更多...
【网络安全】什么样的人适合学？该怎么学？

【网络安全】什么样的人适合学？该怎么学？

有很多想要转行网络安全或者选择网络安全专业的人在进行决定之前一定会有的问题： 什么样的人适合学习网络安全？我适不适合学习网络安全？ 当然，产生这样的疑惑并不奇怪，毕竟网络安全这个专业在2017年才调整为国家一级…
阅读更多...
Arduino ESP8266/ESP32 TCP/UDP通讯例程

Arduino ESP8266/ESP32 TCP/UDP通讯例程

Arduino ESP8266/ESP32 TCP/UDP通讯例程 🔧需要配合上位机软件：网络调试助手（http://www.cmsoft.cn/software.html） 📝ESP8266/ESP32 作为TCP客户端使用 //要将ESP8266/32 Arduino TCPClient的调试输出发送到串口&am…
阅读更多...
OpenAI首个文生视频模型亮相，你觉得咋样？

OpenAI首个文生视频模型亮相，你觉得咋样？

2月16日凌晨，OpenAI再次扔出一枚深水炸弹，发布了首个文生视频模型Sora。据介绍，Sora可以直接输出长达60秒的视频，并且包含高度细致的背景、复杂的多角度镜头，以及富有情感的多个角色。 目前官网上已经更新了48个视频d…
阅读更多...
在Visual Studio中搭建Dynamo Python开发环境，效率飞一般的增长

在Visual Studio中搭建Dynamo Python开发环境，效率飞一般的增长

最近在学习Dynamo中Python Script的用法，发现这个东西用起来太不友好了，不支持自动缩进，不支持自动填充和提示。用过Visual Studio做二开的都知道，在引用了Revit api以后，就可以自动填充和提示了。 本来英语就不好&am…
阅读更多...
UI风格汇：毛玻璃风格风靡的原因解读

UI风格汇：毛玻璃风格风靡的原因解读

Hello，我是大千UI工场，设计风格是我们新开辟的栏目，主要讲解各类UI风格特征、辨识方法、应用场景、运用方法等，本次带来的是毛玻璃风格的解读，有设计需求可以私聊。 一、什么是毛玻璃风格 毛玻璃风格（Fros…
阅读更多...
Mysql5.6忘记密码，如何找回（windows）

Mysql5.6忘记密码，如何找回（windows）

mysql5.6安装 第一步：关闭正在运行的数据库服务 net stop mysql第二步：在my.ini文件当中的[mysqld] 任意一个位置放入 skip-grant-tables第三步：启动mysql服务 net start mysql第四步：服务启动成功后就可以登录了，…
阅读更多...
Typora＋PicGO＋腾讯云COS做图床教程

Typora＋PicGO＋腾讯云COS做图床教程

文章目录 Typora＋PicGO＋腾讯云COS做图床教程一、为什么使用图床二、Typora、PicGO和腾讯云COS介绍三、下载Typora和PicGOTyporaPicGO 四、配置Typora、PicGO和腾讯云COS腾讯云COS配置PicGO配置Typora配置 Typora＋PicGO＋腾讯云COS做…
阅读更多...
AI Infra论文阅读之LIGHTSEQ（LLM长文本训练的Infra工作）

AI Infra论文阅读之LIGHTSEQ（LLM长文本训练的Infra工作）

感觉这篇paper有几个亮点，首先把Megatron-LM的Self-Attention模块的模型并行方式变成序列并行，优化了通信量，同时通过计算和通信重叠近一步压缩了训练迭代时间。另外，在使用重计算的时候发现当前Huggingface/Megatron-LM的重计算策…
阅读更多...
电容充电速度

电容充电速度

对电容充电的过程中，电容器充电的电压为，求电容器的充电速度。
阅读更多...
【Algorithms 4】算法（第4版）学习笔记 08 - 3.1 符号表

【Algorithms 4】算法（第4版）学习笔记 08 - 3.1 符号表

文章目录 前言参考目录学习笔记1：API1.1：遵循的规则1.2：ST 用例举例1.2.1：行为测试用例1.2.2：性能测试用例2：基本实现2.1：无序链表处理2.2：初级ST实现小结2.3：有序数组的…
阅读更多...
2.14：二维数组、非函数实现strcat、strcmp、strcpy、strlen

2.14：二维数组、非函数实现strcat、strcmp、strcpy、strlen

1.编程实现二维数组的杨辉三角 程序代码&#xff1a; 1 #include<stdio.h>2 #include<string.h>3 #include<stdlib.h>4 int main(int argc, const char *argv[])5 {6 int n;7 printf("please enter n:");8 scanf("%d",&…
阅读更多...
Python四级考试笔记

Python四级考试笔记

Python四级考试笔记【源源老师】 四级标准 一、 理解函数及过程、函数的参数、函数的返回值、变量作用域等概念。 二、 能够创建简单的自定义函数。 三、 理解算法以及算法性能、效率的概念&#xff0c;初步认识算法优化 效率的方法。 四、 理解基本算法中递归的概念。 五、 掌…
阅读更多...
如何解决缓存和数据库的数据不一致问题

如何解决缓存和数据库的数据不一致问题

数据不一致问题是操作数据库和操作缓存值的过程中&#xff0c;其中一个操作失败的情况。实际上&#xff0c;即使这两个操作第一次执行时都没有失败&#xff0c;当有大量并发请求时&#xff0c;应用还是有可能读到不一致的数据。 如何更新缓存 更新缓存的步骤就两步&#xff0…
阅读更多...
Linux下解压tar.xz文件的命令

Linux下解压tar.xz文件的命令

tar -c: 建立压缩档案-x&#xff1a;解压-t&#xff1a;查看内容-r&#xff1a;向压缩归档文件末尾追加文件-u&#xff1a;更新原压缩包中的文件 ------------------------------------------ 这五个是独立的命令&#xff0c;压缩解压都要用到其中一个&#xff0c;可以和别的…
阅读更多...
谷歌学术引用无法显示，提示“偶尔出现错误，请F5刷新！”

谷歌学术引用无法显示，提示“偶尔出现错误，请F5刷新！”

如图&#xff0c;我想进行EndNote引用&#xff0c;总是出现提示“偶尔出现错误&#xff0c;请F5刷新&#xff01;” 就是一直在出现&#xff0c;根本无法下载引用的内容。 最后发现了原因&#xff1a;我是使用谷歌学术镜像进行搜索的&#xff0c;并不是在https://scholar.goog…
阅读更多...
最新文章

Copyright @ 2022~2023