大数据离线阶段 10：HQL join连接查询

悠悠 2023-10-15 18:12 23阅读 0赞

#### join概念回顾 ####

根据数据库的三范式设计要求和日常工作习惯来说，我们通常不会设计一张大表把所有类型的数据都放在一起，而是不同类型的数据设计不同的表存储。比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系。而不可以在订单表中添加关于客户其它信息（比如姓名、所属公司等）的字段。

![图片][8de6f07060e1873c724c90750ac42af9.png]

在这种情况下，有时需要基于多张表查询才能得到最终完整的结果，SQL中join语法的出现是用于根据两个或多个表中的列之间的关系，从这些表中共同组合查询数据，因此有时为了得到完整的结果，我们就需要执行 join。

Hive作为面向分析的数据仓库软件，为了更好的支持数据分析的功能丰富，也实现了join的语法，整体上来看和RDBMS中的join语法类似，只不过在某些点有自己的特色。需要特别注意。

--------------------

####  ####

#### Hive join语法 ####

在Hive中，当下版本3.1.2总共支持6种join语法。分别是：

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

####  ####

规则树

join\_table:  
table\_reference \[INNER\] JOIN table\_factor \[join\_condition\]  
| table\_reference \{LEFT|RIGHT|FULL\} \[OUTER\] JOIN table\_reference join\_condition  
| table\_reference LEFT SEMI JOIN table\_reference join\_condition  
| table\_reference CROSS JOIN table\_reference \[join\_condition\] (as of Hive 0.10)  
  
table\_reference:  
table\_factor  
| join\_table  
  
table\_factor:  
tbl\_name \[alias\]  
| table\_subquery alias  
| ( table\_references )  
  
join\_condition:  
ON expression

table\_reference：是join查询中使用的表名，也可以是子查询别名（查询结果当成表参与join）。

table\_factor：与table\_reference相同,是联接查询中使用的表名,也可以是子查询别名。

join\_condition：join查询关联的条件， 如果在两个以上的表上需要连接，则使用AND关键字。

####  ####

#### 语法丰富 ####

Hive中join语法从面世开始其实并不丰富，不像在RDBMS中那么灵活，很多早期接触Hive的用户在使用join的时候，一个最大的感受就是不支持不相等连接。

从Hive 0.13.0开始，支持隐式联接表示法（请参阅HIVE-5558）。这允许FROM子句连接以逗号分隔的表列表，而省略JOIN关键字。例如：

SELECT \*  
FROM table1 t1, table2 t2, table3 t3  
WHERE t1.id = t2.id AND t2.id = t3.id AND t1.zipcode = '02535';

从Hive 2.2.0开始，支持ON子句中的复杂表达式，支持不相等连接（请参阅HIVE-15211和HIVE-15251）。在此之前，Hive不支持不是相等条件的联接条件。

SELECT a.\* FROM a JOIN b ON (a.id = b.id)  
SELECT a.\* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)  
SELECT a.\* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

--------------------

####  ####

#### join查询数据环境准备 ####

为了更好的练习、学习掌握Hive中的join语法，下面我们去创建3张表并且加载数据到表中。

表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;

![图片][dff122823182e7c72ca802d875c4001d.png]

![图片][c3fbce9b5c75b4265eff75d225e9a783.png]

![图片][09adb31d6dfcdb45c1671a48524ec5db.png]

--------------------

####  ####

#### Hive inner join ####

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

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

![图片][20230724024159.png_origin_url_https_3A_2F_2Fmmbiz.qpic.cn_2Fsz_mmbiz_png_2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZS3nG33xiaKjuxOtPrKibPiacNicFNXVAF15BfNExNSLiaMCdO7G3RXXAVzQ_2F640_pos_id_NSyPLljV]

\--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;

![图片][8dceb14ea12cc586f4b9518d5a821b91.png]

####  ####

#### Hive left join ####

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

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

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

![图片][3ed02700885766c0a93801ce154c3bc3.png]

\--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;

![图片][9bd9ddb6572f34280ca5ef6f0f970d29.png]

####  ####

#### Hive right join ####

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

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

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

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

![图片][d185d11c74722f012206cd5db3ff07a2.png]

![图片][fb50455b6efa145e201a385f03d8ce71.png]

\--3、right join  
select e.id,e.name,e\_conn.phno,e\_conn.email  
from employee e right join employee\_connection e\_conn  
on e.id =e\_conn.id;  
  
\--等价于 right outer join  
select e.id,e.name,e\_conn.phno,e\_conn.email  
from employee e right outer join employee\_connection e\_conn  
on e.id =e\_conn.id;

####  ####

#### Hive full outer join ####

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

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

![图片][20230724024159.png_origin_url_https_3A_2F_2Fmmbiz.qpic.cn_2Fsz_mmbiz_png_2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZyrHgGeYBiaojM3iaVyia7aM8PHHQMyGEtPOlVJbPO2aWibnqeMmIh794LQ_2F640_pos_id_WneocBnj]

![图片][eeefbf8fa13329a954258b95001c9aa9.png]

\--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之后只返回左表的结果。

\--5、left semi join  
select \*  
from employee e left semi join employee\_address e\_addr  
on e.id =e\_addr.id;  
  
\--相当于 inner join 只不过效率高一些  
select e.\*  
from employee e inner join employee\_address e\_addr  
on e.id =e\_addr.id;

![图片][2c1f1fe938f1f1459dc863ebd599d433.png]

####  ####

#### 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使用注意事项 ####

总体来说，随着Hive的版本发展，join语法的功能也愈加丰富。当下我们课程使用的是3.1.2版本，有以下几点需要注意：

1.  允许使用复杂的联接表达式

SELECT a.\* FROM a JOIN b ON (a.id = b.id)  
SELECT a.\* FROM a JOIN b ON (a.id = b.id AND a.department = b.department)  
SELECT a.\* FROM a LEFT OUTER JOIN b ON (a.id <> b.id)

1.  同一查询中可以连接2个以上的表

SELECT a.val, b.val, c.val FROM a JOIN b ON (a.key = b.key1) JOIN c ON (c.key = b.key2)

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作业中。

1.  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流式传输。

1.  在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将流式传输最右边的表。

1.  join在WHERE条件之前进行。
2.  如果除一个要连接的表之外的所有表都很小，则可以将其作为仅map作业执行

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。

还有一些其他相关的使用注意事项，可以参考官方

https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

[8de6f07060e1873c724c90750ac42af9.png]: https://img-blog.csdnimg.cn/img_convert/8de6f07060e1873c724c90750ac42af9.png
[dff122823182e7c72ca802d875c4001d.png]: https://img-blog.csdnimg.cn/img_convert/dff122823182e7c72ca802d875c4001d.png
[c3fbce9b5c75b4265eff75d225e9a783.png]: https://img-blog.csdnimg.cn/img_convert/c3fbce9b5c75b4265eff75d225e9a783.png
[09adb31d6dfcdb45c1671a48524ec5db.png]: https://img-blog.csdnimg.cn/img_convert/09adb31d6dfcdb45c1671a48524ec5db.png
[20230724024159.png_origin_url_https_3A_2F_2Fmmbiz.qpic.cn_2Fsz_mmbiz_png_2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZS3nG33xiaKjuxOtPrKibPiacNicFNXVAF15BfNExNSLiaMCdO7G3RXXAVzQ_2F640_pos_id_NSyPLljV]: https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=https%3A%2F%2Fmmbiz.qpic.cn%2Fsz_mmbiz_png%2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZS3nG33xiaKjuxOtPrKibPiacNicFNXVAF15BfNExNSLiaMCdO7G3RXXAVzQ%2F640&pos_id=NSyPLljV
[8dceb14ea12cc586f4b9518d5a821b91.png]: https://img-blog.csdnimg.cn/img_convert/8dceb14ea12cc586f4b9518d5a821b91.png
[3ed02700885766c0a93801ce154c3bc3.png]: https://img-blog.csdnimg.cn/img_convert/3ed02700885766c0a93801ce154c3bc3.png
[9bd9ddb6572f34280ca5ef6f0f970d29.png]: https://img-blog.csdnimg.cn/img_convert/9bd9ddb6572f34280ca5ef6f0f970d29.png
[d185d11c74722f012206cd5db3ff07a2.png]: https://img-blog.csdnimg.cn/img_convert/d185d11c74722f012206cd5db3ff07a2.png
[fb50455b6efa145e201a385f03d8ce71.png]: https://img-blog.csdnimg.cn/img_convert/fb50455b6efa145e201a385f03d8ce71.png
[20230724024159.png_origin_url_https_3A_2F_2Fmmbiz.qpic.cn_2Fsz_mmbiz_png_2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZyrHgGeYBiaojM3iaVyia7aM8PHHQMyGEtPOlVJbPO2aWibnqeMmIh794LQ_2F640_pos_id_WneocBnj]: https://img-home.csdnimg.cn/images/20230724024159.png?origin_url=https%3A%2F%2Fmmbiz.qpic.cn%2Fsz_mmbiz_png%2FBuRsxQibf4JxdEjqgGa0ocliaol5NwpSLZyrHgGeYBiaojM3iaVyia7aM8PHHQMyGEtPOlVJbPO2aWibnqeMmIh794LQ%2F640&pos_id=WneocBnj
[eeefbf8fa13329a954258b95001c9aa9.png]: https://img-blog.csdnimg.cn/img_convert/eeefbf8fa13329a954258b95001c9aa9.png
[2c1f1fe938f1f1459dc863ebd599d433.png]: https://img-blog.csdnimg.cn/img_convert/2c1f1fe938f1f1459dc863ebd599d433.png