0


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

标签: hive sql hadoop

本文转载自: https://blog.csdn.net/qq_24022561/article/details/139606228
版权归原作者 qq_24022561 所有, 如有侵权,请联系我们删除。

“hive sql执行优化策略集合”的评论:

还没有评论