Hive 数据仓库：SQL on Hadoop 零基础入门教程

1. 认识 Hive：Hadoop 上的数据仓库

Hive 是构建在 Hadoop 之上的数据仓库基础工具，它将结构化的数据文件映射为数据库表，并提供类 SQL 查询功能，让熟悉 SQL 的分析师无需编写 MapReduce 程序即可完成海量数据的分析任务。

• 本质：Hive SQL（HQL）会被编译成 MapReduce、Tez 或 Spark 作业在集群上执行。
• 定位：并非传统 OLTP 数据库，适合批量离线分析，不适合毫秒级实时查询。
• 数据位置：数据存在 HDFS 中，Hive 仅储存表的元数据（schema）。
• 典型场景：日志分析、数据 ETL、BI 报表、机器学习特征工程等。

2. Hive 架构与组件

理解 Hive 如何将 SQL 转化为分布式计算是掌握它的关键。

[用户接口] → CLI / JDBC / Web UI
     ↓
[驱动器 Driver] → 解析器(Parser) → 查询编译器(Compiler) → 优化器(Optimizer) → 执行器(Executor)
     ↓
[元数据存储 Metastore] → 关系型数据库(MySQL, PostgreSQL)
     ↓
[执行引擎] → MapReduce / Tez / Spark
     ↓
[存储层] → HDFS (或兼容对象存储)

• Driver：接收查询，管理会话生命周期。
• Compiler：将 HQL 解析成抽象语法树（AST），借助 Metastore 完成语义分析，生成逻辑执行计划。
• Optimizer：对执行计划进行列裁剪、谓词下推、连接重排序等优化。
• Metastore：储存表名、列名、分区、存储格式等元数据，默认使用内嵌 Derby，生产环境必须切换为 MySQL 等。
• 执行引擎：默认 MapReduce，可配置为 Tez（内存计算）或 Spark 以大幅提升性能。

3. Hive 数据模型

Hive 组织数据的方式自上而下为：数据库（Database）→ 表（Table）→ 分区（Partition）→ 桶（Bucket）。

• 数据库：命名空间，隔离表和视图。
• 表：与关系型表类似，但数据实际存储为 HDFS 上的文件目录。
  • 内部表（Managed Table）：Hive 管理数据生命周期，删除表时数据一同删除。
  • 外部表（External Table）：只控制元数据，删除表时 HDFS 数据依然保留。
• 分区：按某个维度（如日期）将数据切分为子目录，查询时可跳过无关分区，提升效率。
• 分桶（Bucket）：将数据按哈希分散到固定数量文件中，适合采样和高效 JOIN。

4. 常用数据类型

5. 实战：表操作与数据加载

5.1 创建数据库与表

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sales_db
COMMENT '销售分析库'
LOCATION '/user/hive/warehouse/sales_db.db';

-- 切换数据库
USE sales_db;

-- 内部表（管理表）默认文本格式
CREATE TABLE IF NOT EXISTS orders (
    order_id      INT,
    user_id       INT,
    amount        DOUBLE,
    order_time    TIMESTAMP
)
COMMENT '订单数据'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

-- 外部表，指向已有HDFS路径
CREATE EXTERNAL TABLE IF NOT EXISTS ext_orders (
    order_id      INT,
    product_name  STRING,
    price         DOUBLE
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LOCATION '/data/external/orders';

5.2 加载数据

-- 从本地文件加载到内部表（文件会被移动）
LOAD DATA LOCAL INPATH '/home/user/orders.csv' OVERWRITE INTO TABLE orders;

-- 从HDFS加载（文件移动）
LOAD DATA INPATH '/input/orders.csv' INTO TABLE orders;

-- 插入数据
INSERT INTO TABLE orders VALUES (1, 101, 299.9, '2024-03-15 10:00:00');

-- 通过查询结果插入
INSERT OVERWRITE TABLE orders PARTITION (dt='202403')
SELECT order_id, user_id, amount FROM source_orders WHERE dt='202403';

注意：

• LOCAL 关键字表示从本地文件系统加载，OVERWRITE 覆盖原数据。
• 外部表加载数据通常直接通过 HDFS 命令放置文件到表目录，然后使用 MSCK REPAIR TABLE 刷新分区元数据。

5.3 分区表操作

-- 创建分区表，分区列为 dt 和 region
CREATE TABLE page_views (
    user_id     INT,
    view_time   TIMESTAMP,
    page_url    STRING
)
PARTITIONED BY (dt STRING, region STRING)
STORED AS ORC;

-- 添加分区后加载数据
ALTER TABLE page_views ADD PARTITION (dt='2024-03-15', region='US');
LOAD DATA INPATH '/logs/20240315/us' INTO TABLE page_views PARTITION (dt='2024-03-15', region='US');

-- 动态分区插入（需开启非严格模式）
SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT INTO TABLE page_views PARTITION (dt, region)
SELECT user_id, view_time, page_url, dt, region FROM staging_views;

6. 表存储格式与压缩

选择合适的文件格式和压缩对性能影响极大。

创建 ORC 表并启用压缩：

CREATE TABLE users_orc (
    id          INT,
    name        STRING,
    age         INT
)
STORED AS ORC
TBLPROPERTIES ("orc.compress"="SNAPPY");

7. 查询与分析

HiveQL 符合 SQL 标准，但有一些差异化特征。

-- 基本查询
SELECT user_id, COUNT(*) AS cnt
FROM orders
WHERE dt = '202403'
GROUP BY user_id
HAVING cnt > 3;

-- JOIN 操作（支持 Inner/Left/Right/Full Join 及 Map Join）
SELECT a.order_id, b.user_name
FROM orders a
JOIN users b ON a.user_id = b.user_id;

-- 排序：ORDER BY（全局排序，一个Reducer） 、SORT BY（局部排序）、
-- DISTRIBUTE BY（控制分发）、CLUSTER BY（DISTRIBUTE + SORT 的结合）
SELECT * FROM orders
DISTRIBUTE BY user_id SORT BY order_time DESC;

-- 窗口函数（分析函数）
SELECT 
    user_id,
    order_id,
    amount,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_time) AS row_num,
    SUM(amount) OVER (PARTITION BY user_id) AS total_spent
FROM orders;

-- 侧视图（Lateral View）和 explode
SELECT user_id, item
FROM orders
LATERAL VIEW explode(items_array) item_table AS item;

8. Hive 常用内置函数

Hive 内置大量函数，覆盖数学、字符串、日期、聚合等领域。

• 日期处理：from_unixtime(), unix_timestamp(), date_add(), datediff(), date_format()
• 字符串：concat(), substr(), split(), regexp_replace(), instr()
• 条件判断：if(), case when, coalesce(), nvl()
• 类型转换：cast(value AS type)
• JSON 解析：get_json_object(json_string, '$.key'), json_tuple()

示例：

SELECT 
    user_id,
    coalesce(nickname, '匿名用户') AS display_name,
    regexp_replace(phone, '(\d{3})\d{4}(\d{4})', '$1****$2') AS masked_phone
FROM users;

9. 性能优化技巧

9.1 常见策略

• 列裁剪与分区裁剪：只选择需要字段，WHERE 中尽量包含分区过滤条件。
• 谓词下推：在 JOIN 前尽早过滤数据。
• 小表 JOIN 大表：使用 Map Join 将小表加载内存。

  SET hive.auto.convert.join=true;
  SET hive.mapjoin.smalltable.filesize=25000000;
  

• 开启矢量查询：SET hive.vectorized.execution.enabled=true;
• 合理选择文件格式：ORC 或 Parquet + SNAPPY 压缩。
• 避免笛卡尔积：务必写出正确 JOIN 条件。
• CBO（基于成本的优化器）：SET hive.cbo.enable=true;

9.2 数据倾斜处理

当某个键的数据量特别大时，会导致单个 Reduce 任务拖慢整体。

• 开启倾斜 Join 优化：

  SET hive.optimize.skewjoin=true;
  SET hive.skewjoin.key=100000; -- 超过此数量则拆分处理
  

• 随机前缀与扩容：对倾斜键加随机数，先聚合再移除前缀。
• 使用 DISTRIBUTE BY rand() 打散数据。

9.3 文件合并

小文件过多会产生大量 Map 任务，降低效率。

-- 合并小文件
SET hive.merge.mapfiles=true;          -- map 输出合并
SET hive.merge.mapredfiles=true;       -- reduce 输出合并
SET hive.merge.size.per.task=256000000; -- 合并后文件大小

10. Hive 常见问题与排错

• 无法连接 Metastore：检查 hive-site.xml 中数据库连接 URL、用户名密码。
• 分区数据查不出来：确认是否已添加分区或执行 MSCK REPAIR TABLE 修复。
• 内存溢出：调整 mapreduce.reduce.memory.mb 和 hive.tez.container.size 等参数。
• 查询计划可在 CLI 中查看：

  EXPLAIN SELECT ...;
  EXPLAIN EXTENDED SELECT ...;  -- 更详细
  

11. 与其他工具对比

• Hive vs. Pig：Hive 面向分析师，声明式语言；Pig 面向开发，过程式数据流。
• Hive vs. Spark SQL：Spark SQL 依赖 Spark 引擎，速度更快，支持更丰富的交互查询和流处理，但 Hive 更成熟稳定，生态工具链完善。
• Hive vs. Impala：Impala 由 Cloudera 开发，基于 MPP 架构提供低延迟交互式查询，但需更多内存，Hive 更适合长时间批处理。

12. 总结与进阶学习

Hive 是 Hadoop 生态中实现 SQL on Hadoop 的核心组件，掌握它能够高效处理 PB 级结构化数据。本教程覆盖了从原理到实战的全流程，接下来建议：

• 深入学习 Hive 调优参数和 YARN 资源管理。
• 结合 Tez 或 Spark 引擎获得实时交互体验。
• 探索 Hive Streaming 和 ACID 事务表（类 ACID 支持 UPDATE/DELETE）。
• 阅读官方文档或《Programming Hive》书籍深入理解。

你现在就可以搭建一个实验环境，载入一份示例数据（如公开的航班或电商数据），亲手编写查询感受 Hive 的强大。