【HIVE】(01)学会使用JOIN语句

系列文章回顾
【HIVE】(01)学会使用JOIN语句
【HIVE】(02)学会理解explain

---

---

一.Hive支持的Join语法

Hive sql只支持 等值连接，不支持 非等值连接 以及 or连接(而 标准sql是支持 非等值连接的，hive sql不支持 非等值连接的原因是，通过mapreduce很难实现这种类型的连接)。

1.inner join(内连接)

取存储2个表中都存在的数据;

2.left outer join(左外连接)

左边的所有记录都保留。

3.right outer join(右外连接）

右边的所有记录都保留

4.full outer join(完全外连接)

5.left semi join（左半开连接）

功能类似于inner join，都会返回2个表中都存在的数据，但是 由于 left semi join的运行机制是对于左表中一条指定的记录，在右边表中一旦找到匹配的记录，hive就会立即停止扫描。因此，left semi join和inner join的主要区别有2个：一是 left semi join更高效；二是，同样在右表有重复记录的情况下，inner join会返回重复记录，left semi join只保留一条记录（因此一旦找到就停止扫描）。

6.笛卡尔积join.

所谓的笛卡尔积join就是 在join后不加 on条件。
如果设置属性 hive.mapred.mode=strict,则hive会阻止用户执行笛卡尔积查询。

7.map-side join

什么时候使用map-side join呢？当2张表进行join时，其中有1张表是小表时，那么可以将小表存入内存中，然后hive可以在map端执行连接过程，即hive可以和内存中的小表进行逐一匹配，从而省略掉常规连接操作所需要的reduce过程。
示例：

select /* + mapjoin(t1) */ 
t1.col1,t2.col1 as t2_col1
from t1
join
t2
on t1.id=t2.id;

用户也可以配置能够使用这个优化的小表的大小，使用命令set hive.mapjoin.smalltable.filesize=25000000。其中right outer join 和 full outer join不支持这个优化。

二.关于join中表的顺序

在hive sql中进行join操作都优先将 按照 表的大小 从左到右依次增加，即大表在后。或者，通过“标记”机制(/*+streamtable (t1) * /) 来显式地告诉查询优化器哪张表是大表。
示例：

select +streamtable (t1)  
t1.number,t2.id 
from std_data t1 
join id_data t2 
where t1.number=t2.number;   

三.join中的模糊匹配（locate)

hive中的join模糊匹配locate，可以实现类似不等连接。
1）用法
locate(substr, str)函数，如果str中包含substr，则返回>0的数，否则返回0.

示例：

• 原始会报错的不等连接代码

select * from table1
right join table2
on table2.x like concat('%', table2.y,'%')

• 应该改成

select * from table1 
right join table2
on (TRUE)
where locate(table1.y, table2.x);

上述更改中，会先进行笛卡尔积，然后在table2.x中查找 table1.y，可以找到的记录保留，不能找到的记录舍弃。则实现了不等连接 table2.x like concat(’%’, table2.y,’%’)

四.总结

以上就是今天要讲的内容，本文仅仅简单介绍了JOIN的常见使用方法，更多的使用可查看官方文档https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins。