【hive调优】

参考文献

Hive企业级优化
Hive调优

Hive的调优主要手段

Hive调优主要从以下方面进行调优

• Hive本身的参数配置
• SQL语句优化
• 任务优化等方案。

其中在开发过程中主要涉及到的可能是SQL优化这块

优化的核心思想是：

• 减少不必要的数据量（例如分区、列剪裁、避免全表扫描）
• 减少job数（例如相同的on条件的join放在一起作为一个任务）
• 避免数据倾斜（例如加参数、Key打散）

1. Hive配置层面优化

1. 列剪裁

2. 分区剪裁

3. 谓词下推

将 SQL 语句中的 where 谓词逻辑都尽可能提前执行，减少下游处理的数据量。对应逻辑优化器是PredicatePushDown。

4. 小文件

小文件过多优化

5. JVM重用

6.严格模式

7.并行执行优化

Hive会将一个查询转化成一个或者多个阶段。这样的阶段可以是MapReduce阶段、抽样阶段、合并阶段、limit阶段。或者Hive执行过程中可能需要的其他阶段。默认情况下，Hive一次只会执行一个阶段。不过，某个特定的job可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个job的执行时间缩短。如果有更多的阶段可以并行执行，那么job可能就越快完成。

通过设置参数hive.exec.parallel值为true，就可以开启并发执行。在共享集群中，需要注意下，如果job中并行阶段增多，那么集群利用率就会增加。

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

当然得是在系统资源比较空闲的时候才有优势，否则没资源，并行也起不来

2. SQL语法层面优化

1. 如何写出高效的 HQL
2. 如何利用一些控制参数来调优SQL

1. from前置

insert into table stu partition(tp) 
select s_age,max(s_birth) stat,'max' tp 
from stu_ori
group by s_age

union all

insert into table stu partition(tp) 
select s_age,min(s_birth) stat,'min' tp 
from stu_ori
group by s_age;

优化：from前置

--开启动态分区 
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict; 

from stu_ori 

insert into table stu partition(tp) 
select s_age,max(s_birth) stat,'max' tp 
group by s_age

insert into table stu partition(tp) 
select s_age,min(s_birth) stat,'min' tp 
group by s_age;

2. count distinct

当要统计某一列去重数时，如果数据量很大，count(distinct) 就会非常慢，原因与 group by 类似，count(distinct) 逻辑只会有1个 reducer 来处理。

优化方法：用 group by 来改写

-- 直接使用 count(distinct xx)
 select count(distinct id) from student;

-- 先 group by 在 count 
select count(1) from (select id from student group by id) tmp;

缺陷：

• 会启动两个MR Job；
• 当数据集很小或者key有倾斜，group by可能比distinct还要慢

使用场景：

• 确保数据量大到启动新的MR Job的开销比计算耗时小

3. Join

分桶Join

实测

1.普通join

创建大表 bigtable2 和 bigtable1的数据是一样样的


88s

2.分桶join


只要49秒；

小表写左边

3. hive建表设计层面调优

1.分区表

当一个 Hive 表的查询大多数情况下，会根据某一个字段进行筛选时，那么非常适合创建为分区表，该字段即为分区字段。

2.分桶表

分桶原理

跟分区的概念很相似，都是把数据分成多个不同的类别，区别就是规则不一样！

1、分区：
一个分区，就只包含这个这一个值的所有记录，不是当前分区的数据一定不在当前分区

2、分桶：Hash散列
一个分桶，包含多个不同的值 如果一个分桶中，包含了某个值，这个值的所有记录，必然都在这个分桶

Hive Bucket，分桶，是指将数据以指定列的值为 key 进行 hash，hash 到指定数目的桶中，这样做的目的和分区表类似，使得筛选时不用全局遍历所有的数据，只需要遍历所在桶就可以了。这样也可以支持高效采样

分桶经常使用的场景：

• 1、采样(采样多少百分比的数据，采样多少条的数据，采样多大量的数据)
• 2、join(doris:OLAP引擎: colocation join: 两张表中的相同分桶编号的数据，在同一个节点)

比如想去分析某个数据的中位数，或者平均值啊 最大最小值，从全表算是很消耗性能的；此时可以用分桶表，分桶表中的数据具备样例，可以做近似分析；
因为分桶表示散列的；

分桶语法

如下例就是以 userid 这一列为 bucket 的依据，共设置 32 个 buckets

CREATE TABLE page_view(
	viewTime INT, 
	userid BIGINT, 
	page_url STRING, 
	referrer_url STRING, 
	ip STRING COMMENT 'IP Address of the User'
) COMMENT 'This is the page view table' 
PARTITIONED BY(dt STRING, country STRING) 
CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS 
ROW FORMAT DELIMITED 
		FIELDS TERMINATED BY '1' 
		COLLECTION ITEMS TERMINATED BY '2' 
		MAP KEYS TERMINATED BY '3' 
STORED AS SEQUENCEFILE;

分桶的语法：

CLUSTERED BY(userid) SORTED BY(viewTime) INTO 32 BUCKETS

CLUSTERED BY(userid) 表示按照 userid 来分桶 
SORTED BY(viewTime) 按照viewtime来进行桶内排序 
INTO 32 BUCKETS 分成多少个桶

分桶表优化join效率 实战

select a.*, b.* from a join b on a.id = b.id;

1. 两张表都得按照这个链接字段来进行分桶
2. 分桶数量要成倍数关系

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 buckets; 

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

-- 直接使用order by来做。如果结果数据量很大，这个任务的执行效率会非常低 
select id,name,age from student order by age desc limit 3;

-- 使用distribute by + sort by 多个reduceTask，每个reduceTask分别有序 
set mapreduce.job.reduces=3; 
drop table student_orderby_result; 
-- 范围分桶 0 < 18 < 1 < 20 < 2 
create table student_orderby_result 
as select * from student distribute by 
(case when age > 20 then 0 
      when age < 18 then 2 else 1 end) 
sort by (age desc);

3.数据存储及压缩

采用数据存储和压缩的优点：

降低 IO 读写，降低网络传输量，这样在一定程度上可以节省存储空间，还能提升 hql 任务执行效

数据存储

Hive提供的格式有TEXT、SequenceFile、RCFile、ORC和Parquet等。

SequenceFile是一个二进制key/value对结构的平面文件，在早期的Hadoop平台上被广泛用于MapReduce输出/输出格式，以及作为数据存储格式。

Parquet是一种列式数据存储格式，可以兼容多种计算引擎，如MapRedcue和Spark等，对多层嵌套的数据结构提供了良好的性能支持，是目前Hive生产环境中数据存储的主流选择之一。

ORC优化是对RCFile的一种优化，它提供了一种高效的方式来存储Hive数据，同时也能够提高Hive的读取、写入和处理数据的性能，能够兼容多种计算引擎。事实上，在实际的生产环境中，ORC已经成为了Hive在数据存储上的主流选择之一。

我们执行同样的SQL语句及同样的数据，只是数据存储格式不同，得到如下执行时长：

查询TextFile类型的数据表耗时33分钟， 查询ORC类型的表耗时1分52秒，时间得以极大缩短，可见不同的数据存储格式也能给HiveSQL性能带来极大的影响。

简单的标准：

1、原始数据，一般来说，都是普通文本格式
2、计算的结果数据

• 如果这种结果数据，通常还要作为另外一种计算的输入，那么可以让当前这个结果数据的格式易于被读取 （序列化文件格式可用）
• 如果这种结果数据，就是最终的结果了，那么看数据量的大小关系：
  如果比较大：可以考虑压缩
  如果不是特别大：直接普通文本格式，甚至存储在 RDBMS

3、计算引擎种应用程序在执行的时候：
中间临时数据：执行引擎的特性：列式存储 + 序列化存储文件格式

使用建议

创建表时，特别是宽表，尽量使用 ORC、ParquetFile 这些列式存储格式，因为列式存储的表，每一列的数据在物理上是存储在一起的，Hive 查询时会只遍历需要列数据，大大减少处理的数据量。

三个方面考量选择合适的压缩格式：
1.压缩比率
2.解压速度
3.是否支持split

Hive 千亿级数据倾斜

1.什么是数据倾斜？

数据倾斜的本质：
并行计算中，key的分布不均匀，从而导致有的任务执行时间长；

现象：
MR程序执行时，reduce节点大部分执行完毕，但是有一个或者几个reduce节点运行很慢，导致整个程序的处理时间很长。

发生数据倾斜的原因有两种：

• reduce端数据倾斜 ：任务中需要处理大量相同 的 key 的数据。
• map端数据倾斜： 任务读取不可分割的大文件。

一个任务中，数据文件在进入 map 阶段之前会进行切分，默认是 128M 一个数据 块，但是如果当对文件使用 GZIP 压缩等不支持文件分割操作的压缩方式时，MR 任 务读取压缩后的文件时，是对它切分不了的，该压缩文件只会被一个任务所读取， 如果有一个超大的不可切分的压缩文件被一个 map 读取时，就会发生 map 阶段的 数据倾斜。

MapReduce 和 Spark 中的数据倾斜解决方案原理都是类似的，以下讨论 Hive 使 用 MapReduce 引擎引发的数据倾斜，Spark 数据倾斜也可以此为参照

2.Hive中会出现数据倾斜的场景

• join
• count(distinct)
• group by
• 一些窗口函数中用了partition by一会造成数据倾斜

2.1 Join 场景

2.1.1 大表和小表join

比如，一个上千万行的记录表和一个几千行表之间join关联时，容易发生数据倾斜。为什么大表和小表容易发生数据倾斜（无非有的reduce执行时间被延迟）具体小表join大表数据倾斜请使用请参考：

解决方案：

使用Map Join让小的维度表缓存到内存，在map端完成join过程，从而省略掉redcue端的工作

--1.开启map-side join的设置属性(默认关闭)：
set hive.auto.convert.join=true

--2.设置使用这个优化的小表的大小(默认值25M)：
set hive.mapjoin.smalltable.filesize=25000000

2.2.2 大表和大表的join

1. 空值 join

场景：

业务中有些大量的 null 值或者一些无意义的数据参与到计算作业中，这样所有的 null 值都会被分配到一个 reduce 中，必然产生数据倾斜。

解决方案1： user_id 为空的不参与关联，即不让 null 值有 shuffle 阶段

SELECT * FROM 
log a JOIN 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;

解决方案2：因为 null 值参与 shuffle 时的 hash 结果是一样的，那么我们可以给 null 值随机赋值，这样它们的 hash 结果就不一样，就会进到不同的 reduce 中：

SELECT * FROM 
log a LEFT JOIN users b 
ON CASE WHEN 
	a.user_id IS NULL THEN concat('hive_', rand())
	ELSE a.user_id END = b.user_id;

方案对比：

• 方案1读取两次log表，job数位2，方案2只读取一次，job数为1；
• 这个优化适合无效 id（比如-99，’’，null）产 生的数据倾斜，把空值的 key 变成一个字符串加上一个随机数，就能把造成数据倾斜的 数据分到不同的 reduce 上解决数据倾斜的问题。

改变之处：使本身为 null 的所有记录不会拥挤在同一个 reduceTask 了，会由于有替代的 随机字符串值，而分散到了多个 reduceTask 中了，由于 null 值关联不上，处理后并不影响最终结果。

2. 不同数据类型 join

用户表中 user_id 字段为 int，log 表中 user_id 为既有 string 也有 int 的类型， 当按照两个表的 user_id 进行 join 操作的时候，默认的 hash 操作会按照 int 类型的 id 进 行分配，这样就会导致所有的 string 类型的 id 就被分到同一个 reducer 当中

SELECT * FROM 
users a LEFT JOIN logs b ON a.usr_id = CAST(b.user_id AS string);

2.2 GroupBy场景

优化手段1：预聚合

配置： hive.map.aggr=true
该参数控制在group by的时候是否map局部聚合，这个参数默认是打开的。


适用场景：

• 聚合方式不影响业务逻辑（count\sum\min\max\） （count distinct 会关闭预聚合）
• groupBy_key 重复越多效果越好；如果groupBy_key是唯一键，开启此参数没有意义，并且造成计算资源的浪费。
  
  

相关参数：

• hive.groupby.mapaggr.checkinterval = 100000
• Hive.map.aggr.hash.min.reduction=0.5

上面这两个参数控制关掉map聚合的策略。Map开始的时候先尝试给前100000 条记录做hash聚合，如果聚合后的记录数/100000>0.5说明这个groupby_key没有什么重复的，再继续做局部聚合没有意义，在聚合100000 以后就自动把预聚合开关关掉，降级到普通的Aggregation。

优化手段2：两阶段聚合

不一定非要数据倾斜才可以使用；如果groupByKey本身就很少，数据量很大，只能交给几个ReduceTask处理，这个时候也可以使用两阶段聚合，将Key散列，增加并行处理；

比如下面sql，gender只有两个值，因此能用到的reduceTask只有两个，处理压力很大

select user.gender,count(distinct user.id) from user 
group by user.gender

不开启两阶段聚合： 只有2个redcue做聚合，每个reduce处理100亿条记录。

开启两阶段聚合：

第一阶段：map将groupBy_key 散列

第二个MR完成最终的聚合，统计男女的distinct id值

配置： hive.groupby.skewindata =true

• 该参数会把上面的sql翻译成两个MR，第一个MR的reduce_key是groupBy_key + id。因为id是一个随机散列的值，因此这个MR的reduce计算是很均匀的，reduce完成局部聚合的工作。
• 默认是关闭的，因此如果确定有不均衡的情况，需要手动打开这个开关。当然，并不是所有的有distinct的group by都需要打开这个开关

select gender,count (distinct id) from user group by user.gender

userid是一个散列的值，因此已经是计算均衡的了，所有的reduce都会均匀计算。只有在groupby_key不散列才需要打开这个开关，其他的情况map聚合优化就足矣。

优化手段3：不做聚合时

在一些操作中，我们没有办法减少数据量，如在使用 collect_list 函数时：

select s_age,collect_list(s_score) list_score from student group by s_age 

• collect_list：将分组中的某列转为一个数组返回。
• 在上述 sql 中，s_age 有数据倾斜，但如果数据量大到一定的数量，会导致处理 倾斜的 Reduce 任务产生内存溢出的异常。
• collect_list 输出一个数组，中间结果会放到内存中，所以如果 collect_list 聚 合太多数据，会导致内存溢出。
• 有小伙伴说这是 group by 分组引起的数据倾斜，可以开启 hive.groupby.skewindata来优化。我们接下来分析下： 开启该配置会将作业拆解成两个作业，第一个作业会尽可能将 Map 的数据平均分 配到 Reduce 阶段，并在这个阶段实现数据的预聚合，以减少第二个作业处理的 数据量；第二个作业在第一个作业处理的数据基础上进行结果的聚合。 hive.groupby.skewindata核心作用在于生成的第一个作业能够有效减少数量。 但是对于 collect_list 这类要求全量操作所有数据的中间结果的函数来说，明 显起不到作用，反而因为引入新的作业增加了磁盘和网络 I/O 的负担，而导致性 能变得更为低下.

解决方案： 这类问题最直接的方式就是调整 reduce 所执行的内存大小。 调整 reduce 的内存大小使用 mapreduce.reduce.memory.mb 这个配置。

2.3 count(distinct)引起数据倾斜

使用sum…group by代替：

select a,sum(1) from (select a, b from t group by a,b) group by a;