一、Hive 到底是什么

Hive 说白了就是一个把 SQL 翻译成分布式计算任务的工具。你写一段看起来像 MySQL 的 SQL，Hive 帮你把它转成 Spark 或者 Tez 能执行的任务，然后调度到集群上去跑，最后把结果返回给你。

你不需要关心底层是怎么拆任务、怎么分发到几十台机器上跑的，就像你在 MySQL 里查表一样写 Hive SQL 就行。

1.1 它和 MySQL 的区别

这个我面试的时候被问过无数次，每次都有人答得云里雾里。

Hive MySQL
数据存哪 HDFS / S3 / OSS（分布式文件系统） 本地磁盘文件（.ibd / .MYD）
查询引擎 翻译成 Spark / Tez 任务执行 自己直接读磁盘执行
数据量 GB～ PB 级别起步，不怕数据大 GB 级别，数据太大就跪了
延迟 分钟到小时级别（离线分析） 毫秒到秒级别（实时查询）
事务支持 Hive 3.x 开始支持 ACID，但生产上很少用 成熟完善，日常必备
索引 基本靠 ORC 文件的 min/max 做谓词下推 B+Tree 索引，非常成熟

记住一句话：Hive 是做离线数据分析的，不是用来替代 MySQL 做业务系统的。你要是在 Hive 里跑个 SELECT * WHERE id = 123 想毫秒级返回，那属于找错工具了。

1.2 它和 Hadoop 的关系

很多人搞不清 Hive 和 Hadoop 到底是啥关系。简单说：

  • HDFS：分布式文件系统，你的数据文件实际存在这里（也可以存在 S3 上，不强制 HDFS）。
  • YARN：资源调度器，管着集群上哪些机器有空闲资源可以跑任务。
  • Hive：提供 SQL 接口和元数据管理，SQL 解析完后交给 Spark/Tez 去跑，Spark/Tez 再向 YARN 申请资源。

所以 Hive 本身不存储数据，也不直接执行任务。它更像是一个"翻译官 + 目录管理员"。

二、Hive 核心架构

下面这张图是 Hive 3.x 生产环境的标准部署架构，看懂了这张图，你对 Hive 的整体工作原理就把握得差不多了。

Hive 核心架构图

2.1 各组件是干嘛的

HiveServer2（HS2）

这是你跟 Hive 打交道的主要入口。Beeline、Hue、Python 脚本、BI 工具（Tableau、FineBI 等），都是通过 JDBC/ODBC 协议连到 HiveServer2。它负责接收你的 SQL，做语法解析、逻辑优化（CBO，基于代价的优化器），生成物理执行计划，然后交给底层的执行引擎（Spark/Tez）去跑。

Metastore（元数据服务）

这是一个独立的 Thrift 服务，管理着表名、字段、分区、数据文件位置等元数据信息。元数据本身存在 MySQL 或 PostgreSQL 这类关系型数据库里（Hive 自带的 Derby 只能单用户使用，生产环境绝对不能用）。

踩坑：Metastore 挂了，你啥表都查不了，因为 Hive 连表结构都不知道。生产环境 Metastore 一定要做高可用（至少部署两台，前面挂负载均衡）。

执行引擎（Spark / Tez）

Hive 3.x 开始，MapReduce 已经被官方弃用了。生产环境现在基本是二选一：

  • Hive on Spark：用 Spark 的 DAG 调度能力来执行 Hive SQL，性能比老旧的 MapReduce 好得多，社区支持也最活跃。
  • Hive on Tez：Hortonworks 推出来的 DAG 计算框架，延迟比 Spark 更低，适合交互式查询场景（搭配 LLAP 效果更佳）。

LLAP（Live Long and Process）

Hive 2.0 引入的特性，原理是在每个 NodeManager 上常驻一个守护进程，缓存热点数据和元数据。当你反复查询同一张表的时候，LLAP 可以直接从内存里返回结果，不用再走一遍完整的任务调度流程。对于 BI 工具那种点一下等几秒的场景，LLAP 提升非常明显。

LLAP 适合的场景：数据仓库上层做 Ad-hoc 查询、BI 报表。如果你的任务是半夜跑批处理 ETL，LLAP 没什么帮助，因为每次查的数据不一样，缓存命中率低。

2.2 部署模式：一定要用远程模式

Hive 的 Metastore 有三种部署模式，生产环境只有一种正确选择：

模式 说明 能不能上生产
内嵌模式 Metastore 和 HiveServer2 在同一个进程里，元数据存 Derby 不能，单用户且不稳定
本地模式 Metastore 单独进程，但仍然跟 HiveServer2 在同一台机器 不能，单机故障就全挂
远程模式 Metastore 作为独立服务部署，多客户端共享 唯一正确选择

远程模式的核心配置就这几项：

# hive-site.xml
hive.metastore.uris=thrift://node1:9083,thrift://node2:9083
hive.metastore.warehouse.dir=/user/hive/warehouse

MySQL 连接信息也要配好：

<property>
  <name>javax.jdo.option.ConnectionURL</name>
  <value>jdbc:mysql://mysql_host:3306/hive_metastore?createDatabaseIfNotExist=true</value>
</property>
<property>
  <name>javax.jdo.option.ConnectionDriverName</name>
  <value>com.mysql.cj.jdbc.Driver</value>
</property>

踩坑：MySQL 连接超时设置要调大（默认 30 秒在任务高峰期很容易断），建议 ConnectionURL 里加上 ?connectTimeout=60000&socketTimeout=60000。另外 MySQL 8 一定要用 com.mysql.cj.jdbc.Driver，老版的 com.mysql.jdbc.Driver 已经废弃了。

三、DDL：建表那些事儿

Hive 的建表语法跟 MySQL 很像，但背后原理完全不同。理解这点，后面很多问题就好解释了。

3.1 数据类型

Hive 的数据类型分两大类：

基础类型（日常 90% 的场景就用这几个）：

类型 说明 示例
INT 4字节整数 42
BIGINT 8字节整数 9223372036854775807
DOUBLE 双精度浮点 3.14159
STRING 变长字符串 ‘hello’
VARCHAR(n) 定长字符串 ‘hello’（超过 n 会截断）
TIMESTAMP 时间戳 ‘2024-01-01 00:00:00’
DATE 日期 ‘2024-01-01’

复杂类型（处理嵌套结构时用到）：

类型 说明 示例
ARRAY<T> 数组 ['a', 'b', 'c']
MAP<K,V> 键值对 {"k1":"v1", "k2":"v2"}
STRUCT<...> 结构体 {street:'xxx', city:'bj'}

踩坑点：Hive 的 STRING 没有长度限制，VARCHAR 有。如果你用 VARCHAR(10) 存了超过 10 个字符的数据，Hive 会静默截断，不会报错。如果你不希望数据被截断，直接用 STRING。

3.2 分隔符声明

Hive 默认字段分隔符是 \001（ASCII 里的 SOH 控制字符），但通常我们建表时会显式指定：

CREATE TABLE user_log (
  id INT,
  name STRING,
  age INT
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'   -- 字段用 Tab 分隔
LINES TERMINATED BY '\n';   -- 行用换行分隔

如果你用的是 ORC 或 Parquet 格式，不需要指定分隔符，因为这些格式自己内部管理数据结构。

3.3 内部表 vs 外部表

这是 Hive 建表里最重要的概念之一，很多人刚开始用的时候都在这里栽过跟头。

内部表 vs 外部表

内部表（Managed Table）：

Hive 既管理元数据，也管理数据文件。数据文件存放在 Hive 的仓库目录下（默认 /user/hive/warehouse）。你执行 DROP TABLE 的时候，元数据和数据文件会一起删掉。

外部表（External Table）：

Hive 只管理元数据，数据文件的实际位置由你指定，可以是 HDFS 上的任意路径。执行 DROP TABLE 时，只删元数据，不碰数据文件。

-- 内部表
CREATE TABLE internal_table (id INT, name STRING);

-- 外部表
CREATE EXTERNAL TABLE external_table (id INT, name STRING)
LOCATION '/data/external/user_data';

踩坑点：如果数据不是你通过 Hive 产生的，而是上游系统（比如 Flume、Spark Streaming、Flink）写到 HDFS 上的，一定要建外部表。否则哪天手抖 DROP TABLE 了，上游数据也没了，下游任务全崩，背锅的就是你。

另一个坑：内部表的数据文件位置不要手动去 HDFS 上 hdfs dfs -rm 删掉，这样元数据还在，但查表会报错。要删就用 Hive 的 DROP TABLE，或者外部表的话只删元数据。

3.4 分区表 vs 分桶表（重点，面试必考）

分区表和分桶表是 Hive 里两个最核心的物理优化手段，但原理完全不同，很多人搞混。先上对比：

分区表 (Partition) 分桶表 (Bucket)
划分依据 按字段的实际值划分 按字段的哈希值取模划分
HDFS 表现 不同值 = 不同目录 不同哈希 = 不同文件
目的 查询时跳过无关目录，避免全表扫描 让相同 key 的数据落在同一文件，Join 时免 Shuffle
字段要求 分区字段必须是表外字段（PARTITIONED BY 声明） 分桶字段必须是表内字段（CLUSTERED BY 声明）
数量限制 不能太多，几万级就卡死 Metastore 固定数量，一般 128/256/512 个
是否可组合 支持多级分区（dt + hour） 支持分区后再分桶
分区表详解

分区表的核心思路是按查询条件把数据拆目录放。你按日期查，那就每天一个目录；你按省份查，那就每个省一个目录。查询时 Hive 直接跳过不相关的目录，不用全表扫描。

-- 最常见的日期分区
CREATE TABLE user_log (
  user_id STRING,
  event_type STRING,
  duration INT
)
PARTITIONED BY (dt STRING)
STORED AS ORC;

建完后 HDFS 上的实际目录：

/user/hive/warehouse/user_log/
  dt=2024-01-01/
    000000_0.orc
    000001_0.orc
  dt=2024-01-02/
    000000_0.orc
  dt=2024-01-03/
    000000_0.orc

分区裁剪怎么工作的：

你写 WHERE dt = '2024-01-01'，Hive 先问 Metastore：这个表有哪些分区？Metastore 返回分区列表。Hive 发现 dt=2024-01-01 存在，就只去扫这个目录下的两个 orc 文件，别的目录压根不碰。

如果不写分区条件：SELECT COUNT(*) FROM user_log，Hive 会把 dt=2024-01-01、dt=2024-01-02、dt=2024-01-03 全部扫一遍。数据量大的时候这就是全表扫描，能把集群跑挂。

分区表目录结构

多级分区：

CREATE TABLE user_log (
  user_id STRING,
  event_type STRING
)
PARTITIONED BY (dt STRING, hour STRING)
STORED AS ORC;

HDFS 结构变成两层嵌套：

user_log/
  dt=2024-01-01/
    hour=00/
    hour=01/
    ...
  dt=2024-01-02/
    hour=00/

多级分区查询时支持逐级裁剪。WHERE dt='2024-01-01' 会跳过所有其他日期的目录；WHERE dt='2024-01-01' AND hour='00' 再进一步只扫那一个小时的目录。

踩坑点：

  1. 分区字段不能跟表字段重名。dt 只能写在 PARTITIONED BY 里，不能同时出现在括号内的字段列表中。
  2. 分区数不能膨胀。我见过有人按 dt + hour + minute 三级分区，一天 1440 个分区，一个月 4 万多，Metastore 查个分区列表就超时。分区最多到小时级，分钟级用分桶解决。
  3. 空分区（有目录没文件）也会占用 Metastore 记录，定期清理 MSCK REPAIR TABLE 或者 ALTER TABLE ... DROP PARTITION。

动态分区插入：

SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT OVERWRITE TABLE user_log PARTITION(dt)
SELECT user_id, event_type, dt
FROM ods_raw_log;

Hive 自动根据 dt 字段的值把数据分发到对应分区目录。不用你手动写 PARTITION(dt='xxx')。

踩坑点：动态分区一次最多产生 1000 个分区（hive.exec.max.dynamic.partitions），超过直接报错。如果一个 SQL 要产生几千个分区，八成是分区粒度太细了，赶紧 redesign。

分桶表详解

分桶的核心思路是把数据按哈希打散到固定数量的文件里。相同哈希值的数据一定在同一个文件里，Join 时直接文件对文件匹配，省去 Shuffle。

CREATE TABLE user_bucket (
  id INT,
  name STRING,
  age INT
)
CLUSTERED BY (id) INTO 256 BUCKETS
STORED AS ORC;

分桶的哈希计算：

对每一条数据，计算 hash(id) % 256，得到 0~255 之间的桶编号。这条数据就写入对应的桶文件。

HDFS 上的结构：

/user/hive/warehouse/user_bucket/
  000000_0.orc   -- 桶0
  000001_0.orc   -- 桶1
  ...
  000255_0.orc   -- 桶255

注意：分桶后不是目录，而是固定数量的文件。文件数 = 桶数。

分桶表优化 Join（Bucket Join）：

分桶表Join优化

假设表 A 和表 B 都按 id 分桶，且桶数相等（都是 256）。做 JOIN ON a.id = b.id 时：

  • 表 A 的桶 0 里的所有 id，和表 B 的桶 0 里的所有 id，桶编号一定相同
  • 所以桶 0 对桶 0 直接 Join，桶 1 对桶 1 直接 Join，不需要 Shuffle
  • 本质上每个桶文件对桶文件做 MapJoin，性能极好

前提条件：

  1. 两张表的 Join key = 分桶字段
  2. 两张表的桶数相等，或者成整数倍关系（比如 256 和 512）
  3. 两张表都是 ORC 或同种格式

分区 + 分桶组合使用：

生产环境最常见的做法是先分区再分桶：

CREATE TABLE user_log (
  user_id STRING,
  event_type STRING,
  duration INT
)
PARTITIONED BY (dt STRING)
CLUSTERED BY (user_id) INTO 256 BUCKETS
STORED AS ORC;

效果：每天一个分区目录，每个分区目录下有 256 个桶文件。查询时先用分区条件跳过无关日期，再在目标日期内用分桶优化 Join。

踩坑点：

  1. 分桶表 INSERT 时必须开 hive.enforce.bucketing=true，否则数据不按桶拆分，随机落文件，后续 Join 优化失效。
  2. 桶数要根据数据量来定。总数据量 1TB，分 256 桶，每桶约 4GB，太大了；分 1024 桶，每桶约 1GB，合适。一般建议单桶 500MB~2GB。
  3. 分桶字段的分布要均匀。如果 80% 的数据 id 都是同一个值（比如 NULL），那 80% 的数据会落入同一个桶，分桶优化彻底失效。
  4. SORTED BY 可以和分桶一起用：CLUSTERED BY (id) SORTED BY (id) INTO 256 BUCKETS。这样每个桶内的数据是有序的，Join 时还能进一步加速（范围查找）。

3.6 分区表和分桶表怎么选

场景 用什么
按时间范围查询（查最近 7 天） 分区表，按 dt 分区
大表 Join 大表，Join key 固定 分桶表，按 Join key 分桶
日志表，每天几十 GB，经常按天查也按用户 Join 分区 + 分桶，dt 分区 + user_id 分桶
数据量小（< 100GB），查询不固定 不分区不分桶，直接 ORC
小表（< 1GB）Join 大表 不分桶，靠 MapJoin 优化

一句话总结：分区解决"查哪里"的问题（目录裁剪），分桶解决"怎么查更快"的问题（Join 免 Shuffle）。两者不冲突，大表通常两个都用。

四、DML：数据导入导出

4.1 LOAD DATA

这是最简单的数据导入方式，把 HDFS 上或者本地文件系统的文件直接 move 到 Hive 表的目录下。

-- 从 HDFS 加载（文件会被 move 到表目录，原位置不再保留）
LOAD DATA INPATH '/tmp/data/user_log.txt' INTO TABLE user_log PARTITION(dt='2024-01-01');

-- 从本地加载（文件会被 copy 到表目录，原文件保留）
LOAD DATA LOCAL INPATH '/home/hadoop/user_log.txt' INTO TABLE user_log;

踩坑点：

  1. LOAD DATA INPATH 是 move 操作，不是 copy！源文件会被删掉。如果你不小心把上游系统的原始数据文件 load 了，上游系统可能会崩溃。保险起见先用 hdfs dfs -cp 复制一份。
  2. LOAD DATA 不会做格式转换。如果你的文件是 CSV 格式，但表是 ORC 格式，直接 load 进去查询会报错或返回乱码。这种情况应该用 INSERT ... SELECT 的方式插入。

4.2 INSERT

-- 静态分区插入
INSERT OVERWRITE TABLE user_log PARTITION(dt='2024-01-01')
SELECT user_id, event_type, duration FROM source_table WHERE dt = '2024-01-01';

-- 动态分区插入
INSERT OVERWRITE TABLE user_log PARTITION(dt)
SELECT user_id, event_type, duration, dt FROM source_table;

踩坑点：INSERT OVERWRITE 会覆盖目标分区的所有已有数据。如果你只是想追加数据，用 INSERT INTO。但在生产环境中，更推荐的做法是用 INSERT OVERWRITE 做全量覆盖，或者在表设计层面用小时分区来实现增量写入，避免同一分区里新旧数据混在一起。

4.3 数据导出

-- 导出到 HDFS 目录
INSERT OVERWRITE DIRECTORY '/tmp/export/user_log'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM user_log WHERE dt = '2024-01-01';

-- 导出到本地
INSERT OVERWRITE LOCAL DIRECTORY '/home/hadoop/export'
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
SELECT * FROM user_log;

踩坑点：导出的目录如果已经存在，Hive 会报错或者直接覆盖（取决于版本）。建议先 hdfs dfs -rm -r 删掉目标目录，或者用日期时间戳命名导出目录来避免冲突。

五、DQL：查询语法

Hive 的查询语法跟 MySQL 非常像，但有几个关键差异点需要注意。

5.1 基本的 SELECT

SELECT user_id, COUNT(*) AS cnt
FROM user_log
WHERE dt = '2024-01-01'
GROUP BY user_id
HAVING COUNT(*) > 10;

WHERE 和 HAVING 的区别跟 MySQL 一样：WHERE 在分组前过滤，HAVING 在分组后过滤。

5.2 排序（Order By vs Sort By vs Distribute By）

这是很多人容易搞混的地方，三张图也说不清，看下面这张图就懂了：

排序原理对比

语法 作用 性能 使用场景
ORDER BY 全局排序，所有数据汇总到一个 Reduce 里排序 极差，数据量大时单点瓶颈 结果集很小（<10万行）时做最终展示排序
SORT BY 每个 Reduce 内部排序，全局无序 好，并行执行 配合 DISTRIBUTE BY 使用
DISTRIBUTE BY 控制数据分到哪个 Reduce 好 按指定字段分桶，配合 SORT BY 实现桶内有序
CLUSTER BY DISTRIBUTE BY + SORT BY 的简写 好 分桶且桶内排序，字段必须相同 
-- 按 user_id 哈希分桶，每个桶内按 age 降序
SELECT * FROM user_log
DISTRIBUTE BY user_id
SORT BY age DESC;

-- 简写形式（分桶字段和排序字段必须相同）
SELECT * FROM user_log CLUSTER BY user_id;

踩坑点：

  1. 生产环境大数据量排序，绝对不要直接用 ORDER BY。如果只需要 TopN，用窗口函数 ROW_NUMBER() 配合子查询。
  2. DISTRIBUTE BY 和 GROUP BY 的区别：GROUP BY 是做聚合（reduce 阶段合并），DISTRIBUTE BY 只是控制数据分布，不做聚合。

5.3 JOIN（重点）

Hive 支持标准 SQL 的 6 种 Join：

-- 内连接：只保留两边都匹配上的数据
SELECT * FROM a INNER JOIN b ON a.id = b.id;

-- 左连接：保留左表全部，右表没匹配的填 NULL
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

-- 右连接：保留右表全部
SELECT * FROM a RIGHT JOIN b ON a.id = b.id;

-- 全外连接：两边都保留
SELECT * FROM a FULL OUTER JOIN b ON a.id = b.id;

-- 左半连接：只返回左表中在右表存在匹配的行
SELECT * FROM a LEFT SEMI JOIN b ON a.id = b.id;

-- 笛卡尔积：每行交叉组合，数据量爆炸
SELECT * FROM a CROSS JOIN b;

踩坑点：

  1. Hive 3.x 之前的版本不支持非等值 Join（ON a.id > b.id 这种），3.x 之后有限支持，但性能很差。建议把非等值条件放到 WHERE 里做过滤。
  2. 多表 Join 时，把最大的表放到最后。Hive 的优化器默认会把前面的表尝试做 MapJoin（广播到小表到内存），最后一个大表作为主驱动表流式读取。
  3. CROSS JOIN 谨慎使用，两张百万行的表 cross join 会产生万亿行数据，直接把集群跑崩。
MapJoin（小表广播 Join）

MapJoin原理

当 Join 的两张表中有一张足够小（默认小于 25MB）时，Hive 会自动触发 MapJoin，把小表全部加载到内存中，大表逐条在 Map 端做匹配。不需要 Shuffle，不需要 Reduce，性能提升数倍到数十倍。

-- Hive 3.x 默认开启自动 MapJoin
SET hive.auto.convert.join=true;
SET hive.mapjoin.smalltable.filesize=25600000;  -- 25MB阈值

你也可以手动强制指定：

SELECT /*+ MAPJOIN(b) */ * FROM big_table a JOIN small_table b ON a.id = b.id;

踩坑点：小表加载到内存后，每个 MapTask 都持有一份完整拷贝。如果小表其实没那么小（比如 100MB），或者集群有几百个 MapTask，内存总占用 = 100MB × MapTask 数量，很容易 OOM。调大阈值之前先算一下内存账。

六、函数

Hive 内置了大量函数，日常工作中经常用到的是这些。

6.1 数值与字符串

-- 四舍五入
SELECT ROUND(3.14159, 2);  -- 3.14

-- 字符串截取
SELECT SUBSTRING('hello world', 1, 5);  -- hello

-- 字符串拼接
SELECT CONCAT('a', '-', 'b');  -- a-b

-- 条件判断
SELECT IF(age > 18, 'adult', 'minor');
SELECT CASE WHEN score >= 90 THEN 'A' WHEN score >= 60 THEN 'B' ELSE 'C' END;

-- 空值处理
SELECT COALESCE(null, null, 'default');  -- default
SELECT NVL(null, 'default');  -- default（Oracle兼容）

-- 类型转换
SELECT CAST('123' AS INT);
SELECT CAST(123 AS STRING);

6.2 集合函数

-- 数组长度
SELECT SIZE(array('a', 'b', 'c'));  -- 3

-- 取数组元素
SELECT ARRAY_CONTAINS(array('a', 'b'), 'a');  -- true

-- Map 取值
SELECT MAP_KEYS(map('k1', 'v1', 'k2', 'v2'));  -- ["k1","k2"]
SELECT MAP_VALUES(map('k1', 'v1', 'k2', 'v2'));  -- ["v1","v2"]

6.3 炸裂函数：EXPLODE + LATERAL VIEW

这是 Hive 里处理数组、Map 展开的核心技巧，面试常考。

-- 假设有一列 tags 是数组类型
SELECT id, tag
FROM user_tags
LATERAL VIEW EXPLODE(tags) t AS tag;

-- 输入
-- id | tags
-- 1  | ["java", "python", "go"]

-- 输出
-- id | tag
-- 1  | java
-- 1  | python
-- 1  | go

LATERAL VIEW 的作用是把 EXPLODE 展开的结果跟原表的每一行做关联，形成多行输出。没有 LATERAL VIEW，EXPLODE 单独用会报错或者只能返回单列。

踩坑点：如果数组是空的（[]），EXPLODE 默认不会输出任何行，原表的这行数据就"消失"了。如果你希望空数组也保留一行（输出 NULL），用 OUTER：

LATERAL VIEW OUTER EXPLODE(tags) t AS tag;

6.4 行列转换

多行转多列（聚合 + CASE WHEN）：

-- 把每个科目的成绩从多行转成多列
SELECT user_id,
  MAX(CASE WHEN subject = 'math' THEN score END) AS math_score,
  MAX(CASE WHEN subject = 'english' THEN score END) AS english_score,
  MAX(CASE WHEN subject = 'physics' THEN score END) AS physics_score
FROM scores
GROUP BY user_id;

多列转多行（UNION ALL）：

-- 把多列数据拆成多行
SELECT user_id, 'math' AS subject, math_score AS score FROM scores
UNION ALL
SELECT user_id, 'english' AS subject, english_score AS score FROM scores
UNION ALL
SELECT user_id, 'physics' AS subject, physics_score AS score FROM scores;

多行转单列（CONCAT_WS + COLLECT_SET）：

SELECT user_id, CONCAT_WS(',', COLLECT_SET(tag)) AS tag_list
FROM user_tags
GROUP BY user_id;
-- 结果：1 | "java,python,go"

COLLECT_SET 会去重，COLLECT_LIST 不去重。

6.5 JSON 解析

Hive 处理 JSON 有三种方式，按推荐程度排序：

方式1：get_json_object（简单但性能差）

-- 每次只能取一个字段，解析效率低
SELECT 
  get_json_object(json_col, '$.name') AS name,
  get_json_object(json_col, '$.age') AS age
FROM user_json;

方式2：json_tuple（推荐）

-- 一次解析多个字段，效率更高
SELECT a.json_col, b.name, b.age, b.city
FROM user_json a
LATERAL VIEW json_tuple(json_col, 'name', 'age', 'city') b AS name, age, city;

踩坑点：

  1. json_tuple 返回的字段名是固定的 c1, c2, c3...，必须用 LATERAL VIEW ... AS alias1, alias2 重命名。
  2. 如果 JSON 字段不存在，get_json_object 返回 NULL，不会报错。
  3. 对于嵌套 JSON，比如 {"user":{"name":"xxx"}}，用 $.user.name 可以取到值。

七、窗口函数

窗口函数是 Hive SQL 进阶必备，平时用的很多。

7.1 排序三兄弟

窗口函数排序对比

函数 重复值处理 是否跳号 场景
ROW_NUMBER() 给不同编号 不跳号 最常用，取每组 TopN
RANK() 给相同编号 跳号 排行榜（允许并列，下一名跳过）
DENSE_RANK() 给相同编号 不跳号 等级划分（A/B/C档） 
-- 取每个班级的前3名
SELECT *
FROM (
  SELECT student, class, score,
    ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC) AS rn
  FROM exam_scores
) t
WHERE rn <= 3;

踩坑点：ROW_NUMBER() 如果 ORDER BY 的字段有重复值，相同值的行之间的排序是随机的（取决于数据读取顺序）。如果你需要稳定的排序结果，务必加一个唯一字段（比如主键 id）到 ORDER BY 中：

ROW_NUMBER() OVER (PARTITION BY class ORDER BY score DESC, id ASC)

7.2 聚合类窗口函数

-- 累计求和
SELECT user_id, amount,
  SUM(amount) OVER (ORDER BY create_time) AS cumsum
FROM orders;

-- 每组内累计求和
SELECT dept, emp_id, salary,
  SUM(salary) OVER (PARTITION BY dept ORDER BY hire_date) AS dept_cumsum
FROM employees;

-- 移动平均（前2行到当前行）
SELECT dt, daily_amount,
  AVG(daily_amount) OVER (ORDER BY dt ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS ma3
FROM daily_stats;

7.3 NTILE：分桶

-- 把学生按成绩分成5组（每组人数尽量相等）
SELECT student, score,
  NTILE(5) OVER (ORDER BY score DESC) AS bucket
FROM exam_scores;
-- bucket=1 是前20%，bucket=5 是后20%

八、数据压缩与存储格式

8.1 存储格式：ORC vs Parquet

存储格式对比

Hive 支持多种存储格式，生产环境上基本只会用到 ORC 和 Parquet 两种。

特性 ORC Parquet
列存储 是 是
谓词下推 原生支持 支持
与 Hive 兼容性 最好，Hive 原生推荐 良好（Spark 生态更推荐）
索引 Stripe 级别 min/max 统计 Row Group 级别统计
ACID 事务 Hive 3.x 支持 不支持

建表时直接指定：

-- Hive 生态推荐 ORC
CREATE TABLE user_log (...) STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

-- 如果下游主要是 Spark 任务，也可以用 Parquet
CREATE TABLE user_log (...) STORED AS PARQUET
TBLPROPERTIES ("parquet.compression"="SNAPPY");

踩坑点：

  1. 不要用 TextFile 上生产。TextFile 是行式存储，占空间大、查询慢、不支持谓词下推。只有做数据接入（比如从上游系统原样接收文本文件）时临时用一下，入仓后立刻转 ORC。
  2. ORC 的 stripe.size 默认 256MB，如果你的 HDFS block 也是 256MB，那就一个 block 对应一个 stripe，查询效率最高。如果你把 stripe 设得太大（比如 1GB），查询时需要读取的数据量会增加；设得太小（比如 64MB），文件数量会膨胀。

8.2 压缩算法

Hive 支持多种压缩，生产环境最常用的是 Snappy 和 ZSTD：

算法 压缩比 解压速度 是否切分 适用场景
Snappy 中等 极快 否 默认推荐，CPU 开销低
GZIP 高 慢 否 冷数据归档
LZO 中等 快 是 需要 Map 切分的场景
ZSTD 高 快 否 Hive 3.x 新增，综合最优 
-- 设置压缩（全局配置或 session 级别）
SET hive.exec.compress.intermediate=true;
SET hive.exec.compress.output=true;
SET mapreduce.map.output.compress=true;
SET mapreduce.map.output.compress.codec=org.apache.hadoop.io.compress.SnappyCodec;

踩坑点：中间结果（Map 输出）开启压缩能减少 Shuffle 阶段的网络传输量，但会消耗额外 CPU。如果你的集群网络不是瓶颈而是 CPU 先打满，反而可能变慢。建议监控一下，CPU 富余就开，CPU 紧张就关。

九、调优（生产环境救命指南）

9.1 执行引擎选择

Hive 3.x 已经弃用 MapReduce了，执行引擎必须是 Spark 或 Tez：

SET hive.execution.engine=spark;  -- 推荐
-- 或
SET hive.execution.engine=tez;    -- 低延迟场景

Spark 是绝大多数公司的选择，因为 Spark 生态更成熟，跟 Hive 之外的组件（Spark Streaming、MLlib）整合也更好。Tez 配合 LLAP 在交互式查询场景下更有优势。

9.2 Fetch 抓取

对于简单的 SELECT * LIMIT 10 这类查询，Hive 可以直接从 HDFS 读取数据返回，不需要启动 Spark/Tez 任务。

SET hive.fetch.task.conversion=more;  -- 默认就是 more，确认一下

9.3 本地模式

数据量很小（输入 < 128MB）时，让 Hive 直接在本地 JVM 里跑，不用提交到集群：

SET hive.exec.mode.local.auto=true;
SET hive.exec.mode.local.auto.inputbytes.max=134217728;  -- 128MB
SET hive.exec.mode.local.auto.input.files.max=4;

踩坑点：本地模式只在测试环境或者数据量极小的时候有用。如果自动触发了本地模式但实际数据不止 128MB，会直接报错 java.lang.OutOfMemoryError。生产环境跑批处理建议关掉这个参数。

9.4 MapJoin 优化

前面 Join 部分讲过，不再赘述。核心配置：

SET hive.auto.convert.join=true;                          -- 自动转 MapJoin
SET hive.mapjoin.smalltable.filesize=25600000;            -- 小表阈值 25MB
SET hive.auto.convert.join.noconditionaltask=true;        -- 无条件转 MapJoin
SET hive.auto.convert.join.noconditionaltask.size=20971520; -- 多表Join时总内存限制

9.5 数据倾斜

数据倾斜示意图

数据倾斜是 Hive 生产环境最常见的性能问题，没有之一。表现为：任务跑了 99% 之后一直卡住，最后几个 Reduce 迟迟不结束。

倾斜的典型原因：

  1. Join key 里有大量 NULL 值
  2. Join key 的某个值占比过高（比如"北京"占全国 30% 的用户）
  3. Group By 字段分布不均

解决方案：

-- 方案1：过滤掉 NULL 值（如果业务允许）
SELECT * FROM a JOIN b ON a.id = b.id WHERE a.id IS NOT NULL AND b.id IS NOT NULL;

-- 方案2：给 NULL 赋随机值，分散到不同 Reduce
SELECT * FROM a JOIN b 
ON CASE WHEN a.id IS NULL THEN CONCAT('rand_', RAND()) ELSE a.id END = b.id;

-- 方案3：开启 Hive 的倾斜自动优化（Hive 3.x 推荐）
SET hive.optimize.skewjoin=true;
SET hive.skewjoin.key=100000;  -- 超过这个行数的key认为是倾斜key

Group By 倾斜：

-- 开启 Group By 倾斜优化
SET hive.groupby.skewindata=true;

原理是把 Group By 拆成两个阶段：第一阶段先局部聚合（Map 端预聚合），第二阶段再全局聚合。这样能显著减少倾斜 key 的数据量。

踩坑点：

  1. hive.groupby.skewindata=true 会增加一个 MapReduce/Spark 阶段，如果数据没有倾斜，反而会因为多一个阶段变慢。确认有倾斜再开。
  2. 加随机前缀的方案虽然有效，但会改变数据分布，下游任务可能会因此受到影响。最好跟下游团队沟通清楚。

9.6 小文件合并

Hive 任务产生大量小文件（< HDFS block size 的 80%）会导致：

  1. NameNode 内存压力大（每个文件都是一个元数据对象）
  2. 查询时需要启动大量 MapTask，任务调度开销远大于实际计算时间
-- Map 端合并输入文件
SET hive.input.format=org.apache.hadoop.hive.ql.io.CombineHiveInputFormat;

-- 输出时合并小文件
SET hive.merge.mapfiles=true;
SET hive.merge.mapredfiles=true;
SET hive.merge.size.per.task=256000000;       -- 合并后目标大小 256MB
SET hive.merge.smallfiles.avgsize=16000000;   -- 小于 16MB 就触发合并

踩坑点：动态分区容易产生小文件（每个分区一个写入线程）。可以在 INSERT 前调大 hive.exec.reducers.bytes.per.reducer（比如 512MB），让每个 Reduce 写出的文件更大。

9.7 严格模式

Hive 有一个严格模式，开启后会禁止一些"危险操作"：

SET hive.mapred.mode=strict;  -- 开启严格模式（默认是 nonstrict）

严格模式下禁止的操作：

  1. 不带 WHERE 条件的 SELECT *
  2. ORDER BY 不带 LIMIT
  3. 对分区表查询不带分区条件

踩坑点：严格模式在生产环境强烈建议开启。我见过太多半夜全表扫描把集群干趴的案例，大部分都是忘写分区条件或者忘加 limit。严格模式至少能在提交阶段就拦住这些 SQL。Hive 3.x 里严格模式被拆成了多个细粒度参数（hive.strict.checks.*），但老的 hive.mapred.mode=strict 仍然兼容。

9.8 CBO 优化器

Hive 3.x 默认开启了基于代价的优化器（CBO），根据表的统计信息选择最优执行计划。

SET hive.cbo.enable=true;
SET hive.compute.query.using.stats=true;

-- 分析表统计信息（建表后定期执行）
ANALYZE TABLE user_log COMPUTE STATISTICS;
ANALYZE TABLE user_log COMPUTE STATISTICS FOR COLUMNS;

踩坑点：如果统计信息不准确（比如表已经导入了 1 亿行数据但统计信息还是空），CBO 可能会选出一个烂的执行计划，性能反而比关闭 CBO 还差。建议每次大批量写入后都执行 ANALYZE TABLE 更新统计信息。

9.9 列裁剪和谓词下推

这两个优化 Hive 默认开启，一般不需要手动干预，但理解原理对排查问题有帮助。

列裁剪：SELECT name, age FROM user 只会读取 name 和 age 两列的数据（ORC/Parquet 格式下），不会读取整行。

谓词下推：WHERE age > 18 这个条件会尽量下推到存储层，在读取数据时就过滤掉不满足条件的行，减少数据传输量。

SET hive.optimize.ppd=true;        -- 谓词下推（默认开）
SET hive.optimize.cp=true;         -- 列裁剪（默认开）

十、生产环境踩坑总结

最后把我在生产环境中踩过（或看别人踩过）的坑汇总一下，按严重程度排序：

P0 - 能造成事故的那种：

  1. 内部表 + DROP TABLE 删了上游数据：上游系统写到 HDFS 的数据被你一个 DROP TABLE 全清空了，下游任务全崩。对策：外部表是默认选择，内部表只在确定数据生命周期由 Hive 管理时使用。
  2. 分区条件忘写导致全表扫描：半夜 ETL 任务 SELECT * FROM huge_table WHERE dt = yesterday，结果因为变量没生效变成了 SELECT * FROM huge_table，集群资源被打满，其他任务全卡死。对策：开严格模式 + 代码里做分区条件非空校验。
  3. 动态分区产生几十万个分区：PARTITIONED BY (dt, hour, minute) 这种设计，一天就产生 1440 个分区，一个月 4 万多个。Metastore 查询超时，HiveServer2 假死。对策：分区粒度最多到小时，分钟级别用分桶而不是分区。

P1 - 性能奇慢但是不会崩的那种：

  1. 大表直接用 ORDER BY 排序：千万行以上的数据 ORDER BY，强制单 Reduce 执行，跑了 2 小时还没完。对策：用 DISTRIBUTE BY + SORT BY 代替，或者窗口函数取 TopN。
  2. TextFile 格式上生产：表大了之后查询慢得像蜗牛，压缩率不到 ORC 的 1/5。对策：所有生产表默认 STORED AS ORC TBLPROPERTIES("orc.compress"="SNAPPY")。
  3. MapJoin 阈值设太大导致 OOM：小表其实有 500MB，硬调到 MapJoin 阈值里，Task 内存爆了重试 4 次才 fallback 到 Common Join，浪费半小时。对策：调阈值之前先看 DESCRIBE FORMATTED 里表的 totalSize。
  4. CBO 统计信息过期：表数据量翻了 10 倍但统计信息还是老的，CBO 选了 Broadcast Join 但内存根本装不下。对策：大批量写入后执行 ANALYZE TABLE。

P2 - 细节问题，查起来很烦：

  1. NULL 值参与 Join 或聚合：COUNT(DISTINCT user_id) 如果 user_id 里有 NULL，结果比预期少；Join 时 NULL = NULL 在 SQL 语义里是 false。对策：WHERE user_id IS NOT NULL 提前过滤，或者用 COALESCE 处理。
  2. JSON 字段里有特殊字符导致解析失败：get_json_object 遇到换行符或回车会截断。对策：上游写入 JSON 时做转义，或者用 regexp_replace(json_col, '[\\n\\r]', '') 预处理。
  3. UNION ALL 忘记写 ALL：UNION 默认会去重，多了个去重操作性能差很多。对策：只要不需要去重，永远写 UNION ALL。
  4. Metastore MySQL 连接超时：任务跑了几十分钟后报 Communications link failure。对策：MySQL URL 加超时参数，或者检查 MySQL 的 wait_timeout 配置。

结语

Hive 这个东西，入门很容易——会写 SQL 就能用。但要在生产环境里把它用好、用稳，里面的门道还是挺多的。

# hive # hadoop
Logo
https://edu.csdn.net/learn/39067/627173?utm_source=2019755004

汇聚全球AI编程工具，助力开发者即刻编程。

更多推荐

cover

一个 API 入口调用多个大模型：AiiOnly客户端 + CC Switch 打通 Codex AI编程流程

avatar AI编程社区
cover

VSCode Claude Code + MiMo 中转免登录完整配置教程（解决原生登录弹窗 + 模型报错）

avatar AI编程社区
cover

从零开始做一个属于自己的 Skill（保姆级教程）

avatar AI编程社区