带你读《2022技术人的百宝黑皮书》——ODPS SQL优化总结(9)

1、创建测试表
create table tmp_zhangtao_test_hash_range like dws_tm_brd_pwr_deep_brd_usr_cat_1d LIFECY
CLE 2;
2、查看测试表结构
desc mkt.tmp_zhangtao_test_hash_range;
3、修改测试表支持桶；测试时发现user_id倾斜情况不太严重
ALTER TABLE tmp_zhangtao_test_hash_range CLUSTERED BY (user_id)
SORTED by ( user_id) INTO 1024 BUCKETS;
4、插入数据，这里发现多了一个1024个任务的reduce。
insert OVERWRITE table mkt.tmp_zhangtao_test_hash_range partition(ds,cate_flag)
SELECT
 brand_id,
 cate_id,
 user_id,
 shop_type,
 deep_score,
 brd_ord_amt,
 discovery_score,
 engagement_score,
 enthusiasm_score,
 itm_sty_tme,
 itm_vst_cnt,
 liv_sty_tme,
 liv_vst_cnt,
 vdo_sty_tme,
 vdo_vst_cnt,
 img_txt_sty_tme,
 img_txt_vst_cnt,
 col_cnt_ufm,
 crt_cnt_ufm,
 sch_cnt_ufm,
 mkt_iat_cnt,
 fan_flw_cnt,
 fst_itm_sty_tme,
 fst_itm_vst_cnt,
 col_cnt_fm,
 crt_cnt_fm,
 sch_cnt_fm,
 shr_cnt,
 cmt_cnt,
 pvt_iat_cnt,
 ds,
 cate_flag
 FROM dws_tm_brd_pwr_deep_brd_usr_cat_1d
 WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -89, 'dd'), 'yyyymmdd');
 5、查询数据性能比对
SELECT cate_id
 ,shop_type
 ,user_id
 ,SUM(deep_score) AS deep_score
 ,SUM(brd_ord_amt) AS brd_ord_amt
 ,SUM(discovery_score) AS discovery_score
 ,SUM(engagement_score) AS engagement_score
 ,SUM(enthusiasm_score) AS enthusiasm_score
 ,SUM(itm_sty_tme) AS itm_sty_tme
 ,SUM(itm_vst_cnt) AS itm_vst_cnt
 ,SUM(liv_sty_tme) AS liv_sty_tme
 ,SUM(liv_vst_cnt) AS liv_vst_cnt
 ,SUM(vdo_sty_tme) AS vdo_sty_tme
 ,SUM(vdo_vst_cnt) AS vdo_vst_cnt
 ,SUM(img_txt_sty_tme) AS img_txt_sty_tme
 ,SUM(img_txt_vst_cnt) AS img_txt_vst_cnt
 ,SUM(col_cnt_ufm) AS col_cnt_ufm
 ,SUM(crt_cnt_ufm) AS crt_cnt_ufm
 ,SUM(sch_cnt_ufm) AS sch_cnt_ufm
 ,SUM(mkt_iat_cnt) AS mkt_iat_cnt
 ,SUM(fan_flw_cnt) AS fan_flw_cnt
 ,SUM(fst_itm_sty_tme) AS fst_itm_sty_tme
 ,SUM(fst_itm_vst_cnt) AS fst_itm_vst_cnt
 ,SUM(col_cnt_fm) AS col_cnt_fm
 ,SUM(crt_cnt_fm) AS crt_cnt_fm
 ,SUM(sch_cnt_fm) AS sch_cnt_fm
 ,SUM(shr_cnt) AS shr_cnt
 ,SUM(cmt_cnt) AS cmt_cnt
 ,SUM(pvt_iat_cnt) AS pvt_iat_cnt
 FROM dws_tm_brd_pwr_deep_usr_cat_1d/tmp_zhangtao_test_hash_range
 WHERE ds = TO_CHAR(DATEADD(TO_DATE('${bizdate}', 'yyyymmdd'), -89, 'dd'), 'yyyymmdd')
 AND cate_flag = '1'
 GROUP BY cate_id
 ,shop_type
 ,user_id