ODPS SQL优化总结

ODPS(Open Data Processing Service)是一个海量数据处理平台，基于阿里巴巴自主研发的分布式操作系统(飞天)开发，是公司云计算整体解决方案中最核心的主力产品之一。本文结合作者多年的数仓开发经验，结合ODPS平台分享数据仓库中的SQL优化经验。

背景

数据仓库，是一个面向主题、集成的、随时间变化的、信息本身相对稳定的数据集合。数据仓库从Oracle（单机、RAC)，到MPP(Green plum)，到Hadoop(Hive、Tez、Sprak)，再到批流一体Flink/Blink、数据湖等，SQL都是其主流的数据处理工具。海量数据下的高效数据流转，是数据同学必须直面的一个挑战。本文结合阿里自研的ODPS平台，从自身工作出发，总结SQL的一些优化技巧。

▐ SQL的一些使用技巧

• null

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

• select *

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

• multi insert

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

• 分区限定

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

• limit的使用

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

• UDF函数的使用

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

• 行转列、列转行

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

• 窗口函数的使用

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

• 关联

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

• 笛卡尔积的应用

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

▐ 数据倾斜问题

• 大表关联小表

大表关联小表出现倾斜时，可以使用mapjoin的hint(/+mapjoin(b)/)。

同时可适当调整mapjoin中小表的内存大小：

**set odps.sql.mapjoin.memory.max=512； **默认512,单位M，[128，2048]之间调整。

• 大表关联大表

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

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

• count distinct

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

• odps新特性

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

▐ 常用的参数设置

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

• 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

作用：设定控制文件被合并的最大阈