Hive优化总结

一、hive表优化

1、分区（不同文件夹）

分区是以字段的形式在表结构中存在，通过desc table命令可以查看到字段存在， 但是该字段不存放实际的数据内容，仅仅是分区的表示（伪列）

create table if not exists table_name(id int,name string,tel string)
partitioned by(dt string)
row format delimited
fields terminated by ','
stored as textfile;

使用动态分区前，需要配置以下参数：

• set hive.exec.dynamic.partition = true  // 在DML/DDL中是否支持动态分区，默认为false
• set hive.exec.dynamic.partition.mode = nonstrict  // 默认为strict模式，在strict模式下，动态分区的使用必须保证主分区为静态分区，其他分区可以是动态；nonstrict模式下，允许所有的分区字段都可以使用动态分区。

注意：strict是避免全分区字段是动态的，必须有至少一个分区字段是指定有值的， 避免产生大量分区文件

2、分桶（同一文件夹下的不同文件）

使用分桶前，需要配置以下参数：

• set hive.enforce.bucketing = true  // 数据分桶是否被强制执行，默认false，如果开启，则写入table数据时会启动分桶
• set hive.enforce.sorting = true  // 开启强制排序时，插数据到表中会进行强制排序，默认false

create table stu_buck(Sno int,Sname string,Sex string,Sage int,Sdept string)
clustered by(Sno) 
sorted by(Sno desc)
into 4 buckets
row format delimited
fields terminated by ',';

分桶原理：对于每一张表（table）或者分区， Hive可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。Hive也是针对某一列进行桶的组织。Hive采用对列值哈希，然后除以桶的个数求余的方式决定该条记录存放在哪个桶当中

注意：设置reduce数与分桶数相同，且cluster by 与 sort by不共存

二、Hive SQL优化

1、数据倾斜优化

1）、Join过程中数据倾斜

Common join过程原理

Map阶段
读取源表的数据，Map输出时以Join on条件中的列为key，如果Join有多个关联键，则以这些关联键的组合作为key；Map输出的value为join之后所关心的(select或者where中需要用到的)列，同时在value中还会包含表的Tag信息，用于标明此value对应哪个表。 
Shuffle阶段
根据key的值进行hash，并将key/value按照hash值推送至不同的reduce中，这样确保两个表中相同的key位于同一个reduce中。 
Reduce阶段
根据key的值完成join操作，期间通过Tag来识别不同表中的数据。

倾斜优化参数设置

• set hive.optimize.skewjoin = true  // 默认值为false，是否优化数据倾斜的 Join，对于倾斜的 Join 会开启新的 Map/Reduce Job 处理。
• set hive.skewjoin.key = 100000  // 默认值为100000，倾斜键数目阈值，超过此值则判定为一个倾斜的 Join 查询。

提示：当hive.optimize.skewjoin设置为true时，不管该Join查询是否倾斜，该查询计划都会生成两个Job任务

提示：尽可能使用相同的连接键，当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

2）、Mapjoin(map端执行join）

在map端把小表加载到内存中，然后读取大表，和内存中的小表进行联结操作(即小表Join大表)，其中使用了分布式缓存技术
优缺点：

• 不消耗集群的reduce资源（reduce相对紧缺）
• 减少了reduce阶段操作，提高查询效率
• 降低网络负载（map端到reduce端，数据的merge造成网络IO）
• 占用部分内存，加载到内存中的表不能过大，因为每个节点都会加载一份小表到内存中
• 生成较多的小文件

实现mapjoin的两种方式

启动方式一：(自动判断）

• set hive.auto.convert.join = true  // 默认为true，是否根据输入小表的大小，自动将需要Reduce 阶段的 Common Join 转化为 Map Join，从而加快小表关联大表的 Join 速度。
• set hive.mapjoin.smalltable.filesize = 25000000  // 默认值为25MB，小表文件大小的mapjoin阈值，如果输入的小表文件小于该值，则试图将common join转化为mapjoin。

启动方式二：(手动模式）

select /*+mapjoin(A)*/ f.a,f.b from A t join B f on (f.a=t.a)
# 使用mapjoin指定小表
# 或者标记哪张表是大表：/*streamtable(table_name) */

• Map JOIN支持不等值条件
• Reduce JOIN不支持在ON条件中不等值判断

注意：Map JOIN不适合FULL/RIGHT OUTER JOIN

3）、Bucket join(数据访问可以精确到桶级别）
使用条件：

• 两个表以相同方式划分桶
• 两个表的桶个数是倍数关系

如下例子：

create table order(cid int,price float) clustered by(cid)   into 32 buckets;
create table customer(id int,first string) clustered by(id)   into 32/64 buckets;

select price from order t join customer s on t.cid=s.id;

4）、where条件优化（关系数据库会自动优化）
由于join操作是在where操作之前执行，所以当你在执行join时，where条件并不能起到减少join数据的作用。
优化前的查询语句：

select m.cid,u.id from order m join customer u on m.cid =u.id where m.dt='2018-11-11';

优化后的查询语句：

select m.cid,u.id from （select * from order where dt='2018-11-11'） m join customer u on m.cid =u.id;

优化后，查询语句的where条件在map端执行而不是在reduce端执行，在map端通过where语句过滤掉一些数据，提高了reduce端的执行效率

5）、group by 过程中的数据倾斜

数据倾斜
现象：任务进度长时间维持在99%（或100%），查看任务监控页面，发现只有少量（1个或几个）reduce子任务未完成。因为其处理的数据量和其他reduce差异过大。造成单个reduce的记录数与平均记录数差异过大，通常可能达到3倍甚至更多。 最大时长远大于平均时长，导致单个reduce处理时间过长，从而影响整个job的运行时间。

产生原因：

1. key分布不均匀
2. 业务数据本身的特性
3. 建表时考虑不周
4. 某些SQL语句本身就有数据倾斜

参数优化：

• set hive.groupby.skewindata = true  // 是否优化数据倾斜，默认是false；当设置为true时，执行计划会生成两个map/reduce作业，即两个Job
• set hive.groupby.mapaggr.checkinterval = 100000：默认是100000，map端做聚合时，group by 的key所允许的数据行数，超过该值则进行分拆

优化原理：

查询计划会有两个 MR Job，第一个 MR Job 中，Map 的输出结果集合会随机分布到 Reduce 中，每个Reduce做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

6）、count distinct 倾斜优化
优化前（只有一个reduce，先去重再count负担比较大）：

select count(distinct id) from table_name;

在数据量大时，可以使用如下优化：

优化后（启动两个job，一个job负责子查询(reduce个数可以设置)，另一个job负责count(1))

select count(1) from (select distinct id from table_name) tmp;
    或者
select count(1) from (select id from table_name group by id) tmp;

优化原理：

优化前查询只会启动一个job来完成，完成数据去重和计数时都只在一个reduce端进行（即便mapred.reduce.tasks设置reduce的数目为多个，但实际执行时仍然只有一个），优化后会启动两个job来完成，同时可以通过mapred.reduce.tasks设定更多的reduce数，所以适合在数据量很大的情况下使用；在数据量较小的情况下，优化后的执行效率反而比优化前的执行效率要低，原因可能就是初始化一个job花费的时间也会很长。

看一个更复杂的例子：
优化前：

select a,sum(b),count(distinct c),count(distinct d) from test group by a;

优化后：

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) tmp group by a;

7）、无效ID在关联时的数据倾斜问题

问题：日志中常会出现信息丢失，比如每日约为 20 亿的全网日志，其中的 user_id 为主 键，在日志收集过程中会丢失，出现主键为 null 的情况，如果取其中的 user_id 和 bmw_users 关联，就会碰到数据倾斜的问题。原因是 Hive 中，主键为 null 值的项会被当做相同的 Key 而分配进同一个Reduce

解决方案一：user_id 为空的不参与关联，子查询过滤 null

SELECT 
* 
FROM log a 
JOIN 
bmw_users b 
ON a.user_id IS NOT NULL AND a.user_id=b.user_id 
UNION All 
SELECT 
* 
FROM log a 
WHERE a.user_id IS NULL

解决方案二：函数过滤 null

SELECT * 
FROM log a 
LEFT OUTER JOIN 
bmw_users b 
ON 
(CASE WHEN a.user_id IS NULL THEN CONCAT('dp_hive',RAND()) ELSE a.user_id END) = b.user_id;

三、Hive架构层面优化

1、Hive job优化

1）、并行化执行

hive会将一个查询转化为一个或多个阶段，包括：MapReduce阶段、抽样阶段、合并阶段、limit阶段等。默认情况下，一次只执行一个阶段，顺序进行的。 不过，如果某些阶段不是互相依赖，是可以并行执行的。

一个HQL中job之间无依赖关系也没有相互影响可以并行执行。

• set hive.exec.parallel=true  // 开启并发执行。
• set hive.exec.parallel.thread.number=8  // 同一个sql允许最大并行度，默认为8。会比较耗系统资源。

注意：就是控制对于同一个sql来说同时可以运行的job的最大值，该参数默认为8，此时最大可以同时运行8个job

2）、本地化执行（在存放数据的节点上执行）

使用本地化执行前，需要配置以下参数：

• set hive.exec.mode.local.auto = true  // 默认是false，是否由hive决定自动在local模式下运行

配置本地运行后，job还必须满足以下条件，才能真正的使用本地模式：

1. hive.exec.mode.local.auto.inputbytes.max(默认128MB）// job的输入数据大小必须小于参数
2. hive.exec.mode.local.auto.tasks.max(默认为4）// 太多没有足够的slots，job的map数必须小于参数：
3. job的reduce数必须为0或1

3）、 job合并输入小文件

• set mapred.max.split.size = 256000000 // 每个Map最大输入大小
• set mapred.min.split.size.per.node = 100000000 // 一个节点上split的至少的大小 
• set mapred.min.split.size.per.rack = 100000000 // 一个交换机下split的至少的大小
• set hive.input.format = org.apache.hadoop.hive.ql.io.CombineHiveInputFormat // 执行Map前进行小文件合并

注意：多个split合成一个,合并后的split数由mapred.max.split.size限制的大小决定

4）、job合并输出小文件（为后续job优化做准备）

• hive.merge.mapfiles  // 默认开启true，在只有map的作业结束时合并小文件
• hive.merge.mapredfiles  // 默认false，在一个map/reduce作业结束后合并小文件
• hive.merge.size.per.task  // 默认256MB，合并后每个文件的大小
• set hive.merge.smallfiles.avgsize=16000000  // 当输出文件平均大小小于该值，启动新job合并文件
• set hive.merge.size.per.task=256000000  // 合并之后的每个文件大小

5）、 JVM重利用

• set mapreduce.job.jvm.numtasks = 1  // 默认值为1，表示同一个JVM上最多可以顺序执行的task数目（属于同一个Job）为1。也就是说一个task启动一个JVM

适当地增大该值对于有较多task的job非常有意义，但是也有个缺点就是：开启JVM重用将会一直占用使用到的task插槽，以便进行重用，直到该Job任务完成后才能释放。如果某个“不平衡“的job中有几个reduce task 执行的时间要比其他reduce task消耗的时间多得多的话，那么保留的插槽就会一直空闲着却无法被其他的job使用，直到所有的task都结束了才会释放。

6）、压缩数据（多个job）
（1）中间压缩处理hive查询的多个job之间的数据，对于中间压缩，最好选择一个节省cpu耗时的压缩方式

• set hive.exec.compress.intermediate = false  // 默认值为false，决定查询的中间 map/reduce job （中间 stage）的输出是否为压缩格式。 
• set hive.intermediate.compression.codec  // 中间 map/reduce job 的压缩编解码器的类名（一个压缩编解码器可能包含多种压缩类型），该值可能在程序中被自动设置。
• hive.intermediate.compression.type  // 中间 map/reduce job 的压缩类型，如 "BLOCK"，"RECORD"

（2）最终输出压缩（选择压缩效果好的，减少储存空间） 

• set hive.exec.compress.output = false  // 默认值为false，决定查询中最后一个 map/reduce job 的输出是否为压缩格式。   
• set mapred.output.compression.codec  // 默认为org.apache.hadoop.io.compress.DefaultCodec，最后结果的压缩编解码器的类名
• set mapred.output.compression.type=RECORD  // 最后结果的压缩类型

四、底层MapReduce优化

Hive Map优化

1、map tasks的优化
set mapred.map.tasks = 2 // 默认值为2，其在实际查询过程中无效，实际map task数量由block块的大小决定
（1）默认map个数
default_num=total_size/block_size;
  (2)   期望大小(手动设置的个数）
goal_num =mapred.map.tasks;
（3）设置处理的文件大小(根据文件分片大小计算的map个数）
split_size=max(block_size,mapred.min.split.size = 1);
split_num=total_size/split_size // hadoop中的split的大小默认为一个block块的大小，hive中的split.size指和block
（4）最终计算的map个数（实际map个数）
compute_map_num=min(split_num,max(default_num,goal_num))

总结：其实，split_num、default_num是相同的，个人认为map task的个数取决于Hdfs block

2、map端聚合

• set hive.map.aggr=true  // 相当于map端执行combiner

3、推测执行（默认为true）

• set mapred.map.tasks.speculative.execution = true  // 默认为true，hadoop中的参数
• set hive.mapred.reduce.tasks.speculative.execution  // 默认为true，hive里与hadoop中效果相同的参数

Hive Shuffle优化

1、Map 端
io.sort.mb  // 默认大小为100MB
io.sort.spill.percent  // 溢写的百分比为 80%
min.num.spill.for.combine  
io.sort.factor
io.sort.record.percent

2、reduce端
mapred.reduce.parallel.copies
mapred.reduce.copy.backoff
io.sort.factor
mapred.job.shuffle.input.buffer.percent

Reduce个数的优化(reduce个数设置）

• set mapred.reduce.tasks = -1 // 默认值为-1，查询不经过reduce阶段，直接设置即生效
• set hive.exec.reducers.max = 1009 // 默认值为1009
• set hive.exec.reducers.bytes.per.reducer = true  // 默认值为256MB，每个reducer计算的文件量大小

队列

• set mapred.queue.name = queue3;设置队列queue3
• set mapred.job.queue.name = queue3;设置使用queue3
• set mapred.job.priority=HIGH；

五、表连接优化 

1、将大表放后头

Hive假定查询中最后的一个表是大表。它会将其它表缓存起来，然后扫描最后那个表。

因此通常需要将小表放前面，或者标记哪张表是大表：/*streamtable(table_name) */

2、使用相同的连接键

当对3个或者更多个表进行join连接时，如果每个on子句都使用相同的连接键的话，那么只会产生一个MapReduce job。

3、尽量尽早地过滤数据

减少每个阶段的数据量,对于分区表要加分区，同时只选择需要使用到的字段。

4、尽量原子化操作

尽量避免一个SQL包含复杂逻辑，可以使用中间表来完成复杂的逻辑

注意：Hive中小表与大表关联(join)的性能分析：

https://blog.csdn.net/WYpersist/article/details/80001475

 5、用insert into替换union all
如果union all的部分个数大于2，或者每个union部分数据量大，应该拆成多个insert into 语句，实际测试过程中，执行时间能提升50%，如下：

insert overwite table tablename partition (dt= ....)
select ..... from ( select ... from A
union all
select ... from B
union all
select ... from C ) R
where ...;

可以改写为：

insert into table tablename partition (dt= ....) 
select .... from A WHERE ...; 
insert into table tablename partition (dt= ....) 
select .... from B WHERE ...; 
insert into table tablename partition (dt= ....) 
select .... from C WHERE ...;