《离线和实时大数据开发实战》（五）Hive 优化实践_大数据hive离线计算开发实战(1)

jarckv

于 2024-05-10 20:35:21 发布

阅读量793

点赞数 19

分类专栏：程序员文章标签：大数据面试学习

本文链接：https://blog.csdn.net/jarckv/article/details/138680744

版权

程序员专栏收录该内容

59 篇文章 0 订阅

订阅专栏

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，涵盖了95%以上大数据知识点，真正体系化！

由于文件比较多，这里只是将部分目录截图出来，全套包含大厂面经、学习笔记、源码讲义、实战项目、大纲路线、讲解视频，并且后续会持续更新

需要这份系统化资料的朋友，可以戳这里获取

Hive 的优化正是采用各种措施和方法对上述场景的倾斜问题进行优化和处理。

二、Hive 的优化

其实在实际 Hive SQL 开发的过程中， Hive SQL 性能的问题上实际只有一小部分和数据倾相关。

很多时候， Hive SQL 运行得慢是由开发人员对于使用的数据了解不够以及一些不良的使用习惯引起的。

开发人员要确定以下几点：

需要计算的指标真的需要从数据仓库的公共明细层来自行汇总么？ 是不是数据公共层团队开发的公共汇总层已经可以满足自己的需求？对于大众的、 KPI 相关的指标等通常设计良好的数据仓库公共层肯定已经包含了，直接使用即可。
真的需要扫描这么多分区么？ 比如对于销售明细事务表来说，扫描一年的分区和扫描一周的分区所带来的计算、 IO 开销完全是两个量级，所耗费的时间肯定也是不同的。作为开发人员，我们需要仔细考虑业务的需求，尽量不要浪费计算和存储资源！
尽量不要使用 select * from your_table 这样的方式，用到哪些列就指定哪些列。 如 select coll, col2 from your_table ，另外， where 条件中也尽量添加过滤条件，以去掉无关的数据行，从而减少整个 MapReduce 任务中需要处理、分发的数据量。
输入文件不要是大量的小文件。 Hive 的默认 Input Split 是 128MB （可配置），小文件可先合并成大文件。

在保证了上述几点之后，有的时候发现 Hive SQL 还是要运行很长时间，甚至运行不出来，这时就需要真正的 Hive 优化技术了！

三、Join 无关的优化

Hive SQL 性能问题基本上大部分都和 join 相关，对于和 join 无关的问题主要有 groupby 相关的倾斜和 count distinct 相关的优化。

3.1 group by 引起的倾斜优化

group by 引起的倾斜主要是输入数据行按照 group by 列分布不均匀 引起的。

比如，假设按照供应商对销售明细事实表来统计订单数，那么部分大供应商的订单量显然非常多，而多数供应商的订单量就一般，由于 group by 的时候是按照供应商的 ID 分发到每个 Reduce Task ，那么此时分配到大供应商的 Reduce Task 就分配了更多的订单，从而导致数据倾斜。

对于 group by 引起的倾斜，优化措施非常简单，只需设置下面参数即可：

set hive.map.aggr = true 
set hive.groupby.skewindata=true

此时 Hive 在数据倾斜的时候会进行负载均衡，生成的查询计划会有两个 MapReduce Job。

第一个 MapReduce Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个Reduce 做部分聚合操作并输出结果。这样处理的结果是相同的 GroupBy Key 有可能被分布到不同的 Reduce 中，从而达到负载均衡的目的；
第二个 MapReduce Job 再根据预处理的数据结果，按照 GroupBy Key 分布到 Reduce 中（这过程可以保证相同的GroupBy Key被分布到同一个Reduce中），最后完成最终的聚合操作。

3.2 count distinct 优化

在 Hive 开发过程中，应该小心使用 count distinct ，因为很容易引起性能问题，比如下面的 SQL：

select count(distinct user) from some_table

由于必须去重，因此 Hive 将会把 Map 阶段的输出全部分布到 Reduce Task 上，此时很容易引起性能问题。对于这种情况，可以通过先 group by 再 count 的方式来优化，优化后的 SQL 如下：

select count(\*) from (
	select user 
	from some_table 
	group by user
	) tmp;

原理为：先利用 group by 去重，再统计 group by 的行数目。

四、大表 Join 小表优化

join 相关的优化主要分为 mapjoin 可以解决的优化（即大表 join 小表）和 mapjoin 无法解决的优化（即大表 join 大表）。大表 join 小表相对容易解决，大表 join 大表相对复杂和难以解决，但也不是不可解决的，只是相对比较麻烦而已。

首先介绍大表 join 小表优化。仍以销售明细事实表为例来说明大表 join 小表的场景。

假如供应商会进行评级，比如（五星、四星、两星、一星），此时业务人员希望能够分析各供应商星级的每天销售情况及其占比。

开发人员一般会写出如下 SQL:

select Seller_srar, count(order_id) as ordre_cnt
from (
	select order_id,seller_id 
    from dwd_sls_fact_detail_table
    where partition_value ='20170101'
) a
Left outer join(
	select seller_id,seller_star
    from dim_seller
    where partition_value='20170101'
) b
on a.seller_id = b.seller_id
group by b.seller_star;

但正如上述所言，现实世界的二八准则将导致订单集中在部分供应商上，而好的供应商的评级通常会更高，此时更加剧了数据倾斜的程度。如果不加以优化，上述 SQL 将会耗费很长时间，甚至运行不出结果！

通常来说，供应商是有限的，比如上千家、上万家，数据量不会很大，而销售明细事实表比较大，这就是典型的大表 join 小表问题，可以通过 mapjoin 的方式来优化，只需添加 mapjoin hint 即可，优化后的 SQL 如下：

select /\*+mapjoin(b)\*/ Seller_srar, count(order_id) as ordre_cnt
from (
	select order_id,seller_id 
    from dwd_sls_fact_detail_table
    where partition_value ='20170101'
) a
Left outer join(
	select seller_id,seller_star
    from dim_seller
    where partition_value='20170101'
) b
on a.seller_id = b.seller_id
group by b.seller_star;

/*+mapjoin(b)*/ 即 mapjoin himt，如果需要 mapjoin 多个表，则格式为/*+mapjoin(b,c,d)*/ 。

Hive 对于 mapjoin 是默认开启的，设置参数为：

Set hive.auto.convert.join=ture;

mapjoin 优化是在 Map 阶段进行 join ，而不是像通常那样在 Reduce 阶段按照 join 列进行分发后在每个 Reduce 任务节点上进行 join ，不需要分发也就没有倾斜的问题，相反 Hive 会将小表全量复制到每个 Map 任务节点（对于本例是 dim_seller ，当然仅全量复制 b表 sql 指定的列），然后每个 Map 任务节点执行 lookup 小表即可。

从上述分析可以看出，小表不能太大，否则全量复制分发得不偿失。

实际上 Hive 根据参数 hive.mapjoin.smalltable.filesize ( 0.11.0 本后是 hive.auto.convert.join.noconditionaltask.size ）来确定小表的大小是否满足条件（默认 25M）。
实际中此参数值所允许的最大值可以修改，但是一般最大不能超过 1GB （太大的话 Map 任务所在的节点内存会撑爆， Hive 会报错。另外需要注意的是， HDFS 显示的文件大小是压缩后的大小，当实际加载到内存的时候，容量会增大很多，很多场景下可能会膨胀 10 倍）。

五、大表 Join 大表优化

如果上述 mapjoin 中小表 dim_seller 很大呢？比如超过了 1GB 的大小？这种就是大表join 大表的问题。

这类问题相对比较复杂，我们首先引入具体的问题场景，然后基于此介绍各种优化方案。

5.1 问题场景

我们先假设一个问题场景：

A 表为一个汇总表，汇总的是卖家买家最近 N 天交易汇总信息，即对于每个卖家最近 N 天，其每个买家共成交了多少单、总金额是多少，我们这里 N 先只取 90 天，汇总值仅取成交单数。A 表的字段有：buyer_id 、seller_id 和 pay_cnt_90d 。

B 表为卖家基本信息表，其中包含卖家的一个分层评级信息，比如把卖家分为 6 个级别： S0、S1、S2、S3、S4、S5、S6 。

要获得的结果是每个买家在各个级别卖家的成交比例信息，比如：

某买家 S0:10%; S1:20%; S2:20%; S3:10%; S4:20%; S4:10%; S5:10%。

B表的字段有： seller_id 和 s_level。

正如 mapjoin 中的例子一样，我们的第一反应是直接 join 表并统计：

select 
m.buyer_id 
,sum(pay_cnt_90d) as pay_cnt_90d 
,sum(case when m.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0 
,sum(case when m.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl 
,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2 
,sum(case when m.s level=3 then pay cnt 90d end) as pay_cnt_90d_s3
,sum(case when m.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4 
,sum(case when m.s_level=S then pay_cnt_90d end) as pay_cnt_90d_s5 
from 
(
select 
a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d 
from 
(
select buyer_id ,seller_id,pay_cnt_90d 
from table A 
) a 
join 
(
select seller_id,s_level 
from table B 
) b 
on a.seller_id=b.seller_id 
) m 
group by m.buyer_id

但是此 SQL 会引起数据倾斜，原因在于卖家的二八准则。某些卖家 90 天内会有几百万甚至上千万的买家，但是大部分卖家 90 天内的买家数目并不多， join table_A 和table_B 的时候 ODPS 会按照 Seller_id 进行分发， table_A 的大卖家引起了数据倾斜。

但是本数据倾斜问题无法用 mapjoin table_B 解决，因为卖家有超过千万条、文件大小几个GB ，超过了 mapjoin 表最大 1GB 的限制。

方案 1：转化为 mapjoin

大表无法直接mapjoin，那么是否可以间接呢？实际上此思路有两种途径：限制行和限制列。

限制行: 不需要join B全表，只需要join其在A表中存在的。对于本问题场景，就是过滤掉 90 天内没有成交的卖家。
限制列: 只取需要的字段。

select 
m.buyer_id 
,sum(pay_cnt_90d) as pay_cnt_90d 
,sum(case when m.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0 
,sum(case when m.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl 
,sum(case when m.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2 
,sum(case when m.s level=3 then pay cnt 90d end) as pay_cnt_90d_s3
,sum(case when m.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4 
,sum(case when m.s_level=S then pay_cnt_90d end) as pay_cnt_90d_s5 
from 
(
select /\*+mapjoin(b)\*/
a.buyer_id,a.seller_id,b.s_level,a.pay_cnt_90d 
from 
(
select buyer_id ,seller_id,pay_cnt_90d 
from table_A 
) a 
join 
(
select b0.seller id,s_level 
from table_B b0
    join
    (select seller_id from table_A group by seller_id) a0
    on b0.seller_id=a0.seller_id
) b 
on a.seller_id=b.seller_id 
) m 
group by m.buyer_id

此方案在一些情况下可以起作用，但很多时候还是无法解决上述问题，因为大部分卖家尽管 90 买家不多，但还是有一些的，过滤后的 B
表仍然很大。

方案 2：join 时用 case when 语句

应用场景为: 倾斜的值是明确的而且数量很少，比如null值引起的倾斜。

将这些引起倾斜的值随机分发到Reduce，其主要核心逻辑在于 join 时对这些特殊值concat 随机数,从而达到随机分发的目的。核心逻辑如下：

Select a.user_id,a.order_id,b.user_id 
From table_a a 
Join table_b b 
On (case when a.user_id is null then concat ('hive' ,rand()) else a.user_id end)=b.user_id

Hive已对此进行了优化，不需要修改SQL，只需要设置参数；比如 table_B 的值 “0” 和 “1” 引起倾斜，只需要如下设置：

set hive.optimize.skewinfo=table_B:(seller_id)[("0")("1")];


![img](https://img-blog.csdnimg.cn/img_convert/ab881a9a811b10e12fae927aafe58632.png)
![img](https://img-blog.csdnimg.cn/img_convert/13fc1930750c96f06e581f75e8b27258.png)

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**

[("0")("1")];


[外链图片转存中...(img-GXPOAsXc-1715344490257)]
[外链图片转存中...(img-jF7dKr0N-1715344490257)]

**网上学习资料一大堆，但如果学到的知识不成体系，遇到问题时只是浅尝辄止，不再深入研究，那么很难做到真正的技术提升。**

**[需要这份系统化资料的朋友，可以戳这里获取](https://bbs.csdn.net/forums/4f45ff00ff254613a03fab5e56a57acb)**


**一个人可以走的很快，但一群人才能走的更远！不论你是正从事IT行业的老鸟或是对IT行业感兴趣的新人，都欢迎加入我们的的圈子（技术交流、学习资源、职场吐槽、大厂内推、面试辅导），让我们一起学习成长！**