01.Hive调优篇-典型数据倾斜
2021-10-29 本文已影响0人
木讷DATA
数据倾斜发现旅程
-
在reduce阶段，存在一个步骤，时间明显高于其他步骤；
yarn-application
处理阶段-分析阶段
- 大表 join 大表
大表关联大表【看关联条件，在两个表的分布条件】
- 为空的比例比较大的情况，使用union all进行拆分
- 分布比较紧凑的，可在字段值前表添加上两位随机数值，将数据进行打散
处理阶段-举例优化
- 优化前脚本：
insert overwrite table hdw_opn${g_he_dbpostfix}.opn_xyj partition (pdate = '${g_tm_ymd}')
select
t1.statistics_dt -- 统计日期
,t1.mac_id -- macid
,t1.machine_seq_num -- 机器编号
,t1.device_model_num -- 设备型号编号
,t1.device_model_name -- 设备型号名称
,t1.big_type_cd -- 设备大类编码
,t1.big_type_desc -- 设备大类名称
,t1.workorder_num -- 工单号
,t2.province_cd -- 省代码
,t2.province_desc -- 省描述
,t2.city_cd -- 市代码
,t2.city_desc -- 市描述
,t2.county_cd -- 县代码
,t2.county_desc -- 县描述
,t1.branch_work_over_tm -- 网点结单时间
from (
select
substring(hsicrm_servicestationcompletetime,1,10) as statistics_dt -- 统计日期
,mac_id as mac_id -- macid
,hsicrm_serialnumber as machine_seq_num -- 机器编号
,hsicrm_productmodelcode as device_model_num -- 设备型号编号
,hsicrm_productmodelname as device_model_name -- 设备型号名称
,hsicrm_productcategorycode as big_type_cd -- 设备大类编码
,hsicrm_productcategoryname as big_type_desc -- 设备大类名称
,hsicrm_workorderid as workorder_num -- 工单号
,hsicrm_servicestationcompletetime as branch_work_over_tm -- 网点结单时间
from hdw_dwb${g_he_dbpostfix}.dwb_xyj
WHERE pdate = '${g_tm_ymd}'
) t1
left join (
select
mac_id -- macid
,province_cd -- 省代码
,province_desc -- 省描述
,city_cd -- 市代码
,city_desc -- 市描述
,county_cd -- 县代码
,county_desc -- 县描述
from hdw_opn.opn_dim_dev_install_area
group by
mac_id -- macid
,province_cd -- 省代码
,province_desc -- 省描述
,city_cd -- 市代码
,city_desc -- 市描述
,county_cd -- 县代码
,county_desc -- 县描述
) t2 on t1.mac_id = t2.mac_id
;
- 数据分布分析
- 由于第一个大表，mac_id为空，高达90%，在和第二个大表关联时出现明显的数据倾斜
- 将mac_id 为空和不为空的进行拆分，再进行关联
- 优化后脚本
with tmp as (
select
substring(hsicrm_servicestationcompletetime,1,10) as statistics_dt -- 统计日期
,mac_id as mac_id -- macid
,hsicrm_serialnumber as machine_seq_num -- 机器编号
,hsicrm_productmodelcode as device_model_num -- 设备型号编号
,hsicrm_productmodelname as device_model_name -- 设备型号名称
,hsicrm_productcategorycode as big_type_cd -- 设备大类编码
,hsicrm_productcategoryname as big_type_desc -- 设备大类名称
,hsicrm_workorderid as workorder_num -- 工单号
,hsicrm_servicestationcompletetime as branch_work_over_tm -- 网点结单时间
,hsicrm_industrycode as industry_cd
,hsicrm_industryname as industry_desc
from hdw_dwb${g_he_dbpostfix}.dwb_hcc_hsicrm_wo_workorderbase_lingbaoyuan
WHERE pdate = '99991231'
and ptype = 'T02'
)
insert overwrite table hdw_opn${g_he_dbpostfix}.opn_hcc_hsicrm_wo_workorderbase_install partition (pdate = '99991231')
select
t1.statistics_dt -- 统计日期
,t1.mac_id -- macid
,t1.machine_seq_num -- 机器编号
,t1.device_model_num -- 设备型号编号
,t1.device_model_name -- 设备型号名称
,t1.big_type_cd -- 设备大类编码
,t1.big_type_desc -- 设备大类名称
,t1.workorder_num -- 工单号
,t2.province_cd -- 省代码
,t2.province_desc -- 省描述
,t2.city_cd -- 市代码
,t2.city_desc -- 市描述
,t2.county_cd -- 县代码
,t2.county_desc -- 县描述
,t1.branch_work_over_tm -- 网点结单时间
,t1.industry_cd
,t1.industry_desc
from ( -- 先提取mac不为空的数据
select
t1.statistics_dt -- 统计日期
,t1.mac_id -- macid
,t1.machine_seq_num -- 机器编号
,t1.device_model_num -- 设备型号编号
,t1.device_model_name -- 设备型号名称
,t1.big_type_cd -- 设备大类编码
,t1.big_type_desc -- 设备大类名称
,t1.workorder_num -- 工单号
,t1.branch_work_over_tm -- 网点结单时间
,t1.industry_cd
,t1.industry_desc
from tmp t1
WHERE mac_id is not null
or mac_id <> ''
) t1
left join (
select
mac_id -- macid
,province_cd -- 省代码
,province_desc -- 省描述
,city_cd -- 市代码
,city_desc -- 市描述
,county_cd -- 县代码
,county_desc -- 县描述
from hdw_opn.opn_dim_dev_install_area
group by
mac_id -- macid
,province_cd -- 省代码
,province_desc -- 省描述
,city_cd -- 市代码
,city_desc -- 市描述
,county_cd -- 县代码
,county_desc -- 县描述
) t2 on t1.mac_id = t2.mac_id
union all -- union上mac为空的数据
select
t1.statistics_dt -- 统计日期
,t1.mac_id -- macid
,t1.machine_seq_num -- 机器编号
,t1.device_model_num -- 设备型号编号
,t1.device_model_name -- 设备型号名称
,t1.big_type_cd -- 设备大类编码
,t1.big_type_desc -- 设备大类名称
,t1.workorder_num -- 工单号
,'' province_cd -- 省代码
,'' province_desc -- 省描述
,'' city_cd -- 市代码
,'' city_desc -- 市描述
,'' county_cd -- 县代码
,'' county_desc -- 县描述
,t1.branch_work_over_tm -- 网点结单时间
,t1.industry_cd
,t1.industry_desc
from tmp t1
WHERE mac_id is null;
- 优化结果
由原来的20min，优化到7miin
