Hive的三种Join方式

本文介绍了Hive中几种常见的Join优化方法,包括ReduceJoin、MapJoin、SMB Join等,并详细解释了各种Join的适用场景及如何配置参数来提高查询性能。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

Hive中就是把Map,Reduce的Join拿过来,通过SQL来表示。
参考链接:https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Joins

Common/Shuffle/Reduce Join

Reduce Join在Hive中也叫Common Join或Shuffle Join
如果两边数据量都很大,它会进行把相同key的value合在一起,正好符合我们在sql中的join,然后再去组合,如图所示。

Reduce Join

Map Join

1) 大小表连接:

如果一张表的数据很大,另外一张表很少(<1000行),那么我们可以将数据量少的那张表放到内存里面,在map端做join。
Hive支持Map Join,用法如下

select /*+ MAPJOIN(time_dim) */ count(1) from
store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

2) 需要做不等值join操作(a.x < b.y 或者 a.x like b.y等)

这种操作如果直接使用join的话语法不支持不等于操作,hive语法解析会直接抛出错误
如果把不等于写到where里会造成笛卡尔积,数据异常增大,速度会很慢。甚至会任务无法跑成功~
根据mapjoin的计算原理,MapJoin会把小表全部读入内存中,在map阶段直接拿另外一个表的数据和内存中表数据做匹配。这种情况下即使笛卡尔积也不会对任务运行速度造成太大的效率影响。
而且hive的where条件本身就是在map阶段进行的操作,所以在where里写入不等值比对的话,也不会造成额外负担。

select /*+ MAPJOIN(a) */
a.start_level, b.*
from dim_level a
join (select * from test) b
where b.xx>=a.start_level and b.xx<end_level;

3) MAPJOIN 结合 UNIONALL
原始sql:

select a.*,coalesce(c.categoryid,’NA’) as app_category
from (select * from t_aa_pvid_ctr_hour_js_mes1
) a
left outer join
(select * fromt_qd_cmfu_book_info_mes
) c
on a.app_id=c.book_id;

速度很慢,老办法,先查下数据分布:

select *
from
(selectapp_id,count(1) cnt
fromt_aa_pvid_ctr_hour_js_mes1
group by app_id) t
order by cnt DESC
limit 50;

数据分布如下:

NA      617370129
2       118293314
1       40673814
d       20151236
b       1846306
s       1124246
5       675240
8       642231
6       611104
t       596973
4       579473
3       489516
7       475999
9       373395
107580  10508

我们可以看到除了NA是有问题的异常值,还有appid=1~9的数据也很多,而这些数据是可以关联到的,所以这里不能简单的随机函数了。而t_qd_cmfu_book_info_mes这张app库表,又有几百万数据,太大以致不能放入内存使用mapjoin。

解决方:首先将appid=NA和1到9的数据存入一组,并使用mapjoin与维表(维表也限定appid=1~9,这样内存就放得下了)关联,而除此之外的数据存入另一组,使用普通的join,最后使用union all 放到一起。

select a.*,coalesce(c.categoryid,’NA’) as app_category
from --if app_id isnot number value or <=9,then not join
(select * fromt_aa_pvid_ctr_hour_js_mes1
where cast(app_id asint)>9
) a
left outer join
(select * fromt_qd_cmfu_book_info_mes
where cast(book_id asint)>9) c
on a.app_id=c.book_id
union all
select /*+ MAPJOIN(c)*/
a.*,coalesce(c.categoryid,’NA’) as app_category
fromif app_id<=9,use map join
(select * fromt_aa_pvid_ctr_hour_js_mes1
where coalesce(cast(app_id as int),-999)<=9) a
left outer join
(select * fromt_qd_cmfu_book_info_mes
where cast(book_id asint)<=9) c
--if app_id is notnumber value,then not join
on a.app_id=c.book_id

设置:

当然也可以让hive自动识别,把join变成合适的Map Join如下所示
注:当设置为true的时候,hive会自动获取两张表的数据,判定哪个是小表,然后放在内存中

set hive.auto.convert.join=true;
select count(*) from store_sales join time_dim on (ss_sold_time_sk = t_time_sk)

SMB(Sort-Merge-Buket) Join

场景:

大表对小表应该使用MapJoin,但是如果是大表对大表,如果进行shuffle,那就要人命了啊,第一个慢不用说,第二个容易出异常,既然是两个表进行join,肯定有相同的字段吧。

tb_a - 5亿(按排序分成五份,每份1亿放在指定的数值范围内,类似于分区表)
a_id
100001 ~ 110000 - bucket-01-a -1亿
110001 ~ 120000
120001 ~ 130000
130001 ~ 140000
140001 ~ 150000

tb_b - 5亿(同上,同一个桶只能和对应的桶内数据做join)
b_id
100001 ~ 110000 - bucket-01-b -1亿
110001 ~ 120000
120001 ~ 130000
130001 ~ 140000
140001 ~ 150000

注:实际生产环境中,一天的数据可能有50G(举例子可以把数据弄大点,比如说10亿分成1000个bucket)。

原理:

在运行SMB Join的时候会重新创建两张表,当然这是在后台默认做的,不需要用户主动去创建,如下所示:

SMB Join

设置(默认是false):

set hive.auto.convert.sortmerge.join=true
set hive.optimize.bucketmapjoin=true;
set hive.optimize.bucketmapjoin.sortedmerge=true;

总结:

其实在写程序的时候,我们就可以知道哪些是大表哪些是小表,注意调优。

转自:http://www.cnblogs.com/raymoc/p/5323824.html

<think>嗯,我现在需要了解一下Hive中有哪几种Join方式。以前学过SQL里的Join,但Hive是基于Hadoop的,可能有些不同。让我先回忆一下。 首先,Hive处理的是大数据,所以Join方式可能要考虑数据量和性能。常见的Join类型有内连接、左连接、右连接、全外连接,这些在Hive里应该也是存在的。不过Hive作为数据仓库工具,可能还有一些特定的优化方式,比如Map Join或者Reduce Join? 对了,HiveJoin实现可能分为几种不同的执行引擎,比如MapReduce或者Tez。不同的引擎可能影响Join方式。比如Map Join可能是在Map阶段完成,不需要Reduce阶段,适合小表和大表连接的情况。 然后,Hive中有分桶表,可能分桶Join也是一种方式。分桶后相同桶的数据更容易Join,减少数据移动,提高效率。这个是不是叫做Bucket Map Join? 还有,HiveJoin是否支持倾斜数据优化?比如当某个键的值特别多时,可能用Skew Join来处理数据倾斜的问题,避免某些Reducer负载过重。 另外,Hive文档里提到的Common Join,也就是普通的Reduce Side Join,这种可能需要Shuffle过程,将相同的键发送到同一个Reducer处理。这可能在处理大表和大表连接时使用,但效率可能不高。 那总结起来的话,HiveJoin方式可能有以下几种: 1. Inner Join、Left Join、Right Join、Full Outer Join这些基本的连接类型。 2. 执行方式上的不同,比如Map Join(Broadcast Join)、Reduce Join(Common Join)。 3. 针对分桶表的Bucket Map Join。 4. 处理数据倾斜的Skew Join。 5. 还有可能SMB Join(Sort Merge Bucket Join),需要分桶且排序的情况下使用,这样在Join时可以直接归并,减少计算量。 不过需要确认这些是否都是Hive支持的。比如Map Join是否自动触发,还是需要设置参数,比如hive.auto.convert.join=true。当小表符合条件时会自动转为Map Join,避免Shuffle。 还有,Skew Join可能需要设置hive.optimize.skewjoin=true,这样在遇到倾斜键时会进行特殊处理,比如将倾斜键的数据分到多个Reducer。 那在回答的时候,应该按照Hive中不同的Join分类方式来介绍,包括连接类型和执行优化类型。可能需要分开说明,比如连接类型有内连接、左外连接等,而执行方式或优化策略包括Map Join、Reduce Join、Bucket Join、Skew Join等。 还要注意Hive不同版本可能支持的Join方式不同,比如某些特性在较新的版本中才有。比如SMB Join可能需要表都分桶并且排序,并且桶的数量成倍数关系,这样在Join时可以直接合并,提高效率。 所以最终的分类可能包括: 1. 按连接结果分类: - 内连接(INNER JOIN) - 左外连接(LEFT OUTER JOIN) - 右外连接(RIGHT OUTER JOIN) - 全外连接(FULL OUTER JOIN) - 交叉连接(CROSS JOIN,笛卡尔积) 2. 按执行方式/优化策略分类: - Common Join(Reduce Join):常规的Shuffle-based Join - Map Join(Broadcast Join):小表广播到所有Map任务 - Bucket Map Join:利用分桶特性优化 - Sort Merge Bucket (SMB) Join:分桶且排序后的高效Join - Skew Join:处理数据倾斜的优化方式 可能还有其他的,比如Left Semi Join用来替代IN EXISTS子查询,不过这个属于连接类型,而不是执行方式。 需要确保这些分类正确,以及每种方式的适用场景。比如Map Join适用于小表和大表连接,Bucket Map Join需要两个表在连接键上都分桶,并且小表的桶数量是大表的倍数,这样可以直接对应桶进行Join,而不必全量Shuffle。 另外,Hive是否支持其他类型的Join,比如Anti Join(排除匹配项)?可能用LEFT JOIN加上WHERE NULL来实现,但Hive是否有直接的语法?可能不是单独的Join类型,而是通过组合实现。 在回答时,应该明确说明不同分类下的具体Join方式,并简要说明其原理和使用场景。这样用户能清晰了解Hive中的多种Join方式及其适用情况。</think>Hive 支持多种 Join 方式,这些方式既包括标准 SQL 的连接类型,也包含针对大数据场景的优化策略。以下是具体分类和说明: --- ### 一、按连接结果分类 1. **内连接(INNER JOIN)** 返回两个表中匹配的行。 示例: ```sql SELECT * FROM table_a a JOIN table_b b ON a.key = b.key; ``` 2. **左外连接(LEFT OUTER JOIN)** 返回左表所有行,右表无匹配时填充 `NULL`。 示例: ```sql SELECT * FROM table_a a LEFT JOIN table_b b ON a.key = b.key; ``` 3. **右外连接(RIGHT OUTER JOIN)** 返回右表所有行,左表无匹配时填充 `NULL`。 示例: ```sql SELECT * FROM table_a a RIGHT JOIN table_b b ON a.key = b.key; ``` 4. **全外连接(FULL OUTER JOIN)** 返回两表所有行,无匹配部分填充 `NULL`。 示例: ```sql SELECT * FROM table_a a FULL OUTER JOIN table_b b ON a.key = b.key; ``` 5. **交叉连接(CROSS JOIN)** 返回两表的笛卡尔积(慎用,数据量爆炸)。 示例: ```sql SELECT * FROM table_a CROSS JOIN table_b; ``` 6. **左半连接(LEFT SEMI JOIN)** 返回左表中在右表存在匹配的行(类似 `IN` 或 `EXISTS` 子查询)。 示例: ```sql SELECT * FROM table_a a LEFT SEMI JOIN table_b b ON a.key = b.key; ``` --- ### 二、按执行策略分类(优化方式) 1. **Common Join(Reduce Join)** - **原理**:通过 MapReduce 的 Shuffle 阶段将相同键的数据分发到同一 Reducer,完成连接。 - **场景**:默认方式,适合大表间连接。 - **缺点**:Shuffle 可能导致性能瓶颈。 2. **Map Join(Broadcast Join)** - **原理**:将小表广播到所有 Map 任务内存中,直接在 Map 阶段完成连接,避免 Shuffle。 - **场景**:小表(默认 ≤25MB)与大表连接。 - **配置**: ```sql SET hive.auto.convert.join=true; -- 自动转换 SET hive.mapjoin.smalltable.filesize=25000000; -- 调整小表阈值 ``` 3. **Bucket Map Join** - **原理**:若两个表均按连接键分桶且桶数量成倍数,可避免全表 Shuffle,直接按桶匹配。 - **场景**:分桶表间连接,且桶数量匹配。 - **要求**: - 两表均为分桶表。 - 大表的桶数量是小表的整数倍。 - **配置**: ```sql SET hive.optimize.bucketmapjoin=true; ``` 4. **Sort Merge Bucket (SMB) Join** - **原理**:在分桶基础上,每个桶内部按连接键排序,归并时直接顺序读取,无需比较。 - **场景**:分桶且排序的大表间连接,效率极高。 - **要求**: - 两表均分桶且排序。 - 桶数量相等。 - **配置**: ```sql SET hive.optimize.bucketmapjoin.sortedmerge=true; SET hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat; ``` 5. **Skew Join(倾斜连接)** - **原理**:对倾斜键(如 `NULL` 或高频值)单独处理,分散到多个 Reducer。 - **场景**:数据分布严重倾斜时避免长尾任务。 - **配置**: ```sql SET hive.optimize.skewjoin=true; SET hive.skewjoin.key=100000; -- 键出现次数超过此值则视为倾斜 ``` --- ### 三、选择策略建议 1. **小表 + 大表**:优先使用 **Map Join**。 2. **大表 + 大表**: - 若分桶且排序 → **SMB Join**。 - 若仅分桶 → **Bucket Map Join**。 - 否则 → **Common Join**,可尝试 **Skew Join** 优化倾斜。 3. **高频键倾斜**:启用 **Skew Join** 避免性能问题。 通过合理选择 Join 方式,可显著提升 Hive 查询性能,尤其是在处理大规模数据时。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值