初识Hive（2）

一、引言

在上一篇博客中，我们学习了Hive的基础架构、安装搭建以及基础的DDL（数据定义）和DML（数据操作）。我们知道了如何建表、如何加载数据、如何写简单的SQL。

但面对企业级海量数据时，我们会遇到新的问题：

• 一张表存了几十亿条数据，每次查询都要全表扫描，效率极低怎么办？

• 文本文件存储占用空间大，读取速度慢，有没有更好的存储格式？

• 业务需求越来越复杂，需要计算排名、环比、累加和，SQL怎么写？

为了解决这些问题，本篇博客将带你进入Hive的进阶世界。我们将深入学习分区表与分桶表（优化查询效率）、文件格式与压缩（优化存储）、复杂查询（Join与排序），以及Hive中最强大的窗口函数。

二、Hive数据组织方式进阶

2.1 分区表（Partition）——按目录拆分数据

概念：分区就是把一张大表的数据按照业务需求分散地存储到多个目录，每个目录成为该表的一个分区。查询时通过 WHERE 子句指定分区，可以避免全表扫描。

语法：

CREATE TABLE dept_partition(
deptno INT COMMENT '部门编号',
dname STRING COMMENT '部门名称',
loc STRING COMMENT '部门位置'
)
PARTITIONED BY (day STRING COMMENT '天分区字段')
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\\t';

基本操作：

— 1. 添加分区
ALTER TABLE dept_partition ADD PARTITION(day='20250830');

— 2. 删除分区
ALTER TABLE dept_partition DROP PARTITION(day='20250830');

— 3. 查看分区
SHOW PARTITIONS dept_partition;

— 4. 修复分区（当HDFS上手动创建了目录，元数据未同步时）
MSCK REPAIR TABLE dept_partition;

二级分区：如果一天的数据量也很大，可以继续按小时拆分：

PARTITIONED BY (day STRING, hour STRING)

动态分区：
向分区表插入数据时，如果不确定有哪些分区，可以让Hive根据数据最后一列的值自动创建分区。

— 开启动态分区并设为非严格模式
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;

— 插入数据，最后一个字段 loc 自动作为分区字段
INSERT INTO TABLE dept_partition_dynamic PARTITION(loc)
SELECT deptno, dname, loc FROM dept;

2.2 分桶表（Bucket）——按文件拆分数据

概念：分区针对的是目录，而分桶针对的是文件。分桶是对数据进行更细粒度的划分。Hive会对分桶字段求Hash值，然后对分桶数量取模，结果相同的行写入同一个文件。

语法：

CREATE TABLE stu_buck(
id STRING,
name STRING,
age INT
)
CLUSTERED BY(id) INTO 4 BUCKETS — 按id分成4个桶
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

分桶排序表：在分桶的基础上，让桶内的数据有序。

CLUSTERED BY(id) SORTED BY(id) INTO 4 BUCKETS

三、Hive文件格式与压缩

3.1 存储格式对比

Hive支持多种文件格式，选择合适的格式能大幅提升性能。

列式存储的优势：查询时只读取需要的列，跳过无关列，极大减少IO。

建表指定格式：

STORED AS ORC
— 或者
STORED AS PARQUET

3.2 数据压缩

大数据场景下，压缩是必选项。常见的压缩算法有 Gzip、Snappy、Lzo 等。

在Hive中配置压缩：

• TextFile：通过设置参数开启输出压缩。

• ORC：建表时指定 TBLPROPERTIES("orc.compress"='SNAPPY')。

• Parquet：建表时指定 TBLPROPERTIES("parquet.compression"='SNAPPY')。

四、Hive复杂查询操作

4.1 基础查询与过滤

SELECT * FROM emp WHERE sal > 8000 AND deptno = 30;

4.2 分组与聚合

— 统计每个部门的平均工资，并筛选出平均工资大于4000的部门
SELECT deptno, AVG(sal) AS avg_sal
FROM emp
GROUP BY deptno
HAVING avg_sal > 4000;

注：WHERE 在分组前过滤，不能跟聚合函数；HAVING 在分组后过滤，可以使用聚合函数。

4.3 Join 多表连接

Hive支持标准SQL的连接操作：

— 内连接 (交集)
SELECT e.ename, d.dname FROM emp e JOIN dept d ON e.deptno = d.deptno;

— 左外连接 (左表全保留)
SELECT e.ename, d.dname FROM emp e LEFT JOIN dept d ON e.deptno = d.deptno;

— 满外连接 (Hive特有，MySQL不支持)
SELECT e.ename, d.dname FROM emp e FULL JOIN dept d ON e.deptno = d.deptno;

4.4 四大排序机制（重点）

Hive的排序非常讲究，分为以下四种：

— 按部门编号分区，每个区内按薪资降序排列
SELECT * FROM emp DISTRIBUTE BY deptno SORT BY sal DESC;

五、Hive内置函数大全

Hive函数分为四大类：单行函数、聚合函数、炸裂函数、窗口函数。

5.1 单行函数（一进一出）

• 字符串：substring() 截取、concat_ws() 拼接、regexp_replace() 正则替换、get_json_object() 解析JSON。

• 日期：date_add() 加天数、datediff() 日期间隔、date_format() 格式化。

• 流程控制：if() 条件判断、CASE WHEN 分支。

5.2 集合函数

Hive支持三种复杂数据类型：array (数组)、map (键值对)、struct (结构体)。

— 创建并访问 map
SELECT map('name','张三', 'age', 18);
SELECT students['name'] FROM test_tbl;

5.3 炸裂函数（UDTF – 一进多出）

explode() 是最常用的炸裂函数，可以将数组或Map中的元素拆分成多行。
典型应用：WordCount（单词统计）与行转列

— 将一行逗号分隔的字符串拆分成多行单词
SELECT explode(split(line, ',')) AS word FROM lines;

— 配合 LATERAL VIEW 进行侧写展开
SELECT clazz, sex
FROM clazz_sex
LATERAL VIEW explode(split(sexs, '|')) T AS sex;

六、Hive的“王炸”——窗口函数

窗口函数是Hive中非常强大的功能。它能为每行数据划分一个“窗口”（计算范围），对窗口内的数据进行计算，然后将结果返回给该行。

基础语法：

函数名() OVER (
[PARTITION BY 分区字段] — 分组
[ORDER BY 排序字段] — 排序
[ROWS | RANGE BETWEEN … AND …] — 窗口范围
)

6.1 窗口范围 (Window Frame)

边界关键字：

• UNBOUNDED PRECEDING：无边界起点（从第一行开始）

• CURRENT ROW：当前行

• UNBOUNDED FOLLOWING：无边界终点（到最后一行）

6.2 常用窗口函数实战

1. 累加求和 / 滑动窗口

— 计算截止到当前日期的总销售额（默认从第一行累加到当前行）
SELECT
order_date,
order_sal,
SUM(order_sal) OVER (ORDER BY order_date) AS total_sal
FROM orders;

2. 偏移函数：LAG / LEAD
获取当前行前后第 N 行的值（常用于计算环比、同比）。

SELECT
order_date,
— 获取上一行的日期（如果不存在，默认填1970-01-01）
LAG(order_date, 1, '1970-01-01') OVER (ORDER BY order_date) AS last_date,
— 获取下一行的日期
LEAD(order_date, 1, '9999-12-31') OVER (ORDER BY order_date) AS next_date
FROM orders;

3. 首尾函数：FIRST_VALUE / LAST_VALUE
获取窗口内第一个或最后一个值。

SELECT
order_id,
order_date,
FIRST_VALUE(order_date, false) OVER (ORDER BY order_date) AS first_date,
LAST_VALUE(order_date, false) OVER (ORDER BY order_date) AS last_date
FROM emp;

4. 排名函数：RANK / DENSE_RANK / ROW_NUMBER

这是常用的函数。假设有学生成绩：99, 98, 95, 95, 90。

SELECT
stu_id,
score,
RANK() OVER (ORDER BY score DESC) AS rk,
DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rk,
ROW_NUMBER() OVER (ORDER BY score DESC) AS rm
FROM scores;

七、总结

本篇博客从数据存储和查询分析两个维度，深入讲解了Hive的进阶用法：