HIve数据倾斜原理以及治理方案

1.数据倾斜原理
1.1 MapReduce原理
MapReduce分为Map、Shuffle、Reduce三个阶段：
1， Map阶段完成数据分割(Split)和单Map内数合并与排序：
经过map方法后，map的输出映射为key,value，key,value会参与分区计算，拿着key算出分区号(根据key的hash值，对reduce个数取余)，最终map输出key,value,分区(partition)。
2， Shuffle跨节点洗牌；
3， Reduce完成夸节点的数据合并与单Reduce内数据排序
总结：
1） 数据倾斜是由于关联字段KRY值记录分布不均匀导致Reduce端倾斜；
2） MapReduce都是反复地执行排序，合并操作；
3） 1次MapReuce一般Map的数量>>Reduce的数量；
4） 同一段SQL中相同的关联字段(KEY)只执行1次MapReuce；
5） 可以通过参数控制Map和Reduce的个数增加并行度
1.2 MapReduce过程中数据会落磁盘
Maptask的输出是一个文件(中间数据集)，存在本地的文件系统中，也就是说中间数据集并不是存放到HDFS中的。
每个map处理之后的结果将会进入环形缓冲区。内存缓冲区是有大小限制的，默认是100MB。当map task的输出结果很多时，就可能会撑爆内存，所以需要在一定条件下将缓冲区中的数据临时写入磁盘，然后重新利用这块缓冲区。这个从内存往磁盘写数据的过程被称为spill，中文可理解为溢写。溢写是由单独线程来完成，不影响往缓冲区写map结果的线程。溢写线程启动时不应该阻止map的结果输出，所以整个缓冲区有个溢写的比例spill.percent。比例默认是0.8，也就是当缓冲区的数据值已经达到阈值(buffer size * spill percent = 100MB * 0.8 = 80MB)，溢写线程启动，锁定这80MB的内存，执行溢写过程。map task的输出结果还可以往剩下的20MB内存中写，互不影响。
当环形缓冲区达到阈值80%，开始将分区排序后的数据溢写磁盘变成文件，每次溢写会在磁盘上生成一个溢写文件，如果map的输出结果真的很大，有多次这样的溢写发生，磁盘上相应的就会有多个溢写文件存在。最终会产生多个小文件。
总结：
1， Mapjoin：小表是在Map端存储在内存中参与计算；
2， Mapjoin的默认参数25M指的是文件大小，如果数据载入内存超过100M，就不会执行Mapjoin，按照Commjoin执行；
3， Mapjoin的效率>>Commjoin的效率
4， 与SparkSQL差异：在内存中计算，中间不落磁盘，理论上采用SparkSQL进行数据探查效率更快，大批量处理Hive更稳定。
1.2 数据倾斜原理
1.2.1 Reduce端倾斜
通常说的数据倾斜是Reduce端倾斜，某个Reduce(或者节点)处理的数据量远大于其他Reduce(或者节点)。
注意点：

1. 只有Map因子，没有Reduce因子：
   类似于SparkSQL的只有转化（Transformation，窄依赖），没有行动（Action，宽依赖）
   Mapjoin：小表与小表关联、大表与小表关联(开启Mapjoin)
   Uinon All
   Filter：WHERE ……
   Map：INSERT OVERWRITE TABLE_A SELECT COLUMN FROM TABLE_B
2. 产生Reduce因子的操作
   Commjoin：大表与大表关联
   Goup by
   Sort by
   Order by
   Distinct
   Uion
3. 只有1个Reduce因子：全局排重需要在1个Reduce内完成，不同Reduce排重无法做到数据唯一
   Order by
   Distinct
   1.2.1 Map端倾斜
   某些Map处理的数据量太大积压：Map端倾斜发生比较少
   1， HDFS文件采用了lzo压缩，文件分割没识别，会按照文件个数启用Map导致某些Map处理数据量积压；
   2， 文件压缩比太高，导致单个Map处理的数据量太大：
4. 大量的重复记录；
5. 窄表(字段个数比较小)且都是编码
   总结：Map倾斜属于个例，特殊情况下会发生
   2 数据倾斜识别
   通过HIVE的执行日志可以识别数据倾斜，在执行日志中出现执行百分比进度等待的情况，表明出现了数据倾斜。
   Reduce端数据倾斜：等待34分钟：
   总结：
   1， 数据倾斜不一定任务会失败，但是会导致执行时长变长，浪费资源，严重情况会造成节点宕机；
   2， 不是所有的数据倾斜都需要优化，数据倾斜：2分钟
   3 数据倾斜优化
   3.1 SQL语法优化
   1, 禁用DISTINCT，使用相应的语法代替；
6. 根据数据粒度，使用窗口函数排重：
   ROW_NUMBER OVER(PARTION BY …… SORT BY )
7. 统计去重指标数：
   SELECT COUNT(CUST_ID)
   FROM (
   SELECT CUST_ID
   FROM TABLE_A
   GROUP BY STORE_ID,CUST_ID
   ) T1
   优化方案1：
   SELECT item_code
   ,0 --统计订单量
   ,COUNT(contract_no) --统计合同量
   FROM (
   SELECT item_code,contract_no
   FROM dwr_supply.dwd_so_order_2bp_crs_f T
   WHERE contract_no IS NOT NULL AND item_code IS NOT NULL
   GROUP BY item_code,contract_no)
   UNION ALL
   SELECT item_code
   ,COUNT(order_head_id) --统计订单量
   ,0 --统计合同数
   FROM (
   SELECT item_code,contract_no
   FROM dwr_supply.dwd_so_order_2bp_crs_f T
   WHERE contract_no IS NOT NULL AND item_code IS NOT NULL
   GROUP BY item_code,order_head_id)

优化方案2：
SELECT item_code
, size(collect_set(contract_no)) --统计合同量
, size(collect_set(order_head_id)) --统计订单量
FROM dwr_supply.dwd_so_order_2bp_crs_f T
WHERE contract_no IS NOT NULL AND item_code IS NOT NULL
GROUP BY item_code;
2，禁用UNION；
优化方案：
SELECT
COLUMN
FROM (
SELECT
COLUMN
FROM TABLE_A
UNION ALL
SELECT
COLUMN
FROM TABLE_B) T
GROUP BY COLUMN
3， 慎用ORDER BY，注意结合LIMIT；
4， GROUP BY倾斜：默认情况下，Map 阶段同一 Key 数据分发给一个 reduce，当一个 key 数据过大时就倾斜，比如双11热销商品；

1. 对最核心字段，预先通过hash取模进一步对数据进行分组，减少单个Reduce处理的数据量。
   SELECT
   ITEM_CODE
   ,SUM(X_SUM)
   FROM (
   SELECT ITEM_CODE
   ,pmod(hash(ITEM_CODE),10) AS HMOD
   ,SUM(1) AS X_SUM
   FROM DWR_SUPPLY.dwd_so_order_2bp_crs_f
   GROUP BY ITEM_CODE
   ,pmod(hash(ITEM_CODE),10) AS HMOD --1个Reduce拆分为10个
   )
   GROUP BY ITEM_CODE;
2. 通过参数优化，效果不一定理想
   为了解决group by数据倾斜的情况,可以采用Map端数据聚合的操作。
   （1）是否在 Map 端进行聚合，默认为 True，
   set hive.map.aggr = true
   （2）在 Map 端进行聚合操作的条目数目

set hive.groupby.mapaggr.checkinterval = 100000
（3）有数据倾斜的时候进行负载均衡（默认是 false）
set hive.groupby.skewindata = true
当选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出 结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果 是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二 个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证 相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。
3.2 默认值倾斜优化
主表直接或者间接关联KEY的默认值或者空值记录数非常大，导致1个Reduce处理记录量过多。
解决方案：识别关联KEY的默认值或者空值，针对默认值或者空值进行随机数打散
示例：NVL(t1.s_lot_id,concat(‘HONOR-’,rand()))—针对空值打散
优化前脚本：

总结：

1. 表直接关联字段默认值或者空值过多；
   示例：FROM TABLEA A
   LEFT JOIN TABLEB B ON
   DECOE(A.CUST_PO_NO,’SNULL’, concat(‘HONOR-’,rand()),A.CUST_PO_NO)=B.CUST_PO_NO
   –表直接关联默认值倾斜解决方案

2. 主表传递关联字段默认值或者空值过多(示例)
   示例：FROM TABLEA A
   LEFT JOIN TABLEB B ON A.CUST_PO_NO=B.CUST_PO_NO
   LEFT JOIN TABLEB C ON B NVL(B.OLD_CUST_PO_NO, concat(‘HONOR-’,rand()))=C.OLD_CUST_PO_NO
   3.3 Mapjoin优化
   Mapjoin：保障大小表关联按照Mapjoin执行，而非Commjoin
   优化前脚本：
   DWR_DIM.DWR_DIM_CBG_ORG_GEO_D:数据量：1358671
   SELECT T1.contract_no,T2.region_cn_name
   FROM DWR_SUPPLY.DWD_SO_ORDER_2BP_CRS_F T1
   LEFT JOIN (SELECT ORG_CODE,region_cn_name FROM DWR_DIM.DWR_DIM_CBG_ORG_GEO_D WHERE org_lv IN (‘1’,‘2’,‘3’)) T2
   ON T1.demand_region_code=T2.ORG_CODE
   LIMIT 10;

   通过查看执行日志，发现产生了一个reduce，执行了Commjoin

优化后脚本：
–利用临时表过滤减少记录量，使得临时表记录满足小表的条件：3000条左右
CREATE TABLE DWR_SUPPLY.TMP_GSJ_4
STORED AS ORC
AS
SELECT ORG_CODE,REGION_CN_NAME
FROM DWR_DIM.DWR_DIM_CBG_ORG_GEO_D
WHERE ORG_LV IN (‘1’,‘2’,‘3’);
–实现Mapjoin：大表与小表关联
SELECT T1.CONTRACT_NO,T2.REGION_CN_NAME
FROM DWR_SUPPLY.DWD_SO_ORDER_2BP_CRS_F T1
LEFT JOIN DWR_SUPPLY.TMP_GSJ_4 T2
ON T1.DEMAND_REGION_CODE=T2.ORG_CODE
LIMIT 10;
临时表执行日志：执行Mapjoin，reduce个数为 0
总结：
1） 子查询执行是按照原始表文件的大小识别是否执行Mapjoin;
2） 关闭Mapjoin容易导致关联字段默认值或者空值倾斜；
3） Mapjoin的相关参数
–开启Mapjoin，一般默认开启
set hive.auto.convert.join = true;
– 设置Mapjoin识别的文件大小(5M)，防止压缩比过高导致内存溢出(100M)，执行Commjoin
set hive.mapjoin.smalltable.filesize = 5000000;
–开启多个Mapjoin合并执行，并设置合并执行小表的文件大小和不超过10M
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;
3.4 热点值倾斜优化
关联KEY的倾斜字段不是固定的默认值或者空值，比如双11大促某款商品热销。
解决方案：利用Mapjoin，针对热销商品独立建立小临时表
–第一步生成热销物料top1万临时表
CREATE TABLE DWR_SUPPLY_TMP.TMP_TOP_ITEM
STORED AS ORC
AS
SELECT ITEM_CODE
FROM (
SELECT ITEM_CODE,SUM(1) AS SM
FROM DWR_SUPPLY. D_SO_ORDER_KK_2BP_CRS_F
GROUP BY ITEM_CODE) T
ORDER BY SM DESC LIMIT 10000
–第二步获取热销物料的属性
CREATE TABLE DWR_SUPPLY_TMP.TMP_TOP_ITEM_ATTR
STORED AS ORC
AS
SELECT ITEM_CODE
,ITEM_ATTR
FROM DWR_SUPPLY_TMP.TMP_TOP_ITEM T1
JOIN DWR_DIM.DWR_CBG_ITEM_D T1
ON T1. ITEM_CODE=T2. ITEM_CODE
–第三步生成目标表
SELECT
COLUMN
FROM DWR_SUPPLY. D_SO_ORDER_2BP_CRS_F T1
LEFT JOIN DWR_SUPPLY_TMP.TMP_TOP_ITEM T2
ON T1. ITEM_CODE=T2. ITEM_CODE —热销商品执行Mapjoin
LEFT DWR_DIM.DWR_CBG_ITEM_D T3
ON T1. ITEM_CODE=T3. ITEM_CODE
AND T2. ITEM_CODE IS NULL --非热销商品执行Commjoin
4.其他参数优化
4.1 常见使用参数
建议使用的常见参数
–设置Map的个数(80/50M)，分割大文件、合并小文件
set mapreduce.input.fileinputformat.split.maxsize=100000000;
set mapreduce.input.fileinputformat.split.minsize=80000000;
– 设置Reduce的个数(按照80M进行分割或者合并) ，最多2000个
set hive.exec.reducers.bytes.per.reducer=80000000;
set hive.exec.reducers.max=2000;
–开启Mapjoin
set hive.auto.convert.join = true;
– 设置识别Mapjoin的文件大小(<=5M)
set hive.mapjoin.smalltable.filesize = 8000000;
–开启多个Mapjoin合并执行，并设置合并执行的小表总文件大小<=10M
set hive.auto.convert.join.noconditionaltask=true;
set hive.auto.convert.join.noconditionaltask.size=10000000;
–开启reduce端输出进行合并，减少Reduce端小文件输出
set hive.merge.mapredfiles = true;
–设置合并文件的大小(80M)
set hive.merge.size.per.task = 80000000;
–当输出文件的平均大小小于该值时，启动一个独立的MapReduce任务进行文件merge(50M)
set hive.merge.smallfiles.avgsize=50000000;
–开启动态分区
set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode=nonstrict;
总结：
1） 新旧版本参数
set mapreduce.input.fileinputformat.split.maxsize——set mapred.max.split.size
set mapreduce.input.fileinputformat.split.minsize——set mapred.min.split.size
2） 不建议使用参数，存在排序的SQL开启容易导致脏数据
–开启并行执行
set hive.exec.parallel=true;
hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段。 不过，如果某些阶段不是互相依赖，是可以并行执行的。
4.2 Map分割原则

总结：

1. 当Max>Min>64M，文件大小>Min按照Min进行分割；<Min，按照Min进行合并；
2. 当Max>64M>Min，文件大小>64M按照64M进行分割；<Min按照Min进行合并；
3. 当64M>Max>Min，文件大小>Max按照Max进行分割；<Min，按照Min进行合并：
   一般用户高比例压缩文件进行分割的时候进行特殊设置
   5 数据倾斜优化案例
   5.1 方案分析
   第一步：统计每个表记录量，识别Mapjoin和数据倾斜
   第二部：传递关联字段：t1.s_lot_id存在大量空值
   5.2 脚本优化
   优化点：
   1、 表记录量WHERE过滤前后差异很大表，提前采用临时表处理：子查询仍然是按照原始表的记录量计算Map和Reduce个数
   2、 禁用UION改成UION ALL
   优化前：

3、 处理空值倾斜
LEFT JOIN TABLEB B ON A.CUST_PO_NO=B.CUST_PO_NO
LEFT JOIN TABLEB C ON B NVL(B.OLD_CUST_PO_NO, concat(‘HONOR-’,rand()))=C.OLD_CUST_PO_NO

4,、处理关联字段过多性能问题
使用concat 先拼接成一个关联键
5.3 优化效果
优化前：
严重数据倾斜：优化前：1小时10分钟(15点20分到16点37分)
优化后：数据倾斜已解决，27分钟(22点27分到22点54分)