最具影响力的数字化技术在线社区

168大数据

 找回密码
 立即注册

QQ登录

只需一步,快速开始

1 2 3 4 5
开启左侧

[Hive] Hive SQL优化

[复制链接]
发表于 2019-6-26 21:15:36 | 显示全部楼层 |阅读模式

马上注册,结交更多数据大咖,获取更多知识干货,轻松玩转大数据

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
本帖最后由 168主编 于 2019-6-26 21:17 编辑

出处:https://blog.csdn.net/l1028386804/article/details/80629279

一、Hive优化目标
在有限的资源下,提高执行效率

二、Hive执行
HQL——> Job——> Map/Reduce

三、执行计划
查看执行计划
explain [extended] hql
四、Hive表优化
1、分区
静态分区
动态分区
[AppleScript] 纯文本查看 复制代码
set hive.exec.dynamic.partition=true;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.exec.dynamic.partition.mode=nonstrict;


2、分桶
[AppleScript] 纯文本查看 复制代码
set hive.enforce.bucketing=true;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.enforce.sorting=true;
3、数据
相同数据尽量聚集在一起

五、Hive查询操作优化
1、join优化
[AppleScript] 纯文本查看 复制代码
hive.optimize.skewjoin=true;如果是join过程中出现倾斜 应该设置为true[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.skewjoin.key=100000; 这个是join的键对应的记录条数,超过这个值则会进行优化


2、mapjoin
[AppleScript] 纯文本查看 复制代码
set hive.auto.convert.join=true;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]hive.mapjoin.smalltable.filesize默认值是25mb
select /*+mapjoin(A)*/f.a, f.b from A t join B f on (f.a=t.a)


3、mapjoin的使用场景
关联操作中有一张表非常小
不等值的连接操作
4、bucket join
两个表以相同方式划分桶
两个表的桶个数是倍数关系
[AppleScript] 纯文本查看 复制代码
create table order(cid int, price float) clustered by(cid) into 32 buckets;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]create table customer(id int, first string) clustered by(id) into 32 buckets;
select price from order t join customers s on t.cid = s.id 
join优化前
[AppleScript] 纯文本查看 复制代码
[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]select m.cid, u.id
from order m
join customer u
on m.cid = u.id
where m.dt='2018-06-08'
join优化后
[AppleScript] 纯文本查看 复制代码
select m.cid, u.id[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]from
(select cid from order
where dt = '2018-06-08')m
join customer u
on m.cid = u.id;
5、group by优化
[AppleScript] 纯文本查看 复制代码
hive.group.skewindata=true; 如果是group by过程出现倾斜,应该设置为true[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.groupby.mapaggr.checkinterval=100000; 这个是group的键对应的记录条数超过这个值则会进行优化
6、count distinct优化
优化前
[AppleScript] 纯文本查看 复制代码
select count(distinct id) from tablename;

优化后
[AppleScript] 纯文本查看 复制代码
select count(1) from (select distinct id from tablename) tmp;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]select count(1) from (select id from tablename group by id) tmp;
优化前
[AppleScript] 纯文本查看 复制代码
select a, sum(b), count(distinct c), count(distinct d) from test group by a;

优化后
[AppleScript] 纯文本查看 复制代码
select a, sum(b) as b, count(c) as c, count(d) as d
from(
select a,0 as b, c, null as d from test group by a,c
union all
select a,0  as b, null as c, d from test group by a, d
union all
select a,b,null as c, null as d from test
)tmp1 group by a; 


六、Hive job优化

1、并行化执行
每个查询被Hive转化为多个阶段,有些阶段关联性不大,则可以并行化执行,减少执行时间
[AppleScript] 纯文本查看 复制代码
set hive.exec.parallel=true;[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.exec.parallel.thread.number=8;
2、本地化执行
set hive.exec.mode.local.auto=true;
当一个join满足如下条件才能真正使用本地模式:
Job的输入数据大小必须小于参数
hive.exec.mode.local.auto.inputbytes.max(默认128MB)
Job的map数必须小于参数
hive.exec.mode.local.auto.tasks.max(默认4)
Job的reduce数必须为0或者1
3、job合并输入小文件
[AppleScript] 纯文本查看 复制代码
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat

合并文件数由mapred.max.split.size限制的大小决定
4、job合并输出小文件
[AppleScript] 纯文本查看 复制代码
[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.merge.smallfiles.avgsize=256000000;当输出文件平均大小小于该值,启动新job合并文件
set hive.merge.size.per.task=64000000;合并之后的文件大小
七、JVM重利用
set mapred.job.reuse.jvm.num.tasks=20;
JVM重利用可以是Job长时间保留slot,直到作业结束,这在对于有较多任务和较多小文件的任务时非常有意义的,减少执行时间。但是这个值不能设置过大,因为有些作业会有reduce任务,如果reduce任务没有完成,则map任务占用的slot不能释放,其他的作业可能就需要等待。
八、压缩数据
中间压缩就是处理hive查询的多个job之间的数据,对于中间压缩,最好选择一个节省CPU耗时的压缩方式
[AppleScript] 纯文本查看 复制代码
[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.exec.compress.intermediate=true;
set hive.intermediate.compression.codec=org.apache.hadoop.io.compress.SnappyCodec;
set hive.intermediate.compression.type=BLOCK; 
hive查询最终的输出也可以压缩
[AppleScript] 纯文本查看 复制代码
[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set hive.exec.compress.output=true;
set mapred.output.compression.codec=org.apache.hadoop.io.compress.GzipCodec;
set mapred.output.compression.type=BLOCK;
九、Hive Map优化
set mapred.map.tasks=10; 无效


1、Map个数计算流程
(1) 默认map个数
default_num = total_size / block_size;
(2)期望大小
goal_num = mapred.map.tasks;
(3)设置处理的文件大小
split_size=max(mapred.min.split.size, block_size)
split_num = total_size / split_size;
(4)计算map个数
compute_map_num = min(split_num, max(default_num, goal_num))
设置map个数总结如下:
(1) 如果想增加map个数,则设置mapred.map.tasks为一个较大的值
(2) 如果想减少map个数,则设置mapred.min.split.size为一个较大的值
情况1:输入文件size巨大,但不是小文件
增大mapred.min.split.size的值
情况2:输入文件数量巨大,且都是小文件,就是单个文件的size小于blockSize。


这种情况通过增大mapred.min.split.size不可行,需要使用CombineFileInputFormat将多个input path合并成一个InputSplit送给mapper处理,从而减少mapper的数量。


2、Map端聚合
set hive.map.aggr=true;
3、推测执行
mapred.map.tasks.speculative.execution
十、Hive Shuffle优化
Map端
[AppleScript] 纯文本查看 复制代码
io.sort.mb
io.sort.spill.percent
min.num.spill.for.combine
io.sort.factor
io.sort.record.percent

Reduce端
[AppleScript] 纯文本查看 复制代码
mapred.reduce.parallel.copies
mapred.reduce.copy.backoff
io.sort.factor
mapred.job.shuffle.input.buffer.percent
mapred.job.reduce.input.buffer.percent

十一、Hive Reduce优化
需要reduce操作的查询


聚合函数
sum,count,distinct...


高级查询
group by, join, distribute by, cluster by...
order by 比较特殊,只需要一个reduce


推测执行
[AppleScript] 纯文本查看 复制代码
mapred.reduce.tasks.speculative.execution[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]hive.mapred.reduce.tasks.speculative.execution
Reduce优化
[AppleScript] 纯文本查看 复制代码
[/color][/size][/font]
[font=微软雅黑][size=2][color=#000000]set mapred.reduce.tasks=10;直接设置
hive.exec.reducers.max 默认999
hive.exec.reducers.bytes.per.reducer 默认1G
计算公式
numTasks = min(maxReducers, input.size / perReducer)
maxReducers = hive.exec.reducers.max
perReducer=hive.exec.reducers.bytes.per.reducer
---------------------
作者:冰 河
来源:CSDN
原文:https://blog.csdn.net/l1028386804/article/details/80629279  



楼主热帖
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

关闭

站长推荐上一条 /1 下一条

关于我们|小黑屋|Archiver|168大数据 ( 京ICP备14035423号|申请友情链接

GMT+8, 2024-3-29 21:31

Powered by BI168大数据社区

© 2012-2014 168大数据

快速回复 返回顶部 返回列表