hive sql执行优化策略集合

1、列裁剪、分区裁剪：hive本身提供这两种功能，指的是hive SQL会针对地拉取需要的数据进行运算，相关的参数
set hive.optimize.cp=true（默认true）
set hive.optimize.pruner=true（默认true）

2、谓词下推：hive支持谓词下推功能，在多表进行join时，会先对数据进行过滤、取业务所需数据进行join运算
set hive.optimize.ppd=true（默认true）

3、mapper数量调整：一般来讲，如果输入文件是少量大文件，就减少mapper数；如果输入文件是大量非小文件，就增大mapper数。
减少mapper的方法是调大mapred.min.split.size（默认1B）
增大mapper的方法是调小mapred.min.split.size

4、开启动态分区：hive提供动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区
set hive.exec.dynamic.partition=true（默认false），表示开启动态分区功能
set hive.exec.dynamic.partition.mode=nonstrict（默认strict），表示允许所有分区都是动态的，否则必须有静态分区字段
set hive.exec.max.dynamic.partitions.pernode=1000 （默认1000）表示每个maper或reducer可以允许创建的最大动态分区个数，可以设置大一些，主要看表分区有多少个，超出则会报错
set hive.exec.max.dynamic.partitions=1000 (默认值1000) 表示一个动态分区语句可以创建的最大动态分区个数，超出报错
set hive.exec.max.created.files=10000 (默认10000) 全局可以创建的最大文件个数，超出报错

5、开启并行度：hive中同一个sql中涉及到n个job，默认情况下，每个job是顺序执行的，若每个job没有前后依赖关系，可以设置参数并发执行任务
set hive.exec.parallel=true （默认false）
set hive.exec.parallel.thread.number=8（默认是8）

6、join优化：hive SQL中join操作可能会导致数据倾斜，这样会导致个别分区任务占用大量资源和时间，可以开启以下参数优化
set hive.optimize.skewjoin=true 如果是join过程中出现倾斜，应该设置为true
set hive.skewjoin.key=100000 这个是join的键对应的记录条数，超过这个值则会将数据写进临时文件，准备第二个job进行处理，默认值100000
set hive.skewjoin.mapjoin.map.tasks=100000参数还可以控制第二个job的mapper数量，默认10000

7、group by优化：group by语句会把相同的key的数据传输到一个reduce进行运算，可分别设置如下参数进行优化
①开启map端预聚合
set hive.map.aggr=true（默认true）
set hive.groupby.mapaggr.checkinterval=100000（默认值100000，参数可以设置map端预聚合的行数阈值，超过该值就会分拆job）
②倾斜均衡配置项
hive.group.skewindata=true（默认false）如果是group by过程出现倾斜，应该设置为true

8、使用map join：当某张表数据量很小，需要跟大表做join操作，则可以开启提高运行效率。
set hive.auto.convert.join=true
set hive.mapjoin.smalltable.filesize=2510001000 默认值是25mb
set hive.mapjoin.cache.numrows=25000 默认值是25000，表示map端缓存的数据多少行

9、开启中间数据压缩策略：hive任务执行过程中，各个阶段会有大量数据写磁盘、读磁盘操作，开启以下参数以减轻数据传输压力
set hive.exec.compress.intermediate=true

set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
set hive.intermediate.compression.type=BLOCK

10、reducer数量调整：可以直接设置reducer数量
set mapred.reduce.tasks=200
如果不设这个参数的话，Hive就会自行推测，推算逻辑为下面两个参数计算
set hive.exec.reducers.bytes.per.reduce=256000000用来设定每个reducer能够处理的最大数据量，默认值256MB
set hive.exec.reducers.max=1009用来设定每个job的最大reducer数量
reducer数由上两个参数可以计算得出
reducer_num=MIN(total_input_size/reducers.bytes.per.reducer,reducers.max)
注意：reducer数量与输出文件的数量相关。如果reducer数太多，会产生大量小文件，对HDFS造成压力。如果reducer数太少，每个reducer要处理很多数据，容易拖慢运行时间或者造成OOM。

11、合并小文件：
①输入端合并小文件，先修改输入的文件格式
set ive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
（默认值是org.apache.hadoop.hive.ql.io.HiveInputFormat）
之后修改需要合并的文件大小阈值，有两个参数
mapred.min.split.size.per.node和mapred.min.split.size.per.rack含义是单节点和单机架上的最小split大小。如果发现有split大小小于这两个值（默认都是100MB），则会进行合并。
②输出端合并小文件
set hive.merge.mapfiles=true表示将map-only任务的输出合并
set hive.merge.mapredfiles=true表示将map-reduce任务的输出合并
set hive.merge.size.per.task=256000000 指定每个task输出后合并文件大小的期望值

12、in操作优化：若in后是范围数据，则写between…and…

13、使用is null 或者is not null来判断是不是null：

14、使用sort by替换order by：hive的order by会导致所有map端数据都进入一个reducer中，在数据量大时可能会长时间计算不完，可以使用sort by来替换。使用sort by，那么还是会视情况启动多个reducer进行排序，并且保证每个reducer内局部有序。为了控制map端数据分配到reducer的key，往往还要配合distribute by一同使用。如果不加distribute by的话，map端数据就会随机分配到reducer。如果用order by请一定带上limit
使用示例 ：
以UID为key，以上传时间倒序、记录类型倒序输出记录数据：
select uid,upload_time,event_type,record_data
from calendar_record_log
where pt_date >= 20190201 and pt_date <= 20190224
distribute by uid
sort by upload_time desc,event_type desc;

15、使用group by替换count(distinct)语句：该语句会把所有数据传输到一个reduce进行运算，如果数据量过大，会导致任务执行过长，且有OOM的风险，具体优化见下
优化前： select count(distinct id) from tablename
优化后： select count(1) from (select id from tablename group by id) tmp