join实践: 万亿级数据量任务优化历程
本帖最后由 Mirinda 于 2022-4-11 12:09 编辑问题导读：
1.你熟悉join吗?
2.文中三次优化有什么不同？
3.如果是你你会怎么优化？
优化前SELECTcount(*)
FROM tbl_0 a
JOIN tbl_1 b
ON a.ds = 20220310
AND b.ds = 20220310
AND a.key = b.key
;大概执行2h,还未得出结果。
第一次优化暴力增加join 的并行度， 没有什么优化是比加资源来得更直接。set odps.sql.joiner.instances=1000; //表示join 的并行度加到1000
SELECTcount(*)
FROM tbl_0 a
JOIN tbl_1 b
ON a.ds = 20220310
AND b.ds = 20220310
AND a.key = b.key
;大概执行2h,仍未得出结果。
第二次优化重新分析两张表数据量，a 表数据量750w+,b 表数据量350w+， 在未做任何优化情况下数据是需要经过shuffle， 将相同的key分布到相同的节点上， 首先考虑使用mapjoin 解决，使其不用执行shuffle操作。SELECT /*+mapjoin(b)*/ count(*)
FROM tbl_0 a
JOIN tbl_1 b
ON a.ds = 20220310
AND b.ds = 20220310
AND a.key = b.key
;大概执行2h,仍未得出结果。
第三次优化重新分析表数据分布情况， 查看a、b 两张表的join-key 的数据情况：SELECT
key
,count(*)
FROM tbl_0/tbl_1
WHERE ds =20220312
GROUP BY KEY
ORDER BY count(*) desc;
a 表
WorkWell1586079
GoodQuality1428452
ProductExperience1186742
BuyerRecomendSeller1147469
UserExperience763998
b表
ProductExperience832075
UserExperience309142
GoodQuality245208
BuyerRecomendSeller213484
SPS_Material196508
两张表的key 的类型不多，但是单个key值的个数比较多，例如GoodQuality 在a表中1428452条记录，在b表中245208条记录，最终就会产生 1428452*245208=3500亿的数据量，这样相同的GoodQuality 分布到同一个节点去处理，很明显发生数据长尾效应。对于这样的情况，普通的mapjoin 或者是sort-merge已经不适合了，需要尽可能的将key分散，分发到不同的节点去处理，因此使用随机前缀+扩容的方式处理。什么是随机前缀+扩容？对其中一张表数据量扩容n倍，另外一张表对join-key生成随机0~n的随机前缀数据，通过这种方式将join-key充分打散到下游不同的节点处理，以达到优化效果。在这里通过定义udf 实现随机前缀， udtf实现数据扩容://生成max以内的随机数
public class RandomData extends UDF {
public Random r;
@Override
public void setup(ExecutionContext ctx) throws UDFException {
r=new Random();
}
public Integer evaluate(Integer max) {
returnr.nextInt(max);
}
}
//数据量扩充
public class ExpandData extends UDTF {
@Override
public void setup(ExecutionContext ctx) throws UDFException {
}
@Override
public void process(Object[] args) throws UDFException {
Long expand=(Long)(args);//代表了扩充的倍数
Object[] args1=new Object;
for(int i=0;i<expand;i++){
for(int j=0;j<args.length;j++){
args1=i+"_"+args;
}
super.forward(args1);
}
}
@Override
public void close() throws UDFException {
}
}然后重新执行SQL:set odps.sql.joiner.instances=1000;
SELECT
count(*)
from (
select *, CONCAT_WS('_',RandomData(1000),key) newKeyfromtbl_0
where ds=20220312
) a join (
SELECTnewKey from (
SELECT
key
from
tbl_1where ds=20220312)
LATERAL viewExpandData(1000,key) tmp as cnt,newKey
) b on a.newKey=b.newKey;耗时20min左右得出结果，最终得到的结果大于一万亿。
最新经典文章，欢迎关注公众号
http://www.aboutyun.com/data/attachment/forum/201903/18/215536lzpn7n3u7m7u90vm.jpg
原文链接：https://mp.weixin.qq.com/s/VdR874eGPp5gSr7YAiBaKA
页:
[1]