join写法:
优化写法:
从执行计划可以看出,join写法的执行步骤要更多,多次shuffle也会消耗更多的资源,串行运行的时间也会更长。优化写法只需要在读取所有数据之后,做一次reduce就可以完成。最后对比一下运行时间和资源消耗,优化写法运行时间快20%,资源使用减少30%。(场景越复杂,效果越好)
总结
由于JOIN是离线数据开发中最常出现低效的环节,那么直接干掉JOIN其实是更好的选择。
当多张表的关联键相同取int类型、聚合的值的场景下,union all + group by写法运行更快、更节省资源、代码开发运维更加简单,并且在表行数越多、关联表越多、关联键越多的场景下,优势会更加突出。
关于两种优化写法,优化写法二更加灵活、更好维护、资源占用更少,但是对于需要使用占位数据的场景(比如聚合map),方法一更加适合。
多张大表join提速(字符串类型)
场景介绍
日常开发中,经常遇到从一个主体多张表取属性的情况,比如客户信息相关的数据,A表取地址、B表取电话号、C表取uv、D表取身份信息、E表取偏好。
写法&执行计划探查
假设有3份数据需要关联得到属性,常规的写法为使用2次full outer join + coalesce来关联取值;或者先将3份数据主体合并在一起,再使用3次left join。
-- 本案例和上边案例类似,使用先将主体合并在一起,再使用三次left join SELECT base.cst_id AS cst_id ,t1.bal_init_prin AS bal_init_prin ,t2.amt_retail_prin AS amt_retail_prin ,t3.amt_buy_prin AS amt_buy_prin FROM ( SELECT cst_id FROM @bal_init -- 日初资产 UNION SELECT cst_id FROM @amt_retail -- 当天放款资产 UNION SELECT cst_id FROM @amt_buy -- 当天买入资产 )base LEFT JOIN @bal_init t1 -- 日初资产 ON base.cst_id = t1.cst_id LEFT JOIN @amt_retail t2 -- 当天放款资产 ON base.cst_id = t2.cst_id LEFT JOIN @amt_buy t3 -- 当天买入资产 ON base.cst_id = t3.cst_id ;
接下来看优化写法:
-- STRING数据类型利用json来实现 SELECT cst_id ,GET_JSON_OBJECT(all_val, '$.bal_init_prin') AS bal_init_prin ,GET_JSON_OBJECT(all_val, '$.amt_retail_prin') AS amt_retail_prin ,GET_JSON_OBJECT(all_val, '$.amt_buy_prin') AS amt_buy_prin FROM ( SELECT cst_id ,CONCAT('{',CONCAT_WS(',', COLLECT_SET(all_val)) , '}') AS all_val FROM ( SELECT cst_id ,CONCAT('\"bal_init_prin\":\"', bal_init_prin, '\"') AS all_val FROM @bal_init -- 日初资产 UNION ALL SELECT cst_id ,CONCAT('\"amt_retail_prin\":\"', amt_retail_prin, '\"') AS all_val FROM @amt_retail -- 当天放款资产 UNION ALL SELECT cst_id ,CONCAT('\"amt_buy_prin\":\"', amt_buy_prin, '\"') AS all_val FROM @amt_buy -- 当天买入资产 )t1 GROUP BY cst_id )tt1 ;
对比join写法和优化写法的执行计划
join写法的执行计划:
优化写法:
对比两个执行计划,join写法对于每一张表的数据使用了两次,分别为构建主体和取值,所以每一个MAP、JOIN任务的复杂度还是比较高的,但是优化写法MAP、REDUCE任务简洁明了。并且随着表的增多,JOIN写法的JOIN任务负责度会更高。对比运行时间和资源消耗,优化写法运行快了20%,资源消耗减少20%。(场景越复杂,效果越好)
由于使用到collect_set,所以需要考虑该节点是否存在超内存的问题并进行内存调整,该场景一般情况下不会出现。
总结
同大表join(聚合类型),区别在于此方法适用于STRING类型。注意collect_set函数的内存占用。
mapjoin为什么快?是否生效了?
场景介绍
日常开发中,经常会遇到大表join小表的情况,mapjoin是老生常谈的处理方式,但是也要注意写法、小表内存参数调整以保障mapjoin生效。
写法&执行计划探查
目前ODPS对mapjoin做了优化可以自动开启,不用手动写/* +mapjoin(a,b)*/来开启了。inner join,大表left join小表都可以直接使mapjoin生效。
mapjoin生效写法:
-- base为大表,fee_year_rate为小表 -- 方式一,inner join SELECT base.* ,fee_year_rate.* FROM @base base INNER JOIN @fee_year_rate fee_year_rate ON (base.terms = fee_year_rate.terms) ; -- 方式一,LEFT join SELECT base.* ,fee_year_rate.* FROM @base base LEFT JOIN @fee_year_rate fee_year_rate ON (base.terms = fee_year_rate.terms) ;
mapjoin未生效写法:
-- 方式三,right join SELECT base.* ,fee_year_rate.* FROM @base base RIGHT JOIN @fee_year_rate fee_year_rate ON (base.terms = fee_year_rate.terms) ; -- 方式四, full outer join SELECT base.* ,fee_year_rate.* FROM @base base FULL OUTER JOIN @fee_year_rate fee_year_rate ON (base.terms = fee_year_rate.terms) ;