hive学习笔记

凌逍5

已于 2022-02-19 18:52:31 修改

阅读量1.3k

点赞数 1

分类专栏：大数据文章标签： hive 学习 hadoop

于 2022-02-17 17:08:04 首次发布

本文链接：https://blog.csdn.net/qq_41940018/article/details/122987509

版权

大数据专栏收录该内容

4 篇文章 0 订阅

订阅专栏

文章目录

一基本概念
二下载、配置、启用
三常用命令
四分区表和分桶表
五函数
六压缩和存储
七调优
八 Tez引擎
九解题思路
- 9.1 连续问题
- 9.2 同时在线问题

一基本概念

1.1 描述

Hive 是基于 Hadoop 的一个数据仓库工具，可以将结构化的数据文件映射为一张表，并提供类 SQL 查询功能。

Hive可以看做是Hadoop的一个客户端工具。

Hive的数据分析引擎默认使用MapReduce，是对Hadoop中MapReduce进行了封装，提供了一些模板化的MapReduce程序，可以将 HQL 转化成 MapReduce 程序。也可以替换为其他引擎如spark等。

Hive 处理的数据存储在 HDFS上。执行程序运行在 Yarn 上。

1.2 优缺点

1.2.1 优点

操作接口采用类 SQL 语法，提供快速开发的能力；
Hive 的执行延迟比较高，因此 Hive 常用于数据分析，对实时性要求不高的场合；
Hive 优势在于处理大数据，对于处理小数据没有优势，因为 Hive 的执行延迟比较高；
Hive 支持用户自定义函数，用户可以根据自己的需求来实现自己的函数。

1.2.2 缺点

Hive 的 HQL 表达能力有限：迭代式算法无法表达；数据挖掘方面不擅长，由于 MapReduce 数据处理流程的限制，效率更高的算法却无法实现。
Hive 的效率比较低：Hive 自动生成的 MapReduce 作业，通常情况下不够智能化；Hive 调优比较困难，粒度较粗。

1.3 架构

在这里插入图片描述

1.3.1 client

CLI（command-line interface）、JDBC/ODBC(jdbc 访问 hive)、WEBUI（浏览器访问 hive）。

1.3.2 Metastore

元数据包括：表名、表所属的数据库（默认是 default）、表的拥有者、列/分区字段、表的类型（是否是外部表）、表的数据所在目录等；默认存储在自带的 derby 数据库中，由于derby数据库是单链接数据库，同时只能有一个链接存在，推荐使用 MySQL 存储 Metastore。

1.3.3 HDFS

存放数据

1.3.4 MapReduce

分析数据

1.3.5 Driver

解析器（SQL Parser）：将 SQL 字符串转换成抽象语法树 AST，这一步一般都用第三方工具库完成，比如 antlr；对 AST 进行语法分析，比如表是否存在、字段是否存在、SQL 语义是否有误。
编译器（Physical Plan）：将 AST 编译生成逻辑执行计划。
优化器（Query Optimizer）：对逻辑执行计划进行优化。
执行器（Execution）：把逻辑执行计划转换成可以运行的物理计划。对于 Hive 来说，就是 MR/Spark。

1.4 运行机制

在这里插入图片描述

Hive 通过给用户提供的一系列交互接口，接收到用户的指令(HQL)，使用自己的 Driver，结合元数据(MetaStore)，将这些指令翻译成 MapReduce，提交到 Hadoop 中执行，最后，将执行返回的结果输出到用户交互接口。

1.5 和数据库的对比

1.5.1 相似点

查询语言：针对 Hive 的特性设计了类 SQL 的查询语言 HQL。HQL的绝大部分语法和SQL一样，方便使用。

1.5.2 不同点

数据规模：

由于 Hive 建立在集群上并可以利用 MapReduce 进行并行计算，因此可以支持很大规模的数据；对应的，数据库可以支持的数据规模较小。
数据更新：

由于 Hive 是针对数据仓库应用设计的，而数据仓库的内容是读多写少的，并且Hive的数据是存放在Hadoop的HDFS上的。因此，Hive 中不建议对数据的改写，所有的数据都是在加载的时候确定好的。而数据库中的数据通常是需要经常进行修改的，因此可以使用 INSERT INTO … VALUES 添加数据，使用 UPDATE … SET 修改数据。
执行延迟：

Hive 在查询数据的时候，由于没有索引，需要扫描整个表，因此延迟较高。另外一个导致 Hive 执行延迟高的因素是 MapReduce 框架。由于 MapReduce 本身具有较高的延迟，因此在利用 MapReduce 执行 Hive 查询时，也会有较高的延迟。相对的，数据库的执行延迟较低。

1.6 配置

1.6.1 配置文件

默认配置文件：hive-default.xml

用户自定义配置文件：hive-site.xml。用户自定义配置会覆盖默认配置。另外，Hive 也会读入 Hadoop 的配置，因为 Hive 是作为 Hadoop 的客户端启动的，Hive 的配置会覆盖 Hadoop 的配置。配置文件的设定对本机启动的所有 Hive 进程都有效。

1.6.2 命令行参数

启动 Hive 时，可以在命令行添加 -hiveconf param=value 来设定参数。仅对本次 hive 启动有效。

1.6.3 参数声明

可以在 HQL 中使用 SET 关键字设定参数。仅对本次 hive 启动有效。

上述三种设定方式的优先级依次递增。即配置文件 < 命令行参数 < 参数声明。

注意某些系统级的参数，例如 log4j 相关的设定，必须用前两种方式设定，因为那些参数的读取在会话建立以前已经完成了。

1.7 数据类型

1.7.1 基本数据类型

Hive 数据类型	Java 数据类型	注释
TINYINT	byte	1byte 有符号整数
SMALINT	short	2byte 有符号整数
INT	int	4byte 有符号整数
BIGINT	long	8byte 有符号整数
BOOLEAN	boolean	布尔类型，true 或者 false
FLOAT	float	单精度浮点数
DOUBLE	double	双精度浮点数
STRING	string	字符系列。可以指定字符集。可以使用单引号或者双引号。
TIMESTAMP		时间类型
BINARY		字节数组

对于 Hive 的 String 类型相当于数据库的 varchar 类型，该类型是一个可变的字符串，不过它不能声明其中最多能存储多少个字符，理论上它可以存储 2GB 的字符数。

1.7.2 集合类型

数据类型	描述	示例	访问方式
STRUCT	相当于java中的类	struct<a:string, b:int>	字段.属性
MAP	键值对	map<string, int>	字段[“key值”]
ARRAY	相当于java的数组	array	字段[下标]

示例：

create table person(
    name string,
    friends array<string>,
    children map<string, int>,
    address struct<street:string, city:string>
)
-- 一行数据中每列的分隔符
row format delimited fields terminated by ','
-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
collection items terminated by '_'
-- MAP 中的 key 与 value 的分隔符
map keys terminated by ':'
-- 每行之间的分隔符。默认：'\n'
lines terminated by '\n';

1.7.3 类型转换

Hive 的原子数据类型是可以进行隐式转换的，类似于 Java 的类型转换，例如某表达式使用 INT 类型，TINYINT 会自动转换为 INT 类型（小转大），但是 Hive 不会进行反向转化（大转小），例如，某表达式使用 TINYINT 类型，INT 不会自动转换为 TINYINT 类型，它会返回错误，除非使用 CAST 操作。

1.7.3.1 隐式类型转换规则

任何整数类型都可以隐式地转换为一个范围更广的类型，如 TINYINT 可以转换成 INT，INT 可以转换成 BIGINT；
所有整数类型、FLOAT 和 STRING 类型的数字都可以隐式地转换成 DOUBLE；
TINYINT、SMALLINT、INT 都可以转换为 FLOAT；
BOOLEAN 类型不可以转换为任何其它的类型

1.7.3.2 显式转换

可以使用 CAST 操作显示进行数据类型转换。

例如 CAST(‘1’ AS INT)将把字符串’1’ 转换成整数 1；如果强制类型转换失败，如执行 CAST(‘X’ AS INT)，表达式返回空值 NULL

二下载、配置、启用

前提：搭建好Hadoop集群，安装好MySQL。

2.1 下载

进入官网，选择版本：hadoop1.X选择hive1.X，hadoop2.X选择hive2.X，hadoop3.X选择hive3.X
下载压缩包：

2.2 配置

上传后解压

添加环境变量，刷新：

# HIVE_HOME
export HIVE_HOME=/usr/local/big_data/apache-hive-3.1.2-bin
export PATH=$PATH:$HIVE_HOME/bin

配置日志保存位置（日志默认保存在临时目录下）

# 复制模板文件得到配置文件
scp $HIVE_HOME/conf/hive-log4j2.properties.template $HIVE_HOME/conf/hive-log4j2.properties
vim $HIVE_HOME/conf/hive-log4j2.properties

# 修改日志文件保存位置，在第24行
property.hive.log.dir = /usr/local/big_data/apache-hive-3.1.2-bin/logs

将 MySQL 的 JDBC 驱动拷贝到 Hive 的 lib 目录下

登录MySQL数据库，创建Hive元数据库

mysql -uroot -proot
create database hive_metastore;
show databases;
quit;

Hive内部自定义配置

新建 $HIVE_HOME/conf/hive-site.xml 文件，内容如下

该配置文件会覆盖默认配置文件 $HIVE_HOME/conf/hive-default.xml

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.10.102:3306/hive_metastore?useSSL=false</value>
        <description>hive 通过 jdbc 连接到存放元数据的数据库的 URL</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>hive 通过 jdbc 连接到存放元数据的数据库的驱动</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
        <description>hive 通过 jdbc 连接到存放元数据的数据库的用户名</description>
    </property>
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>root</value>
        <description>hive 通过 jdbc 连接到存放元数据的数据库的密码</description>
    </property>
    <property>
        <name>hive.metastore.schema.verification</name>
        <value>false</value>
        <description>Hive 元数据存储版本的验证。默认true</description>
    </property>
    <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
        <description>元数据存储授权 。默认true</description>
    </property>
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
        <description>Hive 数据在 HDFS 上的存放目录，默认：/user/hive/warehouse</description>
    </property>
    
    <property>
        <name>hive.metastore.uris</name>
        <value>thrift://192.168.10.102:9083</value>
        <description>metastore 服务的地址和端口。默认端口9083</description>
    </property>
    
    <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>192.168.10.102</value>
        <description>hiveserver2 服务的地址</description>
    </property>
    <property>
        <name>hive.server2.thrift.port</name>
        <value>10000</value>
        <description>hiveserver2 服务的端口。默认端口10000</description>
    </property>
    
    <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
        <description>Hive 客户端直连时是否显示数据库名称。默认 false</description>
    </property>
    <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
        <description>Hive 客户端直连时是否显示数据库表名和字段名。默认 false</description>
    </property>
</configuration>

初始化 Hive 元数据库

schematool -initSchema -dbType mysql -verbose

修改Hadoop集群的配置文件，添加配置

vim $HADOOP_HOME/etc/hadoop/core-site.xml

<configuration>
    <!-- root 为Hadoop集群的启动用户。允许 root 用户模拟其他用户来提交任务 -->
    <property>
        <name>hadoop.proxyuser.root.hosts</name>
        <value>*</value>
    </property>
    <property>
        <name>hadoop.proxyuser.root.groups</name>
        <value>*</value>
    </property>
    <property>
        <name>hadoop.proxyuser.root.users</name>
        <value>*</value>
    </property>
</configuration>

2.3 启动

会启动metastore和hiveserver2两个服务。

其中metastore服务用来管理元数据。如果不配置和启动Metastore服务，那么每个hive连接或每启动一个hiveserver2服务，都会启动一个嵌入式的Metastore服务，浪费资源与时间。

hiveserver2服务的功能是方便其他客户端连接hive，提供连接hive的IP和端口。其他客户端包括beeline、hive和idea等工具。

启动Hadoop集群，启动MySQL。

启动脚本

touch $HOME/bin/myhive
chmod u+x $HOME/bin/myhive
vim $HOME/bin/myhive

#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs
if [ ! -d $HIVE_LOG_DIR ]
then
    mkdir -p $HIVE_LOG_DIR
fi
# 检查进程是否运行正常，参数 1 为进程名，参数 2 为进程端口
function check_process()
{
    pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}')
    ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d '/' -f 1)
    echo $pid
    [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}
function hive_start()
{
    metapid=$(check_process HiveMetastore 9083)
    cmd="nohup hive --service metastore > $HIVE_LOG_DIR/metastore.log 2>&1 &"
    [ -z "$metapid" ] && eval $cmd || echo "Metastroe 服务已启动"
    server2pid=$(check_process HiveServer2 10000)
    cmd="nohup hive --service hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
    [ -z "$server2pid" ] && eval $cmd || echo "HiveServer2 服务已启动"
}
function hive_stop()
{
    metapid=$(check_process HiveMetastore 9083)
    [ "$metapid" ] && kill $metapid || echo "Metastore 服务未启动"
    server2pid=$(check_process HiveServer2 10000)
    [ "$server2pid" ] && kill $server2pid || echo "HiveServer2 服务未启动"
}
case $1 in
"start")
    hive_start
;;
"stop")
    hive_stop
;;
"restart")
    hive_stop
    sleep 2
    hive_start
;;
"status")
    check_process HiveMetastore 9083 >/dev/null && echo "Metastore 服务正在运行" || echo "Metastore 服务未启动"
    check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 服务正在运行" || echo "HiveServer2 服务未启动"
;;
*)
    echo Invalid Args!
    echo 'Usage: '$(basename $0)' start|stop|restart|status'
;;
esac

连接

# 直连
hive
# 启动 beeline 客户端连接
beeline -u jdbc:hive2://192.168.10.102:10000 -n root

测试
```
show databases;
show tables;
```

三常用命令

HQL官方文档

3.1 DDL

DDL官方文档

3.1.1 database

创建数据库

-- 创建数据库 test_db
create database if not exists test
-- 数据库的注释，可选
comment "test database"
-- 数据库数据在 HDFS 的存放位置。可选。默认/user/hive/warehouse/test.db
location "/user/hive/warehouse/test.db"
-- 自定义数据库属性，键值对，可选
with dbproperties("createtime"="2022-02-07","createuser"="zhangsan");

查看数据库

-- 显示当前所有数据库
show databases;

-- 根据条件显示数据库
show databases like "*test*";

-- 查看数据库 test 的信息
desc database test;

-- 查看数据库 test 的详细信息，包括自定义属性
desc database extended test;

切换数据库

-- 将当前数据库切换为 test
use test;

修改数据库

-- 修改数据库的自定义属性，如果没有该属性就会新增
alter database test
set dbproperties('createtime'='20170830');

删除数据库

-- 删除空数据库
drop database if exists test;

-- 强制删除非空数据库
drop database if exists test cascade;

3.1.2 table

创建表

-- 创建表
-- EXTERNAL：表示外部表，没有该关键字则是管理表。
-- 删除管理表会同时删除元数据和 HDFS 数据，而删除外部表只删除元数据，不删除 HDFS 数据
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] 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 delimited fields terminated by ',']
-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
[collection items terminated by '_']
-- MAP 中的 key 与 value 的分隔符
[map keys terminated by ':']
-- 指定文件存储格式，默认为 textfile
-- 常用：SEQUENCEFILE（二进制序列文件，压缩使用）、TEXTFILE（文本）、ORC（列式存储格式文件）
[STORED AS file_format]
-- 指定表中数据在 HDFS 中的存储路径
[LOCATION hdfs_path]
-- 表的自定义属性。也可以用来指定压缩格式
[TBLPROPERTIES (property_name=property_value, ...)]
-- 根据查询结果创建表。即在建好表之后，把查询结果插入进去
[AS select_statement
| -- 或
-- 根据已存在的表创建新表。即复制表结构，不复制数据
like table_name];


-- 创建表person
create table if not exists person(
    name string comment "姓名",
    friends array<string> comment "朋友",
    children map<string, int> comment "孩子",
    address struct<street:string, city:string> comment "住址"
)
-- 表的描述信息
comment "人"
-- 
partitioned by (day string)
-- 一行数据中每列的分隔符
row format delimited fields terminated by ','
-- MAP STRUCT 和 ARRAY 的分隔符(数据分割符号)
collection items terminated by '_'
-- MAP 中的 key 与 value 的分隔符
map keys terminated by ':'
-- 每行之间的分隔符。默认：'\n'
lines terminated by '\n'
-- 指定文件存储格式，默认为 textfile
stored as textfile
-- 指定表中数据在 HDFS 的存储路径。在test数据库中创建时默认：/user/hive/warehouse/test.db/person
location "/user/hive/warehouse/test.db/person"
-- 表的自定义属性
tblproperties("createtime"="2022-02-02");

查看表

-- 显示所有表
show tables;

-- 根据过滤条件显示表
show tables like "*p*";

-- 查看表的结构信息
desc person;

-- 查看表的详细信息
desc formatted person;

修改表

-- 将表改为外部表。EXTERNAL 必须大写
alter table person set tblproperties ('EXTERNAL'='TRUE');

-- 将表改为管理表。EXTERNAL 必须大写
alter table person set tblproperties ('EXTERNAL'='FALSE');


-- 将表 person1 的表名改为 person
alter table person1 rename to person;

-- 增加列
alter table person add columns (id int comment "id");

-- 修改列 name 为 nm，数据类型为字符串
alter table person change column name nm string comment "姓名"

-- 修改列 name 为 nm，数据类型为字符串，并放到字段 id 后面。修改很容易失败，不好用
alter table person change column name nm string comment "姓名" after id;

-- 修改列 nm 为 name，数据类型为字符串，并把列放到第一位
alter table person change column nm name string comment "姓名" first;

-- 替换表中所有的列，表替换前后的列数可以不相等
alter table dept replace columns(deptno string comment "编号", 
                                 dname string comment "姓名", 
                                 loc string);

删除表
```
drop table person;
```

3.2 DML

3.2.1 插入

3.2.1.1 load

load 本地数据时，如果有开启MR任务，而MR任务又是交给YARN调控，如分区、分桶表加载数据，那么有可能出现找不到文件的问题。因为YARN可能把任务分给其他节点去执行了，而其他节点上是没有数据的。

-- 加载数据
load data
-- local 表示从本地加载，不加则是从 HDFS 加载数据
-- 本地加载即把本地文件上传到 HDFS 中表数据所在位置。从 HDFS 加载则是移动文件到表数据所在位置
[local]
-- 数据路径
inpath 'data_path'
-- overwrite 表示覆盖已有数据，不加则是追加
[overwrite]
-- 指定要加载数据的表
into table table_name
-- 指定表的分区
[partition (partcol1=val1,…)];

-- 加载本地数据到表person中
load data local inpath '/root/person.txt' into table person;

-- 分区表加载数据要指定分区
load data local inpath '/tmp/data/dept_20200401.log'
into table dept_partition
-- 指定分区，day 为分区字段
partition(day='20200401');

3.2.1.2 insert

-- 追加插入
insert into table student_par values(1,'wangwu'),(2,'zhaoliu');

-- 覆盖插入
insert overwrite table student_par values(1,'wangwu'),(2,'zhaoliu');

-- 将查询结果追加插入另一张表
insert into table student_par
select id, name from student where month='201709';

-- 将查询结果覆盖插入另一张表
insert overwrite table student_par
select id, name from student where month='201709';

3.2.1.3 建表时插入

-- 建表时根据查询结果插入
create table if not exists student_bak
as select id, name from student;

-- 建表时通过指定数据所在路径插入数据
create external table if not exists student5(
    id int, name string
)
row format delimited fields terminated by '\t'
location '/student';

3.2.1.4 import

-- 将 export 导出的数据导入
import table student2 from '/user/hive/warehouse/export/student';

3.2.2 导出

3.2.2.1 insert

如果表中数据为 NULL，导出到文件中显示为 \N。

-- 将查询的结果导出到本地，没有格式。每行数据将没有分隔符
insert overwrite local directory '/tmp/data/student'
select * from student;

-- 将查询的结果格式化导出到本地
insert overwrite local directory '/tmp/data/student'
-- 每行数据中每列使用tab分隔
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
select * from student;

-- 将查询的结果导出到 HDFS 上(没有 local)
insert overwrite directory '/user/root/student2'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t' 
select * from student;

3.2.2.2 Hadoop

-- 使用 HDFS 命令把 HDFS 上的文件下载到本地
dfs -get /user/hive/warehouse/student/student.txt /tmp/data/student.txt;

3.2.2.3 shell

-- 通过shell命令查询数据后，写入本地文件
hive -e 'select * from default.student;' > /tmp/data/student.txt;

3.2.2.4 export

不仅会导出数据，还会导出元数据。所以适合 hive 数据迁移。

-- 将表 student 的数据导出到 HDFS 的指定位置
export table default.student to '/user/hive/warehouse/export/student';

3.2.3 查询

-- 查询：默认使用 ALL ，可以省略。DISTINCT 为去重。一般不用，任务太重，会使用其他方式
SELECT [ALL | DISTINCT] select_expr, select_expr, ...
-- 指定要查询的表
FROM table_reference
-- 过滤条件
[WHERE where_condition]
-- 分组
[GROUP BY col_list]
-- 对分组结果进行过滤
[HAVING having_condition]
-- 全局排序
[ORDER BY col_list]
-- 分区排序。CLUSTER 相当于下面 DISTRIBUTE 和 SORT 两个的组合
[
    CLUSTER BY col_list
    |
    -- 分区
    [DISTRIBUTE BY col_list]
    -- 区内排序
    [SORT BY col_list]
]
-- 限制返回结果的行数
[LIMIT number]

3.2.3.1 基本查询

-- 查看全部字段
select * from emp;
select empno,ename,job,mgr,hiredate,sal,comm,deptno from emp;

-- 查看特定字段
select empno,ename from emp;

-- 使用列的别名，可以选择使用 as 或省略
select ename AS name, deptno dn from emp;

-- 算术运算
select sal +1 from emp;

3.2.3.2 函数

见第五章

3.2.3.3 limit

-- 限制返回的行数，从下标0开始，查询5行
select * from emp limit 5;
-- 限制返回的行数，从下标2开始，查询3行
select * from emp limit 2,3;

3.2.3.4 where

注意：where 子句中不能使用字段别名。

3.2.3.4.1 比较运算

-- 过滤出 sal 等于 1000 的数据
select * from emp where sal = 1000;

-- 过滤出 sal 大于 1000 的数据
select * from emp where sal > 1000;

-- 查找 mgr 和 comm都为 NULL的数据
select * from emp where mgr <=> comm;

-- 过滤出 sal 不等于 1000 的数据
select * from emp where sal <> 1000;
select * from emp where sal != 1000;

-- 查询 sal 在 500 到 1000 的员工信息
select * from emp where sal between 500 and 1000;

-- 查询 comm 为空的所有员工信息
select * from emp where comm is null;

-- 查询工资是 1500 或 5000 或 2000 的员工信息
select * from emp where sal IN (1500, 5000, 2000);

3.2.3.4.2 逻辑运算

-- 查询 sal 大于 1000，deptno 是 30
select * from emp where sal > 1000 and deptno = 30;

-- 查询 sal 大于 1000，或者 deptno 是 30
select * from emp where sal > 1000 or deptno = 30;

-- 查询除了 deptno 是 20 和 30 以外的员工信息
select * from emp where deptno not IN(30, 20);

3.2.3.4.3 模糊匹配

-- 查询 ename 中有 M 的数据、% 表示匹配任意个任意字符
select * from emp where ename like "%M%";

-- 查询 ename 中第三个字符是 M 的数据。一个 _ 表示一个任意字符
select * from emp where ename like "__M%";

rlike

# rlike 匹配正则
select * from emp where ename rlike "^[M].*[L]{2}";

3.2.3.5 分组

-- 计算 emp 表每个部门的平均工资
select t.deptno, avg(t.sal) avg_sal
from emp t
group by t.deptno;

-- 计算 emp 每个部门中每个岗位的最高薪水
select t.deptno, t.job, max(t.sal) max_sal
from emp t 
group by t.deptno, t.job;

-- 求每个部门的平均薪水大于 2000 的部门
select deptno, avg(sal) avg_sal
from emp
group by deptno
having avg_sal > 2000;

3.2.3.6 join

inner（内）和 outer（外）都是可以省略的

内连接

-- 根据员工表和部门表中的部门编号相等，查询员工编号、员工名称和部门名称。
select e.empno, e.ename, d.deptno, d.dname
from emp e
inner join dept d
on e.deptno = d.deptno;

左外连接

select e.empno, e.ename, d.deptno
from emp e
left outer join dept d
on e.deptno = d.deptno;

右外连接

select e.empno, e.ename, d.deptno
from emp e
right outer join dept d
on e.deptno = d.deptno;

满外连接

select e.empno, e.ename, d.deptno
from emp e
full outer join dept d
on e.deptno = d.deptno;

3.2.3.7 排序

全局排序（order by）：只有一个ReduceTask

-- 查询员工信息按工资升序排列。默认升序（asc）
select * from emp order by sal;

-- 查询员工信息按工资降序排列
select * from emp order by sal desc;

-- 按照别名排序：按照员工薪水的 2 倍排序
select ename, sal*2 twosal from emp order by twosal;

-- 按照部门和工资升序排序
select ename, deptno, sal from emp order by deptno, sal;

分区排序（distribute by … sort by）：

-- 设置ReduceTask个数
set mapreduce.job.reduces=3;

-- 每个ReduceTask内部根据 deptno 倒序排序
-- 由于没有 distribute by 指定根据哪个字段进行分区，所以会随机分区
select * from emp
sort by deptno desc;

-- 先按照部门编号分区，再按照员工编号降序排序。
-- distribute by 指定分区字段即MapReduce中分区的 key，分区规则为默认分区规则（key的hashcode）
-- distribute by 必须在 sort by 之前
select * from emp
distribute by deptno
sort by empno desc;

-- 当 distribute by 的字段和 sort by 的字段都是同一个时，可以使用 cluster by
-- cluster by 不能指定排序方式，只能是升序
select * from emp
cluster by deptno;

select * from emp
distribute by deptno
sort by deptno;

3.2.3.8 拼接

将多条查询语句的查询结果上下垂直拼接在一起

union all：不去重

select e.empno, e.ename, d.deptno
from emp e
left outer join dept d
on e.deptno = d.deptno
union all
select e.empno, e.ename, d.deptno
from emp e
right outer join dept d
on e.deptno = d.deptno;

union：去重

select e.empno, e.ename, d.deptno
from emp e
left outer join dept d
on e.deptno = d.deptno
union
select e.empno, e.ename, d.deptno
from emp e
right outer join dept d
on e.deptno = d.deptno;

3.2.3.9 抽样

-- TABLESAMPLE(BUCKET x OUT OF y)：x 必须小于等于 y
-- 将表 student 中数据根据 id 分为 4 个桶，取第 1 个桶中的数据
select * from student tablesample(bucket 1 out of 4 on id);

-- 取 5 行数据
select * from emp tablesample(5 rows);

-- 取大概 10% 的数据。以 Hadoop 中的块为最小单位
select * from emp tablesample(0.1 percent);

3.2.4 删除

-- 清除表中数据。只能在管理表中使用
truncate table student;

3.3 其他

# 启动Metastore服务
hive --service metastore
# 启动hiveserver2服务
hive --service hiveserver2
# 直连
hive
# 执行HQL
hive -e "select count(id) from test;"
# 执行脚本 hive.sql
hive -f hive.sql
# 通过命令行配置单次启动的参数：配置ReduceTask的个数
hive --hiveconf mapred.reduce.tasks=10;

# 查看在 hive 中输入的所有历史命令
cat $HOME/.hivehistory

# 退出hive，也可以直接Ctrl + C
quit:
exit;

# 在 hive cli 命令窗口中查看 hdfs 文件系统
dfs -ls /user;

# 查看所有配置
set;
# 查看ReduceTask个数配置
set mapreduce.job.reduces;
# 设置ReduceTask个数
set mapreduce.job.reduces=3;

四分区表和分桶表

分区表和分桶表都是为了提高查询效率。将数据分开存储后，查询前先确定数据在哪个位置，从而避免查询所有数据。该思路类似于索引。

4.1 分区表

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，以提高查询效率。

4.1.1 操作分区表

-- 创建分区表
create table dept_partition(
    deptno int,
    dname string,
    loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

-- 分区表加载数据
load data local inpath '/root/datas/dept_20200403.log'
into table dept_partition
partition(day='20200403');

-- 分区查询
select * from dept_partition where day='20200401';

-- 创建单个分区
alter table dept_partition add partition(day='20200404');
-- 同时创建多个分区，各分区之间不能有逗号
alter table dept_partition add
partition(day='20200405')
partition(day='20200406');

-- 查看分区
show partitions dept_partition;

-- 删除单个分区
alter table dept_partition drop partition (day='20200404');
-- 同时删除多个分区，各分区之间必须有逗号
alter table dept_partition drop
partition (day='20200406'),
partition (day='20200405');

-- 创建二级分区表
create table dept_partition2(
    deptno int,
    dname string,
    loc string
)
-- 二级分区：即两个分区字段
partitioned by (day string, hour string)
row format delimited fields terminated by '\t';
-- 查看
select * from dept_partition2 where day='20200401' and hour='13';

4.1.2 分区表关联数据

上传数据后修复分区

-- 创建分区目录与上传数据
dfs -mkdir -p /user/hive/warehouse/test.db/dept_partition2/day=20200401/hour=13;
dfs -put /root/datas/dept_20200401.log /user/hive/warehouse/test.db/dept_partition2/day=20200401/hour=13;

-- 修复分区
msck repair table dept_partition2;

上传数据后添加分区

-- 创建分区目录与上传数据
dfs -mkdir -p /user/hive/warehouse/test.db/dept_partition2/day=20200401/hour=14;
dfs -put /root/datas/dept_20200401.log /user/hive/warehouse/test.db/dept_partition2/day=20200401/hour=14;

-- 添加分区
alter table dept_partition2 add partition(day='20200401',hour='14');

load 数据到分区

load data local inpath '/root/datas/dept_20200401.log'
into table dept_partition2
partition(day='20200401',hour='15');

4.1.3 动态分区调整

关系型数据库中，对分区表 Insert 数据时候，数据库自动会根据分区字段的值，将数据插入到相应的分区中，Hive 中也提供了类似的机制，即动态分区(Dynamic Partition)，只不过，使用 Hive 的动态分区，需要进行相应的配置。

动态分区加载数据：有几个分区字段，就会将数据倒数几列数据当做分区字段的值，来进行分区。

4.1.3.1 参数调整

# 开启动态分区功能（默认 true，开启）
set hive.exec.dynamic.partition=true;

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

# 在集群中所有执行 MR 的节点上，最大一共可以创建多少个动态分区。默认 1000
set hive.exec.max.dynamic.partitions=1000;

# 在每个执行 MR 的节点上，最大可以创建多少个动态分区。默认值 100
# 该参数需要根据实际的数据来设定。比如：源数据中包含了一年的数据，即 day 字段有 365 个值，
# 那么该参数就需要设置成大于 365，如果使用默认值 100，则会报错。
set hive.exec.max.dynamic.partitions.pernode=100;

# 整个 MR Job 中，最大可以创建多少个 HDFS 文件。默认 100000
set hive.exec.max.created.files=100000;

# 当有空分区生成时，是否抛出异常。一般不需要设置。默认 false
set hive.error.on.empty.partition=false;

4.1.3.2 案例

-- 创建分区表
create table dept_partition_dy(id int, name string) 
partitioned by (loc int) row format delimited fields terminated by '\t';

-- 开启动态分区
set hive.exec.dynamic.partition.mode = nonstrict;

-- 将 dept 表的查询结果，根据 loc 自动分区插入表 dept_partition_dy
insert into table dept_partition_dy partition(loc)
select deptno, dname, loc from dept;

-- 查看表 dept_partition_dy 的分区情况
show partitions dept_partition_dy;

4.2 分桶表

分区提供一个隔离数据和优化查询的便利方式。不过，并非所有的数据集都可形成合理的分区。对于一张表或者分区，Hive 可以进一步组织成桶，也就是更为细粒度的数据范围划分。

分桶是将数据集分解成更容易管理的若干部分的另一个技术。

分区针对的是数据的存储目录，一个分区一个目录；

分桶针对的是数据的存储文件，一个分桶一个文件。

分桶原理：桶数为ReduceTask的个数，然后将分桶字段作为MApReduce分区的 key 进行分区。使用默认分区方式，即根据 key 的哈希值除以ReduceTask个数取余。

4.2.1 操作分桶表

create table stu_buck(id int, name string)
clustered by(id) 
into 4 buckets
row format delimited fields terminated by '\t';

load data inpath '/datas/student.txt' into table stu_buck;

insert into table stu_buck select * from student_insert;

4.2.2 注意事项

reduce 的个数设置为-1，让 Job 自行决定需要用多少个 reduce ，否则必须将 reduce 的个数设置为大于等于分桶表的桶数；
分桶表不能手动将文件上传到 HDFS 中表所在的目录；
从 hdfs 中 load 数据到分桶表中，避免本地文件找不到问题；
不要使用本地模式。

五函数

5.1 内置函数

5.1.1 查看内置函数

-- 显示所有内置函数
show functions;
-- 查看是否存在某个函数
show functions abs;
-- 查看某个函数的用法
desc function abs;
-- 查看某个函数的用法的详细信息
desc function extended abs;

5.1.2 日期函数

unix_timestamp：日期转时间戳

-- 获取当前时间戳
select unix_timestamp();
-- 将日期转化为时间戳
select unix_timestamp('2021-04-05', 'yyyy-MM-dd');

from_unixtime：将时间戳转化为日期

select from_unixtime(1644462273);
select from_unixtime(1644462273, 'yyyy-MM-dd HH:mm:ss');

current_date：获取当前日期
```
select current_date;
```
current_timestamp：获取当前时间
```
select current_timestamp;
```
to_date：抽取日期部分
```
select to_date('2009-07-30 04:17:52');
```
year：抽取年
```
select year('2009-07-30 04:17:52');
```
month：抽取月
```
select month('2009-07-30 04:17:52');
```
day：抽取日
```
select day('2009-07-30 04:17:52');
```
hour：抽取时
```
select hour('2009-07-30 04:17:52');
```
minute：抽取分
```
select minute('2009-07-30 04:17:52');
```
second：抽取秒
```
select second('2009-07-30 04:17:52');
```
weekofyear：查看当前时间时一年中的第几周
```
select weekofyear('2009-07-30 04:17:52');
```
dayofmonth：查看当天是这个月中的第几天
```
select dayofmonth('2009-07-30 04:17:52');
```

last_day：当前日期或时间所在月份的最后一天的日期

-- 返回结果为日期
select last_day('2009-07-30');
select last_day('2009-07-30 04:17:52');

months_between：查看两个日期相差的月数

-- 第一个时间减去第二个时间，返回结果为小数
select months_between('2009-07-30 04:17:52', '2009-02-20 04:17:52');

datediff：查看两个日期相差的天数

-- 第一个时间减去第二个时间
select datediff('2009-07-30 04:17:52', '2009-02-20 04:17:52');

add_months：日期或时间加月数

-- 返回结果为日期
select add_months('2009-07-30 04:17:52', 2);
select add_months('2009-07-30', -2);

date_add：日期或时间加天数

-- 返回结果为日期
select date_add('2009-07-30 04:17:52', -50);
select date_add('2009-07-30', 50);

date_sub：日期或时间减天数

-- 返回结果为日期
select date_sub('2009-07-30 04:17:52', -50);
select date_sub('2009-07-30', 50);

date_add：

-- 返回结果为日期
select date_add('2009-07-30 04:17:52', -50);
select date_add('2009-07-30', 50);

date_format：格式化日期

select date_format('2009-07-30 04:17:52', 'yyyy/MM/dd hh/mm/ss');

5.1.3 数学函数

abs：取绝对值
```
select abs(-3.4);
```
round：四舍五入
```
select round(3.4);
select round(3.5);
```
ceil：向上取整
```
select ceil(3.4);
select ceil(3.5);
```
floor：向下取整
```
select floor(3.4);
select floor(3.5);
```

count：求行数

-- 求表的总行数（count）
select count(*) cnt from emp;

max：求列中的最大值

-- 求列 sal 的最大值（max）
select max(sal) max_sal from emp;

min：求列中的最小值

-- 求列 sal 的最小值（min）
select min(sal) min_sal from emp;

sum：对一列中的数据求和

-- 求列 sal 的总和（sum）
select sum(sal) sum_sal from emp;

avg：对一列中的数据求平均值

-- 求列 sal 的平均值（avg）
select avg(sal) avg_sal from emp;

5.1.4 字符串函数

concat：字符串拼接。类似于java中的append方法

-- 将字符串拼接起来，可以自动将其他数据类型转换为字符串
concat(str1, str2, ... strN)

SELECT concat('abc', 'def');
-- 将 empno 和 ename 的查询结果拼接起来，中间用冒号分隔
select concat(empno, ': ', ename) from emp;

concat_ws：字符串拼接。类似于java中的 String.join，但功能更强

-- 将后面的字符串、或者数组里面的字符串拼接起来，用第一个参数作为分隔符。会跳过 NULL
-- 不能自动将其他数据类型转换为字符串，需要手动转换：casr(id as string)
concat_ws(separator, [string | array(string)]+)

SELECT concat_ws('.', 'www', array('facebook', 'com'), NULL, '/');
-- 将 empno 和 ename 的查询结果拼接起来，中间用冒号分隔
select concat_ws(': ', ename, job) from emp;

split：字符串切割转数组。类似于java的split，但功能更强

-- 将 str 字符串根据 regex 进行分割转换为 array
split(str, regex)

-- 把字符串 oneAtwoBthreeC 根据 A 或 B 或 C 进行分隔
SELECT split('oneAtwoBthreeC', '[ABC]');

-- 一行数据转换为多行数据
-- split(category,",")：将 category 列中每行数据根据逗号切割转化为数组
-- explode(split(category,","))：将数组中的每个元素转换为单独一行数据
SELECT movie, category_name
FROM movie_info
lateral VIEW
explode(split(category,",")) movie_info_tmp AS category_name;

substring：字符串切割。类似于java中的substring。不同在于java中是从下标0开始，这里是从1开始

-- str 为要截取的字符串
-- pos 为开始截取的位置，正数为从左往右数，负数为从右往左数
-- len 为要截取的字符长度。可选，没有则表示截取到末尾
substring(str, pos[, len])

-- 从左往右数第五个字符开始往右截取，到末尾
SELECT substring('Facebook', 5);
-- 从右往左数第五个字符开始往右截取，到末尾
SELECT substring('Facebook', -5);
-- 从左往右数第五个字符开始往右截取，截取一个字符
SELECT substring('Facebook', 5, 1);

upper：转大写
```
select upper('Abc');
```
lower：转小写
```
select lower('Abc');
```
length：求字符串长度
```
select length('Abc');
```
trim：字符串去掉前后空格
```
select trim(' Abc ');
```
lpad：把目标字符串（第一个参数）用指定字符串（第三个参数）向左补齐到指定长度（第二个参数）
```
select lpad('Abc', 8, 'ab');
```
rpad：把目标字符串（第一个参数）用指定字符串（第三个参数）向右补齐到指定长度（第二个参数）
```
select rpad('Abc', 8, 'ab');
```
regexp_replace：替换。第一个参数为待处理的字符串，第二个参数为匹配的正则，第三个为替换用的字符串
```
select regexp_replace('2021/07/02', '/', '-');
```

5.1.5 集合函数

size：集合中元素的个数

select size(array('a', 'b', 'c', 'd', 'e'));
select size(map('a':'A', 'b':'B'));

map_keys：返回map中的key组成的数组
map_values：返回map中的values组成的数组

array_contains：判断数组中是否包含某个元素

select array_contains(array('a', 'b', 'c', 'd', 'e'), 'a');

sort_array：数组排序

select sort_array(array(3, 1, 2, 4));
select sort_array(array('d', 'a', 'c', 'b'));
select sort_array(array('zhangsan', 'lisi', 'wangwu', 'zhaoliu'));

collect_set：一列数据转换为一行数据，返回数组类型。去重
collect_list：一列数据转换为一行数据，返回数组类型。不去重
explode：将 hive 一列中复杂的 Array 或者 Map 结构拆分成多行。
```
select explode(array('a', 'b', 'c'));
```

5.1.6 多维分析

grouping sets()

select a, b, count(*) from demo group by a, b grouping sets(a, b, (a, b));

相当于

select a, NULL, count(*) from demo group by a
union
select NULL, b, count(*) from demo group by b
union
select a, b, count(*) from demo group by a, b;

5.1.7 判断函数

nvl：判空

-- 如果 value 的值为 NULL，则返回 default_value，否则返回 value
nvl(value,default_value)

SELECT nvl(null,'bla') FROM src LIMIT 1;
-- 查询 comm 列数据，将查询结果中的 NULL 用 -1 替换
select nvl(comm, -1) comm from emp;
-- 查询 comm 列数据，将查询结果中的 NULL 用 mgr 列对应行的值替换
select nvl(comm,mgr) comm from emp;

if：两个分支的判断

-- 如果第一个参数的判断结果为 true，则返回第二个参数，否则返回第三个参数
select if(1 = 1, 'a', 'b');
select if(1 = 2, 'a', 'b');

case：多分支判断，类似于java中的Switch语句

-- 当 a 等于 b，则返回 c，当 a 等于 d，则返回 e，否则返回 f
CASE a WHEN b THEN c [WHEN d THEN e]* [ELSE f] END

select
  case 'a' 
    when 'a' 
      then 1
    when 'c'
      then 3
    else 0 
  end,
  case 'b' 
    when 'a' 
      then 1 
    else 0 
  end;

-- 统计各部门的男女人数
select
dept_id,
sum(case sex when '男' then 1 else 0 end) male_count,
sum(case sex when '女' then 1 else 0 end) female_count
from emp_sex
group by dept_id;

5.1.8 侧写表

-- 用于和 split, explode 等 UDTF 一起使用，
-- explode 函数可以将一行array或map数据拆分为多行基本类型的数据，这时和其他列一起查询时，
-- 就会造成其他基本数据类型的列是一行数据，对应array或map类型拆分得到的多行数据，无法对应
-- lateral view 可以让其他基本数据类型的列的数据复制为多行，和array或map类型拆分得到的多行数据一一对应
LATERAL VIEW udtf(expression) tableAlias AS columnAlias

-- 一行数据转换为多行数据
-- split(category,",")：将 category 列中每行数据根据逗号切割转化为数组
-- explode(split(category,","))：将数组中的每个元素转换为单独一行数据
select movie, category_name
from movie_info
lateral view
explode(split(category,","))
-- 侧写表的名字
movie_info_tmp
-- explode(split(category,",")) 结果在侧写表中的列名
AS category_name;

5.1.9 开窗函数

作用为根据现有数据分析后，新增一列，用于存放统计分析结果

开窗：over()

-- count、sun等统计分析函数在分组后，是作用于组内的，over()开窗之后，就作用于窗口
-- over()：为每一条数据开启一个窗口，指定分析函数工作的数据窗口大小，
-- 这里的count(*)即统计分组之后name的行数
select name,count(*) over()
from business
where substring(orderdate,1,7) = '2017-04'
group by name;

根据分区进行开窗：over(partition by …)

-- partition by：分区，将分区字段（列）值相同的数据放入一个分区中，可以是多个字段。
-- 每条数据都会开启一个窗口，窗口数据默认为当前数据所在分区的所有数据
select 
  name,
  orderdate,
  cost,
  sum(cost) over(partition by name, substring(orderdate,1,7)) name_month_c
from business;

窗口排序：order by

-- 求每个客户（name）的购买数量（cost）随着日期（orderdate）累加的结果
-- order by orderdate：每个窗口内根据 orderdate 排序
-- 排序后，每个窗口的内容，默认是当前行所在分区从开始位置到当前行，而不是当前行所在分区的全部数据
-- 等价于下一个案例
select
  name,
  orderdate,
  cost,
  sum(cost) over(
      -- 分区
      partition by name
      -- 根据 orderdate 排序，默认窗口数据为从开始行到当前行
      order by orderdate)
from
  business;

指定窗口大小（行数）：rows between … and …

-- 求每个客户（name）的购买数量（cost）随着日期（orderdate）累加的结果
-- 等价于上一个（窗口排序）案例
-- CURRENT ROW：当前行
-- n PRECEDING：当前行往前 n 行数据
-- n FOLLOWING：当前行往后 n 行数据
-- UNBOUNDED PRECEDING：表示从前面的起点，
-- UNBOUNDED FOLLOWING：表示到后面的终点
select
  name,
  orderdate,
  cost,
  sum(cost) over(
      -- 窗口数据根据 name 分区
      partition by name
      -- 分区后排序
      order by orderdate
      -- 指定窗口数据范围：排序后从起始位置（UNBOUNDED PRECEDING）到当前行（CURRENT ROW）
      -- rows between必须在order by之后
      rows between unbounded preceding and current row)
from
  business;

将其他行的数据转换为当前行的数据（加一列）

select
  name,
  orderdate,
  cost,
  -- 当前行的前一行的 orderdate，可以有默认值
  lag(orderdate, 1) over(partition by name order by orderdate) before_one_row_date,
  -- 当前行的后一行的 orderdate，可以有默认值
  lead(orderdate, 1, '1970-01-01') over(partition by name order by orderdate) after_one_row_date
from
  business;

所有数据平均分组

-- 取所有数据的第一个 20% 的数据
select * from (
  select
    name,
    orderdate,
    cost,
    -- 开启一窗口，窗口包含所有数据，按照orderdate排序，之后分为 5 组，
    -- neile返回每行数据所属组的编号，即为每行数据添加一个分组字段sorted，值从 1 开始，
    -- n 必须为 int 类型
    ntile(5) over(order by orderdate) sorted
  from business
) t
where sorted = 1;

排名

-- 排序后增加一列，显示排名
select name,
  subject,
  score,
  -- 排序相同时会重复，总数不会变。如4个排名： 1 1 3 4
  rank() over(partition by subject order by score desc) rp,
  -- 排序相同时会重复，总数会减少。如4个排名：1 1 2 3
  dense_rank() over(partition by subject order by score desc) drp,
  -- 当前行的行号。如4个排名：1 2 3 4
  row_number() over(partition by subject order by score desc) rmp
from score;

5.2 自定义函数

5.2.1 函数分类

UDF（User-Defined-Function）：一进一出。如：year、substring等
UDAF（User-Defined Aggregation Function）：聚集函数，多进一出。如：count、max、min等
UDTF（User-Defined Table-Generating Functions）：一进多出。如：lateral view explode()

5.2.2 编程步骤

添加maven依赖

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>

继承 Hive 提供的类：

org.apache.hadoop.hive.ql.udf.generic.GenericUDF；

org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
实现类中的抽象方法

在 hive 的命令行窗口创建函数

永久函数：直接将jar包放入 %HADOOP_HOME%/lib 下即可。

临时函数：单次连接有效

# 添加 jar，linux_jar_path为jar包在Linux的路径
add jar linux_jar_path

# 创建 function
# temporary：表示临时函数
# function_name：函数名
# class_name：函数所在全类名
create [temporary] function [dbname.]function_name AS class_name;
# 在 hive 的命令行窗口删除函数
drop [temporary] function [if exists] [dbname.]function_name;

5.2.3 自定义 UDF 函数

求字符串长度

添加maven依赖

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>

编写函数底层

package com.guoli.hive.udf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

/**
 * 自定义求字符串长度
 *
 * @author guoli
 * @data 2022-02-14 20:06
 */
public class MyStringLengthUDF extends GenericUDF {
    /**
     * 初始化
     *
     * @param arguments 输入参数对应类型的鉴别器对象
     * @return 返回值类型的鉴别器对象
     * @throws UDFArgumentException UDF参数异常
     */
    @Override
    public ObjectInspector initialize(ObjectInspector[] arguments) throws UDFArgumentException {
        // 判断输入参数的个数
        if (arguments.length != 1) {
            throw new UDFArgumentLengthException("函数参数个数不正确!!!");
        }
        // 判断输入参数的类型
        if (!arguments[0].getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
            throw new UDFArgumentTypeException(0, "函数的参数类型不正确!!!");
        }
        // 函数本身返回值为 int，需要返回 int 类型的鉴别器对象
        return PrimitiveObjectInspectorFactory.javaIntObjectInspector;
    }

    /**
     * 核心处理逻辑
     * 求每个输入的字符串的长度
     *
     * @param arguments 输入参数
     * @return 函数的输出结果
     * @throws HiveException hive异常
     */
    @Override
    public Object evaluate(DeferredObject[] arguments) throws HiveException {
        if (arguments[0].get() == null) {
            return 0;
        }
        return arguments[0].get().toString().length();
    }

    @Override
    public String getDisplayString(String[] children) {
        return "";
    }
}

添加jar包

add jar /root/hivejar/hive-study-1.0-SNAPSHOT.jar;

创建临时函数

-- 创建临时函数 my_len
create temporary function my_len as "com.guoli.hive.udf.MyStringLengthUDF";

使用函数

select
  -- 自定义函数
  my_len("abcde"),
  -- 内置函数
  length("abcde");

5.2.4 自定义 UDTF 函数

引入依赖

<dependencies>
    <dependency>
        <groupId>org.apache.hive</groupId>
        <artifactId>hive-exec</artifactId>
        <version>3.1.2</version>
    </dependency>
</dependencies>

编写函数底层

package com.guoli.hive.udtf;

import org.apache.hadoop.hive.ql.exec.UDFArgumentException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentLengthException;
import org.apache.hadoop.hive.ql.exec.UDFArgumentTypeException;
import org.apache.hadoop.hive.ql.metadata.HiveException;
import org.apache.hadoop.hive.ql.udf.generic.GenericUDTF;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.ObjectInspectorFactory;
import org.apache.hadoop.hive.serde2.objectinspector.StructField;
import org.apache.hadoop.hive.serde2.objectinspector.StructObjectInspector;
import org.apache.hadoop.hive.serde2.objectinspector.primitive.PrimitiveObjectInspectorFactory;

import java.util.ArrayList;
import java.util.List;

/**
 * 将一个字符串根据指定的分隔符分割后，转换为多行数据输出
 *
 * @author guoli
 * @data 2022-02-15 14:13
 */
public class MySplit extends GenericUDTF {
    /**
     * 输出的集合
     */
    private final ArrayList<String> outList = new ArrayList<>();

    /**
     * 初始化
     *
     * @param argOIs 输入数据鉴别器对象
     * @return 输出结果的鉴别器对象
     * @throws UDFArgumentException UDF参数异常
     */
    @Override
    public StructObjectInspector initialize(StructObjectInspector argOIs) throws UDFArgumentException {
        // 1.校验参数个数
        List<? extends StructField> allStructFieldRefs = argOIs.getAllStructFieldRefs();
        if (allStructFieldRefs == null || allStructFieldRefs.size() != 2) {
            throw new UDFArgumentLengthException("参数个数不正确");
        }
        // 2.校验参数类型
        for (int i = 0; i < allStructFieldRefs.size(); i++) {
            StructField structField = allStructFieldRefs.get(i);
            if (!structField.getFieldObjectInspector().getCategory().equals(ObjectInspector.Category.PRIMITIVE)) {
                throw new UDFArgumentTypeException(i, "参数类型不正确");
            }
        }
        // 定义输出数据的默认列名和类型
        List<String> structFieldNames = new ArrayList<>();
        List<ObjectInspector> structFieldObjectInspectors = new ArrayList<>();
        // 添加输出数据的默认列名和类型
        structFieldNames.add("Word");
        structFieldObjectInspectors.add(PrimitiveObjectInspectorFactory.javaStringObjectInspector);
        return ObjectInspectorFactory.getStandardStructObjectInspector(structFieldNames, structFieldObjectInspectors);
    }

    /**
     * 核心处理逻辑
     *
     * @param args 输入的数据
     * @throws HiveException hive异常
     */
    @Override
    public void process(Object[] args) throws HiveException {
        if (args[0] == null) {
            outList.clear();
            forward(outList);
        } else {
            // 1.获取原始数据
            String arg = args[0].toString();
            // 2.获取数据传入的第二个参数，此处为分隔符
            String splitKey = args[1].toString();
            // 3.将原始数据按照传入的分隔符进行切分
            String[] fields = arg.split(splitKey);
            // 4.遍历切分后的结果，并写出。一次写出一个，即把一行数据转换为多行
            for (String field : fields) {
                // 集合为复用的，首先清空集合
                outList.clear();
                // 将每一个单词添加至集合
                outList.add(field);
                // 将集合内容写出
                forward(outList);
            }
        }
    }

    @Override
    public void close() throws HiveException {

    }
}

添加jar包

add jar /root/hivejar/hive-study-1.0-SNAPSHOT.jar;

创建临时函数

-- 创建临时函数 my_len
create temporary function my_split as "com.guoli.hive.udtf.MySplit";

使用函数

select my_split("hello,world,hive",",");

六压缩和存储

6.1 压缩

6.1.1 Map输出压缩

-- 开启 hive 中间传输数据压缩功能
set hive.exec.compress.intermediate=true;
-- 开启 mapreduce 中 map 输出压缩功能
set mapreduce.map.output.compress=true;
-- 设置 mapreduce 中 map 输出数据的压缩方式
set mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;
-- 查询
select count(ename) name from emp;

6.1.2 Reduce输出压缩

-- 开启 hive 最终输出数据压缩功能
set hive.exec.compress.output=true;
-- 开启 mapreduce 最终输出数据压缩
set mapreduce.output.fileoutputformat.compress=true;
-- 设置 mapreduce 最终数据输出压缩方式
set mapreduce.output.fileoutputformat.compress.codec
=org.apache.hadoop.io.compress.SnappyCodec;
-- 设置 mapreduce 最终数据输出压缩为块压缩
-- SequenceFile 输出使用的压缩类型：NONE 和 BLOCK
set mapreduce.output.fileoutputformat.compress.type=BLOCK;
-- 测试
insert overwrite local directory '/opt/module/data/distribute-result'
select * from emp distribute by deptno sort by empno desc;

6.2 存储

6.2.1 文件存储格式

Hive 支持的存储数据的格式主要有：TEXTFILE 、SEQUENCEFILE、ORC、PARQUET

TEXTFILE ：

默认格式，数据不做压缩，磁盘开销大，数据解析开销大。可结合 Gzip、Bzip2 使用，但使用 Gzip 这种方式，hive 不会对数据进行切分，从而无法对数据进行并行操作。
ORC：
Orc (Optimized Row Columnar)是 Hive 0.11 版里引入的新的存储格式。自带压缩，不能使用load加载数据。

如下图所示可以看到每个 Orc 文件由 1 个或多个 stripe 组成，每个 stripe 一般为 HDFS 的块大小，每一个 stripe 包含多条记录，这些记录按照列进行独立存储，对应到 Parquet 中的 row group 的概念。每个 Stripe 里有三部分组成，分别是 Index Data，Row Data，Stripe Footer：
- Index Data：一个轻量级的 index，默认是每隔 1W 行做一个索引。这里做的索引应该只是记录某行的各字段在 Row Data 中的 offset。
- Row Data：存的是具体的数据，先取部分行，然后对这些行按列进行存储。对每个列进行了编码，分成多个 Stream 来存储。
- Stripe Footer：存的是各个 Stream 的类型，长度等信息。
每个文件有一个 File Footer，这里面存的是每个 Stripe 的行数，每个 Column 的数据类型信息等；每个文件的尾部是一个 PostScript，这里面记录了整个文件的压缩类型以及 FileFooter 的长度信息等。在读取文件时，会 seek 到文件尾部读 PostScript，从里面解析到 File Footer 长度，再读 FileFooter，从里面解析到各个 Stripe 信息，再读各个 Stripe，即从后往前读。
PARQUET：
Parquet 文件是以二进制方式存储的，所以是不可以直接读取的，文件中包括该文件的数据和元数据，因此 Parquet 格式文件是自解析的。不能使用load加载数据。
- 行组(Row Group)：每一个行组包含一定的行数，在一个 HDFS 文件中至少存储一个行组，类似于 orc 的 stripe 的概念。
- 列块(Column Chunk)：在一个行组中每一列保存在一个列块中，行组中的所有列连续的存储在这个行组文件中。一个列块中的值都是相同类型的，不同的列块可能使用不同的算法进行压缩。
- 页(Page)：每一个列块划分为多个页，一个页是最小的编码的单位，在同一个列块的不同页可能使用不同的编码方式。
通常情况下，在存储 Parquet 数据的时候会按照 Block 大小设置行组的大小，由于一般情况下每一个 Mapper 任务处理数据的最小单位是一个 Block，这样可以把每一个行组由一个 Mapper 任务处理，增大任务执行并行度。Parquet 文件的格式。

上图展示了一个 Parquet 文件的内容，一个文件中可以存储多个行组，文件的首位都是该文件的 Magic Code，用于校验它是否是一个 Parquet 文件，Footer length 记录了文件元数据的大小，通过该值和文件长度可以计算出元数据的偏移量，文件的元数据中包括每一个行组的元数据信息和该文件存储数据的 Schema 信息。除了文件中每一个行组的元数据，每一页的开始都会存储该页的元数据，在 Parquet 中，有三种类型的页：数据页、字典页和索引页。数据页用于存储当前行组中该列的值，字典页存储该列值的编码字典，每一个列块中最多包含一个字典页，索引页用来存储当前行组下该列的索引，目前 Parquet 中还不支持索引页。

6.2.2 列式存储和行式存储

在这里插入图片描述

行式存储：查询满足条件的一整行数据的时候，列存储则需要去每个聚集的字段找到对应的每个列的值，行存储只需要找到其中一个值，其余的值都在相邻地方，所以此时行存储查询的速度更快。

TEXTFILE 和 SEQUENCEFILE 的存储格式都是基于行存储的；
列式存储：因为每个字段的数据聚集存储，在查询只需要少数几个字段的时候，能大大减少读取的数据量；每个字段的数据类型一定是相同的，列式存储可以针对性的设计更好的设计压缩算法。

ORC 和 PARQUET 是基于列式存储的。

七调优

7.1 执行计划

执行计划（explain）：分析SQL的执行过程，帮助优化SQL。其并不会真正执行SQL。

explain select * from emp;
explain extended select * from emp;

7.2 Fetch 抓取

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

在 hive-default.xml.template 文件中 hive.fetch.task.conversion 默认是 more，老版本 hive 默认是 minimal，

该属性修改为 more 以后，在全局查找、字段查找、limit 查找等都不走 mapreduce。

该属性修改为 none 以后，所有查找都需要走MR任务。

7.3 本地模式

大多数的 Hadoop Job 是需要 Hadoop 提供的完整的可扩展性来处理大数据集的。不过，有时 Hive 的输入数据量是非常小的。在这种情况下，为查询触发执行任务消耗的时间可能会比实际 job 的执行时间要多的多。对于大多数这种情况，Hive 可以通过本地模式在单台机器上处理所有的任务。对于小数据集，执行时间可以明显被缩短。

用户可以通过设置 hive.exec.mode.local.auto 的值为 true，来让 Hive 在适当的时候自动启动这个优化，默认是false。

hive 会自动根据 hive.exec.mode.local.auto.inputbytes.max 和 hive.exec.mode.local.auto.input.files.max 来判断是否启用本地模式，以保证本地资源是否满足执行MR。

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

7.4 表的优化

7.4.1 join

7.4.1.1 小表 join 大表

当小表join大表时，可以考虑使用Map Join，即在hadoop的MapReduce任务的Map阶段进行join，小表提前放入内存中，以提高效率。默认开启。

-- 设置自动选择 Mapjoin，默认为 true
set hive.auto.convert.join = true;
-- 大表小表的阈值设置（默认 23.8M 以下认为是小表）
set hive.mapjoin.smalltable.filesize = 25000000;

7.4.1.2 大表join大表

有时 join 超时是因为某些 key 对应的数据太多，而相同 key 对应的数据都会发送到相同的 reducer 上，从而导致内存不够。很多情况下，这些 key 对应的字段为空。

空 key 过滤：如果需求允许，则可以直接过滤掉空 key 的数据；
空 key 转换：如果需求不允许过滤数据，时我们可以将 key 为空的字段赋一个随机的值，使得数据随机均匀地分布到不同的 reducer 上。
```
insert overwrite table jointable
select n.* from nullidtable n full join bigtable o
on nvl(n.id,rand()) = o.id;
```

SMB(Sort Merge Bucket join)：桶join。为每张大表分别创建一个分桶表，达到类似索引的作用，这时再对分桶表进行join就不会扫描全部的数据，只会在对应的桶中进行join，以提高效率。

-- 1.创建分桶表
-- 创建分桶表时，分桶的个数不能大于可用CPU的核数

-- 2.设置参数
-- 默认false
set hive.optimize.bucketmapjoin = true;
-- 默认false
set hive.optimize.bucketmapjoin.sortedmerge = true;
-- 默认 org.apache.hadoop.hive.ql.io.CombineHiveInputFormat
set hive.input.format=org.apache.hadoop.hive.ql.io.BucketizedHiveInputFormat;

7.4.1.3 笛卡尔积

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

7.4.2 group by

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

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

-- 是否在 Map 端进行聚合，默认为 true
set hive.map.aggr = true;
-- 在 Map 端进行聚合操作的条目数目，默认为 100_000
set hive.groupby.mapaggr.checkinterval = 100000;
-- 有数据倾斜的时候进行负载均衡，默认是 false
set hive.groupby.skewindata = true;

开启负载均衡后，即 hive.groupby.skewindata 选项设定为 true，生成的查询计划会有两个 MR Job。第一个 MR Job 中，Map 的输出结果会随机分布到 Reduce 中，每个 Reduce 做部分聚合操作，并输出结果，这样处理的结果是相同的 Group By Key 有可能被分发到不同的 Reduce 中，从而达到负载均衡的目的；第二个 MR Job 再根据预处理的数据结果按照 Group By Key 分布到 Reduce 中（这个过程可以保证相同的 Group By Key 被分布到同一个 Reduce 中），最后完成最终的聚合操作。

7.4.3 Count(Distinct)

去重统计，数据量小的时候无所谓，数据量大的情况下，由于 COUNT DISTINCT 操作需要用一个 Reduce Task 来完成，这一个 Reduce 需要处理的数据量太大，就会导致整个 Job 很难完成，一般 COUNT DISTINCT 使用先 GROUP BY 再 COUNT 的方式替换，但是需要注意 group by 造成的数据倾斜问题.

select count(distinct id) from bigtable;
select count(id) from (select id from bigtable group by id) a;

7.4.4 行列过滤

只取自己需要的数据

列过滤：在 SELECT 中，只拿需要的列，如果有分区，尽量使用分区过滤，少用 SELECT *。
行过滤：先使用 WHERE 过滤之后再进行其他操作。比如在分区剪裁中，当使用外关联时，如果将副表的过滤条件写在 Where 后面，那么就会先全表关联，之后再过滤，
```
select o.id from bigtable b
join bigtable o on o.id = b.id
where o.id <= 10;

select b.id from bigtable b
join (select id from bigtable where id <= 10) o on b.id = o.id;
```

7.5 Map和Reduce个数

7.5.1 增加map数

当 input 的文件都很大，任务逻辑复杂，map 执行非常慢的时候，可以考虑增加 Map 数，来使得每个 map 处理的数据量减少，从而提高任务的执行效率。

-- 增加 map 的方法为：根据：
-- computeSliteSize(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式，
-- 调整 maxSize 最大值。让 maxSize 最大值低于 blocksize 就可以增加 map 的个数。
set mapreduce.input.fileinputformat.split.maxsize=100;

7.5.2 减少map数

在 map 执行前合并小文件，减少 map 数：

-- CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式）。
-- HiveInputFormat 没有对小文件合并功能。
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 在 map-only 任务结束时合并小文件，默认 true
set hive.merge.mapfiles = true;

-- 在 map-reduce 任务结束时合并小文件，默认 false
set hive.merge.mapredfiles = true;

-- 合并文件的大小，默认 256000000（约 244.1M）
set hive.merge.size.per.task = 268435456;

-- 当输出文件的平均大小小于该值时，启动一个独立的 map-reduce 任务进行文件 merge。默认 16000000（约 15M）
set hive.merge.smallfiles.avgsize = 16777216;

7.5.3 调整reduce数

过多的启动和初始化 reduce 也会消耗时间和资源；

另外，有多少个 reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题。在设置 reduce 个数的时候也需要考虑这两个原则：

处理大数据量利用合适的 reduce 数，使单个 reduce 任务处理数据量大小要合适；

方式一：

-- 每个 Reduce 处理的数据量默认是 256000000（约 244.1M）
set hive.exec.reducers.bytes.per.reducer=256000000;
-- 每个任务最大的 reduce 数，默认为 1009
set hive.exec.reducers.max=1009;
-- 计算公式
reduce个数 = Math.min(hive.exec.reducers.max, 输入数据大小 / hive.exec.reducers.bytes.per.reducer)

方式二：
```
set mapreduce.job.reduces = 15;
```

7.6 并行执行

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是 MapReduce 阶段、抽样阶段、合并阶段、limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下，Hive 一次只会执行一个阶段。不过，某个特定的 job 可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个 job 的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么 job 可能就越快完成。不过，在共享集群中，需要注意下，如果 job 中并行阶段增多，那么集群利用率就会增加。

-- /打开任务并行执行，默认 false
set hive.exec.parallel=true;
-- /同一个 sql 允许最大并行度，默认为 8。
set hive.exec.parallel.thread.number=16;

7.7 严格模式

Hive 可以通过设置防止一些危险操作。

分区表必须使用分区过滤：对于分区表，除非 where 语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
```
-- 开启。默认 false
set hive.strict.checks.no.partition.filter = true;
```
使用 order by 必须 limit 过滤：对于使用了 order by 语句的查询，要求必须使用 limit 语句。因为 order by 为了执行排序过程会将所有的结果数据分发到同一个 Reducer 中进行处理，强制要求用户增加这个 LIMIT 语句可以防止 Reducer 额外执行很长一段时间。
```
-- 启用。默认 false
set hive.strict.checks.orderby.no.limit = true;
```

禁用笛卡尔积：

-- 默认 false
set hive.strict.checks.cartesian.product = true;

e(Math.max(minSize,Math.min(maxSize,blocksize)))=blocksize=128M 公式，
– 调整 maxSize 最大值。让 maxSize 最大值低于 blocksize 就可以增加 map 的个数。
set mapreduce.input.fileinputformat.split.maxsize=100;


### 7.5.2 减少map数

> 在 map 执行前合并小文件，减少 map 数：

~~~sql
-- CombineHiveInputFormat 具有对小文件进行合并的功能（系统默认的格式）。
-- HiveInputFormat 没有对小文件合并功能。
set hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 在 map-only 任务结束时合并小文件，默认 true
set hive.merge.mapfiles = true;

-- 在 map-reduce 任务结束时合并小文件，默认 false
set hive.merge.mapredfiles = true;

-- 合并文件的大小，默认 256000000（约 244.1M）
set hive.merge.size.per.task = 268435456;

-- 当输出文件的平均大小小于该值时，启动一个独立的 map-reduce 任务进行文件 merge。默认 16000000（约 15M）
set hive.merge.smallfiles.avgsize = 16777216;

7.5.3 调整reduce数

过多的启动和初始化 reduce 也会消耗时间和资源；

另外，有多少个 reduce，就会有多少个输出文件，如果生成了很多个小文件，那么如果这些小文件作为下一个任务的输入，则也会出现小文件过多的问题。在设置 reduce 个数的时候也需要考虑这两个原则：

处理大数据量利用合适的 reduce 数，使单个 reduce 任务处理数据量大小要合适；

方式一：

-- 每个 Reduce 处理的数据量默认是 256000000（约 244.1M）
set hive.exec.reducers.bytes.per.reducer=256000000;
-- 每个任务最大的 reduce 数，默认为 1009
set hive.exec.reducers.max=1009;
-- 计算公式
reduce个数 = Math.min(hive.exec.reducers.max, 输入数据大小 / hive.exec.reducers.bytes.per.reducer)

方式二：
```
set mapreduce.job.reduces = 15;
```

7.6 并行执行

Hive 会将一个查询转化成一个或者多个阶段。这样的阶段可以是 MapReduce 阶段、抽样阶段、合并阶段、limit 阶段。或者 Hive 执行过程中可能需要的其他阶段。默认情况下，Hive 一次只会执行一个阶段。不过，某个特定的 job 可能包含众多的阶段，而这些阶段可能并非完全互相依赖的，也就是说有些阶段是可以并行执行的，这样可能使得整个 job 的执行时间缩短。不过，如果有更多的阶段可以并行执行，那么 job 可能就越快完成。不过，在共享集群中，需要注意下，如果 job 中并行阶段增多，那么集群利用率就会增加。

-- /打开任务并行执行，默认 false
set hive.exec.parallel=true;
-- /同一个 sql 允许最大并行度，默认为 8。
set hive.exec.parallel.thread.number=16;

7.7 严格模式

Hive 可以通过设置防止一些危险操作。

分区表必须使用分区过滤：对于分区表，除非 where 语句中含有分区字段过滤条件来限制范围，否则不允许执行。换句话说，就是用户不允许扫描所有分区。进行这个限制的原因是，通常分区表都拥有非常大的数据集，而且数据增加迅速。没有进行分区限制的查询可能会消耗令人不可接受的巨大资源来处理这个表。
```
-- 开启。默认 false
set hive.strict.checks.no.partition.filter = true;
```
使用 order by 必须 limit 过滤：对于使用了 order by 语句的查询，要求必须使用 limit 语句。因为 order by 为了执行排序过程会将所有的结果数据分发到同一个 Reducer 中进行处理，强制要求用户增加这个 LIMIT 语句可以防止 Reducer 额外执行很长一段时间。
```
-- 启用。默认 false
set hive.strict.checks.orderby.no.limit = true;
```

禁用笛卡尔积：

-- 默认 false
set hive.strict.checks.cartesian.product = true;

八 Tez引擎

Tez 是一个 Hive 的运行引擎，性能优于 MR。

用 Hive 直接编写 MR 程序，假设有四个有依赖关系的 MR 作业，上图中，绿色是 Reduce Task，云状表示写屏蔽，需要将中间结果持久化写到 HDFS。

Tez 可以将多个有依赖的作业转换为一个作业，这样只需写一次 HDFS，且中间节点较少，从而大大提升作业的计算性能。

8.1 下载

8.1.1 下载Tez

从官网选择tez版本：对于 Tez 0.8.3 及更高版本，Tez 需要 Apache Hadoop 为 2.6.0 或更高版本。对于 Tez 0.9.0 及更高版本，Tez 需要 Apache Hadoop 为 2.7.0 或更高版本。
下载：

8.1.2 下载maven

从maven官网下载
在这里插入图片描述

8.1.3 下载protobuf

根据Tez官网要求下载protobuf-2.5.0版本

从protobuf官网下载
在这里插入图片描述

8.2 安装

8.2.1 安装maven

# 上传后解压
tar -zxvf apache-maven-3.8.4-bin.tar.gz
cd apache-maven-3.8.4/conf/
# 修改本地仓库地址，使用阿里云镜像
vim settings.xml
# 配置maven环境变量
# 刷新配置
source /etc/profile
# 查看maven版本，验证
mvn -v

8.2.2 安装git

# 安装git
yum install git -y
# 查看git版本，验证
git --version

8.2.3 安装protobuf

# 安装protobuf环境
yum -y install autoconf automake libtool cmake ncurses-devel openssl-devel lzo-devel zlib-devel gcc gcc-c++
# 解压
tar -zxvf protobuf-2.5.0.tar.gz
cd protobuf-2.5.0
./autogen.sh
./configure
make
make check
make install

8.3 编译Tez

8.3.1 编译

tar -zxvf apache-tez-0.10.1-src.tar.gz
cd apache-tez-0.10.1-src
# 修改 pom.xml 中的 hadoop.version 为自己的hadoop版本，注释掉 tez-ui 模块
vim pom.xml
# 编译
mvn install -Dhadoop.version=3.3.1 -DskipTests -Dmaven.javadoc.skip=true
cd tez-dist/target/

8.3.2 问题

编译 tez-ui 模块报错：注释掉 tez-ui 模块

编译 tez-aux-services 时总是报错：不兼容的类型: com.google.protobuf.ByteString 无法转换为org.apache.hadoop.thirdparty.protobuf.ByteString

vim tez-plugins/tez-aux-services/src/main/java/org/apache/tez/auxservices/ShuffleHandler.java

# 将第800和801行的
.setIdentifier(ByteString.copyFrom(jobToken.getIdentifier()))
.setPassword(ByteString.copyFrom(jobToken.getPassword()))
# 替换为
.setIdentifier(TokenProto.getDefaultInstance().getIdentifier().copyFrom(jobToken.getIdentifier()))
.setPassword(TokenProto.getDefaultInstance().getPassword().copyFrom(jobToken.getPassword()))

8.4 配置

hadoop fs -mkdir /tez
hadoop fs -put tez-0.10.1.tar.gz /tez
hadoop fs -ls /tez
mkdir $HADOOP_HOME/tez
scp -r tez-0.10.1-minimal $HADOOP_HOME/tez

vim $HADOOP_HOME/etc/hadoop/tez-site.xml

<?xml version="1.0" encoding="UTF-8"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
    <property>
        <name>tez.lib.uris</name>
        <value>${fs.defaultFS}/tez/tez-0.10.1.tar.gz</value>
    </property>
    <property>
        <name>tez.use.cluster.hadoop-libs</name>
        <value>true</value>
    </property>
    <property>
        <name>tez.am.resource.memory.mb</name>
        <value>1024</value>
    </property>
    <property>
        <name>tez.am.resource.cpu.vcores</name>
        <value>1</value>
    </property>
    <property>
        <name>tez.container.max.java.heap.fraction</name>
        <value>0.4</value>
    </property>
    <property>
        <name>tez.task.resource.memory.mb</name>
        <value>1024</value>
    </property>
    <property>
        <name>tez.task.resource.cpu.vcores</name>
        <value>1</value>
    </property>
</configuration>

vim $HIVE_HOME/conf/hive-site.xml

<property>
    <name>hive.execution.engine</name>
    <value>tez</value>
</property>
<property>
    <name>hive.tez.container.size</name>
    <value>1024</value>
</property>

vim $HADOOP_HOME/etc/hadoop/shellprofile.d/tez.sh

hadoop_add_profile tez
function _tez_hadoop_classpath
{
 hadoop_add_classpath "$HADOOP_HOME/etc/hadoop" after
 hadoop_add_classpath "$HADOOP_HOME/tez/*" after
 hadoop_add_classpath "$HADOOP_HOME/tez/lib/*" after
}

8.5 问题

如果更换 Tez 引擎后，执行任务卡住，可以尝试调节容量调度器的资源调度策略

vim $HADOOP_HOME/etc/hadoop/capacity-scheduler.xml

<!-- 默认值为 0.1 -->
<property>
    <name>yarn.scheduler.capacity.maximum-am-resource-percent</name>
    <value>1</value>
    <description>
        Maximum percent of resources in the cluster which can be used to run
        application masters i.e. controls number of concurrent running
        applications.
    </description>
</property>

九解题思路

9.1 连续问题

id	dt
1	2021-10-10
1	2021-10-11
1	2021-10-12
1	2021-10-15
4	2021-10-10
4	2021-10-12

-- 排序后，生成等差数列
select
  id,
  dt,
  row_number() over(partition by id order by dt) rn
from
  test1;
  
-- 结果
+-----+-------------+-----+
| id  |     dt      | rn  |
+-----+-------------+-----+
| 1   | 2021-10-10  | 1   |
| 1   | 2021-10-11  | 2   |
| 1   | 2021-10-12  | 3   |
| 1   | 2021-10-15  | 4   |
| 4   | 2021-10-10  | 1   |
| 4   | 2021-10-12  | 2   |
+-----+-------------+-----+

-- 求差值
select
  id,
  dt,
  date_sub(dt,rn) dt_diff
from
  (select
    id,
    dt,
    row_number() over(partition by id order by dt) rn
  from
    test1) t1;
    
-- 结果
+-----+-------------+-------------+
| id  |     dt      |   dt_diff   |
+-----+-------------+-------------+
| 1   | 2021-10-10  | 2021-10-09  |
| 1   | 2021-10-11  | 2021-10-09  |
| 1   | 2021-10-12  | 2021-10-09  |
| 1   | 2021-10-15  | 2021-10-11  |
| 4   | 2021-10-10  | 2021-10-09  |
| 4   | 2021-10-12  | 2021-10-10  |
+-----+-------------+-------------+
    
-- 求连续登录天数
select
  id,
  count(*) ld
from
  (select
    id,
    dt,
    date_sub(dt,rn) dt_diff
  from
    (select
      id,
      dt,
      row_number() over(partition by id order by dt) rn
    from
      test1) t1) t2
group by
  id,dt_diff;
  
-- 结果
+-----+-----+
| id  | ld  |
+-----+-----+
| 1   | 3   |
| 1   | 1   |
| 4   | 1   |
| 4   | 1   |
+-----+-----+
  
-- 求最大连续登录天数
select
  id,
  max(ld) max_ld
from
  (select
    id,
    count(*) ld
  from
    (select
      id,
      dt,
      date_sub(dt,rn) dt_diff
    from
      (select
        id,
        dt,
        row_number() over(partition by id order by dt) rn
      from
        test1) t1) t2
    group by
      id,dt_diff) t3
group by id;

-- 结果
+-----+---------+
| id  | max_ld  |
+-----+---------+
| 1   | 3       |
| 4   | 1       |
+-----+---------+

9.2 同时在线问题

根据主播的上下线时间，统计最多有多少主播同时在线

思路：

将开播时间和停播时间合并为一列数据 dt;
新增一列num，开播为1，停播为-1；
根据时间 dt 排序；
从最开始行到当前行对 num 求和，即开播人数加一，停播人数减一；
求最大最大同时在线人数

id	start_time			end_time
1	2021-06-14 12:12:12	2021-06-14 18:12:12
3	2021-06-14 13:12:12	2021-06-14 16:12:12
4	2021-06-14 13:15:12	2021-06-14 20:12:12
2	2021-06-14 15:12:12	2021-06-14 16:12:12
5	2021-06-14 15:18:12	2021-06-14 20:12:12
1	2021-06-14 20:12:12	2021-06-14 23:12:12
6	2021-06-14 21:12:12	2021-06-14 23:15:12
7	2021-06-14 22:12:12	2021-06-14 23:10:12

-- 将开播时间和停播时间合并为一列数据 dt，新增一列num，开播为1，停播为-1;
select
  start_time as dt,
  1 as num
from
  test2
union
select
  end_time as dt,
  -1 as num
from
  test2;

-- 结果
+----------------------+----------+
|        _u1.dt        | _u1.num  |
+----------------------+----------+
| 2021-06-14 12:12:12  | 1        |
| 2021-06-14 13:12:12  | 1        |
| 2021-06-14 13:15:12  | 1        |
| 2021-06-14 15:12:12  | 1        |
| 2021-06-14 15:18:12  | 1        |
| 2021-06-14 16:12:12  | -1       |
| 2021-06-14 18:12:12  | -1       |
| 2021-06-14 20:12:12  | -1       |
| 2021-06-14 20:12:12  | 1        |
| 2021-06-14 21:12:12  | 1        |
| 2021-06-14 22:12:12  | 1        |
| 2021-06-14 23:10:12  | -1       |
| 2021-06-14 23:12:12  | -1       |
| 2021-06-14 23:15:12  | -1       |
+----------------------+----------+

-- 排序
select
  dt,
  sum(num) over(order by dt rows between unbounded preceding and current row) sum_num
from
  (select
    start_time as dt,
    1 as num
  from
    test2
  union
  select
    end_time as dt,
    -1 as num
  from
    test2) t1;
    
-- 结果
+----------------------+----------+
|          dt          | sum_num  |
+----------------------+----------+
| 2021-06-14 12:12:12  | 1        |
| 2021-06-14 13:12:12  | 2        |
| 2021-06-14 13:15:12  | 3        |
| 2021-06-14 15:12:12  | 4        |
| 2021-06-14 15:18:12  | 5        |
| 2021-06-14 16:12:12  | 4        |
| 2021-06-14 18:12:12  | 3        |
| 2021-06-14 20:12:12  | 2        |
| 2021-06-14 20:12:12  | 3        |
| 2021-06-14 21:12:12  | 4        |
| 2021-06-14 22:12:12  | 5        |
| 2021-06-14 23:10:12  | 4        |
| 2021-06-14 23:12:12  | 3        |
| 2021-06-14 23:15:12  | 2        |
+----------------------+----------+

-- 求最大在线人数
select
  max(sum_num) max_num
from
  (select
    id,
    dt,
    sum(num) over(order by dt rows between unbounded preceding and current row) sum_num
  from
    (select
      id,
      start_time as dt,
      1 as num
    from
      test2
    union
    select
      id,
      end_time as dt,
      -1 as num
    from
      test2) t1) t2;
      
-- 结果
+----------+
| max_num  |
+----------+
| 5        |
+----------+