文章目录
一、调优工具
1、explain
explain 查询语句;
由于Hive中没有索引,那我们还explain什么呢?
答:第一、ORC文件和Parquet格式文件是自带索引,可以用。第二、explain时用来查看Map,Reducer,Job执行数量,可以根据数量制定相应的优化方案
2、analyze
分析表数据,用于执行计划选择的参考
收集表的统计信息,如行数、最大值等
使用时调用该信息加速查询
ANALYZE TABLE employee COMPUTE STATISTICS;
ANALYZE TABLE employee_partitioned
PARTITION(year=2014, month=12) COMPUTE STATISTICS;
ANALYZE TABLE employee_id COMPUTE STATISTICS
FOR COLUMNS employee_id;
二、优化设计
使用分区表、桶表
使用索引
使用适当的文件格式,如orc, avro, parquet,rc,orc
使用适当的压缩格式,如snappy
考虑数据本地化 - 增加一些副本
避免小文件
使用Tez引擎代替MapReduce
使用Hive LLAP(在内存中读取缓存)
考虑在不需要时关闭并发
三、查询优化
1、行裁剪、列裁剪
将不必要查询的字段和数据过滤掉,减少数据量
2、避免隐式转化
比例:两张表在关联的时候,关联的key一边是int,一边又是string,这样有可能发生数据倾斜
3、合理的编码规范
子查询不能嵌套超过3层,可以通过表关联进行替换,或者with as 替换
4、HiveSQL一些细节
1、使用 not in 的时候要注意 NULL,因为 not in 会自动过滤 NULL值,如果你想保留NULL值,可以通过coalesce函数将NULL转为其他非空值。
四、配置优化
1、设置本地模式
SET hive.exec.mode.local.auto=true; --default false
SET hive.exec.mode.local.auto.inputbytes.max=50000000;
SET hive.exec.mode.local.auto.input.files.max=5; --default 4
将作业自动转自本地模式,这个之前也有讲到,可以大大提高效率,但一般工作中不设置,因为文件内容太大,设置本地模式,自己电脑受不俩
2、JVM重用
set mapred.job.reuse.jvm.num.tasks=5; --默认为1
通过JVM重用减少JVM启动的消耗
- 默认每个Map或Reduce启动一个新的JVM
- Map或Reduce运行时间很短时,JVM启动过程占很大开销
- 通过共享JVM来重用JVM,以串行方式运行MapReduce Job
- 适用于同一个Job中的Map或Reduce任务
- 对于不同Job的任务,总是在独立的JVM中运行
3、并行执行
SET hive.exec.parallel=true; -- default false
SET hive.exec.parallel.thread.number=16; -- default 8,定义并行运行的最大数量
并行执行可提高集群利用率
- Hive查询通常被转换成许多按默认顺序执行的阶段
- 这些阶段并不总是相互依赖的
- 它们可以并行运行以节省总体作业运行时间
- 如果集群的利用率已经很高,并行执行帮助不大
4、配置合理的 join
set hive.auto.convert.join = true; -- 设置mapjoin,适合大表关联小表
set hive.optimize.skewjoin=true; -- 内连接数据倾斜且是大表关联大表
5、启用CBO(Cost based Optimizer):负载均衡
set hive.cbo.enable=true;
set hive.compute.query.using.stats=true;
set hive.stats.fetch.column.stats=true;
set hive.stats.fetch.partition.stats=true;
6、启用Vectorization(矢量化)
在Hive中,矢量化参数用于优化查询性能,特别是对于涉及大量数据的操作。
这些参数可以在查询执行计划中看到效果。以下是一些常见的Hive矢量化参数:
set hive.vectorized.execution.enabled; -- 是否启用矢量化执行引擎。
set hive.vectorized.execution.reduce.enabled; -- 在Reduce端是否启用矢量化执行引擎。
set hive.vectorized.input.format.excludes; -- 用于指定不使用矢量化格式读取的文件格式。
set hive.vectorized.output.format.excludes; -- 用于指定不使用矢量化格式输出的文件格式。
set hive.vectorized.use.dynamic.partition.pruning; -- 是否启用动态分区修剪。
7、使用CTE、临时表、窗口函数等正确的编码约定
8、group by的时候数据倾斜配置如下参数
set hive.map.aggr = true; -- Map 端部分聚合,相当于Combiner;
set hive.groupby.skewindata=true; -- 有数据倾斜的时候进行负载均衡
9、小文件过多处理
正常情况下1个block块的大小是128M,但是小文件的大小远远低于128M,实际清楚基本不足1M,可以通过如下参数进行调整优化
-- 执行Map前进行小文件合并
-- CombineHiveInputFormat底层是 Hadoop的 CombineFileInputFormat 方法
-- 此方法是在mapper中将多个文件合成一个split作为输入
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat; -- 默认
-- 每个Map最大输入大小(这个值决定了合并后文件的数量)
set mapred.max.split.size=256000000; -- 256M
-- 一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000; -- 100M
-- 一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000; -- 100M
-- 设置map端输出进行合并,默认为true
set hive.merge.mapfiles = true;
-- 设置reduce端输出进行合并,默认为false
set hive.merge.mapredfiles = true;
-- 设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000; -- 256M
-- 当输出文件的平均大小小于该值时,启动一个独立的MapReduce任务进行文件merge
set hive.merge.smallfiles.avgsize=16000000; -- 16M
-- hive的查询结果输出是否进行压缩
set hive.exec.compress.output=true;
-- MapReduce Job的结果输出是否使用压缩
set mapreduce.output.fileoutputformat.compress=true;
-- 设置reduce的数量有两种方式,第一种是直接设置reduce个数
set mapreduce.job.reduces=10;
-- 第二种是设置每个reduce的大小,Hive会根据数据总大小猜测确定一个reduce个数
set hive.exec.reducers.bytes.per.reducer=5120000000; -- 默认是1G,设置为5G
10、数据倾斜治理
1、产生的原因:本质是数据分配不均匀
2、主要发生在那些场景:join,group by,count(distinct)
3、如何识别数据倾斜:通过查看某个task是否卡在了99%
4、如何治理:1)数据探查找到倾斜的原因,如果是join阶段发生了倾斜,继续看
如果是大表关联小表,则使用mapjoin,设置小文件大小(小文件大于 >= 小表的大小),默认是256MB
如果是大量关联的key存在大量空值,需要做随机数打散,例如:a.key = if(coalesce(a.key,‘’)=‘’,concat(‘hive’,rand()),b.key)
如果是大表关联大表 ,使用skewjoin参数进行优化,上面有讲到
如果是不同数据类型引发的倾斜,例如:关联表a中的key是int,被关联表中的key是string,可以在关联的时候修改数据类型 on a.key = cast(b.key as int)
如果是group by导致的倾斜,可以设置如下参数
set hive.map.aggr = true; -- Map 端部分聚合,相当于Combiner;
set hive.groupby.skewindata=true; -- 有数据倾斜的时候进行负载均衡
set hive.groupby.mapaggr.checkinterval=100000 // 默认,设置map端预聚合的行数阈值,超过该值就会分拆job。
如果业务数据就是倾斜的:可以采用分治算法
五、压缩
减少传输数据量,会极大提升MapReduce性能
采用数据压缩是减少数据量的很好的方式