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