Apache Hive 是一个分布式、容错的数据仓库系统，能够大规模进行分析。Hive 元数据存储（HMS）提供了一个中央元数据存储库，可轻松进行分析以做出明智的、数据驱动的决策，因此它是许多数据湖架构的关键组件。Hive 构建于 Apache Hadoop 之上，并通过 HDFS 支持在 S3、adls、gs 等上的存储。Hive 允许用户使用 SQL 读取、写入和管理 PB 级的数据。

Hive 分区表

https://juejin.cn/post/7040689938521653285

概念

Hive 中的表对应为 HDFS 上的指定目录，在查询数据时候，默认会对全表进行扫描，这样时间和性能的消耗都非常大。分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件。Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 WHERE 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

意义：

分区的目的是为了就数据，分散到多个子目录中，在执行查询时，可以只选择查询某些子目录中的数据，加快查询效率；
只有分区表才有子目录(分区目录)；
分区目录的名称由两部分确定：分区列列名=分区列列值；
将数据导入到指定的分区之后，元信息表中会附加上分区列的信息；
分区的最终目的是在查询时，使用分区列进行过滤。

使用场景

通常，在管理大规模数据集的时候都需要进行分区，比如将日志文件按天进行分区，从而保证数据细粒度的划分，使得查询性能得到提升。

分区表基本操作

静态分区：

可以根据 PARTITIONED BY 创建分区表，一个表可以拥有一个或者多个分区，每个分区以文件夹的形式单独存在表文件夹的目录下。
分区是以字段的形式在表结构中存在，通过 describe table 命令可以查看到字段存在，但是该字段不存放实际的数据内容，仅仅是分区的表示。
分区建表分为 2 种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

单级分区：

-- （1） 创建分区表语法：注意：分区字段不能是表中已经存在的数据，可以将分区字段看作表的伪列。
create table dept_partition(
deptno int, dname string, loc string
)
partitioned by (day string)
row format delimited fields terminated by '\t';

--（2）查看分区表结构：
describe formatted dept_partition;

-- （3）加载数据：注意：分区表加载数据时，必须指定分区，如果不指定会生成一个默认分区
0: jdbc:hive2://hadoop001:10000> load data local inpath
'/home/hadoop/datas/dept/dept_01.txt' into table dept_partition
partition(day='20211205');

0: jdbc:hive2://hadoop001:10000> load data local inpath
'/home/hadoop/datas/dept/dept_02.txt' into table dept_partition
partition(day='20211206');

0: jdbc:hive2://hadoop001:10000> load data local inpath
'/home/hadoop/datas/dept/dept_03.txt' into table dept_partition
partition(day='20211207');

--（4）查询分区表中数据
-- 单分区查询：
select * from dept_partition where day='20211205';
-- 多分区联合查询：
-- 方式一
select * from dept_partition where day='20211205'
union
select * from dept_partition where day='20211206'
union
select * from dept_partition where day='20211207';

-- 方式二
select * from dept_partition where day='20211205' or day='20211206' or day='20211207';

--（5）增加分区
-- 创建单个分区
alter table dept_partition add partition(day='20211208');
-- 同时创建多个分区；注意：各个分区中间用空格隔开
alter table dept_partition add partition(day='20211209') partition(day='20211210') partition(day='20211211');

-- （6）删除分区
-- 删除单个分区
alter table dept_partition drop partition (day='20211211');
-- 同时删除多个分区；注意：各个分区中间用空逗号隔开
alter table dept_partition drop partition (day='20211209'),partition (day='20211210');

--（7）查看分区表中分区数
show partitions dept_partition;
-- 运行结果
+---------------+
|   partition   |
+---------------+
| day=20211205  |
| day=20211206  |
| day=20211207  |
| day=20211208  |
+---------------+

-- （8）重命名分区
alter table default.dept_partition partition(day = '20211208') rename to partition(day = '20211209');

二级分区：

--（1）创建二级分区表
create table dept_partition2(
 deptno int, dname string, loc string
 )
 partitioned by (day string, hour string)
 row format delimited fields terminated by '\t';

-- （2）加载数据到二级分区表
load data local inpath
'/home/hadoop/datas/dept/dept_01.txt' into table
dept_partition2 partition(day='20211205', hour='10');

load data local inpath
'/home/hadoop/datas/dept/dept_02.txt' into table
dept_partition2 partition(day='20211205', hour='11');

load data local inpath
'/home/hadoop/datas/dept/dept_03.txt' into table
dept_partition2 partition(day='20211206', hour='10');

-- （3）新增分区sql代码解读复制代码
alter table  dept_partition2 add partition(day='20211206',hour = '11');

-- （4）查询分区数据
select * from dept_partition2 where day='20211205' and hour='10';
-- 多条件查询
select * from dept_partition2 where (day='20211205' and hour='10') or (day='20211206' and hour='10');

--（5）重命名分区
alter table dept_partition2 partition(day='20211206',hour='11') rename to partition(day='20211206',hour='12');

修复分区:
把数据直接上传到分区目录上，让分区表和数据产生关联的三种方式:
（1）方式一：上传数据后修复

（2）方式二：上传数据后添加分区

（3）方式三：创建文件夹后 load 数据到分区

动态分区

上面我们测试静态分区的时候，可以看到操作分区表的时候一定要指定分区，动态分区就解决了这个问题，只不过，使用 Hive 的动态分区，需要进行相应的配置。动态分区的值是非确定的，由输入数据来确定。

案例实操：将 dept 表中的数据按照地区（loc 字段），插入到目标表 dept_partition 的相应分区中。

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

--（2）添加数据
insert into table dept_partition_dy partition(loc) select deptno, dname, loc from dept_partition;

--（3）查看目标分区表的分区情况
show partitions dept_partition_dy;

总结

尽量不要用动态分区，因为动态分区的时候，将会为每一个分区分配 reducer 数量，当分区数量多的时候，reducer 数量将会增加，对服务器是一种灾难；
动态分区和静态分区的区别：
1、静态分区不管有没有数据都将会创建该分区；
2、动态分区是有结果集将创建，否则不创建。
hive 提供的的严格模式：为了阻止用户不小心提交恶意 hql：
hive.mapred.mode=nostrict : strict
如果该模式值为 strict，将会阻止以下三种查询：
对分区表查询，where 中过滤字段不是分区字段;
笛卡尔积 join 查询，join 查询语句，不带 on 条件或者 where 条件;
对 order by 查询，有 order by 的查询不带 limit 语句。

ROW_NUMBER() OVER 语法以及示例

语法:ROW NUMBERO OVER(PARTITION BY COLUMNORDER BY COLUMN)详解:
row number() OVER (PARTITION BY COL1 ORDERBY COL2)表示根据 COL1 分组，在分组内部根据 COL2 排序，而此函数计算的值就表示每组内部排序后的顺序编号(该编号在组内是连续并且唯一的)。
场景描述:
在 Hive 中 emplovee 表包括 empid、depid、salary 三个字段，根据部门分组，显示每个部门的工资等级
1、原表查看:在 Hive 中 employee 表及其内容如下所示:

NVL 函数

NVL(expr1, expr2)：
1、空值转换函数；
2、类似于 mysql-ifnull(expr1, expr2)，sqlserver-ifnull(expr1, expr2)。

备注：
1、如果 expr1 为 NULL，返回值为 expr2，否则返回 expr1。
2、适用于数字型、字符型和日期型，但是 expr1 和 expr2 的数据类型必须为同类型。

with as 优化 SQL

with as 也叫做子查询部分，首先定义一个 sql 片段，该 sql 片段会被整个 sql 语句所用到，为了让 sql 语句的可读性更高些，作为提供数据的部分，也常常用在 union 等集合操作中。
with as 就类似于一个视图或临时表，可以用来存储一部分的 sql 语句作为别名，不同的是 with as 属于一次性的，而且必须要和其他 sql 一起使用才可以！
其最大的好处就是适当的提高代码可读性，而且如果 with 子句在后面要多次使用到，这可以大大的简化 SQL；更重要的是：一次分析，多次使用，这也是为什么会提供性能的地方，达到了“少读”的目标。

WITH t1 AS (
		SELECT *
		FROM carinfo
	),
	t2 AS (
		SELECT *
		FROM car_blacklist
	)
SELECT *
FROM t1, t2

over()函数用法

https://blog.csdn.net/weixin_42542689/article/details/127409312

1、排序函数（row_number() over() 或者 rank() over()）
over 中的 order by 只起到窗口内排序作用。

**先按uid分区再按score降序排序
SELECT
    uid,
    score,
    row_number() OVER(PARTITION BY uid ORDER BY score desc) AS row_num
FROM exam_record

2、聚合函数（max() over() 或者 min/count）
over 中的 order by 不仅起到窗口内排序，还起到窗口内从当前行到之前所有行的聚合（多了一个范围）

SELECT
    uid,
    score,
    avg(score) OVER(ORDER BY score desc) AS avg_score
FROM exam_record

HIVE