给技术新人的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条！

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还支持不等式，如下面的例子：

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。

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

Left Semi Join

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

当右表没有重复数据时，和Join是一致的，只会保留相同的列下来
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.一句话：对于左表中的一条数据，如果右表中不存在符合关联条件的数据，则输出左表。

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

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

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

举个栗子，其中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很丝滑：

合理设置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:

在资源充沛的情况下，我们设置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里直接将你的资源名写进去，这样在流程启动后，你的资源才会被有效调用起来。

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
;