Dataworks实践——脚本优化
原创
1.0 引言
Dataworks从零到一的过程，是希望任务越多越好，命名可以不标准，脚本可以重复迭沓，但能够覆盖掉业务数据的100%即可；而从一到一百的过程，是为前一阶段“买单”的时候，如何分散调度时间的集中度，如何设置告警基线，如何识别并下线无效调度，如何对脚本调优降低费用等等。
本文从费用的点来切入，因为想要优化调度任务的主要目的就是为了节省费用。
2.0 Maxcompute计费规则
2.1 Maxcompute计费规则小结
对于任何的优化措施来说，我们首先需要了解其背后计费的规则，对此网页上有比较详细的介绍。我们就总结其中的主要规则来说。
总体的规则如下脑图：
2.2 Maxcompute计费之SQL作业按量计费
根据实际业务的不同，具体的费用项也有差异，而我们目前只涉及到图中标红部分的SQL作业按量计费项。其中主要包括三个因变量：输入数据量，SQL复杂度和单价。
输入数据量，指的是一个SQL作业实际扫描的数据量，而实际扫描的数据量的定义中则涵盖两个可以优化的点：1、列剪切，即在我们具体建模过程中，实际的输入数据量仅统计我们写入的列，SQL中未列出的列不参与统计。2、分区过滤，如果表中有分区，那么如果在SQL中我们限制了分区，即仅有限制的分区的数据量才统计，而限制以外的分区不统计在内。而这两点也给予了我们脚本优化的两个灵感，后续我们展开讲。
SQL复杂度，指的是一个任务中关键字的数量，按其数量的多少，来划分为四个不同的SQL复杂度，分别为1，1.5，2，4四档。所以关键字的多少是直接决定了SQL复杂度的大小，尽管这种相关性并不是线性相关的，因此实际操作中如何减少关键字的个数是脚本优化的关键。
单价，主要是由我们每日的计算量，即$$\sum{i=1}^{\infty}{输入数据量{i}*SQL复杂度_{i}}$$ 决定的。对于我们来说，按照0.3的标准来统计即可。
综上所述，其实可以看到我们对于脚本的优化主要来自于两方面，第一，减少输入数据量；第二，减少关键字，进而降低SQL复杂度。
3.0 脚本优化——降低模型SQL复杂度
我们来看一下具体案例，这段脚本是从任务ads_ilp_pdd_dailyspy_inf中截取的部分
insert overwrite ...
...
...
...
from (select * from yht_dwd.dwd_ilp_trail_detail WHERE pt='${bizdate}' ) x1
left join (select * from yht_dwd.dwd_uexpress_u_express_order_df WHERE pt='${bizdate}' ) x2
on x1.trail_ref1 = x2.number
LEFT JOIN (select * from yht_dws.dws_ilp_ar_billing_detail where pt='${bizdate}' ) x3
ON x1.trail_ref1 = x3.waybill_no
) y1
left join (
--# 获取该揽收时间、该航班号、该批次号下的，最早的 【交航司时效/起飞时间】、【到港时间（中国时间）】、【清关--交USPS时间 / 派送时间】
select b1.*
,b2.flight_arrived_time --# 到港时间（中国时间）
,b3.delivery_time --# 清关--交USPS时间 / 派送时间
from（
select substr(inhouse_time,1,10) as inhouse_time --# 揽收时间
,hawb_no --# 航班号
,bill_no as bill_no_full
,min(flight_time) as flight_time --# 交航司时效/起飞时间
FROM yht_dwd.dwd_ilp_trail_detail y1 WHERE pt='${bizdate}'
and substr(flight_time,1,10) <> '1970-01-01'
group by
substr(inhouse_time,1,10) --# 揽收时间
,hawb_no --# 航班号
,bill_no ) b1
left join (
select substr(inhouse_time,1,10) as inhouse_time --# 揽收时间
,hawb_no --# 航班号
,bill_no as bill_no_full
,min(flight_arrived_time) as flight_arrived_time --# 到港时间（中国时间）
FROM yht_dwd.dwd_ilp_trail_detail y1 WHERE pt='${bizdate}'
and substr(flight_arrived_time,1,10) <> '1970-01-01'
group by
substr(inhouse_time,1,10) --# 揽收时间
,hawb_no --# 航班号
,bill_no ) b2
on b1.inhouse_time = b2.inhouse_time
and b1.hawb_no = b2.hawb_no
and b1.bill_no_full = b2.bill_no_full
left join (
select substr(inhouse_time,1,10) as inhouse_time --# 揽收时间
,hawb_no --# 航班号
,bill_no as bill_no_full
,min(delivery_time) as delivery_time --# 清关--交USPS时间 / 派送时间
FROM yht_dwd.dwd_ilp_trail_detail y1 WHERE pt='${bizdate}'
and substr(delivery_time,1,10) <> '1970-01-01'
group by
substr(inhouse_time,1,10) --# 揽收时间
,hawb_no --# 航班号
,bill_no ) b3
on b1.inhouse_time = b3.inhouse_time
and b1.hawb_no = b3.hawb_no
and b1.bill_no_full = b3.bill_no_full
) a
on y1.inhouse_time = a.inhouse_time
and y1.hawb_no = a.hawb_no
and y1.bill_no_full = a.bill_no_full
...
...
...首先我们来统计一下SQL关键字：
join: #6,#8,#11,#28和#43 ，合计5个
groupby：#23,#35和#50，合计3个
加上一个 insert ,合计9个关键字，那么SQL复杂度是2。
然后，我们再来看一下脚本：其中的b1,b2,b3在我看来其实逻辑都是一致的，改写成如下：
select
substr(inhouse_time,1,10) as inhouse_time --# 揽收时间
,hawb_no --# 航班号
,bill_no as bill_no_full
,min(flight_time) as flight_time --# 交航司时效/起飞时间
,min(flight_arrived_time) as flight_arrived_time --# 到港时间（中国时间）
,min(delivery_time) as delivery_time --# 清关--交USPS时间 / 派送时间
FROM yht_dwd.dwd_ilp_trail_detail y1 WHERE pt='${bizdate}'
and to_char(flight_time,'yyyy-mm-dd') <> '1970-01-01'
-- and to_char(flight_arrived_time,'yyyy-mm-dd') <> '1970-01-01'
-- and to_char(delivery_time,'yyyy-mm-dd') <> '1970-01-01')
group by
substr(inhouse_time,1,10) --# 揽收时间
,hawb_no --# 航班号
,bill_no这样就减少了2个 groupby，减少2个join，而关键字个数会降低到5个，SQL复杂度降低到1.5。
最后再来看，案例中这样的写法：
...
LEFT JOIN (select * from yht_dws.dws_ilp_ar_billing_detail where pt='${bizdate}' and type=1 ) x3
ON x1.trail_ref1 = x3.waybill_no
...按照官方文档上的介绍，这样的写法完全不会降低扫描数据量，也不会提升sql运行效率，有兴趣的可以去官方文档了解 Hive SQL语句的正确执行顺序 ，看下hive sql的底层运行原理，可能会有所领悟。
所以，上述脚本修改成如下：
...
left join yht_dws.dws_ilp_ar_billing_detail x3
on x1.trail_ref1=x3.waybill_no
and x3.pt='${bizdate}'
and x3.type=1
...那么上述上述脚本即可改写为如下：
insert overwrite ...
...
...
from yht_dwd.dwd_ilp_trail_detail x1
-- left join yht_dwd.dwd_uexpress_u_express_order_df x2 on x1.trail_ref1 = x2.number
-- and x2.pt='${bizdate}'
LEFT JOIN yht_dws.dws_ilp_ar_billing_detail x3 ON x1.trail_ref1 = x3.waybill_no
and x3.pt='${bizdate}'
where x1.status_id not in ('TERMINATED','RETURNED_TO_CHINA')
and x3.bussiness_type = 'QLL'
and nvl(x1.carrier,'-') like '%拼多多%'
and substr(x1.inhouse_time,1,10) >= '2022-10-15'
and x1.pt='${bizdate}'
) y1
left join (
--# 获取该揽收时间、该航班号、该批次号下的，最早的 【交航司时效/起飞时间】、【到港时间（中国时间）】、【清关--交USPS时间 / 派送时间】
select substr(inhouse_time,1,10) as inhouse_time --# 揽收时间
,hawb_no --# 航班号
,bill_no as bill_no_full
,min(flight_time) as flight_time --# 交航司时效/起飞时间
,min(flight_arrived_time) as flight_arrived_time --# 到港时间（中国时间）
,min(delivery_time) as delivery_time --# 清关--交USPS时间 / 派送时间
FROM yht_dwd.dwd_ilp_trail_detail y1 WHERE pt='${bizdate}'
and to_char(flight_time,'yyyy-mm-dd') <> '1970-01-01'
group by
substr(inhouse_time,1,10) --# 揽收时间
,hawb_no --# 航班号
,bill_no
) a
on y1.inhouse_time = a.inhouse_time
and y1.hawb_no = a.hawb_no
and y1.bill_no_full = a.bill_no_full 4.0 脚本优化——避免重复造轮子
先来看几个脚本的片段：
来自ads_ilp_pdd_packagecost_inf模型中片段：
来自ads_ilp_product_timely模型的片段：
再来看下ads_ilp_pdd_undeliver_inf模型的片段：
再来看下，ads_ilp_pdd_deliverdelay_inf的脚本片段：
我们可以看到四段脚本中同样用到了几乎相同的逻辑，依赖的表相同：yht_dwd.dwd_ilp_trail_detail，yht_dwd.dwd_uexpress_u_express_order_df，yht_dws.dws_ilp_ar_billing_detail和yht_dwd.dwd_uexpress_order_contact_df，字段也是几乎相同，唯一的区别在于限制条件是有区别的，比如说，对于入库时间的限定，对于渠道的限定等等。那么问题就来了，为什么如此相似的脚本还会重复四次的上线呢？
这不仅是对调度资源的浪费，也同时拖慢了任务的执行时间，如果考虑将这段复用的逻辑物化成一张表，而下游的任务即来依赖物化的表即可，此时对于各个任务的SQL关键字都减少了3个，基本上相当于将SQL复杂度由1.5降低到1，任务的运行效率集中加强。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。
如有侵权，请联系 cloudcommunity@tencent.com 删除。
腾讯云开发者
