Hive 调优总结...持续更新

Fetch抓取（Hive可以避免进行MapReduce）

Fetch 抓取说明

Hive 中对某些情况的查询可以不必使用 MapReduce 计算

例如：select * from employees; 在这种情况下，Hive 可以简单地读取 employee 对应的存储目录下的文件，然后输出查询结果到控制台

在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more ，老版本 hive 默认是 minimal ，该属性修改为 more 以后，在全局查找、字段查找、limit 查找等都不走 mapreduce

Fetch 抓取设置

# 查询语句，都会执行 mapreduce 程序
set hive.fetch.task.conversion=none;

# 查询方式都不会执行 mapreduce 程序
set hive.fetch.task.conversion=more;

本地模式

本地模式说明

多数的 Hadoop Job 是需要 Hadoop 提供的完整的可扩展性来处理大数据集的

不过，有时 Hive 的输入数据量是非常小的，在这种情况下，为查询触发执行任务时消耗可能会比实际 job 的执行时间要多的多

对于大多数这种情况，Hive 可以通过本地模式在单台机器上处理所有的任务，对于小数据集，执行时间可以明显被缩短。

本地模式的开启

--开启本地 mr
set hive.exec.mode.local.auto=true;  

--设置local mr的最大输入数据量，当输入数据量小于这个值时采用local  mr的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=51234560;

--设置local mr的最大输入文件个数，当输入文件个数小于这个值时采用local mr的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

Join 优化

关联优化器

关联优化器说明

在 Hive 的一些复杂关联查询中，可能同时还包含有 group by 等能够触发 shuffle 的操作，有些时候 shuffle 操作是可以共享的，通过关联优化器选项，可以尽量减少复杂查询中的 shuffle，从而提升性能

-- 开启关联优化器
set hive.optimize.correlation=true;

Map 端 Join 优化（大表 Join 小表）

什么是 MapJoin

在 Map 阶段进行表之间的连接，而不需要进入到 Reduce 阶段才进行连接，这样就节省了在 Shuffle 阶段时要进行的大量数据传输，从而起到了优化作业的作用

MapJoin 使用的场景

① 条件一：一份表的数据分布在不同的Map中
② 条件二：其他连接的表的数据必须在每个 Map 中有完整的拷贝
在两个要连接的表中，一个是大表，另外一个是小表，且小表的数据可以放在内存中而不影响性能，这样我们就把小表文件复制到每一个Map任务的本地，再让Map把文件读到内存中待用

优化说明

如果不指定 MapJoin 或者不符合 MapJoin 的条件，那么 Hive 解析器会将 Join 操作转换成 Common Join ，即：在 Reduce 阶段完成 join ，容易发生数据倾斜

可以用 MapJoin 把小表全部加载到内存 ，在 map 端进行 join ，避免 reducer 处理

优化执行过程

① TaskA

通过 MapReduce Local Task ，将小表读入内存，转换为 HashTable 数据结构，写入到本地文件中 HashTableFiles ，然后加载到 Distributed Cache 中

② TaskB

启动 MapTasks 扫描大表 a , 在 Map 阶段，根据 a 的每一条记录去和 DistributeCache 中 b 表对应的 HashTable 关联，由于 MapJoin 没有 Reduce ，也就是在 Map 阶段完成 Join ，然后直接输出结果

map 端的 join 参数配置

① 设置自动选择 mapjoin

-- 默认为true
set hive.auto.convert.join = true; 

② 表文件的大小作为开启 MapJoin 的阈值

-- 旧版本
-- 小表的输入文件大小的阈值（以字节为单位）
-- 如果文件大小小于此阈值，它将尝试将 common join 转换为 map join
-- 在实际使用中，只要根据业务把握住小表的阈值标准即可，hive 会自动帮我们完成 mapjoin，提高执行的效率
-- 如果 hive.auto.convert.join = false ，则该参数配置不生效
set hive.mapjoin.smalltable.filesize= 25000000;

-- 新版本
-- 如果参与连接的 N 个表(或分区) 中的 N-1 个的总大小小于 512MB ，则自动转为 MapJoin
-- 默认值为 20MB
set hive.auto.convert.join.noconditionaltask.size=512000000

MapJoin 使用案例

-- A 表有 100 条记录，B 表有 100w 条记录

-- 优化前
-- B 表的数据倾斜比较严重，某个 key 有 30w 条记录，运行过程中很慢，且 reduce 阶段运行时间过长或内存不够
select f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802)  

-- 优化后
-- 通过 MapJoin 在 Map 阶段做了 join 操作，reduce 阶段的效率就会提高
-- 在 hive 0.7 之前需要通过改种方式才能使 MapJoin 生效，hive 0.7 之后会自动完成 MapJoin
select /*+ mapjoin(A)*/ f.a,f.b from A t join B f  on ( f.a=t.a and f.ftime=20110802) 

Bucket-MapJoin

什么是 Bucket-MapJoin

两个表 Join 的时候，小表不足以放到内存中，但是又想用 map side join ，这个时候就要用到 bucket Map join

Bucket-MapJoin 使用的条件

① set hive.optimize.bucketmapjoin = true;

② 一个表的 bucket 数是另一个表 bucke t数的整数倍

③ bucket列 == join列

④ 必须是应用在 map join 的场景中，如果表不是 bucket 的，则只是做普通 join

Bucket-MapJoin 的使用

两个 join 表在 join key 上都做 hash bucket ，并且把你打算复制的那个（相对）小表的 bucket 数设置为大表的倍数。这样数据就会按照 key join ，做 hash bucket 。小表依然复制到所有节点，Map join 的时候，小表的每一组 bucket 加载成 hashtable ，与对应的一个大表 bucket 做局部 join ，这样每次只需要加载部分 hashtable 就可以了

-- 小表
create table testA(imei string,sndaid string,data_time string) clustered BY(imei) SORTED BY(imei) INTO 10 BUCKETS;

-- 大表
create table testB(imei string,sndaid string,data_time string) clustered BY(imei) SORTED BY(imei) INTO 5 BUCKETS;

-- 开启 Bucket-MapJoin
set hive.optimize.bucketmapjoin = true;
-- 查询
select /*+ mapjoin(b) */ count(1) from testA a join testB b on a.imei = b.imei  

Sort Merge Bucket Join (SMB Join)

什么是 SMB Join

大表对小表应该使用 MapJoin 来进行优化，但是如果是大表对大表，如果进行 shuffle ，那就非常可怕，第一个慢不用说，第二个容易出异常，此时就可以使用 SMB Join 来提高性能。SMB Join 基于 bucket-mapjoin 的 有序bucket ，可实现在 map 端完成 join 操作，可以有效地减少或避免 shuffle 的数据量，SMB join 的条件和 Map join 类似但又不同

SMB Join 的使用条件

bucket mapjoin	SMB join
set hive.optimize.bucketmapjoin = true;	set hive.optimize.bucketmapjoin = true; set hive.auto.convert.sortmerge.join=true; set hive.optimize.bucketmapjoin.sortedmerge = true; set hive.auto.convert.sortmerge.join.noconditionaltask=true;
一个表的bucket数是另一个表bucket数的整数倍	小表的 bucket 数 = 大表 bucket 数
bucket列 == join列	Bucket 列 == Join 列 == sort 列
必须是应用在map join的场景中	必须是应用在bucket mapjoin 的场景中

确保同列排序

hive 并不检查两个 join 的表是否已经做好 bucket 且 sorted ，需要用户自己去保证 join 的表数据 sorted，否则可能数据不正确

# 办法一： 开启强制排序，插数据到表中会进行强制排序，默认false
hive.enforce.sorting = true

# 办法二：插入数据时通过在sql中用
distributed c1 sort by c1 或者 cluster by c1

SMB Join的使用

① 建表

-- 必须包含 CLUSTERED BY 和 SORTED BY
create table test_smb_2(mid string,age_id string)
CLUSTERED BY(mid) SORTED BY(mid) INTO 500 BUCKETS;

② 开启配置

--写入数据强制分桶
set hive.enforce.bucketing=true;
--写入数据强制排序
set hive.enforce.sorting=true;
--开启bucketmapjoin
set hive.optimize.bucketmapjoin = true;
--开启SMB Join
set hive.auto.convert.sortmerge.join=true;
set hive.auto.convert.sortmerge.join.noconditionaltask=true;

空key过滤（大表 Join 大表）

操作说明

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同的 reducer 上，从而导致内存不够。此时我们应该仔细分析这些异常的 key ，很多情况下，这些 key 对应的数据是异常数据，我们需要在 SQL 语句中进行过滤。例如key 对应的字段为空，操作如下：

环境准备

执行过程

-- 不过滤
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM nullidtable a JOIN ori b ON a.id = b.id;

-- 过滤
INSERT OVERWRITE TABLE jointable
SELECT a.* FROM (SELECT * FROM nullidtable WHERE id IS NOT NULL ) a JOIN ori b ON a.id = b.id;

空key转换（大表 Join 大表）

操作说明

有时虽然某个 key 为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在 join 的结果中，此时我们可以表 a 中 key 为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的 reducer 上

执行过程

-- 不随机分布
-- 所有为null值的id全部都变成了相同的字符串，及其容易造成数据的倾斜（所有的key相同，相同key的数据会到同一个reduce当中去）
set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;

INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN 'hive' ELSE a.id END = b.id;

-- 随机分布
-- 通过hive的rand函数，随机的给每一个为空的id赋上一个随机值，这样就不会造成数据倾斜
set hive.exec.reducers.bytes.per.reducer=32123456;
set mapreduce.job.reduces=7;

INSERT OVERWRITE TABLE jointable
SELECT a.*
FROM nullidtable a
LEFT JOIN ori b ON CASE WHEN a.id IS NULL THEN concat('hive', rand()) ELSE a.id END = b.id;

SQL 优化

列裁剪

列裁剪说明

Hive 在读数据的时候，只读取查询中所需要用到的列，而忽略其他列

这样做可以节省读取开销、中间表存储开销和数据整合开销

列裁剪的配置

-- 默认为true
hive.optimize.cp=true

分区裁剪

分区裁剪说明

在查询的过程中减少不必要的分区，Hive 自动执行这种裁剪优化

当使用外关联时，如果将副表 (外表) 的过滤条件写在 Where 后面，那么就会先全表关联，之后再过滤

总之，别被这个 分区 所干扰，只需要记住：使用外关联时，外表的过滤条件别写在 where 后面

分区裁剪配置

-- 默认为true
hive.optimize.pruner=true

示例一

--（多余分区），增加了分区读入的数据量
-- 这个sql中，reduce 阶段拿到了所有的数据，所以分区进入的数据量明显增加
SELECT * FROM (SELECT a1, COUNT(1) FROM T GROUP BY a1) subq WHERE subq.prtn=100;

-- 尽量让活都在 Map 阶段干，这个 sql 中，reduce 阶段拿到的数据只是关联后的数据 
SELECT * FROM T1 JOIN (SELECT * FROM T2) subq ON (T1.a1=subq.a2) WHERE subq.prtn=100;

示例二

-- 假设有三张表，student（学生表）、score（成绩表）
-- 需求：查询成绩 > 90 的学生姓名

-- (多余分区)
SELECT a.name FROM STUDENT a LEFT JOIN SCORE b on a.id = b.stu_id where = b.stu_score > 90;


-- 第一种写法：分区裁剪后
SELECT a.name FROM STUDENT a LEFT JOIN SCORE b ON (b.stu_score > 90 AND a.id = b.stu_id);

-- 第二种写法：分区裁剪后
SELECT a.name FROM STUDENT a RIGHT JOIN (SELECT stu_id FROM SCORE WHERE stu_score >90) b ON a.id = b.stu_id;

示例三

-- 分区裁剪前
selcet b.id from emp1 join emp2 b on a.id = b.id where b.id <= 10;

-- 分区裁剪后 
selcet b.id from emp1 join (select id from emp2 where <= 10) b join on a.id = b.id;

GroupBy

操作说明

默认情况下，Map 阶段同一 Key 数据分发给一个 Reduce ，当一个 key 数据过大时就倾斜了

并不是所有的聚合操作都需要在 Reduce 端完成，很多聚合操作都可以先在 Map 端进行部分聚合，最后在 Reduce 端得出最终结果

开启 Map 阶段的聚合配置

-- 是否在Map端进行聚合，默认为True
set hive.map.aggr = true;

-- 在 Map 端进行聚合操作的条目数目
set hive.groupby.mapaggr.checkinterval = 100000;

-- 有数据倾斜的时候进行负载均衡（默认是false）
set hive.groupby.skewindata = true;

数据倾斜时负载均衡的执行过程

① 当选项设定为 true 时，生成的查询计划会有两个 MR Job

② 第一个 MR Job 负责对数据进行预处理：即 Map 的输出结果会随机分布到 Reduce中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的

③ 第二个 MR Job 负责对预处理的数据得到最终的结果，即：按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作

Count(distinct)

操作说明

数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 MR Job 很难完成，一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换

虽然会多用一个 Job 来完成，但在数据量大的情况下，这个绝对是值得的

示例

-- 建表语句
create table access_log(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) row format delimited fields terminated by '\t';

-- 查询数据量
-- Time taken: 35.49 seconds, Fetched: 1 row(s)
set hive.exec.reducers.bytes.per.reducer=32123456;

SELECT count(DISTINCT id) FROM access_log;


-- 查询数据量(优化后，可以看到，MR任务增加了)
-- Stage-Stage-1: Map: 1  Reduce: 4   Cumulative CPU: 13.07 sec   HDFS Read: 120749896 HDFS Write: 464 SUCCESS
-- Stage-Stage-2: Map: 3  Reduce: 1   Cumulative CPU: 5.14 sec   HDFS Read: 8987 HDFS
set hive.exec.reducers.bytes.per.reducer=32123456;

SELECT count(id) FROM (SELECT id FROM access_log GROUP BY id) a;

笛卡尔积

什么是笛卡尔积

笛卡尔积是一种让两个表全连接的情况

-- 需求：查询所有的员工和所有的部门,按照这个 sql 会取出所有的组合情况，这就是笛卡尔积
select * from emp,dept;

操作说明

① 尽量避免笛卡尔积，即避免 join 的时候不加 on 条件，或者无效的 on 条件，Hive 只能使用 1 个 reducer 来完成笛卡尔积

② 将不等条件写在 where 中，那么 mapreduce 过程中会进行笛卡尔积

-- 该优化 MapJoin 已经帮我们优化
-- MapJoin 在 Map 阶段就完成了不等值的 join 操作，所以在 reduce 阶段的效率会很高
select A.a ,A.b from A join B where A.a>B.a

动态分区调整优化

动态分区介绍

Hive 的动态分区是以第一个表的分区规则，来对应第二个表的分区规则，将第一个表的所有分区，全部拷贝到第二个表中来，第二个表在加载数据的时候，不需要指定分区了，直接用第一个表的分区即可

动态分区参数设置

参数设置说明

往 hive 分区表中插入数据时，hive 提供了一个动态分区功能，其可以基于查询参数的位置去推断分区的名称，从而建立分区。使用 Hive 的动态分区，需要进行相应的配置

简而言之：A 表和B表都有分区，且分区字段相同。B表插入数据时，使用了A表的查询结果，然后B表分区有了结果

参数设置

① 开启动态分区功能

 -- 默认true，开启
 set hive.exec.dynamic.partition=true;

② 设置为非严格模式

-- 动态分区的模式，默认 strict ，表示必须指定至少一个分区为静态分区，nonstrict模式表示允许所有的分区字段都可以使用动态分区
set hive.exec.dynamic.partition.mode=nonstrict;

③ 在所有执行 MR 的节点上，最大一共可以创建多少个动态分区

set  hive.exec.max.dynamic.partitions=1000;

④ 在每个执行 MR 的节点上，最大可以创建多少个动态分区

-- 该参数需要根据实际的数据来设定
set hive.exec.max.dynamic.partitions.pernode=100

⑤ 整个 MR Job 中，最大可以创建多少个 HDFS 文件

-- 在 linux 系统当中，每个 linux 用户最多可以开启 1024 个进程，每一个进程最多可以打开 2048 个文件，即持有 2048 个文件句柄，下面这个值越大，就可以打开文件句柄越大
set hive.exec.max.created.files=100000;

⑥ 当有空分区生成时，是否抛出异常

-- 一般不需要设置
set hive.error.on.empty.partition=false;

案例分析

① 准备数据原表

-- 准备数据
create table access_log(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) 
PARTITIONED BY (p_time bigint) 
row format delimited fields terminated by '\t';

load data local inpath '/opt/software/log.dat' into table access_log partition (p_time='20111230000010');

load data local inpath '/opt/software/log.dat' into table access_log partition (p_time='20111230000011');

② 创建目标分区表

create table access_log_partition(id bigint, time bigint, uid string, keyword string, url_rank int, click_num int, click_url string) PARTITIONED BY (p_time STRING) row format delimited fields terminated by '\t'

③ 向目标分区表中加载数据

如果按照之前介绍的往指定一个分区中 Insert 数据，那么这个需求很不容易实现，这时候就需要使用动态分区来实现

注意：在 SELECT 子句的最后几个字段，必须对应前面 PARTITION (p_time) 中指定的分区字段，包括顺序

INSERT overwrite TABLE access_log_partition PARTITION (p_time)
SELECT id, time, uid, keyword, url_rank, click_num, click_url, p_time
FROM ori_partitioned;

④ 查看分区

-- 查看分区表中的分区
show partitions access_log_partition; 

p_time=20111230000010 
p_time=20111230000011

动态分区的内存溢出问题

① 硬件内存充足

1. 提高 Yarn 的 NodeManager 内存配置，修改参数
yarn.nodemanager.resource.memory-mb

2. 提高MR的内存配置，修改参数
mapreduce.map.java.opts
mapreduce.reduce.java.opts
mapreduce.map.memory.mb
mapreduce.reduce.memory.mb

② 硬件内存不足

# 1. 开启有序动态分区，并关闭Map Join，但过程会比较慢
# 设置为true后，当启用动态分区时，reducer仅随时保持一个记录写入程序，从而降低对 reducer产生的内存压力。但同时也会使查询性能变慢
hive.optimize.sort.dynamic.partition;
# 关闭 MapJoin
set hive.auto.convert.join = false; 

2. 也可以通过 where 条件，按照日期分批进行清洗转换

数据倾斜优化

Map 数据倾斜

MapTask 的数量决定因素

通常情况下，作业会通过 input 的目录产生一个或者多个 MapTask ，它的数量决定于：input 的文件总个数、 input 的文件大小、集群设置的文件块大小

控制 map 数量需要遵循两个原则：使大数据量利用合适的 map 数；使单个 map 任务处理合适的数据量

MapTask 的数量示例

① 假设 input 目录下有 1 个文件 a ，大小为 780M ，那么 hadoop 会将该文件分隔成 7 个块（6个128m的块和1个12m的块），从而产生 7 个 MapTask

② 假设 input 目录下有 3 个文件 a，b，c 大小分别为10m，20m，150m，那么 hadoop 会分隔成4个块（10m，20m，128m，22m），从而产生4个 MapTask ，即：如果文件大于块大小(128m)，那么会拆分，如果小于块大小，则把该文件当成一个块

MapTask 需要关注的两个场景

① 减少 MapTask 的数量

当一个任务有很多小文件（远远小于块大小 128m ），则每个小文件也会被当做一个块，用一个 MapTask 来完成，而一个 MapTask 启动和初始化的时间远远大于逻辑处理的时间，就会造成很大的资源浪费，而且，同时可执行的 MapTask 数是受限的

-- 在 Map 执行前合并小文件，减少 MapTask 数量
-- 大于文件块大小 128M 的，按照 128M 来分隔
set mapred.max.split.size=112345600;
-- 小于 128M , 大于 100M 的，按照 100M 来分隔，把那些小于100M的（包括小文件和分隔大文件剩下的）进行合并
set mapred.min.split.size.per.node=112345600;											
set mapred.min.split.size.per.rack=112345600;
-- 这个参数表示执行前进行小文件合并
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

② 增加 MapTask 的数量

有一个 127M 的文件，正常会用一个 MapTask 去完成，但这个文件只有一个或者两个小字段，却有几千万的记录，如果 MapTask 处理的逻辑比较复杂，用一个 MapTask 去做，肯定比较耗时，此时就需要增加 MapTask 的数量

-- 增加 map 的任务
set mapred.reduce.tasks=10;

-- 建表
create table a_1 as 
select * from tab_info distribute by rand(123);

这样会将a表的记录，随机的分散到包含10个文件的a_1表中，再用a_1代替上面sql中的a表，则会用10个map任务去完成。
每个map任务处理大于12M（几百万记录）的数据，效率肯定会好很多

Reduce 数据倾斜

ReduceTask 的决定因素

ReduceTask 个数的设定极大影响任务执行效率，不指定 ReduceTask 个数的情况下，Hive 会猜测确定一个 ReduceTask 个数，基于以下两个设定

在设置 ReduceTask 个数的时候也需要考虑这两个原则：使大数据量利用合适的 ReduceTask 数；使单个reduce任务处理合适的数据量

① 每个 ReduceTask 任务处理的数据量，默认为 1000^3=1G

hive.exec.reducers.bytes.per.reducer

② 每个任务最大的 ReduceTask 数，默认为 999

hive.exec.reducers.max

ReduceTask 数量的计算公式：

N = min（999，MapTask的输出文件大小/单个ReduceTask的最大数据处理量）

ReduceTask 数量调整

-- 方法一
-- 设置单个 ReduceTask 的最大处理数据量为 500M ，此时 ReduceTask 的数量为20个左右
set hive.exec.reducers.bytes.per.reducer=524288000; 

-- 方法二
-- 直接设定 ReduceTask 的数量
set mapred.reduce.tasks=15;

ReduceTask 数量并不是越多越好

同 MapTask 一样，启动和初始化 ReduceTask 也会消耗时间和资源；

另外，有多少个 ReduceTask ，就会有个多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题

只有一个 ReduceTask 的情况

很多时候你会发现任务中不管数据量多大，不管你有没有调整 ReduceTask 个数的参数，任务中一直都只有一个 ReduceTask ；其实只有一个 ReduceTask 的情况，除了数据量小于 hive.exec.reducers.bytes.per.reducer 参数值的情况外，还有以下原因

① 没有 group by 的汇总

-- 这是正确的写法
select pt,count(1) from tab_info where pt = ‘2020-07-04’ group by pt; 

-- 这样写只会有一个 ReduceTask
select count(1) from tab_info where pt = ‘2020-07-04’; 

② 用了 Order by

③ 有笛卡尔积

表连接数据倾斜（Join skew in）

skewjoin 执行原理

对于关联的 key ，在执行 job 的时候，将他们存入临时的 HDFS 目录，其它数据正常执行。然后对于这些倾斜的数据开启 mapjoin 操作(多个map 并行执行)，对非倾斜的值采取普通 join 操作。最后将倾斜的数据集和非倾斜的数据集进行 合并Union 操作

① 运行时优化

表和大表进行 join 操作，则可采用 skewjoin(倾斜关联) 来开启对倾斜数据的优化

-- 开启表连接数据倾斜优化，默认关闭
set hive.optimize.skewjoin=true;

-- 倾斜数据的阈值，超过该阈值，则认为发生了表连接数据倾斜，默认值 100000
set hive.skewjoin.key=100000;

② 编译时优化

在编译 sql 时已经将执行计划优化完毕。但要注意的是，只有在表的元数据中存储的有数据倾斜信息时，才能生效

-- 开启编译时优化，默认关闭
set hive.optimize.skewjoin.compiletime=true;

-- 开启运行阶段时优化，默认关闭
hive.optimize.skewjoin.runtime=true;

-- 在建表语句中指定倾斜的元数据
CREATE TABLE list_bucket_single (key STRING, value STRING)
    -- 倾斜的字段和需要拆分的key值
    SKEWED BY (key) ON (1,5,6)
    --  为倾斜值创建子目录单独存放
    [STORED AS DIRECTORIES];

③ Union 优化

应用了表连接倾斜优化以后，会在执行计划中插入一个新的 union 操作

-- 开启对 union 的优化配置，默认关闭
set hive.optimize.union.remove=true;

-- 开启如下配置，可减少对 Union all 子查询中间结果的二次读写，可以避免 union 输出的额外扫描过程
set hive.optimize.skewjoin=true;
set hive.optimize.skewjoin.compiletime=true;
set hive.optimize.union.remove=true;

分组统计数据倾斜（Groupby skew in）

① Map 阶段聚合

开启 map 端 combiner ，此配置可以在 group by 语句中提高 HiveQL 聚合的执行性能

这个设置可以将顶层的聚合操作放在 Map 阶段执行，从而减轻数据传输和 Reduce 阶段的执行时间，提升总体性能

-- 默认开启，无需显示声明
hive.map.aggr=true;

② 两个 MRJob

该配置项用于决定 group by 操作是否支持倾斜数据的负载均衡处理，当数据出现倾斜时，Hive 会自动进行负载均衡，此时生成的查询计划会有两个 MR Job

第一个 MR Job：Map 的输出结果集合会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的

第二个 MR Job：根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个Reduce中），最后完成最终的聚合操作

set hive.groupby.skewindata=true;

-- 如果无法避免多个列去重，则将该配置项关闭
set hive.groupby.skewindata=false;

注意：开启该配置后，如果在多个列上进行去重操作，会报错

-- 1、2、3 正常执行，4 会报错
SELECT count(DISTINCT uid) FROM log
SELECT ip, count(DISTINCT uid) FROM log GROUP BY ip
SELECT ip, count(DISTINCT uid, uname) FROMlog GROUP BY ip
SELECT ip, count(DISTINCT uid), count(DISTINCT uname) FROMlog GROUP BY ip

并行执行优化

Hive 编译查询限制

Hive 默认同时只能编译一段 HiveQL ，并上锁，如果在 UDF 中执行了一段 HiveQL，或者多个用户同时使用的话，就会锁住

① 开启多个会话可以同时编译查询

set hive.driver.parallel.compilation=true

② 设置允许同时编译查询的值

-- 0 或 负值 为无限制
set hive.driver.parallel.compilation.global.limit=0

Hive 不同阶段任务并行执行

并行执行说明

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是 MapReduce 阶段、抽样阶段、合并阶段、limit 阶段或者 Hive 执行过程中可能需要的其他阶段

默认情况下，Hive 一次只会执行一个阶段。不过，某个特定的 job 可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个 job 的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么 job 可能就越快完成

并行执行的设置

在共享集群中，需要注意下，如果 job 中并行阶段增多，那么集群利用率就会增加。并行是在系统资源比较空闲的时候才有优势，否则，没资源，并行也起不来

-- 打开任务并行执行
set hive.exec.parallel=true;      

-- 同一个 sql 允许最大并行度，默认为 8
set hive.exec.parallel.thread.number=16;  

其它优化

Hive 小文件合并

注意：此部分设置，要根据硬件内存来进行调整

① 是否开启合并 Map 端小文件，在 Map-only 的任务结束时合并小文件

-- 开启小文件合并
set hive.merge.mapfiles=true

② 是否开启合并 Reduc e端小文件，在 map-reduce 作业结束时合并小文件

-- 开启小文件合并
set hive.merge.mapredfiles=true

③ 合并后 MR 输出文件的大小

-- 合并后的文件大小，默认大小为 256M
set hive.merge.size.per.task=256M

④ 输出文件的平均大小的阈值，超过该阈值则启动一个独立的 map-reduce 任务进行文件 merge

-- 默认大小为 16M
set hive.merge.smallfiles.avgsize=16M

矢量化查询

矢量化查询说明

Hive 的默认查询执行引擎一次处理一行，而矢量化查询执行是一种 Hive 特性，目的是按照每批 1024 行读取数据，并且一次性对整个记录整合（而不是对单条记录）应用操作

注意：要使用矢量化查询执行，就必须以 ORC格式 存储数据

-- 开启矢量化查询
set hive.vectorized.execution.enabled=true;

读取零拷贝

读取零拷贝说明

ORC 可以使用新的 HDFS 缓存 API 和 ZeroCopy 读取器来避免在扫描文件时将额外的数据复制到内存中

-- 开启读取零拷贝
set hive.exec.orc.zerocopy=true;

严格模式

严格模式说明

Hive 提供了一个严格模式，可以防止用户执行那些可能意向不到的不好的影响的查询

严格模式的配置

-- 开启严格模式
set hive.mapred.mode = strict;  

-- 开启非严格模式
set hive.mapred.mode = nostrict; 

<property>
    <name>hive.mapred.mode</name>
    <value>strict</value>
</property>

严格模式可以禁止 3 种类型的查询

① 对于分区表，在 where 语句中必须含有分区字段作为过滤条件来限制范围，否则不允许执行

就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表

② 对于使用了 order by 语句的查询，要求必须使用 limit 语句

因为 order by 为了执行排序过程会将所有的结果数据分发到同一个 Reducer 中进行处理，强制要求用户增加这个 LIMIT 语句可以防止 Reducer 额外执行很长一段时间

③ 限制笛卡尔积的查询

对关系型数据库非常了解的用户可能期望在执行 JOIN 查询的时候不使用 ON 语句而是使用 where 语句，这样关系数据库的执行优化器就可以高效地将 WHERE 语句转化成那个 ON语句。但是 Hive 并不会执行这种优化，因此，如果表足够大，那么这个查询就会出现不可控的情况

JVM 重用

JVM 重用说明

注意：随着 Hadoop 版本的升级，已自动优化了 JVM 重用选项，MRv2 开始不再支持 JVM 重用

JVM 重用是 Hadoop 调优参数的内容，其对 Hive 的性能具有非常大的影响，特别是对于很难避免小文件的场景或 task 特别多的场景，这类场景大多数执行时间都很短

Hadoop 的默认配置通常是使用派生 JVM 来执行 MapTask 和 ReduceTask 任务的。这时 JVM 的启动过程可能会造成相当大的开销，尤其是执行的 job 包含有成百上千 task 任务的情况。JVM 重用可以使得 JVM 实例在同一个 job 中重新使用 N 次。通常在 10-20 之间，具体多少需要根据具体业务场景测试得出

JVM 重用的配置

① 方法一：在 mapred-site.xml 中配置

<!-- 在 mapred-site.xml 中配置 Job 重用的次数 -->
<property>
  <name>mapreduce.job.jvm.numtasks</name>
  <value>10</value>
  <description> How many tasks to run per jvm. If set to -1, there is no limit. </description>
</property>

② 方法二：在 Hive 中配置

set  mapred.job.reuse.jvm.num.tasks=10;

推测执行

推测执行说明

在分布式集群环境下，因为程序 Bug（包括 Hadoop 本身的 bug ），负载不均衡或者资源分布不均等原因，会造成同一个作业的多个任务之间运行速度不一致，有些任务的运行速度可能明显慢于其他任务（比如一个作业的某个任务进度只有 50% ，而其他所有任务已经运行完毕），则这些任务会拖慢作业的整体执行进度。为了避免这种情况发生，Hadoop 采用了推测执行（ Speculative Execution ）机制，它根据一定的法则推测出 “拖后腿” 的任务，并为这样的任务启动一个备份任务，让该任务与原始任务同时处理同一份数据，并最终选用最先成功运行完成任务的计算结果作为最终结果

推测执行的配置参数

① 方法一：在 mapred-site.xml 中配置

<!-- 开启推测执行 -->
<property>
  <name>mapreduce.map.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some map tasks 
               may be executed in parallel.</description>
</property>
<property>
  <name>mapreduce.reduce.speculative</name>
  <value>true</value>
  <description>If true, then multiple instances of some reduce tasks 
               may be executed in parallel.</description>
</property>

② 方法二：在 hive 中开启

set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true
set hive.mapred.reduce.tasks.speculative.execution=true;

推测执行的注意事项

如果用户对于运行时的偏差非常敏感的话，那么可以将这些功能关闭掉。如果用户因为输入数据量很大而需要执行长时间的map或者Reduce task的话，那么启动推测执行造成的浪费是非常巨大大

存储方式和压缩方式

大数据场景下存储格式压缩格式尤为关键，可以提升计算速度，减少存储空间，降低网络io，磁盘io，所以要选择合适的压缩格式和存储格