HiveSQL优化

【Hive】Hive SQL的优化

Hive SQL的执行，Hive的驱动器对SQL进行解析优化，从HDFS获取数据，然后转化为MapReduce，提交到Hadoop集群运行

所以Hive SQL的优化从下面三点来执行。

1. Hive SQL方面的优化

这方面是一些SQL的基本认知，以及如何让Hive的驱动器更好的解析优化你的SQL。

1. 避免笛卡尔积

   也就是避免使用 cross join，或者其它join的时候不加on条件。

2. 避免全字段查询

   这个主要是针对大宽表，不要直接 select *，select的时候只取需要的字段。

3. 避免全表查询，即用分区表

   在表数据量过大的时候，改为用分区表存储；在查询分区表的时候，需要提供分区字段的过滤。

4. 避免distinct

   对于MR来说，一般SQL中的distinct会使结果最终都放到一个reduce中处理，导致影响查询效率。

对于这方面的优化，Hive有一个严格模式可以有效的帮助我们的SQL书写

set hive.mapred.mode=strict; -- 开启严格模式
-- 开启后，下面的三个参数对应的值会是true
hive.strict.checks.no.partition.filter=true; -- 对分区表查询必须带分区条件，否则会查询失败
hive.strict.checks.orderby.no.limit=true; -- 带order by的查询，必须使用limit限制查询数据条数，否则会查询失败
hive.strict.checks.cartesian.product=true; -- 不能进行笛卡尔积的查询

2. Hive配置参数方面优化

第一种：开启Map join

set hive.auto.convert.join=true
set hive.mapjoin.smalltable.filesize=2500000 -- 限制小表大小

所谓Map join就是指将小表加载到分布式内存中，在执行Map任务时可以将join操作只在各数据的本地节点上执行，减少了数据的来回传输，从而提高执行性能。

第二种：处理数据倾斜

set hive.groupby.skewindata=true;
set hive.optimize.skewjoin=true;
set hive.skewjoin.key=100000;

1. 在group by数据倾斜

   可以设置第一个参数，设置之后group by的MR任务会拆成两个执行，先在第一个任务上随机处理group by的key，第二个任务在第一个任务预处理的结果上再做最终聚合。

2. join时数据倾斜（对outer join无效）

   可以设置第二个和第三个参数，设置之后，如果join的key数量超过了 hive.skewjoin.key 这里设置的值，就会将超过对应数量的key值放入单独的目录中进行 join 处理

3. 空值过多引起的数据倾斜

   如果空值是异常数据可以直接先过滤掉；如果不是异常数据的话，可以把空值较多的表字段进行随机赋值，将其随机放在其它reduce中，只要null值关联不上就不会影响结果

第三种：暴力加大内存

-- 所有的效率问题都是因为资源不够
set mapreduce.map.memory.mb=8096;
set mapreduce.reduce.memory.mb=8096;

3. Hadoop集群方面的优化

Hadoop永远的天敌，小文件

小文件过多会导致 导致map任务的数量巨大，占用过多资源，甚至是执行失败

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;-- 开启小文件合并，默认
set mapred.max.split.size=256000000;-- 每个Map最大输入大小 默认256000000 即256mb 增大一倍可以看到 map任务 少一半