【大数据之Hive】_hive尚硅谷

学习笔记，全部来源于尚硅谷，如有侵权请联系删除！

一、Hive基本概念

1. Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类 SQL 查询功能。
2. 本质：用于将HQL（Hive SQL）转化成MapReduce程序；；Hive中每张表的数据存储在HDFS；Hive分析数据底层的实现是MapReduce；执行程序运行在Yarn上
3. 架构原理
   
   1）用户接口：Client：CLI（command-line interface）、JDBC/ODBC。
   JDBC和ODBC的区别：JDBC的移植性比ODBC好，JDBC只需要选取适当的JDBC数据库驱动程序，就不需要额外的配置；两者使用的语言不同，JDBC在Java编程时使用，ODBC一般在C/C++编程时使用。
   2）元数据：Metastore；数据库（默认是default）、表名、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等
   默认存储在自带的derby数据库中，由于derby数据库只支持单客户端访问，生产环境中为了多人开发，推荐使用MySQL存储Metastore。
   3）驱动器：Driver**************
   （1）解析器（SQLParser）：将SQL字符串转换成抽象语法树（AST）
   （2）语义分析（Semantic Analyzer）：将AST进一步划分为QeuryBlock
   （3）逻辑计划生成器（Logical Plan Gen）：将语法树生成逻辑计划
   （4）逻辑优化器（Logical Optimizer）：对逻辑计划进行优化（谓词下推）
   （5）物理计划生成器（Physical Plan Gen）：根据优化后的逻辑计划生成物理计划
   （6）物理优化器（Physical Optimizer）：对物理计划进行优化（map join）
   （7）执行器（Execution）：执行该计划，得到查询结果并返回给客户端
   
   

二、环境部署

hive安装

1.下载地址：http://archive.apache.org/dist/hive/
2. 解压，添加环境变量，初始化元数据库：bin/schematool -dbType derby -initSchema，启动 bin/hive
3. 表的默认路径：/user/hive/warehouse/

mysql安装

1. 先卸载系统自带的mariadb：sudo rpm -qa | grep mariadb | xargs sudo rpm -e --nodeps
2. 安装依赖：
   sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
   sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
   sudo rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
   sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
   sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
3. 启动mysql：sudo systemctl start mysqld
4. 更改密码策略：mysql> set global validate_password_policy=0;mysql> set global validate_password_length=4;
5. 修改user表，把Host表内容修改为%：mysql> update user set host=“%” where user=“root”;刷新： flush privileges;

配置Hive元数据存储到MySQL

1. mysql新建元数据库：create database metastore;
2. 拷贝驱动到Hive/lib目录，在conf目录创建配置文件hive-site.xml;初始化hive元数据库，采用mysql存储。bin/schematool -dbType mysql -initSchema -verbose

Hiveserver2

1. Hiveserver2的hive.server2.enable.doAs参数决定，该参数的含义是是否启用Hiveserver2用户模拟的功能。若启用，则Hiveserver2会模拟成客户端的登录用户去访问Hadoop集群的数据，不启用，则Hivesever2会直接使用启动用户访问Hadoop集群数据。模拟用户的功能，默认是开启的
2. 部署：
   （1）Hadoop端配置：将hiveserver2的启动用户设置为Hadoop的代理用户，core-site.xml
   （2）Hive端配置hive-site.xml文件
   启动hiveserver2 bin/hive --service hiveserver2
   启动hiveserver2 并且加入后台运行：nohup bin/hiveserver2 >/dev/null 2>&1 &

metastore服务

1. metastore有两种运行模式
   （1）嵌入式模式
   
   （2）独立服务模式
   
   生产环境中，不推荐使用嵌入式模式。因为其存在以下两个问题：
   （1）嵌入式模式下，每个Hive CLI都需要直接连接元数据库，当Hive CLI较多时，数据库压力会比较大。
   （2）每个客户端都需要用户元数据库的读写权限，元数据库的安全得不到很好的保证。
   2. 配置独立模式：保证Hiveserver2和每个Hive CLI的配置文件hive-site.xml中包含访问metastore服务所需地址；启动metastore : nohup hive --service metastore &

常用命令

1. “-e”不进入hive的交互窗口执行hql语句：bin/hive -e “select id from student;”
2. “-f”执行脚本中的hql语句：bin/hive -f /opt/module/hive/datas/hivef.sql

参数配置

1. 查看当前所有的配置信息：set;
2. 参数配置方式：1）配置文件：用户自定义配置会覆盖默认配置。另外，Hive也会读入Hadoop的配置，因为Hive是作为Hadoop的客户端启动的，Hive的配置会覆盖Hadoop的配置。配置文件的设定对本机启动的所有Hive进程都有效；2）命令行：bin/hive -hiveconf mapreduce.job.reduces=10;本次启动进程有效；3）参数声明方式：hive(default)> set mapreduce.job.reduces=10;只对本次会话有效；优先级依次递增。即配置文件 < 命令行参数 < 参数声明

三 DDL 数据定义语言

数据库

1. 创建数据库：CREATE DATABASE [IF NOT EXISTS] database_name
   [COMMENT database_comment]
   [LOCATION hdfs_path]
   [WITH DBPROPERTIES (property_name=property_value, …)];
2. 查询数据库：SHOW DATABASES [LIKE ‘identifier_with_wildcards’];like通配表达式说明：*表示任意个任意字符，|表示或的关系。
3. 修改数据库
   –修改dbproperties
   ALTER DATABASE database_name SET DBPROPERTIES (property_name=property_value, …);

–修改location
ALTER DATABASE database_name SET LOCATION hdfs_path;
修改数据库location，不会改变当前已有表的路径信息，而只是改变后续创建的新表的默认的父目录。

–修改owner user
ALTER DATABASE database_name SET OWNER USER user_name;
4. 删除数据库：DROP DATABASE [IF EXISTS] database_name [RESTRICT|CASCADE];
RESTRICT：严格模式，若数据库不为空，则会删除失败，默认为该模式。
CASCADE：级联模式，若数据库不为空，则会将库中的表一并删除。

表

普通创建表：

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
[(col_name data_type [COMMENT col_comment], …)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], …)]
[CLUSTERED BY (col_name, col_name, …) [SORTED BY (col_name [ASC|DESC], …)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]
[TBLPROPERTIES (property_name=property_value, …)]
说明：
1）TEMPORARY临时表，该表只在当前会话可见；
2）EXTERNAL（重点）********
外部表，与之相对应的是内部表（管理表）。管理表意味着Hive会完全接管该表，包括元数据和HDFS中的数据。外部表只接管元数据，不完全接管HDFS数据 ；
3）data_type：字段类型可分为基本数据类型和复杂数据类型；
基本数据类型如下：
tinyint 1byte 有符号整数
smallint 2byte 有符号整数
int 4byte 有符号整数
bigint 8byte 有符号整数
boolean 布尔类型，true或者false
float 单精度浮点数
double 双精度浮点数
decimal 十进制精准数字类型 decimal(16,2)
varchar 字符序列，需指定最大长度，最大长度的范围是[1,65535] varchar(32)
string 字符串，无需指定最大长度
timestamp 时间类型
binary 二进制数据
复杂数据类型
array 数组是一组相同类型的值的集合 array arr[0]
map map是一组相同类型的键-值对集合 map<string, int> map[‘key’]
struct 结构体由多个属性组成，每个属性都有自己的属性名和数据类型 struct<id:int, name:string> struct.id
类型转换
Hive的基本数据类型可以做类型转换，转换的方式包括隐式转换以及显示转换。
方式一：隐式转换
a. 任何整数类型都可以隐式地转换为一个范围更广的类型，如tinyint可以转换成int，int可以转换成bigint。
b. 所有整数类型、float和string类型都可以隐式地转换成double。
c. tinyint、smallint、int都可以转换为float。
d. boolean类型不可以转换为任何其它的类型。
当两者不能转换的时候，会选择两个都能转换的另一个最小类型
参考文档：https://cwiki.apache.org/confluence/display/hive/languagemanual+types#LanguageManualTypes-AllowedImplicitConversions
方式二：显示转换
可以借助cast函数完成显示的类型转换
语法：
4）PARTITIONED BY（重点）创建分区表，比如一个日期一个表
5）CLUSTERED BY … SORTED BY…INTO … BUCKETS（重点）创建分桶表，类似hash分区操作
6) ROW FORMAT（重点）SERDE序列化和反序列化每行数据
语法一：DELIMITED关键字表示对文件中的每个字段按照特定分割符进行分割，其会使用默认的SERDE对每行数据进行序列化和反序列化。
ROW FORAMT DELIMITED
[FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
[NULL DEFINED AS char]
fields terminated by ：列分隔符
collection items terminated by ： map、struct和array中每个元素之间的分隔符
map keys terminated by ：map中的key与value的分隔符
lines terminated by ：行分隔符
NULL DEFINED AS：默认值：\N
语法二：SERDE关键字可用于指定其他内置的SERDE或者用户自定义的SERDE。例如JSON SERDE，可用于处理JSON字符串。
ROW FORMAT SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,property_name=property_value, …)]

7)STORED AS（重点）指定文件格式，（textfile（默认值））

2. Create Table As Select（CTAS）建表：
   该语法允许用户利用select查询语句返回的结果，直接建表，表的结构和查询语句的结构保持一致，且保证包含select查询语句放回的内容。
   CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table_name
   [COMMENT table_comment]
   [ROW FORMAT row_format]
   [STORED AS file_format]
   [LOCATION hdfs_path]
   [TBLPROPERTIES (property_name=property_value, …)]
   [AS select_statement]
3. Create Table Like语法建表：
   该语法允许用户复刻一张已经存在的表结构，与上述的CTAS语法不同，该语法创建出来的表中不包含数据。
   CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name
   [LIKE exist_table_name]
   [ROW FORMAT row_format]
   [STORED AS file_format]
   [LOCATION hdfs_path]
   [TBLPROPERTIES (property_name=property_value, …)]

查看表

查看所有表：SHOW TABLES [IN database_name] LIKE [‘identifier_with_wildcards’];
查看表信息：DESCRIBE [EXTENDED | FORMATTED] [db_name.]table_name

修改表

1. 重命名表：ALTER TABLE table_name RENAME TO new_table_name
2. 修改列信息：
   1）增加列，新增列的位置位于末尾
   ALTER TABLE table_name ADD COLUMNS (col_name data_type [COMMENT col_comment], …)
   2）更新列
   ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name]
   3）替换列：允许用户用新的列集替换表中原有的全部列。
   ALTER TABLE table_name REPLACE COLUMNS (col_name data_type [COMMENT col_comment], …)

删除表

DROP TABLE [IF EXISTS] table_name;

清空表

truncate只能清空管理表，不能删除外部表中数据。：语法：TRUNCATE [TABLE] table_name

四、DML数据操作

load将文件导入hive表中

1. 语法：LOAD DATA [LOCAL] INPATH ‘filepath’ [OVERWRITE] INTO TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)];
   （1）local：表示从本地加载数据到Hive表；否则从HDFS加载数据到Hive表。（如果是本地数据则是复制一份到hive路径，如果是HDFS则是移动到hive路径）
   （2）overwrite：表示覆盖表中已有数据，否则表示追加。
   （3）partition：表示上传到指定分区，若目标是分区表，需指定分区。

insert （会执行map）

将查询结果插入表中

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1=val1, partcol2=val2 …)] select_statement;
（1）INTO：将结果追加到目标表
（2）OVERWRITE：用结果覆盖原有数据

将给定Values插入表中

INSERT (INTO | OVERWRITE) TABLE tablename [PARTITION (partcol1[=val1], partcol2[=val2] …)] VALUES values_row [, values_row …]

将查询结果写入目标路径

INSERT OVERWRITE [LOCAL] DIRECTORY directory
[ROW FORMAT row_format] [STORED AS file_format] select_statement;

Export&Import（配合使用）

Export导出语句可将表的数据和元数据信息一并到处的HDFS路径，Import可将Export导出的内容导入Hive，表的数据和元数据信息都会恢复。Export和Import可用于两个Hive实例之间的数据迁移。
导出：EXPORT TABLE tablename TO ‘export_target_path’
导入：IMPORT [EXTERNAL] TABLE new_or_original_tablename FROM ‘source_path’ [LOCATION ‘import_target_path’]

五、基本查询

基础语法：

1. 官网：https://cwiki.apache.org/confluence/display/Hive/LanguageManual+Select
2. 语法：
   SELECT [ALL | DISTINCT] select_expr, select_expr, …
   FROM table_reference – 从什么表查
   [WHERE where_condition] – 过滤
   [GROUP BY col_list] – 分组查询
   [HAVING col_list] – 分组后过滤
   [ORDER BY col_list] – 排序
   [CLUSTER BY col_list
   | [DISTRIBUTE BY col_list] [SORT BY col_list]
   ]
   [LIMIT number] – 限制输出的行数
3. select * from emp limit 2,3; – 表示从第2行开始，向下抓取3行
4. 聚合函数
   count(*)，表示统计所有行数，包含null值；
   count(某列)，表示该列一共有多少行，不包含null值；
   max()，求最大值，不包含null，除非所有值都是null；
   min()，求最小值，不包含null，除非所有值都是null；
   sum()，求和，不包含null。
   avg()，求平均值，不包含null。

开启本地模式报错报错： Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
5. Having语句：
（1）where后面不能写分组聚合函数，而having后面可以使用分组聚合函数。
（2）having只用于group by分组统计语句。

Join语句*****

1. 内连接：（交集）
   只有进行连接的两个表中都存在与连接条件相匹配的数据才会被保留下来。
   例：select e.empno, e.ename, d.deptno from emp e join dept d on e.deptno = d.deptno;
2. 左外连接：左边全部
   join操作符左边表中符合where子句的所有记录将会被返回。
   例：select e.empno, e.ename, d.deptno from emp e left join dept d on e.deptno = d.deptno;
3. 右外连接：右边全部
   例：select e.empno, e.ename, d.deptno from emp e right join dept d on e.deptno = d.deptno;
4. 满外连接：两边全部
   将会返回所有表中符合where语句条件的所有记录。如果任一表的指定字段没有符合条件的值的话，那么就使用null值替代。
   例：select e.empno, e.ename, d.deptno from emp e full join dept d on e.deptno = d.deptno;
5. 多表连接
   连接n个表，至少需要n-1个连接条件。例如：连接三个表，至少需要两个连接条件。大多数情况下，Hive会对每对join连接对象启动一个MapReduce任务
6. 笛卡尔集
   笛卡尔集会在下面条件下产生
   （1）省略连接条件
   （2）连接条件无效
   （3）所有表中的所有行互相连接
7. 联合union&union all上下拼接
   union和union all都是上下拼接sql的结果，这点是和join有区别的，join是左右关联，union和union all是上下拼接。union去重，union all不去重。取第一个select的字段名
   union和union all在上下拼接sql结果时有两个要求：
   （1）两个sql的结果，列的个数必须相同
   （2）两个sql的结果，上下所对应列的类型必须一致

排序*****

1. 全局排序（Order By）
   全局排序，只有一个Reduce。asc（ascend）：升序（默认）desc（descend）：降序
   通常和limit一起使用，可以让每个map只需要处理limit的个数数据
2. 每个Reduce内部排序（Sort By）
   Sort by为每个reduce产生一个排序文件。每个Reduce内部进行排序，对全局结果集来说不是排序。
3. 分区（Distribute By）
   distribute by类似MapReduce中partition（自定义分区），进行分区，结合sort by使用。
4. 分区排序（Cluster By）
   当distribute by和sort by字段相同时，可以使用cluster by方式。cluster by除了具有distribute by的功能外还兼具sort by的功能。但是排序只能是升序排序，

六、函数

单行函数

输入一行，输出一行

1. round：四舍五入
2. ceil：向上取整
3. floor：向下取整
4. substring：截取字符串substring(string A, int start, int len)
5. replace ：替换 ；replace(string A, string B, string C) 字符串A中的子字符串B替换为C。
6. regexp_replace：正则替换 ；regexp_replace(string A, string B, string C) 将字符串A中的符合java正则表达式B的部分替换为C。
7. regexp：正则匹配；select ‘dfsaaaa’ regexp ‘dfsa+’，正则匹配成功，输出true
8. repeat：重复字符串；repeat(string A, int n)，字符串A重复n遍
9. split(string str, string pat) 字符串切割；按照正则表达式pat匹配到的内容分割str，分割后的字符串，以数组的形式返回
10. nvl(A,B) ：替换null值 ；A的值不为null，则返回A，否则返回B
11. concat(string A, string B, string C, ……) 将A,B,C……等字符拼接为一个字符串
12. concat_ws(string A, string…| array(string)) 指定分隔符拼接字符串或者字符串数组
13. get_json_object(string json_string, string path) 解析json的字符串json_string，返回path指定的内容。如果输入的json字符串无效，那么返回NULL。select get_json_object(‘[{“name”:“大海海”,“sex”:“男”,“age”:“25”},{“name”:“小宋宋”,“sex”:“男”,“age”:“47”}]’,‘$.[0].name’);
14. unix_timestamp：返回当前或指定时间的时间戳；例如：
    select unix_timestamp(‘2022/08/08 08-08-08’,‘yyyy/MM/dd HH-mm-ss’);
15. from_unixtime(bigint unixtime[, string format]) 转化UNIX时间戳到当前时区的时间格式
16. select current_date：当前日期
17. current_timestamp：当前的日期加时间，并且精确的毫秒
18. month (string date) 获取日期中的月
19. datediff：两个日期相差的天数（结束日期减去开始日期的天数）
20. date_add(string startdate, int days) ：日期加天数
21. date_sub (string startdate, int days) 日期减天数
22. date_format:将标准日期解析成指定格式字符串
23. case when：case when a then b [when c then d]* [else e] end ：条件判断函数；如果a为true，则返回b；如果c为true，则返回d；否则返回 e ；针对同一个字段的等值判断可以把字段提在case后面
24. map (key1, value1, key2, value2, …) 根据输入的key和value对构建map类型
25. map_keys： 返回map中的key；map_values: 返回map中的value；
    select map_keys(map(‘xiaohai’,1,‘dahai’,2));
26. array(val1, val2, …) 根据输入的参数构建数组array类
27. array_contains: 判断array中是否包含某个元素 ；select array_contains(array(‘a’,‘b’,‘c’,‘d’),‘a’);
28. sort_array：将array中的元素排序
29. struct(val1, val2, val3, …) 根据输入的参数构建结构体struct类

高级聚合函数

多进一出 （多行传入，一个行输出）。

1. 普通聚合 count/sum.
2. collect_list 收集并形成list集合，结果不去重
3. collect_set 收集并形成set集合，结果去重

炸裂函数 UDTF（制表函数）

接收一行，输出多行。

1. 常用的UDTF ----explode（array as 字段名）
2. 常用的UDTF ----explode（map as (key,value)）
3. 常用的UDTF ----poseexplode（array as 字段名） 会返回位置，索引
4. 常用的UDTF ----inline（array<STRUCTf1:T1,....fn,Tn> as 字段名）
5. Latera View通常与UDTF配合使用。Lateral View可以将UDTF应用到源表的每行数据，将每行数据转换为一行或多行，并将源表中每行的输出结果与该行连接起来，形成一个虚拟表。

窗口函数

窗口函数的语法中主要包括“窗口”和“函数”两部分。其中“窗口”用于定义计算范围，“函数”用于定义计算逻辑。
1

1. 分为基于行的，要求每行数据的窗口为上一行到当前行；，基于值的，要求每行数据的窗口为，值位于当前值-1，到当前值。
2. 基于行的语法 sum（amount） over （order by 字段 rows between 起点行 and 终点行）
   
3. 基于值的语法 sum（amount） over （order by 字段 range between 起点行 and 终点行）
4. 语法—窗口----分区
   
5. 缺省
   
6. 常用窗口可划分为如下几类：聚合函数、跨行取值函数、排名函数
   1）聚合函数
   max：最大值。
   min：最小值。
   sum：求和。
   avg：平均值。
   count：计数。
   2）跨行取值函数
   lead和lag：获取当前行的上、下某行、某个字段的值；不支持自定义窗口范围
   
   first_value和last_value
   

3）排名函数，不支持自定义窗口

自定义UDF函数

（1）打成jar包上传到服务器/opt/module/hive/datas/myudf.jar
（2）将jar包添加到hive的classpath，临时生效
add jar /opt/module/hive/datas/myudf.jar;
（3）创建函数与开发好的java class关联
临时：create temporary function my_len as “com.atguigu.hive.udf.MyUDF”;
永久：create function my_len2 as “com.atguigu.hive.udf.MyUDF” using jar “hdfs://hadoop102:8020/udf/myudf.jar”;
（4）即可在hql中使用自定义的临时函数
select
ename,
my_len(ename) ename_len
from emp;
（5）删除临时函数
drop temporary function my_len;

七、分区表和分桶表

分区表

基本语法

1. 创建分区表
   create table dept_partition
   (
   deptno int, --部门编号
   dname string, --部门名称
   loc string --部门位置
   )
   partitioned by (day string)
   row format delimited fields terminated by ‘\t’;
2. 装载数据
   load data local inpath ‘/opt/module/hive/datas/dept_20220401.log’
   into table dept_partition partition(day=‘20220401’);
3. 读数据
   查询分区表数据时，可以将分区字段看作表的伪列，可像使用其他字段一样使用分区字段。
   select deptno, dname, loc ,day
   from dept_partition
   where day = ‘2020-04-01’;
4. 分区表基本操作
   1）查看所有分区信息
   show partitions dept_partition;
   2）增加分区
   （1）创建单个分区
   alter table dept_partition add partition(day=‘20220403’);
   （2）同时创建多个分区（分区之间不能有逗号）
   alter table dept_partition add partition(day=‘20220404’) partition(day=‘20220405’);
   3）删除分区
   （1）删除单个分区
   alter table dept_partition drop partition (day=‘20220403’);
   （2）同时删除多个分区（分区之间必须有逗号）
   修复分区
   Hive将分区表的所有分区信息都保存在了元数据中，只有元数据与HDFS上的分区路径一致时，分区表才能正常读写数据。若用户手动创建/删除分区路径，Hive都是感知不到的，这样就会导致Hive的元数据和HDFS的分区路径不一致。再比如，若分区表为外部表，用户执行drop partition命令后，分区元数据会被删除，而HDFS的分区路径不会被删除，同样会导致Hive的元数据和HDFS的分区路径不一致。
   修复方法：
   （1）add partition
   若手动创建HDFS的分区路径，Hive无法识别，可通过add partition命令增加分区元数据信息，从而使元数据和分区路径保持一致。
   （2）drop partition
   若手动删除HDFS的分区路径，Hive无法识别，可通过drop partition命令删除分区元数据信息，从而使元数据和分区路径保持一致。
   （3）msck
   若分区元数据和HDFS的分区路径不一致，还可使用msck命令进行修复，以下是该命令的用法说明。
   msck repair table table_name [add/drop/sync partitions];

二级分区

hive (default)>
create table dept_partition2(
deptno int, – 部门编号
dname string, – 部门名称
loc string – 部门位置
)
partitioned by (day string, hour string)
row format delimited fields terminated by ‘\t’;

动态分区

动态分区是指向分区表insert数据时，被写往的分区不由用户指定，而是由每行数据的最后一个字段的值来动态的决定。使用动态分区，可只用一个insert语句将数据写入多个分区。
（1）动态分区功能总开关（默认true，开启）
set hive.exec.dynamic.partition=true
（2）严格模式和非严格模式
动态分区的模式，默认strict（严格模式），要求必须指定至少一个分区为静态分区，nonstrict（非严格模式）允许所有的分区字段都使用动态分区。
set hive.exec.dynamic.partition.mode=nonstrict
（3）一条insert语句可同时创建的最大的分区个数，默认为1000。
set hive.exec.max.dynamic.partitions=1000
（4）单个Mapper或者Reducer可同时创建的最大的分区个数，默认为100。
set hive.exec.max.dynamic.partitions.pernode=100
（5）一条insert语句可以创建的最大的文件个数，默认100000。
hive.exec.max.created.files=100000
（6）当查询结果为空时且进行动态分区时，是否抛出异常，默认false。
hive.error.on.empty.partition=false

分桶表

分区针对的是数据的存储路径，分桶针对的是数据文件。
分桶表的基本原理是，首先为每行数据计算一个指定字段的数据的hash值，然后模以一个指定的分桶数，最后将取模运算结果相同的行，写入同一个文件中，这个文件就称为一个分桶（bucket）。
1）建表语句
create table stu_buck(
id int,
name string
)clustered by(id) [sorted by(id) into 4 buckets row format delimited fields terminated by ‘\t’;

八、文件格式和压缩

文件格式

为Hive表中的数据选择一个合适的文件格式，提高查询性能。Hive表数据的存储格式可以选择text file、orc、parquet、sequence file等。

Text File

文本文件是Hive默认使用的文件格式，文本文件中的一行内容，就对应Hive表中的一行记录。
create table textfile_table (column_specs) stored as textfile;

ORC（Optimized Row Columnar）

一种列式存储的文件格式。ORC文件能够提高Hive读写数据和处理数据的性能。
（1）行存储的特点
查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。
（2）列存储的特点
因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。
text file和sequence file都是基于行存储的，orc和parquet是基于列式存储的。

建表语句
create table orc_table (column_specs) stored as orc tblproperties (property_name=property_value, …);
如果有个文本文件需要输入到orc创建的表，就需要先用一个临时表导入文本文件，然后用insert select的方式写入

Parquet

Parquet文件是Hadoop生态中的一个通用的文件格式，它也是一个列式存储的文件格式。

建表语句
Create table parquet_table (column_specs) stored as parquet tblproperties (property_name=property_value, …);

压缩

hive表压缩

1）TextFile
文件类型为TextFile，若需要对该表中的数据进行压缩，多数情况下，无需在建表语句做出声明。直接将压缩后的文件导入到该表即可，Hive在查询表中数据时，可自动识别其压缩格式，进行解压。在执行往表中导入数据的SQL语句时，用户需设置以下参数，来保证写入表中的数据是被压缩的。
–SQL语句的最终输出结果是否压缩
set hive.exec.compress.output=true;
–输出结果的压缩格式（以下示例为snappy）
set mapreduce.output.fileoutputformat.compress.codec =org.apache.hadoop.io.compress.SnappyCodec;
2）ORC
create table orc_table (column_specs) stored as orc tblproperties (“orc.compress”=“snappy”);
3）Parquet
create table orc_table (column_specs) stored as parquet tblproperties (“parquet.compression”=“snappy”);

计算过程压缩（shuffle）

1）单个MR的中间结果进行压缩
Mapper输出的数据，对其进行压缩可降低shuffle阶段的网络IO，可通过以下参数进行配置：
–开启MapReduce中间数据压缩功能
set mapreduce.map.output.compress=true;
–设置MapReduce中间数据数据的压缩方式（以下示例为snappy）
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
2）单条SQL语句的中间结果进行压缩
单条SQL语句的中间结果是指，两个MR（一条SQL语句可能需要通过MR进行计算）之间的临时数据，可通过以下参数进行配置：
–是否对两个MR之间的临时数据进行压缩
set hive.exec.compress.intermediate=true;
–压缩格式（以下示例为snappy）
set hive.intermediate.compression.codec= org.apache.hadoop.io.compress.SnappyCodec;

九、企业级调优 ********************

计算资源配置

计算资源的调整主要包括Yarn和MR。

yarn资源配置

调整的Yarn参数均与CPU、内存等资源有关。
（1）yarn.nodemanager.resource.memory-mb
一个NodeManager节点分配给Container使用的内存。该参数的配置，取决于NodeManager所在节点的总内存容量和该节点运行的其他服务的数量。一般给总内存的1/2 — 2/3之间。可将该参数设置为64G（65536）
（2）yarn.nodemanager.resource.cpu-vcores
一个NodeManager节点分配给Container使用的CPU核数。该参数的配置，同样取决于NodeManager所在节点的总CPU核数和该节点运行的其他服务。可将该参数设置为16。
（3）yarn.scheduler.maximum-allocation-mb
单个Container能够使用的最大内存。推荐可设置：16384
（4）yarn.scheduler.minimum-allocation-mb
单个Container能够使用的最小内存。推荐可设置：512

MapReduce资源配置

主要包括Map Task的内存和CPU核数，以及Reduce Task的内存和CPU核数
1）mapreduce.map.memory.mb
单个Map Task申请的container容器内存大小，其默认值为1024。该值不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
在hive中，可直接使用SQL语句单独进行配置
set mapreduce.map.memory.mb=2048;
2）mapreduce.map.cpu.vcores
单个Map Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。
3）mapreduce.reduce.memory.mb
单个Reduce Task申请的container容器内存大小，其默认值为1024。该值同样不能超出yarn.scheduler.maximum-allocation-mb和yarn.scheduler.minimum-allocation-mb规定的范围。
SQL配置：set mapreduce.reduce.memory.mb=2048;
4）mapreduce.reduce.cpu.vcores
单个Reduce Task申请的container容器cpu核数，其默认值为1。该值一般无需调整。

explain查看执行计划******

1. Explain呈现的执行计划，由一系列Stage组成，这一系列Stage具有依赖关系，每个Stage对应一个MapReduce Job，或者一个文件系统操作等。
2. 若某个Stage对应的一个MapReduce Job，其Map端和Reduce端的计算逻辑分别由Map Operator Tree和Reduce Operator Tree进行描述，Operator Tree由一系列的Operator组成，一个Operator代表在Map或Reduce阶段的一个单一的逻辑操作。
3. 常见的Operator及其作用如下：
   TableScan：表扫描操作，通常map端第一个操作肯定是表扫描操作
   Select Operator：选取操作
   Group By Operator：分组聚合操作
   Reduce Output Operator：输出到 reduce 操作
   Filter Operator：过滤操作
   Join Operator：join 操作
   File Output Operator：文件输出操作
   Fetch Operator 客户端获取数据操作
4. 基本语法
   EXPLAIN [FORMATTED | EXTENDED | DEPENDENCY] query-sql
   注：FORMATTED、EXTENDED、DEPENDENCY关键字为可选项，各自作用如下。
   FORMATTED：将执行计划以JSON字符串的形式输出
   EXTENDED：输出执行计划中的额外信息，通常是读写的文件名等信息
   DEPENDENCY：输出执行计划读取的表及分区
5. python -m SimpleHTTPServer 8900 执行计划可视化工具

HQL语法优化之分组聚合优化

Hive对分组聚合的优化主要围绕着减少Shuffle数据量进行，具体做法是map-side聚合。所谓map-side聚合，就是在map端维护一个hash table，利用其完成部分的聚合，然后将部分聚合的结果，按照分组字段分区，发送至reduce端，完成最终的聚合。
–启用map-side聚合
set hive.map.aggr=true;

–用于检测源表数据是否适合进行map-side聚合。检测的方法是：先对若干条数据进行map-side聚合，若聚合后的条数和聚合前的条数比值小于该值，则认为该表适合进行map-side聚合；否则，认为该表数据不适合进行map-side聚合，后续数据便不再进行map-side聚合。（会受到数据分布的影响，因为是抽取分片开头的10w数据进行计算比值，会导致比值过高，不适合启动 聚合）
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;

HQL语法优化之Join优化*********

Hive拥有多种join算法，包括Common Join，Map Join，Bucket Map Join，Sort Merge Buckt Map Join
1）Common Join
是Hive中最稳定的join算法，其通过一个MapReduce Job完成一个join操作。Map端负责读取join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。

sql语句中的join操作和执行计划中的Common Join任务并非一对一的关系，一个sql语句中的相邻的且关联字段相同的多个join操作可以合并为一个Common Join任务。
2）Map Join

1. Map Join算法可以通过两个只有map阶段的Job完成一个join操作。适用场景为大表join小表。若某join操作满足要求，则第一个Job会读取小表数据，将其制作为hash table，并上传至Hadoop分布式缓存（本质上是上传至HDFS）。第二个Job会先从分布式缓存中读取小表数据，并缓存在Map Task的内存中，然后扫描大表数据，这样在map端即可完成关联操作。

2. Hive会根据每个Common Join任务所需表的大小判断该Common Join任务是否能够转换为Map Join任务，若满足要求，便将Common Join任务自动转换为Map Join任务。
3. 优化
有些Common Join任务所需的表大小，在SQL的编译阶段是未知的（比如是查询结果作为一个表，子查询）。Hive会在编译阶段生成一个条件任务（Conditional Task），其下会包含一个计划列表，计划列表中包含转换后的Map Join任务以及原有的Common Join任务。最终具体采用哪个计划，是在运行时决定的。

上图中子任务也是map join时，满足条件可以把多个小表一起缓存，一次性完成多个表的合并，然后合并到一起作为一个任务。
上图参数
–启动Map Join自动转换
set hive.auto.convert.join=true;

–一个Common Join operator转为Map Join operator的判断条件,若该Common Join相关的表中,存在n-1张表的已知大小总和<=该值,则生成一个Map Join计划,此时可能存在多种n-1张表的组合均满足该条件,则hive会为每种满足条件的组合均生成一个Map Join计划,同时还会保留原有的Common Join计划作为后备(back up)计划,实际运行时,优先执行Map Join计划，若不能执行成功，则启动Common Join后备计划。
set hive.mapjoin.smalltable.filesize=250000;

–开启无条件转Map Join
set hive.auto.convert.join.noconditionaltask=true;

–无条件转Map Join时的小表之和阈值,若一个Common Join operator相关的表中，存在n-1张表的大小总和<=该值,此时hive便不会再为每种n-1张表的组合均生成Map Join计划,同时也不会保留Common Join作为后备计划。而是只生成一个最优的Map Join计划。
set hive.auto.convert.join.noconditionaltask.size=10000000;

3）Bucket Map Join
Bucket Map Join可用于大表join大表的场景。实际上把大表拆成多个小表（桶），map join的扩展。
核心思想是：若能保证参与join的表均为分桶表，且关联字段为分桶字段，且其中一张表的分桶数量是另外一张表分桶数量的整数倍，就能保证参与join的两张表的分桶之间具有明确的关联关系，所以就可以在两表的分桶间进行Map Join操作了。这样一来，第二个Job的Map端就无需再缓存小表的全表数据了，而只需缓存其所需的分桶即可。

优化
–关闭cbo优化，cbo会导致hint信息被忽略
set hive.cbo.enable=false;
–map join hint默认会被忽略(因为已经过时)，需将如下参数设置为false
set hive.ignore.mapjoin.hint=false;
–启用bucket map join优化功能
set hive.optimize.bucketmapjoin = true;

4）Sort Merge Bucket Map Join

1. 简称SMB Map Join基于Bucket Map Join。SMB Map Join要求，参与join的表均为分桶表，且需保证分桶内的数据是有序的，且分桶字段、排序字段和关联字段为相同字段，且其中一张表的分桶数量是另外一张表分桶数量的整数倍。
2. SMB Map Join与Bucket Join不同的是，分桶之间的join操作的实现原理。Bucket Map Join，两个分桶之间的join实现原理为Hash Join算法；而SMB M ap Join，两个分桶之间的join实现原理为Sort Merge Join算法。
3. Sort Merge Join需要在两张按照关联字段排好序的表中进行
4. 对内存要求更低。
   优化
   –启动Sort Merge Bucket Map Join优化
   set hive.optimize.bucketmapjoin.sortedmerge=true;
   –使用自动转换SMB Join
   set hive.auto.convert.sortmerge.join=true;

HQL语法优化之数据倾斜

map输出数据按key Hash的分配到reduce中，由于key分布不均匀、业务数据本身的特、建表时考虑不周、等原因造成的reduce 上的数据量差异过大。 1)、key分布不均匀; 2)、业务数据本身的特性; 3)、建表时考虑不周; 4)、某些SQL语句本身就有数据倾斜; 如何避免：对于key为空产生的数据倾斜，可以对其赋予一个随机值。
解决方案 ：
1>.参数调节： hive.map.aggr = true 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中），最后完成最终的聚合操作。
2>.SQL 语句调节： 1)、选用join key分布最均匀的表作为驱动表。做好列裁剪和filter操作，以达到两表做join 的时候，数据量相对变小的效果。 2)、大小表Join： 使用map join让小的维度表（1000 条以下的记录条数）先进内存。在map端完成reduce. 4)、大表Join大表： 把空值的key变成一个字符串加上随机数，把倾斜的数据分到不同的reduce上，由于null 值关联不上，处理后并不影响最终结果。 5)、count distinct大量相同特殊值: count distinct 时，将值为空的情况单独处理，如果是计算count distinct，可以不用处理，直接过滤，在最后结果中加1。如果还有其他计算，需要进行group by，可以先将值为空的记录单独处理，再和其他计算结果进行union。

即某个key或者某些key的数据量远超其他key，导致在shuffle阶段，大量相同key的数据被发往同一个Reduce，进而导致该Reduce所需的时间远超其他Reduce，成为整个任务的瓶颈。

1. 分组聚合导致的数据倾斜
   1）Map-Side聚合
   开启Map-Side聚合后，数据会现在Map端完成部分聚合工作。经过Map端的初步聚合后，发往Reduce的数据也就不再倾斜了
   2）Skew-GroupBy优化
   Skew-GroupBy的原理是启动两个MR任务，第一个MR按照随机数分区，将数据分散发送到Reduce，完成部分聚合，第二个MR按照分组字段分区，完成最终聚合。
   –启用分组聚合数据倾斜优化
   set hive.groupby.skewindata=true;
2. Join导致的数据倾斜
   未经优化的join操作，默认是使用common join算法，也就是通过一个MapReduce Job完成计算。Map端负责读取join操作所需表的数据，并按照关联字段进行分区，通过Shuffle，将其发送到Reduce端，相同key的数据在Reduce端完成最终的Join操作。
   解决方案：
   1）map join （大表join小表）
   使用map join算法，join操作仅在map端就能完成，没有shuffle操作，没有reduce阶段，自然不会产生reduce端的数据倾斜。该方案适用于大表join小表时发生数据倾斜的场景。

2）skew join（大表join大表） 默认关闭
skew join的原理是，为倾斜的大key单独启动一个map join任务进行计算，其余key进行正常的common join。
对参与join的源表大小没有要求，但是对两表中倾斜的key的数据量有要求，要求一张表中的倾斜key的数据量比较小（方便走mapjoin）。
–启用skew join优化
set hive.optimize.skewjoin=true;
–触发skew join的阈值，若某个key的行数超过该参数值，则触发
set hive.skewjoin.key=100000;
3）调整SQL语句 （大表join大表）
hive (default)>
select
*
from(
select –打散操作
concat(id,‘‘,cast(rand()*2 as int)) id,
value
from A
)ta
join(
select –扩容操作
concat(id,’’,0) id,
value
from B
union all
select
concat(id,‘_’,1) id,
value
from B
)tb
on ta.id=tb.id;

HQL语法优化之任务并行度

1. Map端并行度，Map的个数
   1）查询的表中存在大量小文件
   使用Hive提供的CombineHiveInputFormat，多个小文件合并为一个切片，从而控制map task个数。
   set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
   2）map端有复杂的查询逻辑
   若SQL语句中有正则替换、json解析等复杂耗时的查询逻辑时，在计算资源充足的情况下，可考虑增大map端的并行度，令map task多一些
   –一个切片的最大值
   set mapreduce.input.fileinputformat.split.maxsize=256000000;
2. Reduce端并行度
   –指定Reduce端并行度，默认值为-1，表示用户未指定
   set mapreduce.job.reduces;
   –Reduce端并行度最大值
   set hive.exec.reducers.max;
   –单个Reduce Task计算的数据量，用于估算Reduce并行度
   set hive.exec.reducers.bytes.per.reducer;

ruduce端并行度估算，Hive自行估算Reduce并行度时，是以整个MR Job输入的文件大小作为依据的

HQL语法优化之小文件合并

1. Map端输入文件合并
   指将多个小文件划分到一个切片中，进而由一个Map Task去处理。目的是防止为单个小文件启动一个Map Task，浪费计算资源
   –可将多个小文件切片，合并为一个切片，进而由一个map任务处理
   set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;
2. Reduce输出文件合并 **
   指将多个小文件合并成大文件。目的是减少HDFS小文件数量。其原理是根据计算任务输出文件的平均大小进行判断，若符合条件，则单独启动一个额外的任务进行合并。
   –开启合并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;

其他优化

1. CBO优化（Cost based Optimizer），基于计算成本的优化。hive会计算同一SQL语句的不同执行计划的计算成本，并选出成本最低的执行计划。目前CBO在hive的MR引擎下主要用于join的优化
   –是否启用cbo优化
   set hive.cbo.enable=true;
2. 谓词下推（where过滤前移）
   谓词下推（predicate pushdown）是指，尽量将过滤操作前移，以减少后续计算步骤的数据量。
   –是否启动谓词下推（predicate pushdown）优化
   set hive.optimize.ppd = true;
3. 矢量化查询（向量计算）
   set hive.vectorized.execution.enabled=true;
   执行计划中，出现“Execution mode: vectorized”字样，即表明使用了矢量化计算。
   
4. Fetch抓取
   Hive中对某些情况的查询可以不必使用MapReduce计算。
   –是否在特定场景转换为fetch 任务
   –设置为none表示不转换
   –设置为minimal表示支持select *，分区字段过滤，Limit等
   –设置为more表示支持select 任意字段,包括函数，过滤，和limit等
   set hive.fetch.task.conversion=more;
5. 本地模式
   Hive的输入数据量是非常小，为查询触发执行任务消耗的时间可能会比实际job的执行时间要多的多，这种情况，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;
6. 并行执行
SQL语句可能会包含多个Stage，但这多个Stage可能并非完全互相依赖，也就是说有些Stage是可以并行执行的。
–启用并行执行优化
set hive.exec.parallel=true;

–同一个sql允许最大并行度，默认为8
set hive.exec.parallel.thread.number=8;
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，会限制笛卡尔积的查询