Hive SQL的各种join总结

说明

Hive join语法有6中连接

inner join(内连接)、left join(左连接)、right join(右连接)、full outer join(全外连接)、left semi join(左半开连接)、cross join(交叉连接,也叫做笛卡尔乘积)。

参考网址:从零开始学大数据(二十九):HQL join连接查询,Hive参数配置 - 知乎

Hive join使用注意事项:LanguageManual Joins - Apache Hive - Apache Software Foundation

数据准备

表1:employee 员工表

表2:employee_address 员工住址信息表

表3:employee_connection 员工联系方式表

--table1: 员工表
CREATE TABLE employee(
id int,
name string,
deg string,
salary int,
dept string
) row format delimited
fields terminated by ',';

--table2:员工住址信息表
CREATE TABLE employee_address (
id int,
hno string,
street string,
city string
) row format delimited
fields terminated by ',';

--table3:员工联系方式表
CREATE TABLE employee_connection (
id int,
phno string,
email string
) row format delimited
fields terminated by ',';--加载数据到表中
load data local inpath '/root/hivedata/employee.txt' into table employee;
load data local inpath '/root/hivedata/employee_address.txt' into table employee_address;
load data local inpath '/root/hivedata/employee_connection.txt' into table employee_connection;

语法说明

Hive inner join

内连接是最常见的一种连接,它也被称为普通连接,而关系模型提出者E.FCodd(埃德加•科德)最早称之为自然连接。其中inner可以省略。inner join == join 等价于早期的连接语法。

内连接,只有进行连接的两个表中都存在与连接条件相匹配的数据才会被留下来。

即,取交集数据

--1、inner join
select e.id,e.name,e_a.city,e_a.street
from employee e inner join employee_address e_a
on e.id =e_a.id;
--等价于 inner join=join
select e.id,e.name,e_a.city,e_a.street
from employee e join employee_address e_a
on e.id =e_a.id;

--等价于 隐式连接表示法
select e.id,e.name,e_a.city,e_a.street
from employee e , employee_address e_a
where e.id =e_a.id;

Hive left join

left join中文叫做是左外连接(Left Outer Jion)或者左连接,其中outer可以省略,left outer join是早期的写法。

left join的核心就在于left左。左指的是join关键字左边的表,简称左表。

通俗解释:join时以左表的全部数据为准,右边与之关联;左表数据全部返回,右表关联上的显示返回,关联不上的显示null返回。

即:取左表所有数据,及右表与左表交集数据

注意空值处理

--2、left join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left join employee_connection e_conn
on e.id =e_conn.id;

--等价于 left outer join
select e.id,e.name,e_conn.phno,e_conn.email
from employee e left outer join employee_connection e_conn
on e.id =e_conn.id;

Hive right join

right join中文叫做是右外连接(Right Outer Jion)或者右连接,其中outer可以省略。

right join的核心就在于Right右。右指的是join关键字右边的表,简称右表。

通俗解释:join时以右表的全部数据为准,左边与之关联;右表数据全部返回,左表关联上的显示返回,关联不上的显示null返回。

很明显,right join和left join之间很相似,重点在于以哪边为准,也就是一个方向的问题。

Hive full outer join

full outer join 等价 full join ,中文叫做全外连接或者外连接。

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

注意空值处理

--4、full outer join
select e.id,e.name,e_a.city,e_a.street
from employee e full outer join employee_address e_a
on e.id =e_a.id;
--等价于
select e.id,e.name,e_a.city,e_a.street
from employee e full join employee_address e_a
on e.id =e_a.id;

Hive left semi join

左半开连接(LEFT SEMI JOIN)会返回左边表的记录,前提是其记录对于右边的表满足ON语句中的判定条件。

从效果上来看有点像inner join之后只返回左表的结果。

Hive cross join

交叉连接cross join,将会返回被连接的两个表的笛卡尔积,返回结果的行数等于两个表行数的乘积。对于大表来说,cross join慎用

在SQL标准中定义的cross join就是无条件的inner join。返回两个表的笛卡尔积,无需指定关联键。

在HiveSQL语法中,cross join 后面可以跟where子句进行过滤,或者on条件过滤。

--6、cross join
--下列A、B、C 执行结果相同,但是效率不一样:
--A:
select a.*,b.* from employee a,employee_address b where a.id=b.id;
--B:
select * from employee a cross join employee_address b on a.id=b.id;select * from employee a cross join employee_address b where a.id=b.id;
--C:
select * from employee a inner join employee_address b on a.id=b.id;

--一般不建议使用方法A和B,因为如果有WHERE子句的话,往往会先进行笛卡尔积返回数据然后才根据WHERE条件从中选择。
--因此,如果两个表太大,将会非常非常慢,不建议使用。

Hive join使用注意事项

1. 如果每个表在联接子句中使用相同的列,则Hive将多个表上的联接转换为单个MR作业

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
--由于联接中仅涉及b的key1列,因此被转换为1个MR作业来执行SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
--会转换为两个MR作业,因为在第一个连接条件中使用了b中的key1列,而在第二个连接条件中使用了b中的key2列。
第一个map / reduce作业将a与b联接在一起,然后将结果与c联接到第二个map / reduce作业中。

join时的最后一个表会通过reducer流式传输,并在其中缓冲之前的其他表,因此,将大表放置在最后有助于减少reducer阶段缓存数据所需要的内存

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
--由于联接中仅涉及b的key1列,因此被转换为1个MR作业来执行,并且表a和b的键的特定值的值被缓冲在reducer的内存中。
然后,对于从c中检索的每一行,将使用缓冲的行来计算联接。
SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)
--计算涉及两个MR作业。其中的第一个将a与b连接起来,并缓冲a的值,同时在reducer中流式传输b的值。
在第二个MR作业中,将缓冲第一个连接的结果,同时将c的值通过reducer流式传输。

在join的时候,可以通过语法STREAMTABLE提示指定要流式传输的表。如果省略STREAMTABLE提示,则Hive将流式传输最右边的表。

SELECT /*+ STREAMTABLE(a) */ a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key1)
--a,b,c三个表都在一个MR作业中联接,并且表b和c的键的特定值的值被缓冲在reducer的内存中。
然后,对于从a中检索到的每一行,将使用缓冲的行来计算联接。
如果省略STREAMTABLE提示,则Hive将流式传输最右边的表。

join在WHERE条件之前进行。

SELECT /*+ MAPJOIN(b) */ a.key, a.value FROM a JOIN b ON a.key = b.key
--不需要reducer。对于A的每个Mapper,B都会被完全读取。
限制是不能执行FULL / RIGHT OUTER JOIN b。

如果除一个要连接的表之外的所有表都很小,则可以将其作为仅map作业执行

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

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

相关文章

批量免费AI写作工具,批量免费AI写作软件

人工智能(AI)的应用在各个领域不断创新。面对繁重的写作任务,我们应该怎么完成?本文将专心分享批量免费AI写作的方法、工具以及选择时需要注意的事项。 批量免费AI写作的方法 利用开源AI模型 一种常见的批量免费AI写作方法是利用开源的AI模…

CUDA简介——CUDA内存模式

1. 引言 前序博客: CUDA简介——基本概念CUDA简介——编程模式CUDA简介——For循环并行化CUDA简介——Grid和Block内Thread索引 CUDA内存模式,采用分层设计,是CUDA程序与正常C程序的最大不同之处: Thread-Memory Correspondenc…

《数字中台建设总体方案》

《数字中台建设总体方案》 制定数字中台战略规划:制定符合企业实际情况的数字中台战略规划,明确建设目标、重点任务和时间表。确定数字中台架构:根据企业业务需求和特点,确定数字中台的架构,包括技术架构、应用架构和数…

vFW搭建IRF

正文共:2328字 40图,预估阅读时间:5 分钟 IRF(Intelligent Resilient Framework,智能弹性架构)技术通过将多台设备连接在一起,虚拟化成一台设备,集成多台设备的硬件资源和软件处理能…

Selenium自动化测试技巧还不知道吗?

1、前言 与以前瀑布式开发模式不同,现在软件测试人员具有使用自动化工具执行测试用例套件的优势,而以前,测试人员习惯于通过测试脚本执行来完成测试。 但自动化测试的目的不是完全摆脱手动测试,而是最大程度地减少手动运行的测试…

玻色量子计算应用奖公布!MathorCup大赛圆满落幕

2023年8月15日,中国优选法统筹法与经济数学研究会主办的2023年第十三届MathorCup高校数学建模挑战赛圆满落幕。为了培养优秀学子的创新意识及运用数学方法和量子计算技术解决实际问题的能力,推动量子计算的实际落地应用,北京玻色量子科技有限…

计算机网络:可靠数据传输(rdt)、流水协议、窗口滑动协议

文章目录 前言一、Rdt1.Rdt1.02.Rdt2.03.Rdt2.14.Rdt2.25.Rdt3.0 二、流水线协议1.滑动窗口(slide window)协议发送窗口接收窗口正常情况下的2个窗口互动异常情况下GBN的2个窗口互动异常情况下SR的2窗口互动GBN协议和SR协议的异同 2.小结 总结 前言 Rdt…

Linux之重谈文件和c语言文件接口

重谈文件 文件 内容 属性, 所有对文件的操作都是: a.对内容操作 b.对属性操作 关于文件 一: 即使文件的内容为空,该文件也会在磁盘上也会占空间,因为文件不仅仅只有内容还有文件对应的属性,文件的内容会占用空间, 文件的属性也…

Leetcode每日一题学习训练——Python3版(从二叉搜索树到更大和树)

版本说明 当前版本号[20231204]。 版本修改说明20231204初版 目录 文章目录 版本说明目录从二叉搜索树到更大和树理解题目代码思路参考代码 原题可以点击此 1038. 从二叉搜索树到更大和树 前去练习。 从二叉搜索树到更大和树 给定一个二叉搜索树 root (BST),请…

麦田医学CEO参加2023年度苏州市青年创业标兵授奖仪式

麦田医学CEO参加2023年度苏州市青年创业标兵授奖仪式 2023年12月4日,麦田(苏州)医学科技有限公司(下简称麦田医学)首席执行官(CEO)李任远同志受邀参加在苏州广电总台举行的2023年度苏州大学生创…

如何在亚马逊平台上找到最畅销的产品

这是您掌握在全球最大的在线市场上销售艺术的首选资源。在本博客中,我们将深入探讨在英国亚马逊上查找最畅销产品的秘密。在浩瀚的选择海洋中导航可能会令人不知所措,但不要害怕——有了正确的策略,您就可以发现利润丰厚的机会并提高销售额。…

C++ day53 最长公共子序列 不相交的线 最大子序和

题目1:1143 最长公共子序列 题目链接:最长公共子序列 对题目的理解 返回两个字符串的最长公共子序列的长度,如果不存在公共子序列,返回0,注意返回的是最长公共子序列,与前一天的最后一道题不同的是子序…

uniapp 在线预览PDF

1、官网地址&#xff1a; https://mozilla.github.io/pdf.js/getting_started/ 2、解压文件到static中 3、定义查看组件FilePreview <template><view><web-view :src"allUrl"></web-view></view> </template><script> e…

(04730)电路分析基础之电阻、电容及电感元件

04730电子技术基础 语雀&#xff08;完全笔记&#xff09; 电阻元件、电感元件和电容元件的概念、伏安关系&#xff0c;以及功率分析是我们以后分析电 路的基础知识。 电阻元件 电阻及其与温度的关系 电阻 电阻元件是对电流呈现阻碍作用的耗能元件&#xff0c;例如灯泡、…

mongoose学习记录

mongoose安装和连接数据库 npm i mongoose导入mongoose const mongoose require(mongoose) mongoose.set("strictQuery",true)连接数据库 mongoose.connect(mongodb:127.0.0.1:27017/test)设置回调 mongoose.connection.on(open,()>{console.log("连接成…

弱口令防护和网站防盗链有什么用

弱口令防护主要针对用户账户的安全。弱口令是指容易被猜测或破解的密码&#xff0c;如常见的密码、简单的数字序列或字典中的单词等。弱口令防护的目的是防止恶意用户或攻击者通过猜测或暴力破解密码的方式获取合法用户的账户权限。通过实施强密码策略、密码复杂度要求和账户锁…

centos 7.9 二进制部署 kubernetes 1.27.7

文章目录 1. 预备条件2. 基础配置2.1 配置root远程登录2.2 配置主机名2.3 安装 ansible2.4 配置互信2.5 配置hosts文件2.6 关闭防firewalld火墙2.7 关闭 selinux2.8 关闭交换分区swap2.9 修改内核参数2.10 安装iptables2.11 开启ipvs2.12 配置limits参数2.13 配置 yum2.14 配置…

【JavaEE进阶】 Spring核⼼与设计思想

文章目录 &#x1f332;Spring 是什么&#xff1f;&#x1f384;什么是IoC呢&#xff1f;&#x1f388;传统程序开发&#x1f388;传统程序开发的缺陷&#x1f388;如何解决传统程序的缺陷&#xff1f;&#x1f388;控制反转式程序开发&#x1f388;对⽐总结规律 &#x1f340;…

十二、FreeRTOS之FreeRTOS任务相关API函数

本节需要掌握以下内容&#xff1a; 1&#xff0c;FreeRTOS任务相关API函数介绍&#xff08;熟悉&#xff09; 2&#xff0c;任务状态查询API函数实验&#xff08;掌握&#xff09; 3&#xff0c;任务时间统计API函数实验&#xff08;掌握&#xff09; 4&#xff0c;课堂总结…

【鸿蒙应用开发】开发环境搭建及IDE安装使用

1.下载安装包 安装包下载地址&#xff1a; 点击跳转下载页面 可以根据自己的操作系统选择对应版本下载。 本文以Windows安装为例&#xff0c;Mac安装方式相同 2. 安装 下载好后&#xff0c;打开安装包&#xff0c;进入安装界面&#xff1a; 点击Next&#xff0c;进入安…