----原语句(运行18min)
INSERT into TABLE schema.dstable
SELECT bb.ip FROM
(SELECT ip, sum(click) click_num, round(sum(click)/sum(imp),4) user_click_rate FROM schema.srctable1
WHERE date = '20171020' AND ip IS NOT NULL AND imp>0 GROUP BY ip) bb
LEFT OUTER JOIN (SELECT round(sum(click)/sum(imp),4) avg_click_rate FROM schema.srctable1 WHERE date = '20171020') aa
LEFT OUTER JOIN schema.dstable cc on cc.ip = bb.ip
WHERE cc.ip is null AND
(bb.user_click_rate > aa.avg_click_rate * 3 AND click_num > 500) OR (click_num > 1000)
分析:
1、aa表存放的就是一个指标数据,1条记录,列为小表
2、bb表存放的是按ip聚合的明细数据,记录很多,列为大表
3、cc表用来过滤ip,数量也很小,列为过滤表,作用很小。
查看执行计划,发现bb与aa进行left outer join时,引发了shuffle过程,造成大量的磁盘及网络IO,影响性能。
优化方案1:调整大小表位置,将小表放在左边后,提升至29s (该方案一直不太明白为啥会提升,执行计划里显示的也就是大小表位置调换下而已
INSERT into TABLE schema.dstable
SELECT bb.ip FROM
(SELECT ip, sum(click) click_num, round(sum(click)/sum(imp),4) user_click_rate FROM schema.srctable1
WHERE date = '20171020' AND ip IS NOT NULL AND imp>0 GROUP BY ip) bb
LEFT OUTER JOIN (SELECT round(sum(click)/sum(imp),4) avg_click_rate FROM schema.srctable1 WHERE date = '20171020') aa
LEFT OUTER JOIN schema.dstable cc on cc.ip = bb.ip
WHERE cc.ip is null AND
(bb.user_click_rate > aa.avg_click_rate * 3 AND click_num > 500) OR (click_num > 1000)
分析:
1、aa表存放的就是一个指标数据,1条记录,列为小表
2、bb表存放的是按ip聚合的明细数据,记录很多,列为大表
3、cc表用来过滤ip,数量也很小,列为过滤表,作用很小。
查看执行计划,发现bb与aa进行left outer join时,引发了shuffle过程,造成大量的磁盘及网络IO,影响性能。
优化方案1:调整大小表位置,将小表放在左边后,提升至29s (该方案一直不太明白为啥会提升,执行计划里显示的也就是大小表位置调换下而已