【Hive】

一、Hive是什么

• Hive是一款建立在Hadoop之上的开源数据仓库系统，将Hadoop文件中的结构化、半结构化数据文件映射成一张数据库表，同时提供了一种类SQL语言（HQL），用于访问和分析存在Hadoop中的大型数据集。
• Hive的核心是将HQL转换成MapReduce程序，然后将其提交到Hadoop集群执行。（用户只需要编写HQL而不需要编写MapReduce程序，减少了学习成本、开发成本。）
• Hive利用HDFS存储数据，利用MapReduce查询分析数据（就可以将Hive理解成一个Hadoop的客户端）
• Hive能将数据文件映射成一张表，能将SQL编译成为MapReduce然后处理这个表
• Hive的底层是用Java语言开发的
• 小数据集使用Hive分析，延迟很高。大数据集使用Hive分析，底层使用MapReduce分布式计算，速度才快。因此Hive是使用在大数据的场景下。

二、Hive的架构图

• hive能够写SQL的前提是针对一张表，而不是文件，因此要将文件和表之间的对应关系记录清楚。这个关系称为元数据信息

元数据信息记录:

1. 表对应的什么文件（对应文件的位置）
2. 表的每列对应文件的哪个字段，是什么类型（字段顺序，字段类型）
3. 文件中各字段的分隔符是什么

Hive工作流程 ：

Hive架构图：

• Metastore元数据服务： 通常用mysql/derby等（关系型数据库）来存储表和文件的映射关系。Metastore服务用来管理metadata元数据，外部只能通过Metastore服务访问元数据的数据库。
• Driver驱动程序： Hive的核心，包括语法解析器、计划编译器、优化器、执行器
• 执行引擎： Hive不处理数据 ，而是由执行引擎处理，目前Hive支持MapReduece、Tez、Spark三种执行引擎。（Hive可以将SQL转换成MapReduce或Tez或Spark，默认是MapReduce）

三、Hive数据模型

Hive从数据模型上看与MySQL很相似，也有库、表、字段。
但是Hive只适合用来做海量数据的离线分析，Hive一般用于数仓，MySQL一般用于业务系统。

	Hive	MySQL
定位	数据仓库	数据库
使用场景	离线数据分析	业务数据事务处理
查询语言	HQL	SQL
数据存储	HDFS	Local FS
执行引擎	MR、Tez、Spark	Excutor
执行延迟	高	低
处理数据规模	大	小
常见操作	导入数据、查询	增删改查

• Hive中的数据粒度如下
  DataBase数据库、Table 表、Partition 分区、Bucket 分桶
• 底层存储：
  数据库存储：itcast数据库对应的存储路径是/user/hive/warehouse/itcast.db（创建数据库相当于创建了个文件夹）
  表存储：itcast数据库下t_user表对应的存储路径/user/hive/warehouse/itcast.db/t_user
  partitions分区：分区是指根据分区列（例如“日期day”）的值将表划分为不同分区，一个文件夹表示一个分区，分区列=分区值
  
  Buckets分桶：分桶是指根据表中字段（例如“编号ID”）的值，经过Hash计算规则将文件划分成指定的若干个小文件
  

四、Hive中的数据类型

基本数据类型：

类型	说明	定义
tinyint	1byte 有符号整数
smallint	2byte 有符号整数
int	4byte 有符号整数
bigint	8byte 有符号整数
boolean	布尔类型，true或者false
float	单精度浮点数
double	双精度浮点数
decimal	十进制精准数字类型
varchar	字符序列，需指定最大长度，最大长度的范围是[1,65535]	decimal(16,2)
string	字符串，无需指定最大长度	varchar(32)
timestamp	时间类型
binary	二进制数据

复杂数据类型：

类型	说明	定义	取值
array	数组是一组相同类型的值的集合	array	arr[0]
map	map是一组相同类型的键-值对集合	map<string, int>	map[‘key’]
struct	结构体由多个属性组成，每个属性都有自己的属性名和数据类型	struct<id:int, name:string>	struct.id

map：{“xiaohu”:1,“dahai”:2}
struct：{“name”:“xiaosong”,“age”:18,“weight”:80}

四、Hive SQL DDL建表语法（重点）

完整的建表语法：

关键字：

1. TEMPORARY：临时表，该表只在当前会话可见，会话结束，表会被删除。
2. EXTERNAL（重点）：外部表，不写的话则为内部表（管理表）。
3. data_type（重点）：指定数据类型
4. PARTITIONED BY（重点）：创建分区表
5. CLUSTERED BY … SORTED BY…INTO … BUCKETS（重点）：创建分桶表
6. ROW FORMAT（重点）：指定SERDE，SERDE是Serializer and Deserializer的简写。Hive使用SERDE序列化和反序列化每行数据。（即指定该数据的每一行应该如何解析）
7. STORED AS（重点）：指定文件存储格式，常用的文件格式有，textfile（默认值），sequence file，orc file、parquet file等等。
8. LOCATION：指定表所对应的HDFS路径，若不指定路径，其默认值为
   ${hive.metastore.warehouse.dir}/db_name.db/table_name
9. TBLPROPERTIES：用于配置表的一些KV键值对参数

不是用loaction的情况下创建表相当于创建了一个文件夹，具体的数据文件需要放在对应的文件夹下
也可以在建表语句中使用location关键字指定数据文件在hdfs上的位置
一个表对应的文件夹下可以放多个数据文件，会一起解析成一张表

导入导出数据语法（重点）：

• Load
  Load语句将文件导入到表中（类似于把数据文件put到表对应的文件夹中）
  

• Insert
  将查询结果插入表中（常用）：
  
  将给定Values插入表中：
  
  将查询结果写入目标路径：
  

• Load
  Load语句将文件导入到表中（类似于把数据文件put到表对应的文件夹中）

• Insert
  将查询结果插入表中（常用）：
  
  将给定Values插入表中：
  
  将查询结果写入目标路径：
  
• Export与Import
  Export语句可将表的数据和元数据一起导出到HDFS路径，Import语句可以将Export导出的数据导入Hive，导入时表的数据和元数据都会恢复。常用于在两个Hive之间迁移数据。

Export语句可将表的数据和元数据一起导出到HDFS路径，Import语句可以将Export导出的数据导入Hive，导入时表的数据和元数据都会恢复。常用于在两个Hive之间迁移数据。
  

4.1 内部表与外部表（关键字EXTERNAL）

建表时用external关键字指定的就是外部表，否则为内部表（Managed Table）
当删除内部表时，会从MetaStore中删除表的元数据，从HDFS中删除表的数据。
当删除外部表时，只会从MetaStore中删除表的元数据，不会删除HDFS中表的数据。
内部表、外部表与是否使用location指定路径没有关系，Location只是指定数据的存储位置，如不使用location指定路径默认存储在hdfs的/user/hive/warehouse下

4.2 指定Serde语法（关键字ROW FORMAT）

语法一：
DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE（即LazySimpleSerde）对每行数据进行序列化和反序列化。

语法二：
SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。

4.3 分区表（关键字PARTITIONED BY）

当Hive表对应的数据量大，文件多时，可以根据指定的字段（比如：日期、地域）对表进行分区，分区后使用where条件筛选分区字段时可以避免全表扫描（速度慢）。分区本质上是通过多个文件夹来管理各分区的数据文件。

例如下图的多重分区：

• HDFS中分区表的存储方式：

分区表中需要确保每个分区的数据文件是干净的，是和分区值所对应的，否则没有意义
分区表创建完，直接把数据文件移动到对应文件夹下是没用的，静态分区需要使用load data或者insert select进行加载
分区表使用 分区字段 = 分区值 来进行分区，这个字段不包含在数据文件中（伪列），select该表时可以当一个正常字段使用。

静态分区：
静态分区指的是分区的字段值是手动写死的

-- 创建分区表，指定两个分区字段，province和city
create table t_user_province_city (id int, name string, age int) 
partitioned by (province string,city string);

-- 静态分区加载数据（静态分区指的是province和city手工指定）
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city partiton(province='zhejiang',city='hangzhou');
load data local inpath '/root/hivedata/user.txt' into table t_user_province_city partiton(province='zhejiang',city='ningbo');
insert overwrite table t_user_province_city partiton(province='zhejiang',city='jiaxing') select ... from ...;

-- 使用分区表(不用全表扫描，直接找到对应分区下的文件)
select * from t_user_province_city where province='zhejiang' and city='hangzhou'

动态分区：
动态分区指的是分区的字段值是基于查询结果的最后一个值动态决定的的。可以实现用一个insert语句将数据写入不同的分区。
核心语法是insert+select

静态分区，使用insert语句导入数据，partition中需指定分区值，select字段中不包含分区字段
动态分区，使用insert语句导入数据，partition中只指定分区字段，select字段中需要包含分区字段

-- 创建分区表，指定两个分区字段，province和city
create table t_user_province_city (id int, name string, age int) 
partitioned by (province string,city string);

-- 动态分区(province和city并没有手动指定,而是从select中查出来的province_tmp和city_tmp)
insert into table t_user_province_city partion(province string,city string)
select tmp.*,tmp.province_tmp,tmp.city_tmp from t_user_province_city_tmp tmp

一、分区表不是建表的必要语法，是一种优化手段
二、分区字段不能是表中已有的字段
三、分区字段是虚拟字段，其数据并不存储在底层的文件中
四、分区字段值来自于手动指定（静态分区）或根据查询结果位置推断（动态分区）
五、Hive支持多重分区，可以在分区的基础上继续分区
六、分区表的分区信息也属于元数据，存在metastore对应的数据库中，如果元数据删除，即使hdfs上有文件也读不到数据。

分区表常用语法：

# 展示某张表所有的分区信息
show partitions 表名;
# 为某个表添加分区
alter table 表名 add partition(分区字段 = 分区值);
# 为某个表删除分区（是否删除数据取决于表是内部表还是外部表）
alter table 表名 drop partition(分区字段 = 分区值);
# msck(metastore check)命令修复分区元数据与HDFS分区路径不一致，（只会修改元数据，不会删除数据）
# add  增加HDFS路径存在但元数据缺失的分区（默认是add）
# drop 删除HDFS路径已删除但元数据存在的分区
# sync 同步HDFS路径和元数据分区信息，相当于同时执行add和drop
msck repair table 表名 [add/drop/sync partitions]

4.4 分桶表（关键字CLUSTERED BY … INTO 分桶数 BUCKETS）

• 分桶表对应的数据文件在底层会被分解为若干各独立的小文件（一个文件 —> n个文件，当某个分区数据量过大时，可以再进行分桶）
• CLUSTERED BY指定根据哪几个字段进行分桶（字段必须是表中已经存在的字段）
• into n buckets表示分成几桶（几个文件）
• 指定的字段如果字段一样，一定会分到一个桶中
• 一张表可以既是分桶表又是分区表
  

hadoop 3.x可以通过load或者insert向分桶表加载数据，3.x版本中的load会自动根据分桶字段进行分桶。
hadoop 2.x只能通过insert向分桶表加载数据，因此需要先将源数据加载到普通表中，再通过insert向分区表中插入数据。

分桶表的好处：

1.基于分桶字段查询时，减少全表扫描（对分桶字段再次计算哈希，找到对应的分桶编号，只查询那一个文件即可）
2.用分桶的字段join时可以提高mr程序效率，减少笛卡尔积数量

3.分桶表数据进行高效抽样（分桶后可以从每个桶中抽取一定比例的数据，可以保证数据更加的平均）

创建分桶表举例：

# 创建普通分桶表
create table itheima.t_usa_covid19_bucket(
	count_date string,
	country string,
	state string,
	fips int,
	cases int,
	deaths int
)
clustered by(state) into 5 buckets; -- 根据state分为5桶

# 创建分桶排序表，会对每一个分桶里的数据根据指定字段进行排序
create table itheima.t_usa_covid19_bucket(
	count_date string,
	country string,
	state string,
	fips int,
	cases int,
	deaths int
)
clustered by(state) 
sorted by (cases desc) into 5 buckets; -- 指定每个分桶内部根据cases降序排列

分桶表数据加载举例（Hadoop 2.x）：

-- step1:把源数据加载到普通hive表中
create table itheima.t_usa_covid19(
	count_date string,
	country string,
	state string,
	fips int,
	cases int,
	deaths int
)
row format delimited fields terminated by ",";

-- 将源数据上传到HDFS，t_usa_covid19表对应的目录下
hadoop fs -put us-covid-counties.dat /user/hive/warehouse/itheima.db/t_usa_covid19
-- step2:使用insert+select语法将数据加载到分桶表中（自动根据分桶字段分桶）
insert into t_usa_covid19_bucket select * from t_usa_covid19

五、其他DDL语句

查看所有表： SHOW TABLES [IN database_name] LIKE [‘identifier_with_wildcards’];
查看表信息： DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name
重命名表： ALTER TABLE table_name RENAME TO new_table_name
删除表： DROP TABLE [IF EXISTS] table_name;
清空表： TRUNCATE [TABLE] table_name（只能清空内部表，不能清空外部表的数据）

六、文件格式和压缩

文件格式和压缩没有关系，两者是独立的
压缩： 因为Hive底层是基于HDFS存储的，所以它所支持的压缩格式也就是HDFS支持的压缩格式，即Deflate、Gzip、bzip2、LZO、Snappy等。

压缩的两个使用场景：

1. 在Hive表的存储时使用压缩（对应Map的输入端使用压缩）（text file文件格式，手动压缩，读取数据会自动解压，导入数据也要手动压缩）
   （orc和parquet文件格式，如需压缩则在建表时直接指定压缩格式，读取和导入数据时都是自动压缩）
2. 在MapReduce计算过程中使用压缩：
   2.1 shuffle结束后的数据进行压缩（对应Mapper的输出端使用压缩）（通过设置参数来开启和指定压缩格式）
   2.2 两个MR之间的临时数据进行压缩（对应Reducer输出的数据进行压缩）（通过设置参数来开启和指定压缩格式）

文件格式： 建表时用stored as指定文件存储格式，常用的有text file（默认）、orc、parquet、sequence file等

列式存储和行式存储
行式存储： 按行查询数据效率比较高（只需要确定行的开头和行的长度）
列式存储： 按列查询数据效率比较高（只需要定位列的开头和列的长度）

text file和sequence file是行式存储，orc和parquet是列式存储
text file： 默认的存储格式，文本文件，一行对应表中的一行记录。（行式存储）
orc（常用）： 一种使用列式存储的文件格式

orc文件由Header、Body、Tail三部分组成。
Header内容为orc，用于表示文件类型。
Body由1个或多个stripe组成，每个stripe一般为HDFS中block的块大小，一个Stripe对应逻辑表的若干行，每个Stripe由Index Data、Row Data、Stripe Footer三部分组成。
Index Data：索引，默认每隔1w行做一个索引，每个索引记录第n万行的位置，和n万行的最大值、最小值等信息。
Row Data：存具体的数据，并对列进行编码，分成多个Stream存储。
Stripe Footer：存放的是各个Stream的位置以及列的编码信息。
Tail由File Footer和PostScript组成。

读orc文件： 先读最后一个字节获取PostScript长度，进而获取PostScript，读取到File Footer长度，进而获取FileFooter，解析到各个Stripe中。
向orc表中导入数据： orc文件的导入，不能直接load文本数据，必须load orc数据。如果想导入text file的数据，则需要先导入到一张text file格式的表，再用insert语句导入到orc格式的表。

建orc表语句： create table orc_table (column_specs)
stored as orc
tblproperties (property_name=property_value, …);

建表语句中tblproperties用于指定一些参数，orc格式支持参数如下：

参数	默认值	说明
orc.compress	zlib	压缩格式，默认不压缩，可选：uncompressed、snappy（常用）、gzip、lzo、brotli、lz4
orc.compress.size	262144	每个压缩块的大小，默认256KB
orc.stripe.size	67108864	每个stripe的大小，默认64MB
orc.row.index.stride	10000	索引步长（每隔多少行数据建一条索引）

parquet： 一种使用列式存储的文件格式

parquet文件首尾都是par1，标志该文件时parquet格式文件，首尾中间由若干个Row Group和一个Footer组成。每个Row Group包含多个Column Chunk，每个Column Chunk中包含多个Page。
行组（Row Group）： 一个行组对应逻辑表中的若干行。
列块（Column Chunk）： 一个行组中的一列保存在一个列块中。
页（Page）：一个列块划分成若干个页。
Footer中存储了每个行组中每个列块的元数据信息

建parquet表语句： create table orc_table (column_specs)
stored as parquet
tblproperties (property_name=property_value, …);

parquet格式支持的参数如下：

参数	默认值	说明
parquet.compression	uncompressed	压缩格式，默认不压缩，可选：uncompressed、snappy（常用）、gzip、lzo、brotli、lz4
parquet.block.size	134217728	行组大小，默认128MB
parquet.page.size	1048576	页大小，默认1MB，压缩是按页进行压缩的

七、Hive中的函数

7.1 四种排序order by、sort by、distribute by、cluster by

• order by： 全局排序，默认升序，要汇总数据才能排序，因此只有一个reduce，排序效率低（一般都会加上limit，limit可以在数据进入到reduce之前就减少一部分数据）
• sort by： 每个Reduce内部排序，即只能保证每个分区内部是有序的（局部有序），无法取代order by
• distribute by： 指定分区字段，即MapReduce的Shuffle中使用哪个字段进行分区（通过key的hashcode对maptask数量取模确定分区）
• cluster by： 分区且排序，对某个字段分组且排序，并且只能升序（当sort by和distribute by使用同一个字段时，则可以简化成cluster by）

举例：

select * from emp distribute by deptno sort by sal desc;
MapReduce中根据deptno分区，每个分区中根据sal排序

语句1：select * from emp distribute by deptno sort by deptno;
语句1：select * from emp cluster by deptno;
语句1 等价于 语句2

7.2 各种函数

常用函数：

round()：四舍五入，round(3.3) = 3
ceil()：向上取整，ceil(3.3) = 4
floor()：向下取整，floor(3.3) = 3
substring()：截取字符串
replace()：替换字符串
regexp_replace()：根据正则表达式替换字符串
regexp：类似于like的用法，只不过这里是用正则判断
repeat()：重复字符串n遍
split()：字符串切割，返回array
nvl(A,B)：A的值不为null，则返回A，否则返回B
concat()：拼接多个字符串
concat_ws()：使用分隔符拼接多个字符串
get_json_object()：解析json字符串
unix_timestamp()：返回当前或指定时间的时间戳（零时区）
from_unixtime()：转化unix时间戳到当前时区的时间格式（零时区）
current_date：当前日期
current_timestamp：当前日期加时间（当前时区）
year()、month()、day()：获取日期中的年、月、日
datediff()：返回两个日期相差的天数
date_add()：返回日期增加n天后的日期
date_sub()：返回日期减去n天后的日期
date_format()：将标准日期解析成指定格式字符串

集合函数：

size()：返回集合中元素的个数
array()：创建array集合（数组）
array_contains()：判断array中是否包含某个元素
sort_array()：将array中的元素排序
map()：创建map集合
map_keys()：返回map所有的key
map_values()：返回map所有的value
struct()：构造struct类（只传字段值）
named_struct()：构造struct类（传字段名和字段值）

聚合函数：
多行变一行，sum，count，avg等简单的函数就不写了

collect_list()：返回某一列的值的array集合，结果不去重
collect_set()：返回某一列的值的array集合，结果去重

UDTF（Table-Generating Functions）函数（炸裂函数）：
UDTF函数接收一行数据，输出一行/多行数据

explode()：拆开array数组（多行1列）、map数组（多行2列）
posexplode()：拆开array数组（多行2列，其中一列是位置索引）
inline()：拆开包含多个struct的array数组（多行多列，字段名是struct的中的key）

Lateral View通常与UDTF配合使用
Lateral View将UDTF应用到源表的每行数据，源表的每行输出结果与该行链接起来，形成一个虚拟表。
举例：
将person lateral view explode(hobbies) tmp as hobby看作是一张表

窗口函数（开窗函数）：
窗口函数执行后行数不变，用于对某个窗口范围内进行计算。
完整语法如下：

#partition by 用于指定分区，每个分区单独划分窗口
#order by 用于排序
#rows|range between ... and ... 用于指定窗口范围
开窗函数 over(partition by 分区字段 order by 排序字段 rows|range between ... and ...)

指定窗口范围	含义
unbounded preceding	首行
[num] preceding	前num行
current row	当前行
[num] following	后num行
unbounded following	最后一行

指定计算的窗口范围有两种，基于行、基于值。

基于行的窗口范围（rows between … and …）：
这里的order by用于确定表中数据的顺序，这样才能确定窗口范围。

基于值的窗口范围（range between … and …）：
这里的order by用于指定根据哪一列的值来确定窗口范围。
基于值划分窗口范围时，如果用到[num] preceding或[num] following时，则要求前面order by的字段必须是整数。

写了order by，默认窗口范围 值 - 首行到当前行
不写order by，默认窗口范围 行 - 首行到最后一行

lag()：返回窗口中上n行的值，不支持自定义窗口
lead()：返回窗口中下n行的值，不支持自定义窗口
first_value()：返回窗口内第一个值
last_value()：返回窗口内第一个值
rank()：排序序号（1,1,3），不支持自定义窗口
dense_rank()：排序序号（1,1,2），不支持自定义窗口
row_number()：排序序号（1,2,3），不支持自定义窗口

自定义函数（不常用）：

自定义函数类型	说明
UDF（User-Defined Function）	一进一出。类似substr
UDAF（User-Defined Aggregation Function）	多进一出。类似sum/count
UDTF（User-Defined Table-Generating Function）	一进多出。类似explode

自定义函数步骤：

1. 编写自定义函数对应的Java代码，打成jar包并上传到服务器上
2. 【创建临时函数前置步骤】使用add jar xxx.jar的路径将对应的jar包添加到hive的classpath
3. 【创建临时函数】使用create temporary function 函数名 as 自定义函数java代码的全类名创建临时函数
4. 【创建永久函数】使用create function 函数名 as 自定义函数java代码的全类名 using jar "jar包所在的hdfs路径"创建永久函数

八、执行计划

语法： explain [formatted | extended | dependency] sql语句

formatted：将执行计划以JSON字符串的形式输出。
extended：输出执行计划中的额外信息，比如读写的文件名
dependency：输出执行计划读取的表及分区

• 执行计划由一系列Stage组成，每个Stage对应一个MapReduce Job（或一个文件系统操作）
• 每个MapReduce Job，Map端和Reduce端的计算逻辑分别由Map Operator Tree和Reduce Operator Tree进行描述，每个Operator Tree由多个Operator组成

常见的Operator作用：

TableScan：表扫描，通常是Map端的第一个操作
Select Operator：选取字段操作
Group By Operator：分组聚合操作
Reduce Output Operator：输出到reduce操作
Filter Operator：过滤操作（对应where/having）
Join Operator：join操作
File Output Operator：文件输出操作
Fetch Operator：客户端获取数据操作

九、调优（面试重点！！！）

9.1 资源配置

1. Yarn资源配置（调整整个集群中可以给MapReduce所使用的内存、cpu）

配置	说明
yarn.nodemanager.resource.memory-mb	一个NodeManager节点分配给Container使用的内存
yarn.nodemanager.resource.cpu-vcores	一个NodeManager节点分配给Container使用的CPU核数
yarn.scheduler.maximum-allocation-mb	单个Container能够使用的最大内存
yarn.scheduler.minimum-allocation-mb	单个Container能够使用的最小内存

2. MapReduce资源配置（调整MapTask、ReduceTask所使用的内存、cpu）

配置	说明
mapreduce.map.memory.mb	单个MapTask申请的Container容器内存大小
mapreduce.map.cpu.vcores	单个MapTask申请的Container容器CPU核数
mapreduce.reduce.memory.mb	单个ReduceTask申请的Container容器内存大小
mapreduce.reduce.cpu.vcores	单个ReduceTask申请的Container容器CPU核数

9.2 分组聚合（group by）的优化

Hive对其的优化主要是通过开启map-side聚合（默认开启）（类似于MapReduce中的Combiner），减少Shuffle的数据量。
map-side聚合原理： 在map端维护一个HashTable，利用其完成部分的聚合，然后将部分聚合的结果，按照分组字段分区，发送至reduce端，完成最终的聚合。

-- 启用map-side聚合
set hive.map.aggr=true;

-- 用于检测源表数据是否适合进行map-side聚合。检测方法：先对若干条数据进行map-side聚合，若聚合后条数/聚合前条数 小于该值，则认为该表适合map-side聚合，否则认为不适合map-side聚合。
set hive.map.aggr.hash.min.reduction=0.5;

-- 用于检测源表是否适合map-side聚合的条数
set hive.groupby.mapaggr.checkinterval=100000;

-- map-side聚合所用的hash table，占用map task堆内存的比例，若超过比例则对hash table进行一次flush
set hive.map.aggr.hash.force.flush.memory.threshold=0.9;

9.3 Join的优化（重点！！！）

Hive中的Join算法有Common Join、Map Join、Bucket Map Join、Sort Merge Bucket Map Join

• Common Join（对应Hadoop中的reduce join）
  Common Join是Hive中最稳定的join算法，其通过一个MapReduce Job完成一个join操作.
  

如上图，表x和表y进行join，则map端切片并读取表数据，按照关联字段分区，通过shuffle，发送到reduce端，相同key的数据在reduce端完成最终的join。

• Map Join（对应Hadoop中的map join）
  Map Join算法可以通过两个只有map阶段的Job完成一个join操作。(没有shuffle和reduce阶段)适用场景为大表join小表。

如上图，则第一个Job（Task A）会读取小表数据，将其制作为hash table，并上传至Hadoop分布式缓存（本质上是上传至HDFS）。第二个Job（Task C）会先从分布式缓存中读取小表数据，并缓存在Map Task的内存中，然后扫描大表数据，这样在map端即可完成关联操作。
总结： Map端读取小表数据存到内存中，在Map端直接完成join操作。

• Bucket Map Join
  Bucket Map Join是对Map Join算法的改进，可用于大表join大表的场景。
  Bucket Map Join的切片是一个Bucket一个切片。（一个MapTask处理一个Bucket）

使用条件：

1. 参与join的表均为分桶表
2. 两张表的关联字段为分桶字段
3. 其中一张表的分桶数量是另外一张表分桶数量的整数倍（能保证参与join的两张表的分桶之间具有明确的关联关系）（分桶规则：hash(分桶字段) 取模 分桶数量）

Bucket Map Join实际上是将大表通过分桶拆成多个小表，这样就可以将拆分后的小表缓存到内存中，进行Map Join。

• Sort Merge Bucket Map Join
  Sort Merge Bucket Map Join（简称SMB Map Join）基于Bucket Map Join。

使用条件（相比Bucket Map Join多了要求排序字段）：

1. 参与join的表均为排序分桶表，且排序字段为分桶字段
2. 两张表的关联字段为分桶字段（即关联字段=分桶字段=分桶排序字段）
3. 其中一张表的分桶数量是另外一张表分桶数量的整数倍（能保证参与join的两张表的分桶之间具有明确的关联关系）（分桶规则：hash(分桶字段) 取模 分桶数量）

Bucket Map Join，两个分桶之间的join实现原理为Hash Join算法；
SMB Map Join，两个分桶之间的join实现原理为Sort Merge Join算法。
Hash Join的原理： 对参与join的一张表构建hash table，然后扫描另外一张表，然后进行逐行匹配。
Sort Merge Join原理： 需要在两张按照关联字段排好序的表中进行，如图所示：

SMB Map Join与Bucket Map Join相比，在进行Join操作时，Map端是无需缓存整个Bucket数据的（因为已经排序好，不需要缓存整个bucket，只需要从上往下读到相等的则开始缓存，读到不等的就结束缓存），每个Mapper只需按顺序逐个key读取两个分桶的数据进行join即可。（由于不需要缓存整个bucket数据，因此SMB Map Join对内存要求更低）

9.3.1 Map Join优化

Hive在编译SQL语句阶段，起初所有的join操作均采用Common Join算法实现。
物理优化阶段，Hive会自动判断Common Join任务是否能够转换为Map Join任务。
Map join自动转换的具体判断逻辑如下图所示：

--启动Map Join自动转换（一般都设置true）
set hive.auto.convert.join=true;
--若该Common Join相关的表中,存在除大表外的表的已知大小总和<=该值,则生成一个Map Join计划（反之不生成）
--执行时优先执行Map Join计划，若不能执行成功，则执行Common Join后备计划。
set hive.mapjoin.smalltable.filesize=250000;
--开启无条件转Map Join（一般都设置true）
set hive.auto.convert.join.noconditionaltask=true;
--无条件转Map Join时的小表之和阈值，若n-1张表的大小之和小于该值，则直接生成一个最优的Map Join计划。（即选最大的表作为大表，其余表作为小表）
--注意这里和上面的filesize均是文件大小，而不是占用内存大小。
--文件占用内存的大小需要扩大10倍。（1G内存可以缓存100MB的文件）
set hive.auto.convert.join.noconditionaltask.size=10000000;```

9.3.2 Bucket Map Join优化

MR中Bucket Map Join不支持自动转换（Spark、Tez支持自动转换），需在SQL中提供Hint提示，并配置相关参数。
(explain中需要加上extended才能看到是否使用了Bucket Map Join)

1）hint提示（使用mapjoin指定小表）
select /*+ mapjoin(ta) */
ta.id,
tb.id
from table_a ta
join table_b tb on ta.id=tb.id;

2）相关参数

--关闭cbo优化，cbo会忽略hint信息
set hive.cbo.enable=false;
--如果为true，会忽略hint信息
set hive.ignore.mapjoin.hint=false;
--启用bucket map join优化功能
set hive.optimize.bucketmapjoin = true;

9.3.3 Sort Merge Bucket Map Join优化

MR中Bucket Map Join不支持自动转换（Spark、Tez支持自动转换），需在SQL中提供Hint提示，并配置相关参数。

--启动Sort Merge Bucket Map Join优化(总开关)
set hive.optimize.bucketmapjoin.sortedmerge=true;
--使用自动转换SMB Join
set hive.auto.convert.sortmerge.join=true;

9.4 数据倾斜优化（重点！！！）

数据倾斜： 参与计算的数据分布不均，某个key的数据量远超其他key，导致在shuffle阶段，大量相同的key被分到一个区，发往同一个reduce，进而导致该reduce所需时间远超其他reduce。
Hive中的数据倾斜常出现在两个场景：分组聚合、join操作

9.4.1 分组聚合的数据倾斜

分组聚合是按分组字段分区，当分组字段分布不均，会导致数据倾斜
Hive中不优化的分组聚合： 通过一个MapReduce Job实现，Map端读数据，按分组字段分区，通过shuffle将数据发送到Reduce端，在Reduce端完成最终的聚合。
解决方案1：
开启Map-Side聚合（具体方式看上面）（每个Map拿到的数据是一个切片，是均匀的）
解决方案2：
Skew-GroupBy优化，启动两个MR任务：
第一个MR按照随机数分区（保证这个MR中每个Reduce拿到的数据是均匀的），送到Reduce完成部分聚合。
第二个MR按照分组字段分区，完成最终聚合。

--启用分组聚合数据倾斜优化（默认false）
set hive.groupby.skewindata=true;

注意： 方案2要启动2个MapReduce，所以效率不如方案1，方案1直接Map端聚合，没有Shuffle阶段。
但是方案1要维护一个hash table，要耗费内存，方案2则不需要。（一般情况下方案1即可）

9.4.2 join操作的数据倾斜

common join是按关联字段分区，当关联字段分布不均，会导致数据倾斜
方案1：
使用map join（适用于大表join小表）
join操作在map阶段完成，没有shuffle和reduce，可以解决数据倾斜。
方案2：
使用skew join（适用于大表join大表，要求只有一张表的key倾斜）
skew join中倾斜的大key单独启动一个map join任务进行计算，其余key进行正常的common join

如下图：a表中K1是倾斜的，a表的K1和b表的K1会发往一个ruduce，将a表的k1和b表的k1写入HDFS，然后可以使用map join，将b表的k1缓存到内存中，在map阶段进行join。

--启用skew join优化
set hive.optimize.skewjoin=true;
--触发skew join的阈值，若某个key的行数超过该参数值，则触发，对大key进行map join
set hive.skewjoin.key=100000;

注意： 数据倾斜且为大表join大表时，无法使用bucket map join或smb map join解决，因为当数据倾斜时，根据关联字段分桶，关联字段相同的会被分到一个桶中，数据依然是倾斜的。

方案3
调整SQL语句
将倾斜的表关联字段通过concat拼接，手动打散，让其分到不同的reduce，将另一表的关联字段也通过concat拼接

-- 调整前
select * from A join B on A.id=B.id;

-- 调整后
select * from
--打散操作，rand()*2对应id后加"_0"或"_1"，对应分区到不同的2个区中
(select concat(id,'_',cast(rand()*2 as int)) id,value from A) ta
join
--扩容操作，由于上面的id字段进行了拼接，则这里数据也要拼接，才能关联
--若上面rand()*n则这里也要对应扩大n倍
(select concat(id,'_',0) id,value from B
union all
select concat(id,'_',1) id,value from B) tb
on ta.id=tb.id;

SQL语句调整前：

SQL语句调整后：
SQL语句调整后，1001的数据被分到了两个reduce中

数据倾斜解决方案总结：

数据倾斜	解决方案
group by分组字段倾斜	map-side聚合、开启Skew-GroupBy
join关联字段倾斜 大表join小表	map join
join关联字段倾斜 大表join大表 （且只有一个表中的关联字段倾斜）	skew join、调整SQL语句
join关联字段倾斜 大表join大表 （且两个表中的关联字段都倾斜）	无法解决（基本遇不到）

9.5 任务并行度

9.5.1 Map端并行度

MapTask的个数由输入文件的切片数决定，一般不用手动调整。
（1）表中存在大量小文件
当输入文件多个小文件时，由于切片是按文件的，可能会启动多个MapTask，则可以开启这个，将多个小文件合并到一个切片中，使用一个MapTask。

--hive是默认开启小文件处理的
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

（2）Map端有复杂的查询逻辑
若SQL中有正则、json解析等复杂逻辑时，可以考虑增加MapTask个数。（将切片调小）

--一个切片的最大值（默认大概256MB）
set mapreduce.input.fileinputformat.split.maxsize=256000000;

9.5.2 Reduce端并行度

ReduceTask的个数可以自行指定，不指定的话Hive会根据文件大小自己估算

--指定Reduce端并行度，默认值为-1，表示用户未指定
set mapreduce.job.reduces;
--Reduce端并行度最大值
set hive.exec.reducers.max;
--单个Reduce Task计算的数据量，用于估算Reduce并行度
set hive.exec.reducers.bytes.per.reducer;

Reduce端并行度的确定逻辑如下：

1. 若指定参数mapreduce.job.reduces的值为一个非负整数，则Reduce并行度> 为指定值。
2. 否则，Hive自行估算Reduce并行度，估算逻辑如下：
   假设Job输入的文件大小为totalInputBytes
   参数hive.exec.reducers.bytes.per.reducer的值为bytesPerReducer。
   参数hive.exec.reducers.max的值为maxReducers。

9.6 小文件合并

9.6.1 Map端输入文件合并

这里的合并指的是切片合并，而不是真正的文件合并

--可将多个小文件切片，合并为一个切片，进而由一个MapTask处理
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

9.6.2 Reduce端输出文件合并

目的是减少HDFS小文件数量，这里是对文件进行真正的合并
原理是根据计算任务输出文件的平均大小进行判断，若符合条件，则单独启动一个额外的任务进行合并。

Reduce输出的文件个数由ReduceTask的个数决定，有几个Reduce任务就会有几个输出文件

--开启合并map only任务输出的小文件
set hive.merge.mapfiles=true;
--开启合并map reduce任务输出的小文件
set hive.merge.mapredfiles=true;
--合并后的文件大小
set hive.merge.size.per.task=256000000;
--触发小文件合并任务的阈值，若某计算任务输出的文件平均大小低于该值，则触发合并
set hive.merge.smallfiles.avgsize=16000000;

9.7 其他优化（了解）

9.7.1 CBO优化

CBO是指Cost based Optimizer，即基于计算成本的优化。
在Hive中，计算成本模型考虑到了：数据的行数、CPU、本地IO、HDFS IO、网络IO等方面。
Hive会计算同一SQL语句的不同执行计划的计算成本，并选出成本最低的执行计划。
目前CBO在hive的MR引擎下主要用于join的优化，例如多表join的join顺序。

--是否启用cbo优化
set hive.cbo.enable=true;

9.7.2 谓词下推

谓词下推（predicate pushdown）是指，尽量将过滤操作前移，以减少后续计算步骤的数据量。

--是否启动谓词下推（predicate pushdown）优化
set hive.optimize.ppd = true;

CBO优化也会完成一部分的谓词下推优化工作，因为在执行计划中，谓词越靠前，整个计划的计算成本就会越低。

9.7.3 矢量化查询

Hive的矢量化查询优化，依赖于CPU的矢量化计算（矢量计算可以理解为两列一起计算）

若执行计划中，出现“Execution mode: vectorized”字样，即表明使用了矢量化计算。

9.7.4 Fetch抓取

Fetch抓取是指，Hive中对某些情况的查询可以不必使用MapReduce计算。
（例如：select * from emp;在这种情况下，Hive可以简单地读取emp对应的存储目录下的文件，然后输出查询结果到控制台。）

--是否在特定场景转换为fetch 任务
--设置为none表示不转换
--设置为minimal表示支持select *，分区字段过滤，Limit等
--设置为more表示支持select 任意字段,包括函数，过滤，和limit等（默认为more）
set hive.fetch.task.conversion=more;

9.7.5 本地模式

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

--开启自动转换为本地模式
set hive.exec.mode.local.auto=true;  
--设置local MapReduce的最大输入数据量，当输入数据量小于这个值时采用local  MapReduce的方式，默认为134217728，即128M
set hive.exec.mode.local.auto.inputbytes.max=50000000;
--设置local MapReduce的最大输入文件个数，当输入文件个数小于这个值时采用local MapReduce的方式，默认为4
set hive.exec.mode.local.auto.input.files.max=10;

9.7.6 并行执行

开启后可以并行执行多个不相互依赖的Stage

Hive会将一个SQL语句转化成一个或者多个Stage，每个Stage对应一个MR Job。
默认情况下，Hive同时只会执行一个Stage。某SQL语句可能会包含多个Stage，多个Stage可能并非完全互相依赖，即有些Stage是可以并行执行的。

--启用并行执行优化
set hive.exec.parallel=true;       
--同一个sql允许最大并行度，默认为8
set hive.exec.parallel.thread.number=8; 

9.7.7 严格模式

通过设置某些参数来防止危险操作；
（1）分区表不使用分区过滤
将hive.strict.checks.no.partition.filter设置为true时，对于分区表，除非where语句中含有分区字段过滤条件来限制范围，否则不允许执行。
（2）使用order by没有limit过滤
将hive.strict.checks.orderby.no.limit设置为true时，对于使用了order by语句的查询，要求必须使用limit语句。
（3）笛卡尔积
将hive.strict.checks.cartesian.product设置为true时，会限制笛卡尔积的查询。