Hive优化总结

一.表优化

1.大表拆成子表,取少数字段,数据量小

2.分区

3.分桶,极大地优化了大表与大表的join(SMB Join)

如果两个桶对相同的字段做了分桶,且分桶个数相同,可开启map端sort-merge-join
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;
set hive.optimize.bucketmapjoin = true;
set hive.optimize.bucketmapjoin.sortedmerge = true;

4.压缩(textfile,orc,parquet) snappy

二.了解表信息和sql执行计划

1.analyze

analyze table tablename compute statistics;

2.explain

3.查看执行日志

三.Hql优化

1.使用分区

2.先where再join,绝不要先join再where

3.join连接字段用on不用where

4.grouping sets和with rollup和with cube

5.去重用union和union all Group by,不要用count(distinct)这个全局排序

6.union all可以减少job数

(eg:select count(1) from a,b…;select type,count(1) from (select ‘a’ type,id from a union all …) tmp group by type)

7.多表同join连接条件,减少job数

(eg:select * from a left join b on a.id=b.id left join c on a.id=c.id?

8.多表插入,读取一次,多次插入

(eg:from a
insert overwrite table b
select col1,col2 where type=‘b’
insert overwrite table c
select col1,col3 where type=‘c’
…)

9.一次计算,多次使用

create table tmp as select …
with tmp as (select …)

四.MR优化

1.小文件

①map输入时合并小文件

set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

②合并输出小文件

输出时进行合并：

set hive.merge.mapfiles = true

#在Map-only的任务结束时合并小文件

set hive.merge.mapredfiles= true

#在Map-Reduce的任务结束时合并小文件

set hive.merge.size.per.task = 1024000000

#合并后文件的大小为1GB左右

set hive.merge.smallfiles.avgsize=1024000000

#当输出文件的平均大小小于1GB时，启动一个独立的map-reduce任务进行文件merge

2.mapTask

①调节mapTask个数

splitSize=max{mapred.split.min.size(1B),min{dfs.block.size(128M),mapred.split.max.size(256M)}}
以128m为界,想要splitSize大于128m就增加min
想要splitSize小于128m就减小max

②自动mapJoin

set hive.auto.convert.join=true;
set hive.mapjoin.smalltable.filesize(25m)=;

3.shuffle

set hive.map.aggr = true是否在 Map 端进行聚合，默认为 True
set hive.groupby.mapaggr.checkinterval = 100000在 Map 端进行聚合操作的条目数目

4.reduceTask

set mapred.reduce.tasks(强制指定reduce的任务数量)

set hive.exec.reducers.bytes.per.reducer（每个reduce任务处理的数据量，默认为1000^3=1G）

set hive.exec.reducers.max（每个任务最大的reduce数，默认为999）

计算reducer数的公式很简单N=min( hive.exec.reducers.max ，总输入数据量/ hive.exec.reducers.bytes.per.reducer )

只有一个reduce的场景：
a、没有group by 的汇总
b、order by
c、笛卡尔积

5.job

set hive.exec.parallel=true; //打开任务并行执行
set hive.exec.parallel.thread.number=16; //同一个sql允许最大并行度，默认为8。

6.jvm

set mapreduce.job.jvm.numtasks(1);
JVM重用可以使得JVM实例在同一个JOB中重新使用N次
JVM的一个缺点是，开启JVM重用将会一直占用使用到的task插槽

7.关闭推测执行

set mapreduce.map.speculative=false(true);
set mapreduce.reduce.speculative=false(true);

8. 数据倾斜(join,group by,count(distinct)的key不均)

①空值数据倾斜

使用coalesce(field,rand()*9999)

②业务数据本身倾斜

增加job个数