《离线和实时大数据开发实战》_Hive优化实践_读书笔记_离线和实时大数据开发实战电子书下载-CSDN博客

本文链接：https://blog.csdn.net/Imflash/article/details/100128266

本文介绍了Hive在处理离线数据时遇到的主要挑战——数据倾斜，以及对应的优化策略，包括group by倾斜优化、count distinct优化、大表join小表优化和大表join大表的多种解决方案，旨在提升Hive查询效率。

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

Hive优化实践

1. 离线数据处理的主要挑战: 数据倾斜

"倾斜"指统计学历的偏态分布;
所谓偏态分布, 即统计数据峰值与平均值不相等的频率分布
根据峰值小于或大于平均值可分为正偏函数和负偏函数,其偏离的程度可用偏态系数刻画;
Hive的优化正是采用各种措施和方法对上述场景的倾斜问题进行优化和处理

在这里插入图片描述

2. Hive使用方面的优化

对于大众的, KPI相关的指标等通常设计良好的数据仓库公共层肯定已经包含了,直接使用即可,不需要自行汇总
扫描的范围尽量缩小,节省计算和存储资源
尽量不要使用*,要查哪列就指定哪些列
输入文件不要是大量的小文件;Hive的默认Input Split是128MB,小文件可以先合并成大文件;
使用Hive优化技术

3. join无关的优化

3.1 group by引起的倾斜优化

主要是输入数据行按照group by列分布不均匀引起的;
- 比如按照供应商对销售明细事实表来统计订单数,那么部分大供应商的订单量显然非常多,而多数的订单量就一般
- 由于group by的时候是按照供应商的ID分发到每个Reduce Task,那么此时分配到大供应商的Reduce Task就分配了更多的订单,从而导致数据倾斜
- 对于group by的优化措施
```
--是否在Map端进行聚合，默认为true
set hive.map.aggr = true;
-- 有数据倾斜的时候进行负载均衡，默认是false
set hive.groupby.skewindata = true;
--还可以设置Map端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval=100000;
```
  - 此时Hive在数据倾斜的时候会进行负载均衡,生成的查询集合会有两个MapReduce Job;
    - 第一MapReduce Job中,Map的输出结果集合会随机分布到Reduce中,每个Reduce做部分聚合操作并输出结果,这样处理的结果是相同的GroupBy Key有可能被分布到不同的Reduce中,从而达到负载均衡的目的;
    - 第二个MapReduce Job再根据预处理的数据结果按照GroupBy Key分布到Reduce中(这过程可以保证相同的GroupBy Key被分布到同一个Reduce中),最后完成最终的聚合操作

3.2 count distinct优化

count distinct需要小心使用,很容易引发性能问题,如下

select count(distinct user) from some_table

由于必须去重,因此Hive将会把Map阶段的输出全部分布到一个Reduce Task上,很容易引起性能问题,可以这样优化

select count(*) from (
	select user from some_table group by user
	) tmp;
--原理为：先利用group by去重,再统计group by的行数目;

4. 大表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;
```
  - 添加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 v0.7之前，需要使用hint提示 /*+ mapjoin(table) */才会执行MapJoin 。Hive v0.7之后的版本已经不需要给出MapJoin的指示就进行优化
  - Hive对于mapjoin是默认开启的（最好查一下）,设置参数为:
```
Set hive.auto.convert.join=ture;
--设置大表小表的阀值为25M（根据需要修改）
```
set hive.mapjoin.smalltable.filesize=25123456;
```
- mapjoin优化是在Map节点进行join,而不是像通常那样在Reduce阶段按照join列进行分发后再每个Reduce任务节点上进行join,不需要分发也就没有倾斜的问题;
- Hive会将小表全量复制到每个Map任务节点(仅复制select指定的列),然后每个Map任务节点进行lookup小表即可;
- 小表不能太大,数 hive.mapjoin.smalltable.filesize (0.11.0 版本后是hive.auto.convert.join.noconditionaltask.size 来确定小表的大小是否满足条件(默认25MB),可以修改;但是最大不能超过1GB（HDFS 显示的文件大小是压缩后的大小，当实际加载到内存的时候，容量会增大很多，很多场景下可能会膨胀倍）；

5. 大表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处理了

5.1 方案1: 转化为mapjoin

大表无法直接mapjoin,那么可以间接

限制行: 不需要join B全表,而只需要join其在A表中存在的
限制列: 只取需要的字段

限制行和列后,满足mapjoin条件即可；伪代码如下

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天内无买家，基本不成立。

5.2 方案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")];
set hive.optimize.skewjoin=true;
--但是方案二还是不能解决上述问题，因为倾斜的卖家大量存在而且动态变化。

5.3 方案3: 倍数B表,再取模join

通用方案

建立一个numbers表,其值只有一列int行,比如从1到10(具体根据倾斜程度确定),然后放大B表10倍,再取模join;

思路核心在于:既然按照seller_id分发会倾斜,那么再人工增加一列进行分发,这样之前倾斜的值的倾斜程度会减少为原来的1/10；代码如下：

select 
m,buer_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 so 
,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 /*+mapjoin(members)*/ 
seller_id,s_level,member 
from table_B 
join 
numbers 
) b 
on a.seller_id=b.seller_id 
and mod(a.pay_cnt_90d,10)+1=b.number 
) m 
group by m.buyer_id
--可以通过配置numbers表修改放大倍数来降低倾斜程度，但弊端就是B表会膨胀N倍

专有方案
- 通用方案思路是把B表的每条数据都放大了相同的倍数，实际上只需要把大卖家放大倍数即可：
  - 首先需要知道大卖家的名单，即先建立一个临时表动态存放每日最新的大卖家（比如dim_big_seller），同时此表的大卖家要膨胀预先设定的倍数（比如1000倍）。
  - 在A表和 B表中分别新建一个 join 列，其逻辑为：如果是大卖家，那么 concat 一个随
    机分配正整数（0到预定义的倍数之间，本例为0~ 1000 ）；如果不是，保持不变；

在这里插入图片描述

5.4 方案4: 动态一分为二

即对倾斜的键值和不倾斜的键值分开处理,不倾斜的正常join即可,倾斜的把它们找出来然后做mapjoin,最后union all其结果即可

在这里插入图片描述

select 
m.buyer_id 
,surn(pay_cnt_90d) as pay_cnt_90d 
,surn(case when rn.s_level=O then pay_cnt_90d end) as pay_cnt_90d_s0 
,surn(case when rn.s_level=l then pay_cnt_90d end) as pay_cnt_90d_sl 
,surn(case when rn.s_level=2 then pay_cnt_90d end) as pay_cnt_90d_s2 
,surn(case when rn.s_level=3 then pay_cnt_90d end) as pay_cnt_90d_s3 
,surn(case when rn.s_level=4 then pay_cnt_90d end) as pay_cnt_90d_s4 
,surn(case when rn.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 ,a.s_level
        from table_A a
        left outer join tmp_table_B b
        on a.user_id = b.seller_id
        where b.seller_id is null
    ) b 
on a.seller id=b.seller id 
union all 
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 seller_id,s_level 
from table B 
) b 
on a.seller id=b.seller id
) m group by m.buyer_id
) m
group by m.byer_id