hive调优
一.针对于Hive内部调优的一些方式
01.请慎重使用COUNT(DISTINCT column)
*原因:*distinct会将b列所有的数据保存到内存中,形成一个类似hash的结构,速度是十分的块;但是在大数据背景下,因为b列所有的值都会形成以key值,极有可能发生OOM;
*解决方案:*可以考虑使用Group By 或者 ROW_NUMBER() OVER(PARTITION BY col)方式代替COUNT(DISTINCT column)
02.小文件会造成资源的过度占用以及影响查询效率
原因: 1)小文件在HDFS中存储本身就会占用过多的内存空间,
那么对于MR查询过程中过多的小文件又会造成启动过多的Mapper Task,
每个Mapper都是一个后台线程,会占用JVM的空间;
2)在Hive中,动态分区会造成在插入数据过程中,生成过多零碎的小文件;
3)不合理的Reducer Task数量的设置也会造成小文件的生成,
因为最终Reducer是将数据落地到HDFS中的Hive中分桶表的设置
解决方案: 在数据源头HDFS中控制小文件产生的个数,比如
a.采用Sequencefile作为表存储格式,不要用textfile,在一定程度上可以减少小文件(常见于在流计算的时候采用Sequencefile格式进行存储)
b.减少reduce的数量(可以使用参数进行控制)
c.慎重使用动态分区,最好在分区中指定分区字段的val值
d.最好数据的校验工作,比如通过脚本方式检测hive表的文件数量,并进行文件合并
e.合并多个文件数据到一个文件中,重新构建表
**03.请慎重使用SELECT * **
*原因:*在大数据量多字段的数据表中,如果使用 SELECT * 方式去查询数据,会造成很多无效数据的处理,会占用程序资源,造成资源的浪费
*解决方案:*在查询数据表时,指定所需的待查字段名,而非使用 * 号
04.不要在表关联后面加WHERE条件
原因: 比如以下语句:
SELECT
*
FROM stu t
LEFT JOIN course t1
ON t.id=t2.stu_id
WHERE t.age=18;
解决方案:
采用谓词下推的技术,提早进行过滤有可能减少必须在数据库分区之间传递的数据量
谓词下推的解释:所谓谓词下推就是通过嵌套的方式,将底层查询语句尽量推到数据底层去过滤,这样在上层应用中就可以使用更少的数据量来查询,这种SQL技巧被称为谓词下推(Predicate pushdown)
那么上面语句就可以采用这种方式来处理:
SELECT
*
FROM
(
SELECT
*
FROM stu
WHERE age=18
) t
LEFT JOIN course t1
on t.id=t1.stu_id
05.处理掉字段中带有空值的数据
原因: 一个表内有许多空值时会导致MapReduce过程中,空成为一个key值,对应的会有大量的value值, 而一个key的value会一起到达reduce造成内存不足
解决方式:
a.在查询的时候,过滤掉所有为NULL的数据,比如:
create table res_tbl as
select
n.*
from
(
select
*
from res
where id is not null
) n
left join org_tbl o
on n.id = o.id;
b.查询出空值并给其赋上随机数,避免了key值为空(数据倾斜中常用的一种技巧)
create table res_tbl as
select
n.*
from res n
full join org_tbl o
on case when n.id is null then concat('hive', rand()) else n.id end = o.id;
06.设置并行执行任务数
通过设置参数 hive.exec.parallel 值为 true,就可以开启并发执行。
不过,在共享集群中,需要注意下,如果 job 中并行阶段增多,那么集群利用率就会增加。
a.打开任务并行执行
set hive.exec.parallel=true;
b.同一个 sql 允许最大并行度,默认为 8
set hive.exec.parallel.thread.number=16;
07.设置合理的Reducer个数
原因: 过多的启动和初始化 reduce 也会消耗时间和资源有多少个Reduer就会有多少个文件产生,如果生成了很多个小文件,那么如果这些小文件作为下一个任务的输入,则也会出现小文件过多的问题
解决方案:
a.Reducer设置的原则:
每个Reduce处理的数据默认是256MB
hive.exec.reducers.bytes.per.reducer=256000000
每个任务最大的reduce数,默认为1009
hive.exec.reducers.max=1009
b.计算reduce数的公式:
N=min(每个任务最大的reduce数,总输入数据量/reduce处理数据量大小)
c.设置Reducer的数量:
set mapreduce.job.reduces=n
08.JVM重用
JVM重用是Hadoop中调优参数的内容,该方式对Hive的性能也有很大的帮助,特别对于很难避免小文件的场景或者Task特别多的场景,这类场景大数据书执行时间都很短Hadood的默认配置通常是使用派生JVM来执行map和reduce任务的,会造成JVM的启动过程比较大的开销,尤其是在执行Job包含有成百上千个task任务的情况。
JVM重用可以使得JVM实例在同一个job中重新使用N次,N的值可以在hadoop的mapred-site.xml文件中进行设置
mapred.job.reuse.jvm.num.tasks10
09.为什么任务执行的时候只有一个reduce?
原因: 使用了Order by (Order By是会进行全局排序)
直接COUNT(1),没有加GROUP BY,比如:有笛卡尔积操作
SELECT COUNT(1) FROM tbl WHERE pt=’201909’
*解决方案:*避免使用全局排序,可以使用sort by进行局部排序
使用GROUP BY进行统计,不会进行全局排序,比如:
SELECT pt,COUNT(1) FROM tbl WHERE pt=’201909’group by pt;
10.选择使用Tez引擎
a.Tez: 是基于Hadoop Yarn之上的DAG(有向无环图,Directed Acyclic Graph)计算框架。它把Map/Reduce过程拆分成若干个子过程,同时可以把多个Map/Reduce任务组合成一个较大的DAG任务,减少了Map/Reduce之间的文件存储。同时合理组合其子过程,也可以减少任务的运行时间
b.设置:
hive.execution.engine = tez;
通过上述设置,执行的每个HIVE查询都将利用Tez
c.可以选择使用spark作为计算引擎
11.选择使用本地模式
有时候Hive处理的数据量非常小,那么在这种情况下,为查询出发执行任务的时间消耗可能会比实际job的执行时间要长,对于大多数这种情况,hive可以通过本地模式在单节点上处理所有任务,对于小数据量任务可以大大的缩短时间,可以通过设置:hive.exec.mode.local.auto=true
12.选择使用严格模式
Hive提供了一种严格模式,可以防止用户执行那些可能产生意想不到的不好的影响查询比如:对于分区表,除非WHERE语句中含有分区字段过滤条件来限制数据范围,否则不允许执行,也就是说不允许扫描所有分区使用ORDER BY 语句进行查询是,必须使用LIMIT语句,因为ORDER BY 为了执行排序过程会将所有结果数据分发到同一个reduce中进行处理,强制要求用户添加LIMIT可以防止reducer额外的执行很长时间;
严格模式的配置:hive.mapred.mode=strict
二.hive问题场景及优化
场景1.oom导致作业失败
1)map阶段oom
发生oom的几率很小,代码逻辑不正常或代码不高效,产生的垃圾太多
2)reduce阶段oom
a.数据倾斜:key分布不均匀,导致某一个reduce所处理的数据超过预期,导致jvm频繁GC
b.value对象过多或过大:某个reduce的value堆积的对象过多,导致jvm频繁GC
解决方法:
a.增加reduce个数,set mapred.reduce.tasks=300
b.在hive-site.xml中设置,set mapred.child.java.opts=-Xmx512m或者设置reduce的最大heap为2G,
并且设置垃圾回收器的类型为并行标记回收器,这样可以减少GC;
c.使用map join 代替 common join :set hive.auto.convert.join = true
d.设置 hive.optimize.skewjoin=true;
3)driver阶段oom
因为扫描的表分区很多,上到3千到6千个分区,这样在对计划进行序列化时,仅仅是路径对象,path就会耗去大半Driver,如果Driver设置的heap太小,甚至会oom.
解决方法:
a.减少分区数量,将历史数据做成一张整合表,做成增量数据表,这样可以减少分区
b.调大Hive CLI Driver的heap size, 默认是256MB,调节成512MB或者更大。
set hive.tez.container.size=5000;
场景2.输入大量小文件
输入文件数量多,其中很多是远远小于256M的小文件
set mapred.max.split.size=100000000;
set mapred.min.split.size.per.node=100000000;
set mapred.min.split.size.per.rack=100000000;
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;–表示执行前进行小文件合并
前面三个参数确定合并文件块的大小,大于文件块大小256M的,按照256M来分隔,小于256M,大于100M的,按照100M来分隔,把那些小于100M的(包括小文件和分隔大文件剩下的)进行合并。
场景3.输出大量小文件
在Map-only的任务结束时合并小文件:set hive.merge.mapfiles=true;
在Map-Reduce的任务结束时合并小文件:
set hive.merge.mapredfiles=true;
合并文件的大小:set hive.merge.size.per.task=25610241024;
当输出文件的平均大小:set hive.merge.smallfiles.avgsize=16000000;
小于该值时,启动一个独立的map-reduce任务进行文件merge
set mapred.comine.input.format.local.only=false;
场景4.数据倾斜
常见的数据倾斜场景:使用join和group by 时。比如group by 时某个取值数量就很多,这样不可避免的就有数据倾斜
解决方法:
a.调节参数:
1)设置hive.map.aggr=true,在map端进行聚合,也就是combiner造作。
这样就会使得在reduce端的数据量有效减少,可以在一定程度上缓解数据倾斜的程度。
2)设置hive.groupby.skewindata=true,这样有数据倾斜时就会进行负载均衡。
eg:group by 时出现数据倾斜时,就可以延时很长的作业分配一部分给其它
已经完成的reduce做,最后再聚合结果
b.处理key分布不均匀
group by 或者join on key 时,对于key中有空值或者数据量明显过多的key可以在原来的值得基础上加一个随机数,把倾斜的数据分到不同的reduce上,最后还原结果
c.join优化
选用join key 分布最均匀的表作为驱动表,并且大表放在右边,小表放在左边。也可以采用mapjoin
d.排序优化
order by 排序,最后只存在一个reduce,效率比较低。可以用sort by 操作,然后结合distribute by 作为reduce 分区键
三.SQL优化技巧
1.调节参数
a.设置hive.map.aggr = true,在 Map 端进行聚合,也就是combiner造作。这样就会使得在reduce端的数据量有效的减少,可以一定程度上缓解数据倾斜的程度。
b.设置hive.groupby.skewindata=true,这样当有数据倾斜时就会进行负载均衡。如在group by时出现数据倾斜了,就可以把延时很长的作业分配一部分给其他已经完成的reduce做,最后再聚合结果。
2.处理key分布不均匀
比如group by key或者join on key时,对于key中有空值或者数据量明显过多的key可以在原来的值得基础上加一个随机数,这样就可以把倾斜的数据分不到不同的reduce上,只是最后要把结果还原。如:
select
case when F_agent_name is null then concat('null',rand()%5) else F_agent_name end,
count(1)
from
ods.t_pay_transaction_df
where ds = '20160519'
group by case when F_agent_name is null then concat('null',rand()%5) else F_agent_name end
这个例子中,因为发现F_agent_name大量的是空值,造成数据倾斜,所以把为空的key随机的分布到5个reduce作业中。
3.join优化
join的优化原则:应该将条目少的表放在join操作符的左边。无论外关联还是内关联,如果join的key相同,不管有多少个表都会放到一个Map/Reduce任务。少量数据多个union会优化成一个job。
Hive.exec.parallel=false
Hive.exec.parallel.thread.number=8
4.group by优化
在Map端进行初步聚合,最后在redcue端输出最终结果,相关参数:
hive.map.aggr=true 是否在map端进行预聚合,默认为true
hive.groupby.mapaggr.checkinterval=100000 在map端进行预聚合操作的条目数目
5.选用合适的Job执行模式
Hadoop的M/R作业有三种执行模式:本地模式、伪分布模式和分布模式。顾名思义,本地模式就是单机模式,也就是map个reduce的作业数都是1。因为我们在hadoop集群上处理数据时,有些时候数据量非常小的job启用分布式job会有大量的初始化、作业协调、进程控制等系统开销,而真正执行数据处理的时间却非常短,这样得不偿失,所以应该使用本地模式。
通过设置Hive.exec.mode.local.auto=true就能够自动开启local mr模式开启本地模式。除此之外,还需要控制输入文件数量和数据总量大小,通过两个参数分别设置,Hive.exec.mode.local.auto.tasks.max和Hive.exec.mode.local.auto.inputbytes.max。这两个参数默认值分别为4和128。也就是在Hive.exec.mode.local.auto=true时,默认情况下文件数不超过4个并且文件总大小小于128MB就启用本地模式。
6.数据过滤
数据过滤应尽早做,同时只选择所需要的列,这个原则在传统的RDMS数据中应该也适用,因为数据提前过滤了之后进行join或者其他操作的数据量就会变少,这时往往会带来效率上很大的提升。
7.巧用中间表
如果一个大表经常要与其他表进行join操作,而且每次join并不需要大表中的所有数据的情况下,合理创建中间表将大大减小后续所有join的操作数据量,性能也能大幅增加。