Hive 知识点八股文记录 ——（二）优化

函数

UDF：用户定义函数

UDAF：用户定义聚集函数

UDTF：用户定义表生成函数

建表优化

分区建桶

1. 创建表时指定分区字段 PARTITIONED BY (date string)
2. 指定分桶字段和数量 ·CLUSTERED BY (id) INTO 10 BUCKETS·
3. 插入数据按分区、分桶字段插入

提高查询速度（查询范围减少），数据聚集性增强，减少连接操作数据流传输

Union

Union

• 去重
• 排序
• 性能较低
  Union all
• 不去重
• 不排序

优化

压缩

1. map阶段压缩（orcfile/parquet算法）

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;

1
2
3
4

2. 输出结果压缩（snappy）

set hive.exec.compress.output=true 
set mapred.output.compression.codec=org.apache.hadoop.io.compress.SnappyCodec
1
2

3. 建表时候压缩

环境参数

参数优化

// 让可以不走mapreduce任务的，就不走mapreduce任务
hive> 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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28

sql

where

where条件优化，join的过程中，对小表先进行where操作（where条件在map端执行），再与另一个表join，而非先join再where

# 优化前
select m.cid,u.id from order m join customer u on( m.cid =u.id )where m.dt='20180808';
# 优化后
select m.cid,u.id from （select * from order where dt='20180818'） m join customer u on( m.cid =u.id)
1
2
3
4

union

少用union，多用union all+group by组合

count distinc

调整为
count(1) from (select col group by col)

in

只需要查询单个列是否出现在别的表的情况
in代替join
select a from t1 where a in (select a in t2)

子查询

group by, count(distinct) max, min可减少job数量

数据倾斜

任务进度长时间维持在99%（或100%），部分reduce子任务处理数据对比其他reduce数据过大。

key本身分布不均匀

1. 字段较为集中, 使用随机值打散，

create table small_table as 
select a.key
,sum(a.Cnt) as Cnt
from(
    select key
    ,count(1) as Cnt
    from table_name
    group by key,
        case when key = "较为集中的字段" then Hash(rand()) % 50
        else key
        end
) a
group by a.key;
1
2
3
4
5
6
7
8
9
10
11
12
13

字段较为集中也可能出现在小表join大表情况，可以将小表存入内存再对达标进行map操作（小表存入内存是hive自己根据表大小确定的）

set hive.auto.convert.join=true; //设置 MapJoin 优化自动开启
set hive.mapjoin.smalltable.filesize=25000000 //设置小表不超过多大时开启 mapjoin 优化
1
2

空值

# 筛选出不为空值的参与关联
select * from log a join user b on a.user_id is not null and a.user_id = b.user_id
union all
select * from log c where c.user_id is null
1
2
3
4

#给空值赋值
select 
* 
from log a 
left outer join user b 
on 
case when a.user_id is null then concat('hive',rand()) else a.user_id end = b.user_id;
1
2
3
4
5
6
7

第二个方法更好

还有一种情况是对空值聚类，这时候可以先筛选出来。1.count distinct时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。 2.如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union；

select 
cast(count(distinct(user_id))+1 as bigint) as user_cnt
from tab_a
where user_id is not null and user_id <> ''
1
2
3
4

不同数据类型关联

产生数据倾斜（如id同时使用string和int，对id进行join操作）
方法：cast将int转换为字符串

大大表关联

先将大表分为小表再map join

select /*+mapjoin(x)*/* 
from log a
left outer join 
	(
	select /*+mapjoin(c)*/ d.*
	 from 
	 ( 
	 	select distinct user_id 
	 	from log 
 	 ) c 
	 join users d 
	 on c.user_id = d.user_id
	 ) x
on a.user_id = x.user_id;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

大表log使用distinct 减少user_id值，得到第一个小表。小表和user连接，得到第二个“小表”，/+mapjoin©/提示hive将c存入内存，以此类推

group by

set hive.map.aggr = true
# 配置代表开启map端聚合；
#万用参数：
set hive.groupby.skewindata=true
#本质：将一个mapreduce拆分为两个MR
1
2
3
4
5

第一个MR，M结果随机分布到reduce，可能相同的key分布到不同的reduce
第二个MR根据预处理数据结果，groupby key分不到reduce

多个distinct

Select day,count(distinct session_id),count(distinct user_id) from log a group by day
1

空间换时间，union后再用判断来统计，否则distinct会重复计算两次全表且产生数据偏移

SELECT
    day,
    COUNT(CASE WHEN type = 'session' THEN 1 ELSE NULL END) AS session_cnt,
    COUNT(CASE WHEN type = 'user' THEN 1 ELSE NULL END) AS user_cnt
FROM (
    SELECT
        day,
        session_id,
        'session' AS type
    FROM
        log
    UNION ALL
    SELECT
        day,
        user_id,
        'user' AS type
    FROM
        log
) t1
GROUP BY
    day;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21

合并小文件

map输入输出和reduce输出会产生小文件

可以设置如下内容设置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;
1
2
3
4
5
6
7

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
1
2
3
4
5
6
7
8

查看sql的执行计划

explain sql
EXPLAIN [EXTENDED|CBO|AST|DEPENDENCY|AUTHORIZATION|LOCKS|VECTORIZATION|ANALYZE] query
1
2

后面可以跟以下可选参数,但不是所有版本都支持

• EXTENDED：加上 extended 可以输出有关计划的额外信息。这通常是物理信息，例如文件名。这些额外信息对我们用处不大
• CBO：输出由Calcite优化器生成的计划。CBO 从 hive 4.0.0 版本开始支持
• AST：输出查询的抽象语法树。AST 在hive 2.1.0 版本删除了，存在bug，转储AST可能会导致OOM错误，将在4.0.0版本修复
• DEPENDENCY：dependency在EXPLAIN语句中使用会产生有关计划中输入的额外信息。它显示了输入的各种属性
• AUTHORIZATION：显示所有的实体需要被授权执行（如果存在）的查询和授权失败
• LOCKS：这对于了解系统将获得哪些锁以运行指定的查询很有用。LOCKS 从 hive 3.2.0 开始支持
• VECTORIZATION：将详细信息添加到EXPLAIN输出中，以显示为什么未对Map和Reduce进行矢量化。从 Hive 2.3.0 开始支持