odps之sql性能优化

简介: 前一段时间做了一些作业成本优化的工作,这里做下总结。 首先说明本篇中谈及的优化主要的目标是在不大幅度增加作业运行时长的条件下对作业运行成本的优化。 ## 1. odps的优化引擎并没有那么智能 odps自带的优化引擎会对sql作业做一定的优化处理,如列裁剪、分区裁剪和谓词下推。

前一段时间做了一些作业成本优化的工作,这里做下总结。
首先说明本篇中谈及的优化主要的目标是在不大幅度增加作业运行时长的条件下对作业运行成本的优化。

1. odps的优化引擎并没有那么智能

odps自带的优化引擎会对sql作业做一定的优化处理,如列裁剪、分区裁剪和谓词下推。但是还会存在一些不会优化处理的地方。甚至有些会和我们想象的存在一定的差异。具体碰到的情况有如下几种

1.1 多路输出(MULTI INSERT)中的想象差异

为了避免多次读取同一份数据,我们会使用multi insert的语句。如果在from语句中有一些较复杂的处理,如select语句有耗时的udf处理或者where语句有耗时的udf处理时会存在一些问题。
如如下sql:

FROM (
  SELECT 
   tolower(bi_udf:bi_get_url_domain(host,1)) root_domain,
  tolower(secods:url_path(flow_str_concat('http://a.cn',uri))) path
   from odl_beaver_log
where ds='20170818' and hh='07'
  and tolower(parse_url(uri,'EXT'))!='do'
)
insert overwrite table a(ds='20170818')
select root_domain...
insert overwrite table b
select ....
insert overwrite table c
select ....

我们查看一下该sql实际的执行计划(具体查看计划请使用MaxCompute Studio工具)
注意:这里的sql与下面的执行计划的图不是一致的,这里只是举例说明
粘贴图片.png

通过执行计划的图我们可以看出其处理流程并没按我们设想的先把from中的结果计算出来,然后再多路输出。而是先按多路输出进行列裁剪,然后再分别进行SEL(select语句)和FIL(where语句)的处理。那这里就会造成重复的计算处理,如果多路输出的表越多,造成的重复计算越多。
解决方法:如果from语句中有较复杂的sql处理逻辑,建议先将from语句的结果存为一张临时表,然后再进行后面的多路输出的处理

1.2 where和select语句中对同一字段的重复的udf处理并不会合并

在where语句中,有时我们会对同一字段的值进行多次的判断,且需要通过一个udf将该字段进行转换了后来进行多次的判断处理。同样select语句中也会存在这样的情况,一个udf处理的结果在多个表达式中用到。实际这里优化器不会对这样重复的表达式进行合并处理。这样就造成了重复的计算处理。如如下sql:

select * from  odl_beaver_log
where ds='20170818' and hh='07'
    and not to_lower(uri) like '%abtest%'
    and not to_lower(uri)  like '%gettimestamp%'                        
    and not to_lower(uri)  like '%tb_eagleeyex_t%'

解决方法:通过做一个子查询,先将该表达式在select语句中处理,然后再过滤。

select * from(
select ..., to_lower(uri) uri
  from  odl_beaver_log
where ds='20170818' and hh='07'
) a
    where not uri like '%abtest%'
    and not uri  like '%gettimestamp%'                        
    and not uri like '%tb_eagleeyex_t%'

1.3 隐藏的隐式转换处理

在sql语句中如果条件2边的类型不一致时会自动进行隐式转换处理。例如如下sql:

select
 sum(case when ret_code=200 then 1 else 0 end) as status_200_cnt,
 sum(case when ret_code in (301,302,419,420) then 1 else 0 end) as status_302_cnt,
 sum(case when ret_code>=300 and ret_code<=399 and ret_code not in (301,302) then 1 else 0 end) as status_3xx_cnt,
 sum(case when ret_code=401 then 1 else 0 end) as status_401_cnt,
 sum(case when ret_code=403 then 1 else 0 end) as status_403_cnt,
 sum(case when ret_code=404 then 1 else 0 end) as status_404_cnt
from a

这里表中的ret_code的字段类型为string,那在执行上述sql时就会多次对ret_code进行string->bigint的处理。这里同样是存在多次的重复计算。
解决方法:这里先做一个子查询将ret_code转换为bigint类型,参考sql如下:

select 
 sum(case when ret_code=200 then 1 else 0 end) as status_200_cnt,
 sum(case when ret_code in (301,302,419,420) then 1 else 0 end) as status_302_cnt,
 sum(case when ret_code>=300 and ret_code<=399 and ret_code not in (301,302) then 1 else 0 end) as status_3xx_cnt,
 sum(case when ret_code=401 then 1 else 0 end) as status_401_cnt,
 sum(case when ret_code=403 then 1 else 0 end) as status_403_cnt,
 sum(case when ret_code=404 then 1 else 0 end) as status_404_cnt
from(
select ...,cast(ret_code as bigint) ret_code from a
) a

1.4 筛选条件的重排

在where语句中如果有多个filter处理条件,这里是支持短路求值的,在这样的场景下,where语句有多个filter处理条件,其中有一个条件计算成本低且能过滤较多的记录数,而另一个条件计算成本高,那我们希望在sql执行时能先执行前面那个过滤条件,而在目前odps的处理逻辑中是按照顺序从左到右执行的(注意:这里要考虑到sql优化引擎的谓词下推的处理,具体的执行顺序请参考执行计划的内容)
解决方法:评估where条件中各个筛选逻辑的计算成本和过滤的记录数的情况,调整sql,优先让计算成本低且过滤记录数较多的条件先执行。

能根据sql执行的实际情况重排筛选条件的这个需求已经给odps那边提了,他们有安排开发处理:http://sqi.alibaba-inc.com/arsenal//requirementDetail.htm?id=43362&departmentId=26718&bu=dataworks,但具体时间点完成还不清楚,如果能确定哪个比较优化,我们先在sql中手工处理了。

2. udf的优化

在odps的sql作业中,我们会用到较多的udf函数,其中有很多是自定义开发的udf函数,那这些函数如果性能有问题,会导致sql作业性能低,计算成本消耗较多。在实际的优化分析中,有碰到如下几类情形。

2.1 自定义开发的udf的功能与系统自带udf功能重复

在sql中的使用的自定义udf实际上系统自带的udf是支持的,一般来说自定义开发的udf的性能是没有系统自带的udf性能高的,所以一般情况下,建议使用系统自带的udf。

2.2 选择合适的udf

在开发中选择适合的udf对计算性能的优化也很明显,下面举几个例子来说明。
1)从字符串中解析出多个key值
业务中较常见从字符串中解析出多个值的情况,如keyvalue格式拼接的字符串,json字符串,url字符串等。
一般我们从json中解析key,系统有提供函数GET_JSON_OBJECT,那如果我们要解析多个key,那写法可能就是如下的:

select
 GET_JSON_OBJECT(json_str, '$.key1'),
 GET_JSON_OBJECT(json_str, '$.key2'),
 GET_JSON_OBJECT(json_str, '$.key3'),
 GET_JSON_OBJECT(json_str, '$.key4')
  from a

这样会对json_str做多次的解析,影响了性能。如果能一次将多个key解析出来,那就只需要解析一次了。自定义的udf:secods:json_tuple可以解决如上的问题。需注意,secods:json_tuple是一个udtf,如果select语句中还有其他字段时,写法上有一点不一样,参考sql如下:

select
   col1,
   col2,
   key1,
   key2,
   key3,
   key4
   from a
lateral view secods:json_tuple(json_str,"key1","key2","key3","key4") json_view as key1,key2,key3,key4

同样keyvalue格式拼接的字符串要解析出多个key值的时候,同样也可以使用str_to_map来替代多次使用KEYVALUE,注意这里str_to_map是一个udf,返回的是一个Map类型。
在url字符串的场景,会有要解析出不同的part的情况,如HOST, PATH, QUERY, REF, PROTOCOL,这样就需要多次使用parse_url来解析处理,如果有一个udf可以一次解析出多个part,这样性能也会优化很多。不过目前还没有这个udf。
另外针对json和keyvalue格式,是否我们在底层就存储为map类型的字段这样会更好。
2)也有系统自带的udf性能没有自定义的高的情况
经测试parse_url就没有secods:url_path secods:url_host的性能高

2.3 优化自定义的udf

如果没有系统自带的udf可以替换,且不是多次解析的情况下,也可以从自定义udf的代码层面来优化。
如python的udf中对正则表达式先进行编译后再使用。这里我们可以使用一些profiling的工具来对自定义udf的性能进行分析,扁鹊中有带了java的profiling功能https://www.atatech.org/articles/38367,python的支持不是很好,那python代码使用profiling来分析,就是要自己准备一下环境。
那如何来快速定位存在性能问题的udf呢,首先我们可以通过查看作业执行的日志来看,在每个Task的日志中会打印如下的日志信息

Filter cursor process data time in milliseconds:1788.45
Filter cursor process data time in milliseconds:6.61
Filter cursor process data time in milliseconds:7.281
Filter cursor process data time in milliseconds:89.214
Filter cursor process data time in milliseconds:7.271
Select cursor process data time in milliseconds:2.782
Select cursor process data time in milliseconds:2.247
Select cursor process data time in milliseconds:2898.92
com.taobao.bi.odps.udf.endecode.UDFMd5 finally processed 107528 records. produced 107528 records. elapsed time in milliseconds: 404

通过这些日志就可以看到哪个操作执行比较耗时。对应的关系猜测是按执行计划中的顺序号排列,待与odps相关同学确认后再更新。同时通过查看对应的执行计划就可以看到这一步中有哪些udf的操作。另外目前日志中会对java的udf把执行所消耗的时间会打印出来。如果是python的udf那就需要自己准备一些测试的sql来进行测试定位了。

3. odps系统参数优化

在一些场景下,我们也可以通过手工调整odps系统参数的值来达到成本优化的效果

3.1 hbo失效

当我们的作业发生修改后,那当时hbo就会失效,对于一些耗资源较多的作业,那成本的增长就非常的明显,那我们在作业修改上线时可以手工配置odps.sql.mapper.cpu和odps.sql.reducer.cpu的值来减少成本的巨大波动。这2个值默认为100,如果有修改上线后成本增长较多,那说明这2个参数在hbo生效时会减少,一般为50。另大家可以通过查看以前该作业hbo有生效的日志来查看这2个参数的具体值。

3.2 map任务执行时间太短

对于简单的sql加工作业,map任务执行的时间非常短(几秒~十几秒),但是可能会有很多的map任务,这种场景下我们可以调大odps.sql.mapper.split.size(单位M,默认值256),减少map任务的个数,增大每个map任务的执行时长。

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps&nbsp;
目录
相关文章
|
11天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用合集之大数据计算MaxCompute即使用相同的SQL语句在DataWorks和Tunnel上执行,结果却不同,如何解决
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
3天前
|
SQL 关系型数据库 数据库
阿里云数据库 RDS SQL Server版实战【性能优化实践、优点探析】
本文探讨了Amazon RDS SQL Server版在云数据库中的优势,包括高可用性、可扩展性、管理便捷、安全性和成本效益。通过多可用区部署和自动备份,RDS确保数据安全和持久性,并支持自动扩展以适应流量波动。可视化管理界面简化了监控和操作,而数据加密和访问控制等功能保障了安全性。此外,弹性计费模式降低了运维成本。实战应用显示,RDS SQL Server版能有效助力企业在促销高峰期稳定系统并保障数据安全。阿里云的RDS SQL Server版还提供了弹性伸缩、自动备份恢复、安全性和高可用性功能,进一步优化性能和成本控制,并与AWS生态系统无缝集成,支持多种开发语言和框架。
23 2
|
4天前
|
SQL 资源调度 监控
Flink SQL性能优化实践
Apache Flink流处理性能优化指南:探索数据源读取并行度、状态管理、窗口操作的优化策略,包括设置默认并行度、使用RocksDB状态后端、调整窗口大小。调优方法涉及数据源分区、JOIN条件优化、使用Broadcast JOIN。注意SQL复杂度、并发控制与资源调度,如启用动态资源分配。源码层面优化自定义Source和Sink,利用执行计划分析性能瓶颈。异常检测与恢复通过启用检查点,监控任务性能。预处理数据、使用DISTINCT去重,结合UDF提高效率。选择高效序列化框架和启用数据压缩,优化网络传输和系统配置。处理数据倾斜,均衡数据分布,动态调整资源和任务优先级,以提升整体性能。
33 2
|
5天前
|
SQL 关系型数据库 MySQL
sql性能优化及实战
sql性能优化及实战
13 0
|
6天前
|
SQL 分布式计算 Java
大数据软件基础(2)—— Java、SQL
大数据软件基础(2)—— Java、SQL
8 0
|
11天前
|
SQL 分布式计算 DataWorks
DataWorks操作报错合集之在DataWorks中使用ODPS SQL时遇到"该文件对应引擎实例已失效,请重新选择可用的引擎实例"的错误提示”,是什么导致的
DataWorks是阿里云提供的一站式大数据开发与治理平台,支持数据集成、数据开发、数据服务、数据质量管理、数据安全管理等全流程数据处理。在使用DataWorks过程中,可能会遇到各种操作报错。以下是一些常见的报错情况及其可能的原因和解决方法。
40 0
|
11天前
|
分布式计算 DataWorks 调度
DataWorks产品使用合集之DataWorks中,填写ODPS SQL任务中的参数和分区信息如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
31 0
|
11天前
|
SQL 分布式计算 DataWorks
DataWorks产品使用合集之在DataWorks的数据开发模式中,在presql和postsql中支持执行多条SQL语句如何解决
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
50 1
|
11天前
|
SQL 分布式计算 DataWorks
MaxCompute产品使用合集之阿里云MaxCompute对SQL语句的长度的长度限制是多少
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
|
11天前
|
SQL 分布式计算 大数据
MaxCompute操作报错合集之在sql 里嵌套查询时,查询条件带有instr时报错,如何解决
MaxCompute是阿里云提供的大规模离线数据处理服务,用于大数据分析、挖掘和报表生成等场景。在使用MaxCompute进行数据处理时,可能会遇到各种操作报错。以下是一些常见的MaxCompute操作报错及其可能的原因与解决措施的合集。

热门文章

最新文章

相关产品

  • 云原生大数据计算服务 MaxCompute