给技术新人的ODPS优化建议

简介: 数据开发基本都是从陌生到熟悉,但是写多了就会发现各种好用的工具/函数,也会发现各种坑,本文分享了作者从拿到数据到数据开发到数据监控的一些实操经验。

写在前面

本文档是组内的一份算法ODPS离线开发分享,仅列出了这些年积累下来的一些重要经验和结论,特别是在算法日常数据处理和调度中的技巧和配置方法,至于具体为什么,建议大家去阿里云官网查看底层map reduce以及data flow的工作原理,会有更加深刻的体会:)参考文档:https://help.aliyun.com/document_detail/89993.html

各种join的用法篇

输入数据:zhule_a, zhule_b两张测试表,具体schema和数据如下(后续为了说明重复数据带来的问题,会在a和b表中各重复插入一条key=2,ds='20220930'的数据):read zhule_a;

key ds
1 20220930
2 20220930
1 20221001
2 20221001
3 20221001

read zhule_b;

key ds
2 20220930
3 20220930
1 20221001
2 20221001
3 20221001
4 20221001
5 20221001


Join/Inner Join

用法:Returns the rows that have matching column values in both the left table and the right table based on the join condition。一句话:找出两个表中共同的部分,注意笛卡尔积下面的性能优化

1、每张表先选出来subset,然后再join。



-- better way to perform join, select small range of data first.
SELECT A.*, B.*
FROM
(SELECT * FROM A WHERE ds='20180101') A
JOIN
(SELECT * FROM B WHERE ds='20180101') B
ON a.key = b.key;

注意:在进行各种jion操作前,一定要自查左右表是否有重复数据,否则最终重复的结果会以笛卡尔积的数量增长,比如左右表各有两条重复数据,那么join后重复数据会多达4条!

image.png

2、最好的情况下是大表join小表,然后利用mapjoin来实现。

官方解释:In the map stage, MAPJOIN loads all data in the specified tables into the memory of the program that performs the JOIN operation. The tables specified for MAPJOIN must be small tables, and the total memory occupied by the table data cannot exceed 512 MB.Limits on JOIN operations in MAPJOIN:

  • The left table in a LEFT OUTER JOIN operation must be a large table.
  • The right table in a RIGHT OUTER JOIN operation must be a large table.
  • MAPJOIN cannot be used in a FULL OUTER JOIN operation.
  • The left or right table in an INNER JOIN operation can be a large table.

SELECT  /*+ MAPJOIN(b) */
        a.*
FROM    test_a a
JOIN test_b b
ON      a.user_key = b.user_key
;
//就是在sql语句前加一个标记说这是mapjoin,把小表别名写在括号里

一个有趣的点:当我们用mapjoin时,除了正常的等式,mapjoin还支持不等式,如下面的例子:

image.png


Left Join

用法:A LEFT JOIN operation first takes the Cartesian product of the rows in Table A and Table B and returns all the rows of Table A and rows in Table B that meet the join condition. If the join condition finds no matching rows in Table B for a row in Table A, the row in Table A is returned in the result set with NULL values in each column from Table B.一句话:输出左表的所有记录,以及右表中符合关联条件的数据。右表中不符合关联条件的行,输出NULL。

  1. 一定要保留左表的内容是,可以选择用left join,例如加入key_attrs
  2. Right Join和Left Join没有本质区别,建议定义好左表后都利用Left Join来执行
  3. 如果右表有重复数据的情况,那么最终left join的结果会有重复

image.png


Left Semi Join

用法:A LEFT SEMI JOIN operation returns only the rows in Table A that have a matching row in Table B. 对于左表中的一条数据,如果右表存在符合关联条件的行,则输出左表,否则不输出

  1. 当右表没有重复数据时,和Join是一致的,只会保留相同的列下来
  2. left semi join并不会返回右表B中的任何数据,所以你没法在where条件中指定关于右表B的任何筛选条件,下面得例子能够有更加清晰的对比(例子引用于开源论坛):



employee (2 columns - e_id and e_name)
10, Tom
20, Jerry 
30, Emily

employee_department_mapping (2 columns - e_id and d_name)
10, IT
10, Sales
20, HR

-- inner join results
SELECT e.e_id, e.e_name, d.d_name FROM 
employee e INNER JOIN employee_department_mapping d
on e.e_id = d.e_id
-- results
10, Tom, IT
10, Tom, Sales
20, Jerry, HR

-- left semi join results
SELECT e.e_id, e.e_name, d.d_name FROM 
employee e LEFT SEMI JOIN employee_department_mapping d
on e.e_id = d.e_id
-- results
10, Tom, IT
20, Jerry, HR



Left Anti Join

用法:A LEFT ANTI JOIN operation returns only the rows in Table A that have no matching rows in Table B.一句话:对于左表中的一条数据,如果右表中不存在符合关联条件的数据,则输出左表。

  1. 最好用的场景就是找出两表的差异部分;
  2. 算法日常调度时可以用于每日新增修改商品的提取,将关键字段放到ON条件中就行


image.png



Full Join

用法:A FULL JOIN operation takes the Cartesian product of the rows in Table A and Table B and returns all the rows in Table A and Table B, whether the join condition is met or not. In the result set, NULL values are returned in the columns from the table that lacks a matching row in the other table.一句话:输出左表和右表的所有记录,对于不符合关联条件的数据,未关联的另一侧输出NULL

  1. 在有增删改情况下更新下游最新数据时,非常好用,但是知道的人比较少

举个栗子,其中today_feat是今天新计算的feature table,yest_feat是上一个分区的feature:


SELECT  COALESCE(a.main_image_url,b.main_image_url) AS main_image_url
        ,COALESCE(a.embedding,b.embedding) AS embedding
FROM    today_feat a
FULL JOIN yest_feat b
ON      a.main_image_url = b.main_image_url

其中full jion的效果如下,正好满足new,old,updated feature的更新,配合COALESCE很丝滑:

image.png

合理设置Mapper和Reducepriority


set odps.instance.priority

目前ODPS更新后只能在开发dev空间生效,通过设置优先级能够一定程度提升排队任务的执行优先级,但是目前线上正式环境不会生效了,建议大家优化好自己健康分,同时对于重要的线上调度任务设置好基线,保证产出的时效。


set odps.sql.mapper.split.size

官方指导:Changes the input data amount of each Map worker, which is the split size of the input file. You can use this property to indirectly control the number of workers at each Map stage (default value: 256, unit: MB)。一句话:如果小文件很多,可以调大split.size的数值,这样可以保证在有限资源下更容易申请到Mapper,提升执行的效率。如果资源丰富,想要更多Mapper资源,那就调小split.size的数值,可以申请到更多的Mapper,提升执行效率。酌情处理哟~举个栗子:



-- original sql
CREATE TABLE if not EXISTS tmp_zhl_test LIFECYCLE 1 AS 
SELECT sig, venture, seller_id, COUNT(product_id) as cnt 
FROM sku_main_image_sig
WHERE LENGTH(sig) > 10 --some bad cases may have weird sigs like '#NEXT#'
GROUP BY sig, venture, seller_id
HAVING cnt>2
;

如果是默认设置,553 mappers 和 184 reducers 被分配,大约耗时 3m18s:

image.png

在资源充沛的情况下,我们设置odps.sql.mapper.split.size=64, 可以申请到更多的Mapper去处理文件的分片,同时更多的reducer也可以被分配到,同样的SQL代码执行时间降为: 2m34s. 同样的,如果你的数据量超大,但是每条数据本身很小,同时空间资源也有限(毕竟现在资源管控比较严格),与其等待9999个Mapper被分配,你可以尝试设置odps.sql.mapper.split.size=2048(甚至更大)去减少需要分配的Mapper数量,让任务能够快速执行:)


set odps.sql.reducer.instances

显示设置reducer的数量(默认值从0到4000),不设置的话会根据任务动态分配,设置后就会分配设置数量的reducer。同样是上面的例子,当我们设置odps.sql.reducer.instances=1000, 耗时变为2m


set odps.sql.mapper(reducer).memory

设置每个Map/Reducer worker的内存(默认值是1024,可以设置为256到12288之间的值)一般我们不需要特别设置这个值,但是当任务报错并说「data exceeds the memory」时,可以根据个人情况来设置这个选项。

在Python UDF中使用第三方库

在这部分主要和大家分享下如何在ODPS的python udf安装需要的第三方库(如numpy,opencv等),以及如果有不同依赖库之间的版本不兼容问题时的有效解决方法。


Upload&Call Package

  • 需要下载第三方库的安装包xxx.whl,可以直接下载到自己的电脑上面,这样可以在离线环境验证多个版本的一致性(下面介绍)。一般来说我们需要去看安装包需要的python版本号以及兼容机器环境,一般来说都是cp37-cp37m or py2.py3-none-any在中间,然后末尾是x86_64的安装包;
  • 本地直接将xxx.whl转换为xxx.zip,利用命令「mv xxx.whl xxx.zip」就行
  • 将zip资源文件上传到ODPS对应的环境
  • 在你的UDF中,利用下面的代码指定资源包的路径和引用(直接copy就行)

def include_package_path(res_name, lib_name):    
    archive_files = get_cache_archive(res_name)
    dir_names = sorted([os.path.dirname(os.path.normpath(f.name)) for f in archive_files
                        if '.dist_info' not in f.name], key=lambda v: len(v))
    for dir_name in dir_names:
        if dir_name.endswith(lib_name):
            sys.path.insert(0, os.path.dirname(dir_name))
            break
        elif os.path.exists(os.path.join(dir_name, lib_name + '.py')):
            sys.path.insert(0, os.path.abspath(dir_name))
            break

class PostProcess(BaseUDTF):
    def __init__(self):
        include_package_path('opencv_python-3.4.0.zip','cv2')
        include_package_path('numpy-1.16.6.zip','numpy')
  • python UDF写完后,就可以在创建函数里面的Resources里直接将你的资源名写进去,这样在流程启动后,你的资源才会被有效调用起来。

image.png

  • python UDF默认的版本是2.x的,如果你的python版本是3.x,那么需要在ODPS运行前加入下面的指令;同时,部分功能是需要打开沙箱的,所以如果报错的话,可以加入第二行的沙箱命令。

set odps.sql.python.version = cp37; --use python 3.7, default is 2.x version
set odps.isolation.session.enable = true; 



Solve Compatibility Issue

有时候在使用多个库时,可能会出现不同版本之间的冲突问题(比如在使用opencv库的时候,如果对应的numpy版本不兼容的话,就会报错)。所以在上传多个库的资源包前,需要先确认版本间的兼容性。一般非常不建议大家用不同版本去试,而应该先在本地确定版本后再上传。可行的步骤如下:

  • 在本地可以用类似conda的工具搭建一个虚拟环境
  • 在本地用pip或者conda install来安装你需要的三方库
  • 查询你下载的三方库以及依赖库的版本,比如python-opencv的话可以打印cv2.__version__
  • 把对应版本的xxx.whl包按照上面的方法现在下来并且上传到ODPS资源中进行依赖

发布任务时的一些额外建议

  • 发布任务配置时,可以灵活使用exclude和extra来去掉或添加你想要的依赖。其中exclude可以去掉你中间产出的临时表,而extra可以帮你增加虽然不在代码里但是希望依赖的上游表(这在汇总不同上游表数据写入下游对应分区并且希望同时产出下游数据时很有用)。


--exclude input or output tables (especially those tmp tables)
--@exclude_input=lsiqg_iqc_sku_product_detection_result
--@exclude_output=lsmp_sku_image_url_bizdate

-- include input or output tables (especially those separate venture tables)
--@extra_input=lsiqg_iqc_sku_product_detection_result
--@extra_output=lsmp_sku_image_url_bizdate
  • 如果在SQL代码过程中你需要使用临时表来过渡中间产出的数据(避免SQL嵌套过于严重,影响运行效率),建议一定在临时表中加入一个时间戳,ex. lsiqg_iqc_input_tmp_${bizdate}不然在补数据或者遇到任务堵塞两个任务同时在调度时,或产生overwrite的一系列问题。
  • 如果存在上游表存在多个分区,但是每个分区处理逻辑一样的话(比如不同国家的分区表处理逻辑其实一样),非常建议在第一步里就把不同分区表的数据汇总起来,可以重新增加一个分区(如venture)来存放融合后的数据。如下示例:



INSERT OVERWRITE TABLE sku_main_image_sig PARTITION (ds = '${bizdate}',venture)
SELECT  id
        ,image_url
        ,venture
FROM    (
            SELECT  id
                    ,image_url
                    ,'ID' AS venture
            FROM    auction_image_id
            WHERE   ds = MAX_PT('auction_image_id')
            UNION
            SELECT  id
                    ,image_url
                    ,'PH' AS venture
            FROM    auction_image_ph
            WHERE   ds = MAX_PT('auction_image_ph')
            UNION
            SELECT  id
                    ,image_url
                    ,'VN' AS venture
            FROM    auction_image_vn
            WHERE   ds = MAX_PT('auction_image_vn')
            UNION
            SELECT  id
                    ,image_url
                    ,'SG' AS venture
            FROM    auction_image_sg
            WHERE   ds = MAX_PT('auction_image_sg')
            UNION
            SELECT  id
                    ,image_url
                    ,'MY' AS venture
            FROM    auction_image_my
            WHERE   ds = MAX_PT('auction_image_my')
            UNION
            SELECT  id
                    ,image_url
                    ,'TH' AS venture
            FROM    auction_image_th
            WHERE   ds = MAX_PT('auction_image_th')
        ) union_table
;


  • 对于重要的数据表,一定要设置监控,防止数据丢失、不正常产出等问题,具体的方法又可以分两类:
  • 设置任务基线(baseline)来保证任务优先级,这样调度的时间更有保障
  • 设置warning的短信/电话或者DQC的监控规则来具体监控数据

简单的任务可以直接在任务中心查看详情中设置:

image.png

对于更加细致的数据层面监控可以通过DQC平台进行配置,比如无数据产出,数据波动,数据最大/最小值监控等。


image.png

写在最后

数据开发基本都是从陌生到熟悉,但是写多了就会发现各种好用的工具/函数,也会发现各种坑,个人拿到数据到数据开发到数据监控的一些经验是:

  1. 拿到数据第一时间验证数据的重复性,有效性;如果是组内问题就反馈,上游链路问题就自己过滤;
  2. 写完数据的每一部分都先验证合理性,这样会提高最终数据的成功率;
  3. 一般节点上线后,会主动去观察3-4天,确保输出是符合预期的(如果会发现应该稳定的数据反而猛然增加or减少,那很可能是数据逻辑有问题);
  4. 定义合理的数据监控,可以避免数据为空,数据波动过大,数据字段不合理等问题;

Enjoy Data Engineering!!


作者 | 周慧玲(逐乐)

来源 | 阿里云开发者公众号

相关实践学习
基于MaxCompute的热门话题分析
本实验围绕社交用户发布的文章做了详尽的分析,通过分析能得到用户群体年龄分布,性别分布,地理位置分布,以及热门话题的热度。
SaaS 模式云数据仓库必修课
本课程由阿里云开发者社区和阿里云大数据团队共同出品,是SaaS模式云原生数据仓库领导者MaxCompute核心课程。本课程由阿里云资深产品和技术专家们从概念到方法,从场景到实践,体系化的将阿里巴巴飞天大数据平台10多年的经过验证的方法与实践深入浅出的讲给开发者们。帮助大数据开发者快速了解并掌握SaaS模式的云原生的数据仓库,助力开发者学习了解先进的技术栈,并能在实际业务中敏捷的进行大数据分析,赋能企业业务。 通过本课程可以了解SaaS模式云原生数据仓库领导者MaxCompute核心功能及典型适用场景,可应用MaxCompute实现数仓搭建,快速进行大数据分析。适合大数据工程师、大数据分析师 大量数据需要处理、存储和管理,需要搭建数据仓库?学它! 没有足够人员和经验来运维大数据平台,不想自建IDC买机器,需要免运维的大数据平台?会SQL就等于会大数据?学它! 想知道大数据用得对不对,想用更少的钱得到持续演进的数仓能力?获得极致弹性的计算资源和更好的性能,以及持续保护数据安全的生产环境?学它! 想要获得灵活的分析能力,快速洞察数据规律特征?想要兼得数据湖的灵活性与数据仓库的成长性?学它! 出品人:阿里云大数据产品及研发团队专家 产品 MaxCompute 官网 https://www.aliyun.com/product/odps 
相关文章
|
5天前
|
供应链
代采系统如何利用大数据分析优化采购决策?
代采系统可以利用大数据分析来优化采购决策
|
5天前
|
存储 关系型数据库 MySQL
大数据量分页查询怎么优化提速
大数据量分页查询怎么优化提速
31 2
|
5天前
|
数据采集 监控 算法
利用大数据和API优化电商决策:商品性能分析实践
在数据驱动的电子商务时代,大数据分析已成为企业提升运营效率、增强市场竞争力的关键工具。通过精确收集和分析商品性能数据,企业能够洞察市场趋势,实现库存优化,提升顾客满意度,并显著增加销售额。本文将探讨如何通过API收集商品数据,并将这些数据转化为对电商平台有价值的洞察。
|
10月前
|
存储 SQL 关系型数据库
大数据量下数据库分页查询优化方案汇总
当需要从数据库查询的表有上万条记录的时候,一次性查询所有结果会变得很慢,特别是随着数据量的增加特别明显,这时需要使用分页查询。对于数据库分页查询,也有很多种方法和优化的点。下面简单说一下我知道的一些方法。
354 2
|
9月前
|
Prometheus 运维 监控
直击运维痛点,大数据计算引擎 EasyMR 的监控告警设计优化之路
监控告警在企业保障系统的稳定性和事故快速恢复的全周期链路中都是至关重要的一环。在新版本的 EasyMR 中袋鼠云开发团队也对监控告警功能进行了全新的优化,通过本文和大家分享监控告警功能的设计思路以及碰到各类问题痛点的解决方法。
311 0
|
5天前
|
数据采集 供应链 安全
利用大数据优化业务流程:策略与实践
【5月更文挑战第11天】本文探讨了利用大数据优化业务流程的策略与实践,包括明确业务目标、构建大数据平台、数据采集整合、分析挖掘及流程优化。通过实例展示了电商和制造企业如何利用大数据改进库存管理和生产流程,提高效率与客户满意度。随着大数据技术进步,其在业务流程优化中的应用将更加广泛和深入,企业需积极采纳以适应市场和客户需求。
|
5天前
|
SQL 分布式计算 资源调度
一文解析 ODPS SQL 任务优化方法原理
本文重点尝试从ODPS SQL的逻辑执行计划和Logview中的执行计划出发,分析日常数据研发过程中各种优化方法背后的原理,覆盖了部分调优方法的分析,从知道怎么优化,到为什么这样优化,以及还能怎样优化。
103490 1
|
5天前
|
机器学习/深度学习 存储 分布式计算
scikit-learn在大数据处理中的优化与加速
【4月更文挑战第17天】面对大数据处理的挑战,scikit-learn的性能优化与加速至关重要。优化策略包括内存管理(数据集缩减、数据流处理、分布式存储)和算法选择(如在线学习、低内存占用算法)。并行化计算通过设置`n_jobs`或使用Dask、Joblib提升速度,分布式计算如Spark、Dask Distributed扩展处理能力。结合案例实践,建议综合考虑各种策略,并关注新技术以适应不断变化的大数据需求。未来,期待更多优化方案提升scikit-learn在大数据场景下的效能。
|
6月前
|
人工智能 Cloud Native 大数据
构建高性能云原生大数据处理平台:融合人工智能优化数据分析流程
构建高性能云原生大数据处理平台:融合人工智能优化数据分析流程
346 0
|
5天前
|
缓存 Java 大数据
CDH大数据环境参数优化指南
CDH大数据环境参数优化指南

热门文章

最新文章

相关产品

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