浅谈Hive的一些优化
hive调优涉及到压缩和存储调优,参数调优,sql的调优,数据倾斜调优,小文件问题的调优等
1、看懂执行计划
Hive执行计划官方解释在哪?如下连接是Hive官网的Wiki地址
https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Explain
语法如下:
EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query
如下图所示,就是一个简单的执行计划解析数。
其中,stage这个是表达的整个语句需要执行的步骤,图中只有一个,那就表示只需要执行一个步骤
1.1、简单的执行计划
hive> explain select count(*) from t_data1 ;
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
#说明stage之间的依赖性
STAGE PLANS: #各个stage的执行计划
Stage: Stage-1
Map Reduce #这个stage是一个MR
Map Operator Tree: #Map阶段的操作树
TableScan #扫描表,获取数据
alias: t_data1 扫描的表别名
Statistics: Num rows: 1 Data size: 43835224 Basic stats: COMPLETE Column stats: COMPLETE
Select Operator #选取操作
Statistics: Num rows: 1 Data size: 43835224 Basic stats: COMPLETE Column stats: COMPLETE
Group By Operator #分组聚合操作,不指定Key,只有一个分组
aggregations: count() 聚合操作
mode: hash
outputColumnNames: _col0 输出列名
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
Reduce Output Operator #输出结果给Reduce
sort order: #不排序
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
value expressions: _col0 (type: bigint) #value表达式
Reduce Operator Tree: #Reduce的操作树
Group By Operator #分组聚合操作
aggregations: count(VALUE._col0) 聚合操作
mode: mergepartial 合并各个map所贡献的各部分
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
File Output Operator #文件输出操作
compressed: false 不压缩
Statistics: Num rows: 1 Data size: 8 Basic stats: COMPLETE Column stats: COMPLETE
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0 #依赖于Stage1的stage0
Fetch Operator #获取数据操作
limit: -1 #不限定
Processor Tree:
ListSink
1.2、count(distinct)
hive> explain select count(distinct sid) from t_data1 ;
STAGE DEPENDENCIES:
Stage-1 is a root stage
Stage-0 depends on stages: Stage-1
STAGE PLANS:
Stage: Stage-1
Map Reduce
Map Operator Tree:
TableScan
alias: t_data1
Statistics: Num rows: 5479403 Data size: 43835224 Basic stats: COMPLETE Column stats: NONE
Select Operator
expressions: sid (type: bigint) #选取SID
outputColumnNames: sid
Statistics: Num rows: 5479403 Data size: 43835224 Basic stats: COMPLETE Column stats: NONE
Group By Operator #分组聚合操作
aggregations: count(DISTINCT sid) #聚合算子
keys: sid (type: bigint) #分组键
mode: hash
outputColumnNames: _col0, _col1
Statistics: Num rows: 5479403 Data size: 43835224 Basic stats: COMPLETE Column stats: NONE
Reduce Output Operator #输出到Reduce
key expressions: _col0 (type: bigint) #键表达式
sort order: + #正向排序
Statistics: Num rows: 5479403 Data size: 43835224 Basic stats: COMPLETE Column stats: NONE
Reduce Operator Tree:
Group By Operator #分组聚合操作
aggregations: count(DISTINCT KEY._col0:0._col0)
mode: mergepartial #合并各个部分聚合结果
outputColumnNames: _col0
Statistics: Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE
File Output Operator
compressed: false
Statistics: Num rows: 1 Data size: 16 Basic stats: COMPLETE Column stats: NONE
table:
input format: org.apache.hadoop.mapred.TextInputFormat
output format: org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat
serde: org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe
Stage: Stage-0
Fetch Operator
limit: -1
Processor Tree:
ListSink
2、优化方法
2.1、数据的压缩与存储格式
2.1.1、map阶段输出数据压缩 ,在这个阶段,优先选择一个低CPU开销的算法。
set hive.exec.compress.intermediate=true
set mapred.map.output.compression.codec= org.apache.hadoop.io.compress.SnappyCodec
set mapred.map.output.compression.codec=com.hadoop.compression.lzo.LzoCodec;
2.1.2、对最终输出结果压缩
set hive.exec.compress.output=true
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
## 当然,也可以在hive建表时指定表的文件格式和压缩编码
结论,一般选择orcfile/parquet + snappy 方式
2.2、合理利用分区分桶
分区是将表的数据在物理上分成不同的文件夹,以便于在查询时可以精准指定所要读取的分区目录,从来降低读取的数据量
分桶是将表数据按指定列的hash散列后分在了不同的文件中,将来查询时,hive可以根据分桶结构,快速定位到一行数据所在的分桶文件,从来提高读取效率
2.3、hive参数优化
// 让可以不走mapreduce任务的,就不走mapreduce任务
set hive.fetch.task.conversion=more;
// 开启任务并行执行
set hive.exec.parallel=true;
// 解释:当一个sql中有多个job时候,且这多个job之间没有依赖,则可以让顺序执行变为并行执行(一般为用到union all的时候)
// 同一个sql允许并行任务的最大线程数
set hive.exec.parallel.thread.number=8;
// 设置jvm重用
// JVM重用对hive的性能具有非常大的 影响,特别是对于很难避免小文件的场景或者task特别多的场景,这类场景大多数执行时间都很短。jvm的启动过程可能会造成相当大的开销,尤其是执行的job包含有成千上万个task任务的情况。
set mapred.job.reuse.jvm.num.tasks=10;
// 合理设置reduce的数目
// 方法1:调整每个reduce所接受的数据量大小
set hive.exec.reducers.bytes.per.reducer=500000000; (500M)
// 方法2:直接设置reduce数量
set mapred.reduce.tasks = 20
// map端聚合,降低传给reduce的数据量
set hive.map.aggr=true
// 开启hive内置的数倾优化机制
set hive.groupby.skewindata=true
2.4、sql优化
2.4.1、PredicatePushDown(谓词下推)
优化前(关系数据库不用考虑会自动优化)
select m.cid,u.id from table1 m join table2 u on( m.cid =u.id ) where m.dt='20210301';
优化后(where条件在map端执行而不是在reduce端执行)
select m.cid,u.id from (select * from table1 where dt='20210301') m join table2 u on( m.cid =u.id);
2.4.2、union优化
尽量不要使用union (union 去掉重复的记录)而是使用 union all 然后在用group by 去重
2.4.3、count distinct优化
不要使用count (distinct cloumn) ,使用子查询,但也不是绝对的,当数据量很小的时候,推荐使用count(distinct column)
select count(1) from (select id from table_name group by id) tmp;
2.4.4、用in 来代替join
如果需要根据一个表的字段来约束另为一个表,尽量用in来代替join . in 要比join 快
select id,name from tb1 a join tb2 b on(a.id = b.id);
select id,name from tb1 where id in(select id from tb2);
2.4.5、优化子查询
消灭子查询内的 group by 、 COUNT(DISTINCT),MAX,MIN。可以减少job的数量。
2.4.6、join 优化
Common/shuffle/Reduce JOIN 连接发生的阶段,发生在reduce 阶段, 适用于大表 连接 大表(默认的方式)Map join :连接发生在map阶段 , 适用于小表 连接 大表
大表的数据从文件中读取
小表的数据存放在内存中(hive中已经自动进行了优化,自动判断小表,然后进行缓存)
set hive.auto.convert.join=true;
SMB join
Sort -Merge -Bucket Join 对大表连接大表的优化,用桶表的概念来进行优化。在一个桶内发生笛卡尔积连接(需要是两个桶表进行join)
set hive.auto.convert.sortmerge.join=true;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;
2.4.7、列裁剪
Hive 在读数据的时候,可以只读取查询中所需要用到的列,而忽略其它列。 可以减少数据在传输过程中暂用的网络io和磁盘io,以达到增快速度。
2.4.8、分区裁剪
Hive分区就相当于一个个磁盘文件路径下面的文本文件,使用分区读取数据,可以快速定位到数据在哪个目录下的文件中,如果不使用分区,就需要挨个遍历整个数据文件目录下的文件。
可以通过EXPLAIN AUTHORIZATION如下执行计划看出差别
EXPLAIN
AUTHORIZATION
SELECT a1.city_name
FROM ads_finish_ord_ds a1
LEFT JOIN dim_t_city a2 ON a1.city_name = a2.city_name
WHERE a2.city_code='320100';
EXPLAIN
AUTHORIZATION
SELECT a1.city_name
FROM ads_finish_ord_ds a1
LEFT JOIN dim_t_city a2 ON a1.city_name = a2.city_name
WHERE a2.city_code='320100'
AND a1.ds='20210301';
通过如上的对比很清晰直观的看出来输入的文件数据少了很多
2.5、数据倾斜
表现:任务进度长时间维持在99%(或100%),查看任务监控页面,发现只有少量(1个或几个)reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。
原因:某个reduce的数据输入量远远大于其他reduce数据的输入量
2.5.1、sql本身导致的倾斜
1)group by
如果是在group by中产生了数据倾斜,是否可以讲group by的维度变得更细,如果没法变得更细,就可以在原分组key上添加随机数后分组聚合一次,然后对结果去掉随机数后再分组聚合
在join时,有大量为null的join key,则可以将null转成随机值,避免聚集
2)count(distinct)
情形:某特殊值过多
后果:处理此特殊值的 reduce 耗时;只有一个 reduce 任务
解决方式:count distinct 时,将值为空的情况单独处理,比如可以直接过滤空值的行,
在最后结果中加 1。如果还有其他计算,需要进行 group by,可以先将值为空的记录单独处理,再和其他计算结果进行 union。
3)不同数据类型关联产生数据倾斜
情形:比如用户表中 user_id 字段为 int,log 表中 user_id 字段既有 string 类型也有 int 类型。当按照 user_id 进行两个表的 Join 操作时。
后果:处理此特殊值的 reduce 耗时;只有一个 reduce 任务
默认的 Hash 操作会按 int 型的 id 来进行分配,这样会导致所有 string 类型 id 的记录都分配
到一个 Reducer 中。
解决方式:把数字类型转换成字符串类型
select * from table1 a
left outer join table2 b
on a.usr_id = cast(b.user_id as string)
2.5.2、业务数据本身的特性(存在热点key)
join的每路输入都比较大,且长尾是热点值导致的,可以对热点值和非热点值分别进行处理,再合并数据
2.5.3、key本身分布不均
可以在key上加随机数,或者增加reduceTask数量
开启数据倾斜时负载均衡
set hive.groupby.skewindata=true;
思想:就是先随机分发并处理,再按照 key group by 来分发处理。
操作:当选项设定为 true,生成的查询计划会有两个 MRJob。
第一个 MRJob 中,Map 的输出结果集合会随机分布到 Reduce 中,每个 Reduce 做部分聚合操作,并输出结果,这样处理的结果是相同的 GroupBy Key 有可能被分发到不同的Reduce 中,从而达到负载均衡的目的;
第二个 MRJob 再根据预处理的数据结果按照 GroupBy Key 分布到 Reduce 中(这个过程可以保证相同的原始 GroupBy Key 被分布到同一个 Reduce 中),最后完成最终的聚合操作。
2.5.4、控制空值分布
将为空的 key 转变为字符串加随机数或纯随机数,将因空值而造成倾斜的数据分不到多个 Reducer。
注:对于异常值如果不需要的话,最好是提前在 where 条件里过滤掉,这样可以使计算量大大减少
2.6、合并小文件
小文件的产生有三个地方,map输入,map输出,reduce输出,小文件过多也会影响hive的分析效率:
设置map输入的小文件合并
set mapred.max.split.size=256000000;
//一个节点上split的至少的大小(这个值决定了多个DataNode上的文件是否需要合并)
set mapred.min.split.size.per.node=100000000;
//一个交换机下split的至少的大小(这个值决定了多个交换机上的文件是否需要合并)
set mapred.min.split.size.per.rack=100000000;
//执行Map前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
设置map输出和reduce输出进行合并的相关参数:
//设置map端输出进行合并,默认为true
set hive.merge.mapfiles = true
//设置reduce端输出进行合并,默认为false
set hive.merge.mapredfiles = true
//设置合并文件的大小
set hive.merge.size.per.task = 256*1000*1000
//当输出文件的平均大小小于该值时,启动一个独立的MapReduce任务进行文件merge。
set hive.merge.smallfiles.avgsize=16000000
感谢 https://blog.csdn.net/conggova/article/details/77985070 的分享技术