ODPS-SQL优化总结
ODPS(Open Data Processing Service)是一个海量数据处理平台，基于阿里巴巴自主研发的分布式操作系统(飞天)开发，是公司云计算整体解决方案中最核心的主力产品之一。本文结合多年的数仓开发经验，结合ODPS平台分享数据仓库中的一些SQL优化技巧。
一些使用小技巧
- null
我们在进行=/<>/in/not in等判断时，null会不包含在这些判断条件中，所以在对null的处理时可以使用nvl或者coalesce函数对null进行默认转换。
- 列裁剪
在数据开发或者线上任务时，尽可能提前对列进行裁剪，即使是全表字段都需要，也尽可能的把字段都写出来，这样做有两个好处：一是减少了数据运算中不必要的数据读取，二是避免后期因为原表或者目标表字段增加，导致的任务报错。
- multi insert
读取同一张表，但粒度不同，需要插入多张表时，可以考虑使用from () tab insert overwrite A insert overwrite B的方式，减少资源的浪费。如果开发规范有限制，具体情况可以视情况尽可能复用公共数据，如通过临时表的方式临时存储这部分逻辑。
- 分区限定
分区表又会根据业务规则分为增量表、全量表、快照表等。在做简单查询，或者数据探查时，一定要养成习惯先限定分区。
- limit的使用
临时查询或者数据探查时，养成习惯加上limit，会快速的查询出你想要的数据，且消耗更少的资源。
- UDF函数的使用
尽可能把UDF的使用下沉到第一层子查询中，效率会有很大的提升。
- 行转列、列转行
collect_set 、lateral view函数可以实现行转列或者列转行的功能。
- 窗口函数的使用
- 分区排序
- 动态Group By
- Top N
- 累计计算
- 层次查询
1.可以通过row_number()/rank() over(partition by order by )的方式实现数据按照某个字段分组的排序，也可以通过max(struct())的方式实现。
2.面对一些针对不同维度上，相同指标的统计，可以巧用GROUPING SETS、ROLLUP、CUBE，再结合GROUPING()、GROUPING_ID()，使用更少的代码，消耗更少的集群资源，实现复杂的多维度聚合查询。
- 关联
左关联、内关联、右关联、left anti join 、left semi join等，可以实现不同情况下的多表关联。关联字段要确保字段类型的一致。
- 笛卡尔积的应用
面对把一行数据翻N倍的场景，可以考虑自己创建一个维表，通过笛卡尔积操作；同时也可以通过LATERAL VIEW POSEXPLODE(split(REGEXP_REPLACE(space(end_num -start_num+1),' ','1,'),',')) t AS pos ,val的方式。
数据倾斜问题
导致数据倾斜的场景
根据使用经验总结，引起数据倾斜的主要原因有如下几类：
- Join
- GroupBy
- Count（Distinct）
- ROW_NUMBER（TopN）
- 动态分区
其中出现的频率排序为JOIN > GroupBy > Count(Distinct) > ROW_NUMBER > 动态分区
解决思路
- 1.查找造成数据倾斜的热点key
当SQL中包含Join/GroupBy/PartitionBy/DistributedBy等操作时，通过对上述操作所用到的列进行统计，通常就能够找到造成数据倾斜的热点key。
- A表JoinB表
针对Join端产生的数据倾斜，会存在多种不同的情况，例如大表和小表Join、大表和中表Join、Join热值长尾。
select * from t1 left join t2 on t1.id = t2.id;
-- 分别查找t1/t2热点id的热点值：
select id, count(1) as c from t1 group by id order by c desc;
select id, count(1) as c from t2 group by id order by c desc;
- GroupBy
select c1,c2,c3 from t1 group by c1,c2,c3;
-- 各个列中热点key的查找：
select c1, count(1) as c from t1 group by c1 order by c desc;
select c2, count(1) as c from t1 group by c2 order by c desc;
select c3, count(1) as c from t1 group by c3 order by c desc;
- 2.查找对应的作业诊断信息
通过查看执行计划，根据jobid查看任务执行情况，查看作业的瓶颈在那个阶段（map/reduce），job的gc情况等。
- 3.业务表结构和数据优化处理
- 过滤掉不关心的热点key值、null值、空值和0值
- 根据预先统计热点的key值，对其处理，打散，如
distribute by rand() - 表结构在设计时，保证相同业务字段的字段类型一致
- 表结构设计时，针对业务使用场景，合理设置分区范围
- 4.SQL优化
- 尽可能早的进行数据裁剪（含聚合）筛选，然后再进行
Join/GroupBy/PartitionBy/DistributedBy等操作 - 公共数据尽可能的共享，推荐使用公用表表达式（CTE）
- 根据作业诊断信息，调整相关参数进行优化
- 1）大表关联小表出现倾斜时，可以使用mapjoin的hint(/+mapjoin(b)/)，同时可适当调整mapjoin中小表的内存大小
- 2）大表关联大表，存在热点key：可以考虑对大表进行拆分，根据join的key，把热点的数据拆出来走mapjoin，其余的考虑普通join即可；也可以尝试skewjoin的hint
- 3）大表关联大表，大表中不存在热点key：可以考虑在分区的基础上加上桶，对关联字段进行分桶，减少shuffle的数据量
- 4）
count distinct导致的数据倾斜，可以尝试使用group by先进行数据去重，再count - 5）在输入数据量太大，MapTask过多，调度开销大，在资源一定的情况下，可以加大单个map处理的数据量，减少map的task个数
- 6）在输入数据量不大，计算逻辑复杂，作业的map/reduce个数通常在个位数或者十位数，增加task并发度，减少单个task处理的数据量来加快任务运行速度
