背景:用户画像需要计算X天的数据,并且是用户维度去计算,所以需要对历史数据进行id关联。比如用户前天登陆A手机,昨天登陆B手机,今天在算的时候就要归到一个人。在计算的时候就需要将(-X)--(-1)天的数据进行id关联计算。由于数据量大,运行时间慢,故需优化。
涉及的表:
- action_di:行为明细表,需要X个分区。每天6点产出。
- id_relation_da:id关联表,每天4点产出。
- browse_cnt_da:用户浏览结果表,需要X个action_di和id_relation_da进行id转换后,再多个维度进行计算。
优化前状态:每天6点开始跑,运行2个小时,每天8点产出。
优化思路:由于最新一天的action_di不需要做id转换,所以做一张browse_cnt_mid_da,将(-X)--(-2)的数据进行id转换,此表每天4点就可以开始运行,在6点之前可以运行完成。那么browse_cnt_da直接使用browse_cnt_mid_da和(-1)的action_di数据计算即可,两部分数据union all。预计7点可以产出。
遇到的问题:两部分数据union all后,多维度计算之前,出现了out_map。相当于X天的用户行为数据读了两遍才开始计算,早上集群压力大,map很慢,所以没有达到优化效果。
解决思路:将两个数据的union all替换成full outer join的写法,避免出现out_map。注意两个点:
- 关联键避免数据倾斜,比如关联键可以加上行为日期。
- 关联后取数据注意不要取到NULL数据,比如nvl(t1.key, t2.key) as key。
总结:一共有两处优化,一个是链路优化,解决id关联问题;一个是sql优化,避免out_map产生。