給技術新人的ODPS優化建議

-- 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;

SELECT  /*+ MAPJOIN(b) */
        a.*
FROM    test_a a
JOIN test_b b
ON      a.user_key = b.user_key
;
//就是在sql語句前加一個標記說這是mapjoin，把小表別名寫在括弧里

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

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

-- 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
;

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')

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

--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

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
;