线上的宽表及报表,由于影响到各个业务线的数据,对数据及时性要求比较高,所以整体的运行效率显得至关重要;
借鉴以往的优化经验,这里在优化方式上提供一些参考。
方式一:sql优化
sql的优化是一个大而广的方向,比较常用的手段,譬如:减少jobs数量、解决数据倾斜的问题、mapjoin的使用等等,大多遵循以下原则:
1. 尽量尽早地过滤数据,减少每个阶段的数据量
方法:列裁剪、分区裁剪,查询过程中只选择需要使用到的字段,分区表使用加分区。
例:
select ... from Ajoin B
on A.key = B.key
where
A.userid > 10
and B.userid < 10
and A.dt ='20120417'
and B.dt ='20120417';
应该改写为:
select .... from
( select .... from Awhere dt = '201200417' and userid > 10 ) a
join
( elect .... from Bwhere dt = '201200417' and userid < 10 ) b
on a.key = b.key;
2. 减少jobs数量
方法:善用muti-insert、union all,不同表的union all相当于multiple inputs,同一个表的union all,相当map一次输出多条。
例:muti-insert的使用
insert overwritetable tmp1
select ...
from a
where 条件1;
insert overwritetable tmp2
select ...
from a
where 条件2;
可以改写为:
from a
insert overwritetable tmp1
select ...
where 条件1
insert overwritetable tmp2
select ...
where 条件2;
3. 写SQL要先了解数据本身的特点,如果有join ,group操作的话,要注意是否会有数据倾斜;
方法一:设置reduce数目等参数
例:
set mapred.reduce.tasks=200; ---增大Reduce个数
sethive.groupby.mapaggr.checkinterval=100000 ; --这个是group的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
sethive.groupby.skewindata=true; --如果是group by过程出现倾斜应该设置为true
sethive.skewjoin.key=100000; --这个是join的键对应的记录条数超过这个值则会进行分拆,值根据具体数据量设置
sethive.optimize.skewjoin=true; --如果是join 过程出现倾斜 应该设置为true
方式二:业务逻辑优化
如果在SQL上优化没有太多空间的话,可以考虑从模型设计下手,优化业务逻辑处理;
例:宽表从POP订单扩展表(fdm_pek_orderexpend_pop_chain)只取用了POP商家编号字段值,而主表运单管理表是可以取到POP商家编号,
所以这里可以舍去POP订单扩展表。
select
t1.Shipping_Bill_Id--运单编号
,t1.Sale_Ord_Id --销售订单编号
,t1.Shipping_Bill_Status_Cd--运单状态代码
,t5.Pop_Vender_Id...
from
(
select ... fromodm.odm_waybill_info
where
dt >='"""+business_date+"""'
andShip_Bill_Cate_Cd = 1
andShip_Bill_Type_Cd <> 2
)
t1------------------------------------运单中间表
left outer join
(
select ... fromodm.odm_pek_t_prefert group by orderid
)
t2-----------------------------优惠
on
t1.Shipping_Bill_Id= t3.sale_ord_id
left outer join
(
select
orderid assale_ord_id,
venderidPop_Vender_Id --POP商家编号
from
fdm.fdm_pek_orderexpend_pop_chain
where
dp in('ACTIVE','HISTORY')
and yn = 1
)
t3-----------------------------POP商家编号
on
t1.Shipping_Bill_Id= t5.sale_ord_id
......
可以改写为:
select
t1.Shipping_Bill_Id--运单编号
,t1.Sale_Ord_Id --销售订单编号
,t1.Shipping_Bill_Status_Cd --运单状态代码
, t1.Pop_Vender_Id...
from
(
select ... fromodm.odm_waybill_info
where
dt >='"""+business_date+"""'
andShip_Bill_Cate_Cd = 1
andShip_Bill_Type_Cd <> 2
)
t1------------------------------------运单中间表
left outer join
(
select ... from odm.odm_pek_t_prefertgroup by orderid
)
t2-----------------------------优惠
on
t1.Shipping_Bill_Id= t3.sale_ord_id
......
方式三:调度流程优化
从整体上把握数据流各个结点的运行状况,对于关键路径上并行运行的作业,若有运行时长较长或数据晚点的作业可考虑分为多个层次处理。
例:
jobA
\
job1 jobC
\ /
job2- jobB
/
job3(较慢)
可以改写为:
job1
\
job2- jobB
\
jobA- jobC
/
job3(较慢)