ODPS SQL 优化总结

code36

已于 2024-07-06 14:43:45 修改

阅读量1.1k

点赞数 26

CC 4.0 BY-SA版权

文章标签： odps sql java

于 2024-06-25 08:57:14 首次发布

本文链接：https://blog.csdn.net/pujungong/article/details/139946058

1、SQL的一些使用技巧

1）null

我们在进行=/<>/in/not in等判断时，null会不包含在这些判断条件中，所以在对null的处理时可以使用nvl或者coalesce函数对null进行默认转换。

2）select *

在数据开发或者线上任务时，尽可能提前对列进行剪裁，即使是全表字段都需要，也尽可能的把字段都写出来(如果实在觉得麻烦，可以使用数据地图的生成select功能)，一是减少了数据运算中不必要的数据读取，二是避免后期因为原表或者目标表字段增加，导致的任务报错。

3）multi insert

读取同一张表，但是因为粒度不同，需要插入多张表时，可以考虑使用from () tab insert overwrite A insert overwrite B的方式，减少资源的浪费。当然，有些团队的数仓开发规范中会规定一个任务不能有两个目标表，具体情况可以视情况尽可能复用公共数据，如通过临时表的方式临时存储这部分逻辑。

4）分区限定

ODPS表大部分都是分区表，分区表又会根据业务规则分为增量表、全量表、快照表等。所以在做简单查询，或者数据探查时，一定要养成习惯先限定分区ds。经常会在jobhistory中看到很多好资源的任务都是因为分区限定不合理或者没有限定分区导致的。

5）limit

的使用临时查询或者数据探查时，养成习惯加上limit，会快速的查询出你想要的数据，且消耗更少的资源。

6）UDF函数的使用

尽可能把UDF的使用下沉到第一层子查询中，效率会有很大的提升。

7）行转列、列转行

collect_set 、lateral view函数可以实现行转列或者列转行的功能，好多大佬也都写过类似的可以参考。

8）窗口函数的使用

可以通过row_number()/rank()over(partition by order by )的方式实现数据按照某个字段分组的排序，也可以通过max(struct())的方式实现。

9）关联

左关联、内关联、右关联、left anti join 、left semi join等，可以实现不同情况下的多表关联。关联字段要确保字段类型的一致。

10）笛卡尔积的应用

有时会存在把一行数据翻N倍的诉求，这时候可以考虑自己创建一个维表，通过笛卡尔积操作；同时也可以通过:LATERAL VIEW POSEXPLODE(split(REGEXP_REPLACE(space(end_num -start_num+1),' ','1,'),',')) t AS pos ,val的方式。

11）提高某一个表的map数

现实任务重可能会使用到商品表，想降低商品表的map任务数，但是某些复杂的逻辑或者记录数较多，但是存储较少的表又想提高map任务数时，使用全局的split size无法满足效率的优化。因此需要引入split size的hint，来优化某一个表的初速度。使用方式为加上/*+SPLIT_SIZE(8)*/这个hint，括号里数值的单位为MB。

2、数据倾斜问题

1）大表关联小表

大表关联小表出现倾斜时，可以使用mapjoin的hint(/*+mapjoin(b)*/)。同时可适当调整mapjoin中小表的内存大小：set odps.sql.mapjoin.memory.max=512；默认512,单位M，[128，2048]之间调整。

2）大表关联大表

一种情况，大表中存在热点key：可以考虑对大表进行拆分，根据join的key，把热点的数据拆出来走mapjoin，其余的考虑普通join即可。当然也有skewjoin的hint可以参考使用。

另一种情况，大表中不存在热点key：可以考虑在分区的基础上加上桶，对关联字段进行分桶，减少shuffle的数据量。

3）count distinct

常见的数据倾斜还有一种情况是因为使用了count distinct，这种情况可以考虑使用group by先进行数据去重，再count。

4）odps新特性

可以关注MaxCompute(ODPS2.0)重装上阵以及Inside MaxCompute SQL系列文章系列文章，很多心得特性对于我们的性能优化有很大的帮助。

5）小表关联大表

小表关联大表，还要取出小表里的所有数据，可以考虑dynamic_filter的新特性，/*+dynamic_filter(A,B)*/。

3、常用的参数设置

常用的调整无外乎调整map、join、reduce的个数，map、join、reduce的内存大小。本文以ODPS的参数设置为例，参数可能因版本不同而略有差异。

3.1 Map设置

set odps.sql.mapper.cpu=100作用：设置处理Map Task每个Instance的CPU数目，默认为100，在[50,800]之间调整。场景：某些任务如果特别耗计算资源的话，可以适当调整Cpu数目。对于大多数Sql任务来说，一般不需要调整Cpu个数的。set odps.sql.mapper.memory=1024作用：设定Map Task每个Instance的Memory大小，单位M，默认1024M，在[256,12288]之间调整。场景：当Map阶段的Instance有Writer Dumps时，可以适当的增加内存大小，减少Dumps所花的时间。set odps.sql.mapper.merge.limit.size=64作用：设定控制文件被合并的最大阈值，单位M，默认64M，在[0,Integer.MAX_VALUE]之间调整。场景：当Map端每个Instance读入的数据量不均匀时，可以通过设置这个变量值进行小文件的合并，使得每个Instance的读入文件均匀。一般会和odps.sql.mapper.split.size这个参数结合使用。set odps.sql.mapper.split.size=256作用：设定一个Map的最大数据输入量，可以通过设置这个变量达到对Map端输入的控制，单位M，默认256M，在[1,Integer.MAX_VALUE]之间调整。场景：当每个Map Instance处理的数据量比较大，时间比较长，并且没有发生长尾时，可以适当调小这个参数。如果有发生长尾，则结合odps.sql.mapper.merge.limit.size这个参数设置每个Map的输入数量。