hive SQL优化总结

1.行过滤

尽早尽量过滤数据，减少每个阶段的数据量。

例如两个表join，把where筛选条件写到单表子查询里再进行join可减少每个表的读取数据量。

2.列裁剪

例如某表有a,b,c,d,e五个字段，但是我们只需要a和b，那么请用select a,b from table 而不是select * from table。

3.分区裁剪

在查询的过程中减少不必要的分区，即尽量指定分区，这一块hive分区表严格模式有强制措施。

4.减少job数

巧妙的使用 UNION ALL 减少 job 数量

对于多表union all场景，我们可以把多个表的数据先读取进来，然后打上标记再去做聚合统计。

利用多表相同的JOIN 条件，去减少 job 的数量

不论是外关联outer join还是内关联inner join，如果join的key相同，不管有多少表，都会合并为一个MapReduce任务。

5.减少任务map数量

主要困扰我们的是小文件问题，首先我们hive配置中添加了set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat参数，

其次从根本上还是要通过采集端文件拆分优化或者小文件合并服务等方面减少小文件的形成。

6.善用multi-insert、善用union all

#查询了两次a

insert overwrite table tmp1 select ... from a where 条件1  union all insert overwrite table tmp2 select ... from a where 条件2;

#查询了一次a

from a insert overwrite table tmp1 select ... where 条件1 insert overwrite table tmp2 select ... where 条件2;

union: 会对获取的结果进行排序去重操作，

union all: 不会对获取的结果进行排序去重操作，

无特殊要求尽量使用union all。

7.善用case when

对于同一个表不同条件筛选的统计需求，可以使用case when语句优化筛选条件并给数据打标签。这样数据只读取一次就可以完成多个指标的统计。

8.join优化

在编写带有join的SQL语句时，应该将条目少的表/子查询放在join操作符的前面。

因为在Reduce阶段，位于join操作符左边的表会先被加载到内存，载入条目较少的表可以有效的防止内存溢出(OOM)。

如果一个表特别小，推荐用mapjoin。在map端完成，尽早结合数据，使reduce端接收数据量减少。同时可以避免小表与大表join产生的数据倾斜。

集群可配置set hive.auto.convert.join = true 参数，我们也可以显式的调用/*+mapjoin(a)*/。

9.count distinct 优化

select count(distinct id) from tablename; select count(1) from (select id from tablename group by id) tmp;

利用group by 优化 大表的count distinct 精确去重统计。

10.多维度分析，grouping sets 、cube、rollup

GROUPING SETS  在一个GROUP BY查询中，根据不同的维度组合进行聚合，等价于将不同维度的GROUP BY结果集进行UNION ALL

CUBE  根据GROUP BY的维度的所有组合进行聚合。

ROLLUP 是CUBE的子集，以最左侧的维度为主，从该维度进行层级聚合。

11.逻辑过于复杂时，引入中间表

首先已有中间表的优先使用中间表，其次对于常用的复杂逻辑处理，可以建立中间表。

12.explain extended + sql

查看sql详细执行计划。