Hive高级用法01-hive调优

文章目录

- - 1. 查看执行计划
  - - - 1.1 执行计划有什么用:
      - 1.2 怎么查询执行计划
      - 1.3 简单示例
  - 2.建表优化
  - - - 2.1 建分区表
      - 2.1.1 什么是分区表
      - 2.1.2 怎么建立分区表
      - 2.1.3 分区表中加载数据必须指定分区
      - 2.1.4 查询分区数据
      - 2.1.5分区表中加载数据必须指定分区
      - 2.1.6 如果一天数据量过大可建立二级分区
      - 2.1.7 动态分区--对表进行insert时根据分区字段的不同的值将数据插入的不同分区中
      - 2.2 建分桶表
      - 2.2.1 什么是分桶表?
      - 2.2.2 怎么创建分桶表
      - 2.2.3 怎么创建分桶表
      - 2.2.4 分桶表的好处
      - 2.3 合适的存储格式和压缩方式
      - 2.3.1 Hive支持的存储格式
      - 2.3.2 行存储格式
      - 2.3.3 合适的压缩方式
  - 3.HQL语法优化
  - - - 3.1 列裁剪和分区裁剪
      - 3.2 group by 优化
      - 3.3 vectorization
      - 3.4 多重模式多个SQL查询类型一样
      - 3.5 in/exists用 left semi join代替
      - 3.6 谓词下推
      - 3.7 orderBy优化
      - 3.8 Count Distinct优化
  - 4 小文件合并
  - - - 4.1.1 小文件优化
  - 5.HiveJob优化
  - - - 5.1 Map端
      - 5.1.1 设置maptask数量
      - 5.2 reduce端
      - 5.2.1 设置reduce个数
  - 6. join优化
  - - - 6.1 先过滤再Join
      - 6.2 大小表连接(mapJoin)
      - 6.3 小表join大表原则
      - 6.4 使用相同的连接键
      - 6.5 大表join大表
  - 7.数据倾斜
  - - - 7.1 什么是数据倾斜
      - 7.2 从HQL角度分析什么情况下可能会引起数据倾斜
      - 7.3 单表数据倾斜优化
      - 7.4 join数据倾斜优化
  - 8 Hive架构层面
  - - - 8.1本地抓取
      - 8.1.1 什么是本地抓取模式
      - 8.1.2 有什么用？
      - 8.1.3 怎么查看和开启本地抓取模式
      - 8.1.4设置后哪些sql 可以不用执行mr
      - 8.2 本地执行优化
      - 8.3 jvm重用
      - 8.4 推测执行
      - 8.5 并行执行

1. 查看执行计划

1.1 执行计划有什么用:

查看SQL的执行流程,可分析优化是否有效

1.2 怎么查询执行计划

explain [EXTENDED | DEPENDENCY | AUTHORIZATION] query sql

1.3 简单示例

在这里插入图片描述

2.建表优化

2.1 建分区表

2.1.1 什么是分区表

把一个大的数据集根据业务需要分割成小的数据集,分文件夹存储。通过查询指定文件夹可以减少数据的扫描 可以大大提高查询效率

2.1.2 怎么建立分区表

create table dept_partition(
deptno int, dname string, loc string)
-- 分区字段不能是表中存在的 可以在将分区字段看成表的伪列
partitioned by (day string)
row format delimited fields terminated by '\t';

2.1.3 分区表中加载数据必须指定分区

方式1 查询结果插入(插入时必须指定分区字段和字段名)

insert overwrite table dept_partition partition(day='2020-06-24') select id,uploader,relatedids from video;

方式2 从文件中加载

hive (default)> load data local inpath
'/opt/module/ dept.log' into table dept_partition
partition(day ='2020-06-24')

2.1.4 查询分区数据

select * from dept_partition where day='2020-06-24

在这里插入图片描述

2.1.5分区表中加载数据必须指定分区

增加减少分区

alter table dept_partition add partition(day='20200404')
-- 增加多个分区
alter table dept_partition add partition(day='20200404') partition(day='20200406')

-- 减少分区
alter table dept_partition drop partition (day='3434')
-- 删除多个分区
alter table dept_partition drop partition(day='20200404'),partition(day='20200405')

查看表由多少个分区

 show partitions dept_partition;

2.1.6 如果一天数据量过大可建立二级分区

创建二级分区表

create table dept_partition2(
deptno int,
dname string,
loc string)
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';

加载数据

load data local inpath
'/opt/module data/dept _20200401.log ' into table
dept_partition2 partition(day ='20200401',hour='12')

2.1.7 动态分区–对表进行insert时根据分区字段的不同的值将数据插入的不同分区中

1.和动态分区有关的参数

(1)开启动态分区功能(默认true 开启)
	set hive.exec.dynamic.partition=true
(2)必须改===设置为非严格模式（动态分区的模式，默认 strict ,表示必须指定至少一个分区为静态分区,
   nonstrict 模式表示允许所有的分区字段都可以使用动态分区) 
	set hive.exec.dynamic.partition.mode=nonstrict
(3)在所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
	set hive.exec.max.dynamic.partitions=1000
(4)在每个执行 MR 的节点上，最大可以创建多少个动态分区。该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即day字段有365个值，那么该参数就需要设置成大于365 ，如果使用默认值100 ，则会报错。
	set hive.exec.max.dynamic.partitions.pernode=100
(5)整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
	set hive.exec.max.created.files=100000
(6)当有空分区生成时，是否抛出异常。一般不需 要设置。默认 false
	set hive.error.on.empty.partition=false

案例
- 需求:将dept表中的数据按照地区 loc字段插入到目标表 dept_partition的相应分
  区中。

(1) 创建目标分区表
	 create table dept_partition _dy (id int, name string)
partitioned by ( loc int) row format delimited fields terminated by ' t';
(2)设置动态分区
	 set hive.exec.dynamic.partition.mode = nonstrict;
	insert into table dept_partition _dy partition( loc ) select
deptno, dname, loc from dept;
（3) 查看目标分区表的分区情况
	 show partitions dept_partition;

2.2 建分桶表

2.2.1 什么是分桶表?

分桶可以理解为MapReduce中的HashPartitioner的原理。都是基于hash值对数据进行分桶。MR：按照key的hash值除以reduceTask个数进行取余(reduce_id = key.hashcode % reduce.num)
Hive:按照分桶字段(列)的hash值除以分桶的个数进行取余(bucket_id = column.hashcode % bucket.num)

2.2.2 怎么创建分桶表

开启分桶

set hive.enforce.bucketing = true;   开启强制分桶

数据准备

-- 数据
1010 ss10
1011 ss11
1012 ss12
1002 ss2
1003 ss3
1004 ss4
1005 ss5
1013 ss13
1016 ss16
1001 ss1
1006 ss6
1007 ss7
1008 ss8
1014 ss14
1015 ss15
1009 ss9

建立分桶表

create table test_bucket (
id int comment 'ID', 
name string comment '名字'
)
comment '测试分桶'
clustered by(id) into 4 buckets
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ';

加载数据
- 错误做法

直接load data不会有分桶的效果，这样和不分桶一样，
在HDFS上只有一个文件。
load data local inpath '/opt/module/hive/testdata/testbucket' into table text_bucket_test;

正确做法

--需要借助中间表 
create table text_bucket_test (
id int comment 'ID', 
name string comment '名字'
)
comment '测试分桶中间表'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ' ' ;

-- 先将数据load到中间表
load data local inpath '/opt/module/hive/testdata/testbucket' into table text_bucket_test;

--然后通过下面的语句，将中间表的数据插入到分桶表中，这样会产生四个文件。
insert overwrite table test_bucket select * from text_bucket_test;

在这里插入图片描述

插入时也可以排序再插入

因此，如果分桶和sort字段是同一个时，此时，
cluster by = distribute by 分桶字段 + sort by
insert overwrite table test_bucket select * from text_bucket_test cluster by id;

2.2.3 怎么创建分桶表

在这里插入图片描述

2.2.4 分桶表的好处

1.提高抽样效率
- 按分桶字段取样时，因为分桶表是直接去对应的桶中拿数据，在表比较大时会提高取样效率


select id,name from test_bucket tablesample(bucket 1 out of 2);
tablesample(bucket x out of y);
x:表示从第几个桶开始抽
y:表示抽几个桶,根据y的大小，决定抽样的比例 总桶数为4   (bucket 1 out of 1) 从第一个桶开始抽  总共抽4/1=4 抽4个桶

2.提高表的join效率
- 连接两个在（包含连接列的）相同列上划分了桶的表，可以使用 Map 端连接（Map-side join）高效的实现。比如JOIN操作。对于JOIN操作两个表有一个相同的列，如果对这两个表都进行了桶操作。那么将保存相同列值的桶进行JOIN操作就可以，可以大大较少JOIN的数据量。

在这里插入图片描述

2.3 合适的存储格式和压缩方式

2.3.1 Hive支持的存储格式

行:TEXTFILE(默认) , SEQUENCEFILE
列 :RCFile ORCFile,PARQUET

2.3.2 行存储格式

ORC存储格式如下

在这里插入图片描述

Parquet文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此Parquet格式文件是自解析的。
存储文件的压缩比总结：ORC > Parquet > textFile
存储文件的查询速度总结：查询速度相近。

2.3.3 合适的压缩方式

什么时候使用压缩？

1、计算密集型，不压缩，否则进一步增加了CPU的负担
2、IO或者网络密集型，推荐压缩，减小网络数据传输

压缩算法从哪几个方面考量的？
- 压缩率
- 解压缩速度
- 是否可拆分(是否可拆分主要在于拆分后的数据有没有元数据)

在这里插入图片描述

3.HQL语法优化

3.1 列裁剪和分区裁剪

列裁剪:只查询需要的列,Hive在读数据的时候，可以只读取查询中所需要用到的列，而忽略其他的列。这样做可以节省读取开销：中间表存储开销和数据整合开销。**不要select ***
分区裁剪: 查询的时候带上分区尽量不要全分区扫描

3.2 group by 优化

在这里插入图片描述

问题:默认情况下 map端所有相同的key会进入到同一个reduce中,若其中某个key的数据量过大,会导致数据倾斜。
优化方式:并非所有的聚合都要在reduce端完成 ,可以开启map端聚合的一些设置
- 是否开启Map端聚和:set hive.map.aggr=true(默认是true)
- 在Map端进行聚和的条目数 : set hive.groupby.mapaggr.checkinterval = 100000
- 有数据倾斜的时候进行负载均衡（默认是 false )set hive.groupby.skewindata = true

set hive.groupby.skewindata 开启负载均衡后,生个两个MRjob
第一个预聚合:key+随机数将数据均分到不同的reduce中,进行部分聚合
第二个job 聚合: 在预聚合的基础上去掉随机数完成真正的聚合

3.3 vectorization

vectorization : 矢量计算的技术在存储为orc格式的文件，在计算类似 scan, filter, aggregation的时候 vectorization技术以设置批处理的增量大小为 1024 行单次来达到比单条记录单次获得更高的效率一次拉取更多的数据处理。

set hive.vectorized.execution.enabled =true;
set hive.vectorized.execution.reduce.enabled =true;

3.4 多重模式多个SQL查询类型一样

问题:多个相同模式的sql，导致表被扫描多次,例如以下一组sql


insert int t_ptn partition(city=A). select id,name,sex, age from student
where city= A;
insert int t_ptn partition(city=B). select id,name,sex, age from student
where city= B;
insert int t_ptn partition(city=c). select id,name,sex, age from student
where city= c;

优化: 以下写法是一次读取，多次插入，有些场景是从一张表读取数据后，
要多次利用。

from student
insert int t_ptn partition(city=A) select id,name,sex, age where city= A
insert int t_ptn partiti on(city=B) select id,name,sex, age where city= B

FROM (SELECT a.status, b.school, b.gender FROM status_updates a JOIN profiles b
ON (a.userid = b.userid and a.ds='2019-03-20' )) subq1
INSERT OVERWRITE TABLE gender_summary PARTITION(ds='2019-03-20')
SELECT subq1.gender, COUNT(1) GROUP BY subq1.gender
INSERT OVERWRITE TABLE school_summary PARTITION(ds='2019-03-20')
SELECT subq1.school, COUNT(1) GROUP BY subq1.school;

3.5 in/exists用 left semi join代替

left semi / anti join ( 左半连接）是 in/ not in /exists/not exists 子查询的一种更高效的实现,只取左表的字段，右边的字段只用来做判断，不会取出来。
- 特点:
  - 1.JOIN 子句中右边的表只能在 ON 子句中设置过滤条件，在 WHERE 子句、SELECT 子句或其他地方过滤都不行
  - 2.left semi join 从右边中找到符合条件会停止扫描。右表有重复值得情况下 left semi join 只产生一条
例子:

select * from   t1 where  t1.id not in (select id from t2);

select * from   t1 where  not exists (select 1 from t2 whre t2.id = t1.id);

优化后:

select * from   t1
left anti join t2 on t1.id = t2.id;

3.6 谓词下推

什么是谓词下推:将SQL语句中的where谓词逻辑都尽可能提前执行减少下游数据的处理


打开谓词下推优化属性 在谓词下推打开的情况下,①②执行计划相同。因为谓词默认为true 此时会将①转为②
hive (default)> set hive.optimize.ppd true ; 谓词下推， 默认是 true

① 先关联再过滤 
hive (default)> explain select o.id from bigtable b join bigtable o on
o.id = b.id where o.id <= 10;

② 先过滤 再关联
hive (default)> explain select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;

3.7 orderBy优化

order by 只能是在一个 reduce 进程中进行，所以如果对一个大数据集进行order by ，会导致一个
reduce 进程中处理的数据相当大，造成查询执行缓慢。

1、在最终结果上进行order by，不要在中间的大数据集上进行排序。如果最终结果较少，可以在一个reduce上进行排序时，那么就在最后的结果集上进行order by。
2、如果是取排序后的前N条数据，可以使用distribute by和sort by在各个reduce上进行排序后前N
条，然后再对各个reduce的结果集合合并后在一个reduce中全局排序，再取前N条，因为参与全局排序的order by的数据量最多是reduce个数 * N，所以执行效率会有很大提升

在Hive中，关于数据排序，提供了四种语法，一定要区分这四种排序的使用方式和适用场景。

1、order by：全局排序，缺陷是只能使用一个reduce
2、sort by：单机排序，单个reduce结果有序
3、cluster by：对同一字段分桶并排序，不能和sort by连用
4、distribute by + sort by：分桶，保证同一字段值只存在一个结果文件当中，结合sort by保证每
个reduceTask结果有序

Hive HQL 中的 order by 与其他 SQL 方言中的功能一样，就是将结果按某字段全局排序，这会导致所
有 map 端数据都进入一个 reducer 中，在数据量大时可能会长时间计算不完。
如果使用 sort by，那么还是会视情况启动多个 reducer 进行排序，并且保证每个 reducer 内局部有
序。为了控制map 端数据分配到 reducer 的 key，往往还要配合 distribute by 一同使用。如果不加
distribute by 的话，map 端数据就会随机分配到 reducer。

提供一种方式实现全局排序：两种方式：
1、建表导入数据准备


create table if not exists student(id int, name string, sex string, age int,
department string) row format delimited fields terminated by ",";
load data local inpath "/home/bigdata/students.txt" into table student;

2、第一种方式

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

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.8 Count Distinct优化

当要统计某一列去重数时，如果数据量很大，count(distinct) 就会非常慢，原因与 order by 类似，count(distinct) 逻辑只会有很少的 reducer 来处理。这时可以用 group by 来改写：

-- 先 group by 在 count
select count(1) from (
select age from student
where department >= "MA"
group by age
) t;

再来一个例子：优化前，一个普通的只使用一个reduceTask来进行count(distinct) 操作

-- 优化前（只有一个reduce，先去重再count负担比较大）：
select count(distinct id) from tablename;

优化后，但是这样写会启动两个MR job（单纯 distinct 只会启动一个），所以要确保数据量大到启动job 的 overhead 远小于计算耗时，才考虑这种方法。当数据集很小或者 key 的倾斜比较明显时，group by 还可能会比 distinct 慢。

-- 优化后（启动两个job，一个job负责子查询(可以有多个reduce)，另一个job负责count(1))：
select count(1) from (select distinct id from tablename) tmp;
select count(1) from (select id from tablename group by id) tmp; // 推荐使用这
种

select t.a, count(t.b) , sum(t.c) from t group by t.a;
select t.a, count(distinct t.b， t.c) from t group by t.a;

4 小文件合并

4.1.1 小文件优化

过多的小文件为什么会影响效率？
- 单个文件小于128M文件会启用一个maptask。如果过多的小文件,导致过多maptask启停,可能启停时间甚至大于处理任务的时间造成资源浪费。
hive中怎么优化?


## 默认 map输入端不合并
set hive.input.format=org.apache.hadoop.hive.ql.io.HiveInputFormat;
-- 设置CombineHiveInputFormat
## 1.1Map端输入、合并文件之后按照block的大小分割（默认）
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

## 1.2 合并文件的大小,默认值为256000000(256M)
set hive.merge.size.per.task=256000000;

## 2. 在Map-only的任务结束时合并小文件 默认值为true 
set hive.merge.mapfiles=true;

## 3.是否合并Reduce端输出文件,默认值为false
set hive.merge.mapredfiles=true;
## 4 每个Map 最大分割大小
set mapred.max.split.size=256000000;

## 5 1一个节点上split的最少值
set mapred.min.split.size.per.node=1; // 服务器节点
## 5.2一个机架上split的最少值
set mapred.min.split.size.per.rack=1; // 服务器机架
  
1、默认情况先把这个节点上的所有数据进行合并，如果合并的那个文件的大小超过了256M就开启另外一个文
件继续合并
2、如果当前这个节点上的数据不足256M，那么就都合并成一个逻辑切片。

5.HiveJob优化

5.1 Map端

5.1.1 设置maptask数量

1.maptask数量不合适会产生什么问题?
- 过多:map 阶段输出文件太小产生大量小文件初始化和创建map的开销很大
- 过少:job执行时间长大量作业时容易拥堵集群
2.怎么调整MapTask 的数量 ?
- (1) 输入数据是大文件: 设置 blockSize minSize maxSize的大小,来调整 mapTask的并行度。最终调整的结果最好是blcokSize的整数倍

 maptask的数量是由输入分片 inputSplit决定的,而输入分片由 FileInputFormat.getSplit()决定 一个输入分片对应一个 mapTask 而输入分片由以下三个参数决定:
// 取这三个参数的中间值
long splitSize = 
Math.max(minSize, Math.min(maxSize, blockSize))

-- HDFS默认数据块大小
set dfs.blocksize =128M 
-- 最小分片大小(MR)
set mapreduce.input.fileinputformat.split.minsize =1  
-- 最大分片大小(MR)      
set mapreduce.input.fileinputformat.split.maxsize =256M

(2) 输入小文件合并 : 小文件合并( 见小文件合并 )
(3) 输入是上一个job的输出 :见小文件合并

5.2 reduce端

5.2.1 设置reduce个数

默认 reduceTask的个数:通过以下两个参数控制(总数据量)


reduceTask个数=总数据量/每个Reduce的默认处理的数据量
-- 每个reduce 默认处理的数据量
hive.exec.reducers.bytes.per.reducer (默认256M)
-- 最大reduce个数 
hive.exec.reducers.max (默认为最大1009)

自定义reduceTask个数:没有orderBy


mapreduce.job.reduces (默认值为-1，表示没有设置，那么就按照以上两个参数进行设置 如果有则按照自己定义的值)

6. join优化

6.1 先过滤再Join

尽量减少每个阶段的数据量，对于分区表能用上分区字段的尽量使用，同时只选择后面需要使用到的列，最大限度的减少参与 Join 的数据量。

6.2 大小表连接(mapJoin)

什么是mapJoin:在map端完成join操作减少后续的shuffer过程
原理：MapJoin 是将 Join 双方比较小的表直接分发到各个 Map 进程的内存中在 Map 进
程中进行 Join 操作这样就不用进行 Reduce 步骤从而提高了速度。
怎么使用：

方法一：

在Hive0.11前，必须使用MAPJOIN来标记显示地启动该优化操作，由于其需要将小表加载进内存所以要注意小表的大小

SELECT/*+ MAPJOIN(smalltable)*/.key,valueFROMsmalltableJOINbigtableONsmalltable.key=bigtable.key

select /*+ mapjoin(b1,b2)*/
b.*
from
(
select /*+ mapjoin(a1,a2,a3,a4,a5,a6,a7,a8)*/
 a.*
from a
join a1 on a.id1=a1.id1
join a2 on a.id2=a2.id2
join a3 on a.id3=a3.id3
join a4 on a.id4=a4.id4
join a5 on a.id5=a5.id5
join a6 on a.id6=a6.id6
join a7 on a.id7=a7.id7
join a8 on a.id8=a8.id8) b
join b1 on b.id11=b1.id11
join b2 on b.id11=b2.id12


方法二：

在Hive0.11后，Hive默认启动该优化，也就是不在需要显示的使用MAPJOIN标记，其会在必要的时候触发该优化操作将普通JOIN转换成MapJoin，可以通过以下两个属性来设置该优化的触发时机
//默认值为true，自动开户MAPJOIN优化
hive.auto.convert.join
//默认值为25000000(25M),通过配置该属性来确定使用该优化的表的大小，最多调节到2G
//如果表的大小小于此值就会被加载进内存中
hive.mapjoin.smalltable.filesize

注意：使用默认启动该优化的方式如果出现默名奇妙的BUG(比如MAPJOIN并不起作用),就将以下两个属性置为fase手动使用MAPJOIN标记来启动该优化

hive.auto.convert.join=false(关闭自动MAPJOIN转换操作)

hive.ignore.mapjoin.hint=false(不忽略MAPJOIN标记)

6.3 小表join大表原则

小表 join 大表的时应遵守小表 join 大表原则，原因是 join 操作的 reduce 阶段，位于 join 左边
的表内容会被加载进内存，将条目少的表放在左边，可以有效减少发生内存溢出的几率。join 中执行顺序是从左到右生成 Job，应该保证连续查询中的表的大小从左到右是依次增加的。

6.4 使用相同的连接键

在 hive 中，当对 3 个或更多张表进行 join 时，如果 on 条件使用相同字段，那么它们会合并为一个MapReduce Job，利用这种特性，可以将相同的 join on 放入一个 job 来节省执行时间。

6.5 大表join大表

1.空key过滤:有时join超时是因为某些key对应的数据太多，而相同key对应的数据都会发送到相同的reducer上，从而导致内存不够。此时我们应该仔细分析这些异常的key，很多情况下，这些key对应的数据是异常数据，我们需要在SQL语句中进行过滤。
2.空key转换:有时虽然某个key为空对应的数据很多，但是相应的数据不是异常数据，必须要包含在join的结果中，此时我们可以表a中key为空的字段赋一个随机的值，使得数据随机均匀地分不到不同的reducer上

-- 空值随机打散到不同的reduce中 
INSERT OVERWRITE TABLE daily_delete_after_20210810_txt PARTITION (log_date = '20210701')
select
  `mid,
  name,
  follow_list
from
  (
    select
      mid,
      name
    from
     t1
    where
      log_date = '20210701'      
  ) c
  left join (
    select
      mid,follow_list
    from
    t2
    where
      log_date = '20210630'
      -- 空key转化
  ) d  ON if((c.mid ='' or c.mid ='0' or c.mid is null),rand(),c.mid) = d.mid;

7.数据倾斜

7.1 什么是数据倾斜

按照key分组后少量任务负责绝大部分数据的计算

7.2 从HQL角度分析什么情况下可能会引起数据倾斜

单表有group by 字段
多表join

7.3 单表数据倾斜优化

单key数据倾斜。当任务中存在GroupBy操作同时聚合函数为 count或者 sum可以设置参数，来处理数据倾斜问题。

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


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

有数据倾斜的时候进行负载均衡（默认是false) 
(1)生成两个job 第一个job 先给key+随机数 ===》分布到不同的reduce上聚合
(2)第二个jo上===》去掉随机数===》聚合

只能对单个字段聚合 
set hive.groupby.skewindata = true;

多个 Key同时导致数据倾斜
- 增加 Reduce数量

7.4 join数据倾斜优化

了解key的分布是否含有大量异常的key(key is null or key =’’) ,如果含有且结果不使用则提前过滤掉。如果含有且空key为有效列,则给key起随机数均发到下游所有的reduce上。避免数据倾斜。

INSERT OVERWRITE TABLE daily_delete_after_20210810_txt PARTITION (log_date = '20210701')
select
  `mid,
  name,
  follow_list
from
  (
    select
      mid,
      name
    from
     t1
    where
      log_date = '20210701'      
  ) c
  left join (
    select
      mid,follow_list
    from
    t2
    where
      log_date = '20210630'
      -- 空key转化
  ) d  ON if((c.mid ='' or c.mid ='0' or c.mid is null),rand(),c.mid) = d.mid;

2.在编写 Join 查询语句时，如果确定是由于 join 出现的数据倾斜，那么请做如下设置

# join的键对应的记录条数超过这个值则会进行分拆，值根据具体数据量设置
set hive.skewjoin.key=10 0000;
# 如果是join过程出现倾斜应该设置为true
set hive.optimize.skewjoin=false;
# 如果开启了skewjoin，在 Join 过程中 Hive 会将超过阈值的key(hive.skewjoin.key=10 0000)的 key对应的对应那一行数据写进临时文件中,再启动另一个 job 做 mapJoin 。
# 通过hive.skewjoin.mapjoin.map.tasks 参数还可以控制第二个 job 的 mapper 数量，默认10000。

8 Hive架构层面

8.1本地抓取

8.1.1 什么是本地抓取模式

HQL不转为MapReduce ,直接从hdfs上拉取数据。

8.1.2 有什么用？

有些HQL可以不必要转为MR 执行直接拉取数据效率更高

8.1.3 怎么查看和开启本地抓取模式

## 查看
set hive.fetch.task.conversion;
## 默认more:在 select、where 筛选、limit 时，都启用直接抓取方式。
## 默认none:关闭抓取模式


set hive.fetch.task.conversion=more;

8.1.4设置后哪些sql 可以不用执行mr

1、只是 select * 的时候
2、where 条件针对分区字段进行筛选过滤时
3、带有 limit 分支语句时

8.2 本地执行优化

当数据量较小的时候没必要使用集群单机模式即可

## 打开hive自动判断是否启动本地模式的开关
set hive.exec.mode.local.auto=true;
## map任务数最大值，不启用本地模式的task最大个数
set hive.exec.mode.local.auto.input.files.max=4;
## map输入文件最大大小，不启动本地模式的最大输入文件大小
set hive.exec.mode.local.auto.inputbytes.max=134217728;

8.3 jvm重用

Hive 语句最终会转换为一系列的 MapReduce 任务，每一个MapReduce 任务是由一系列的 MapTask和 ReduceTask 组成的，默认情况下，MapReduce 中一个 MapTask 或者 ReduceTask 就会启动一个JVM 进程，一个 Task 执行完毕后，JVM 进程就会退出。这样如果任务花费时间很短，又要多次启动JVM 的情况下，JVM 的启动时间会变成一个比较大的消耗，这时，可以通过重用 JVM 来解决。

-- 工作中使用参数是10 
set mapred.job.reuse.jvm.num.tasks=5;

8.4 推测执行

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

# 启动mapper阶段的推测执行机制
set mapreduce.map.speculative=true;
# 启动reducer阶段的推测执行机制
set mapreduce.reduce.speculative=true;

8.5 并行执行

有的查询语句，Hive 会将其转化为一个或多个阶段，包括：MapReduce 阶段、抽样阶段、合并阶段、limit 阶段等。默认情况下，一次只执行一个阶段。但是，如果某些阶段不是互相依赖，是可以并行执行的。多阶段并行是比较耗系统资源的。但是如果集群资源匮乏时，启用并行化反倒是会导致各个 Job 相互抢占资源而导致整体执行性能的下降。

## 可以开启并发执行。
set hive.exec.parallel=true;
## 同一个sql允许最大并行度，默认为8。
set hive.exec.parallel.thread.number=16;

原文链接：https://blog.csdn.net/LIANGFANGWEI/article/details/118635185

文章目录

1. 查看执行计划

1.1 执行计划有什么用:

1.2 怎么查询执行计划

1.3 简单示例

2.建表优化

2.1 建分区表

2.1.1 什么是分区表

2.1.2 怎么建立分区表

2.1.3 分区表中加载数据必须指定分区

2.1.4 查询分区数据

2.1.5分区表中加载数据必须指定分区

2.1.6 如果一天数据量过大可建立二级分区

2.1.7 动态分区–对表进行insert时 根据分区字段的不同的值将数据插入的不同分区中

2.2 建分桶表

2.2.1 什么是分桶表?

2.2.2 怎么创建分桶表

2.2.3 怎么创建分桶表

2.2.4 分桶表的好处

2.3 合适的存储格式和压缩方式

2.3.1 Hive支持的存储格式

2.3.2 行存储格式

2.3.3 合适的压缩方式

3.HQL语法优化

3.1 列裁剪和分区裁剪

3.2 group by 优化

3.3 vectorization

3.4 多重模式 多个SQL查询类型一样

3.5 in/exists用 left semi join代替

3.6 谓词下推

3.7 orderBy优化

3.8 Count Distinct优化

4 小文件合并

4.1.1 小文件优化

5.HiveJob优化

5.1 Map端

5.1.1 设置maptask数量

5.2 reduce端

5.2.1 设置reduce个数

6. join优化

6.1 先过滤再Join

6.2 大小表连接(mapJoin)

6.3 小表join大表原则

6.4 使用相同的连接键

6.5 大表join大表

7.数据倾斜

7.1 什么是数据倾斜

7.2 从HQL角度分析什么情况下可能会引起数据倾斜

7.3 单表数据倾斜优化

7.4 join数据倾斜优化

8 Hive架构层面

8.1本地抓取

8.1.1 什么是本地抓取模式

8.1.2 有什么用？

8.1.3 怎么查看和开启本地抓取模式

8.1.4设置后哪些sql 可以不用执行mr

8.2 本地执行优化

8.3 jvm重用

8.4 推测执行

8.5 并行执行

2.1.7 动态分区–对表进行insert时根据分区字段的不同的值将数据插入的不同分区中

3.4 多重模式多个SQL查询类型一样