Hive SQL入门教程：快速掌握基础知识 – wiki基地

作者： /

Hive SQL 入门教程：快速掌握基础知识

从零开始，带你玩转大数据仓库查询

随着大数据时代的到来，如何高效地存储、管理和分析海量数据成为了一个核心问题。Hadoop 作为分布式存储和处理大数据的事实标准，为我们提供了强大的能力。然而，Hadoop 原生的 MapReduce 编程模型对于习惯了关系型数据库和 SQL 的用户来说，门槛较高。

此时，Apache Hive 应运而生。Hive 是一个构建在 Hadoop 之上的数据仓库基础设施，它提供了类 SQL 的查询语言 HiveQL，让我们可以方便地对 Hadoop HDFS 中的数据进行查询和分析，而无需编写复杂的 MapReduce 程序。Hive 将 HiveQL 查询翻译成底层的 MapReduce、Tez 或 Spark 作业，从而实现对大数据的处理。

本篇文章将带你从零开始，系统地学习 Hive SQL 的基础知识，帮助你快速掌握在大数据环境下进行数据查询和分析的核心技能。无论你是刚接触大数据的新手，还是希望将 SQL 技能应用于大数据平台的传统数据库用户，这篇教程都将为你打下坚实的基础。

1. Hive 是什么？为什么要用 Hive？

1.1 什么是 Hive？

简单来说，Hive 是一个数据仓库系统，它运行在 Hadoop 之上，提供了一种便捷的方式来查询存储在 Hadoop 文件系统（如 HDFS）中的大规模数据集。它的核心功能是将类 SQL 的查询（HiveQL）转换为一系列在 Hadoop 集群上执行的作业（通常是 MapReduce、Tez 或 Spark）。

1.2 为什么使用 Hive？

  • SQL 接口： 对于熟悉 SQL 的用户来说，学习成本极低。可以直接使用熟悉的 SQL 语法进行数据查询和分析，大大降低了进入大数据分析领域的门槛。
  • 降低复杂度： Hive 隐藏了底层 Hadoop 的复杂性。用户不需要关心数据是如何分块存储的，也不需要手动编写复杂的 MapReduce 代码来处理分布式计算。
  • 可扩展性： 继承了 Hadoop 的可扩展性，可以处理PB级别的数据。
  • 容错性： 依赖于 Hadoop 的容错机制，数据存储和计算都具有高可用性。
  • ETL 功能： Hive 提供了一些内置的功能，可以方便地进行数据提取、转换和加载（ETL）操作。
  • 社区活跃： 作为一个 Apache 顶级项目，拥有庞大的社区支持，生态系统成熟。

1.3 Hive 与传统关系型数据库（RDBMS）的区别

虽然 HiveQL 看起来很像 SQL，但 Hive 在设计上与 RDBMS 有着本质的区别：

  • 数据模型： Hive 适用于批量处理和分析，而不是实时事务处理（OLTP）。它更侧重于吞吐量而不是低延迟。
  • 更新/删除： 早期版本的 Hive 对数据的更新和删除支持较弱（通常通过覆盖整个分区或表来实现）。尽管后续版本引入了 ACID 属性支持，但其实现方式与 RDBMS 的行级更新/删除不同，且性能开销较大，不适合频繁的细粒度更新。
  • 索引： Hive 对索引的支持不如 RDBMS 成熟和高效，主要依赖于分区、分桶等方式进行查询优化。
  • 延迟： Hive 查询通常有较高的启动延迟（因为需要启动 MapReduce/Tez/Spark 作业），执行时间也相对较长，不适合交互式查询或对响应时间要求高的场景。
  • 数据存储： Hive 数据通常存储在 HDFS 中，可以是各种格式（文本、Parquet、ORC等），而 RDBMS 数据存储在数据库特定的文件系统中，通常是行式存储。

理解这些区别很重要，它决定了 Hive 的最佳使用场景——即大数据仓库、ETL 批处理、大规模数据分析和报表生成。

2. Hive 架构概述（简化版）

为了更好地理解 Hive 的工作原理，我们简要了解一下其核心组件：

  • Client (客户端): 用户通过客户端与 Hive 交互，发送 HiveQL 查询。常见的客户端有 Hive CLI (命令行接口)、Beeline (推荐使用)、JDBC/ODBC 驱动等。
  • HiveServer2 (HS2): 负责接收客户端请求，支持多用户并发和各种客户端类型。
  • Driver (驱动): Hive 的核心，负责接收来自客户端的查询，并执行以下步骤：
    • Compiler (编译器): 解析 HiveQL 查询，进行语法分析和类型检查，生成抽象语法树 (AST)。
    • Optimizer (优化器): 根据查询计划和 Metastore 的元数据信息，进行查询优化，生成逻辑执行计划和物理执行计划。
    • Executor (执行器): 调用底层计算引擎（MapReduce/Tez/Spark）执行物理计划，与 Hadoop 集群交互。
  • Metastore (元数据服务): Hive 的“大脑”，存储着关于 Hive 表、分区、列、数据类型、数据存储位置（HDFS路径）、SerDe (序列化/反序列化器) 等元数据信息。Metastore 通常配置在一个关系型数据库中（如 MySQL）。查询优化器和执行器都需要依赖 Metastore 的信息来理解数据的结构和位置。
  • Hadoop HDFS: 存储实际的数据文件。
  • Execution Engine (计算引擎): 执行物理计划的引擎，可以是 MapReduce (较慢)、Tez (更快)、Spark (通常最快)。可以通过配置进行切换。

查询执行流程简化：

  1. 用户在客户端提交 HiveQL 查询。
  2. 查询被发送到 HiveServer2。
  3. Driver 接收查询。
  4. Compiler 解析和验证查询。
  5. Optimizer 根据 Metastore 的元数据优化查询计划。
  6. Executor 将优化后的计划转换成 MapReduce/Tez/Spark 作业。
  7. 作业提交给 Hadoop 集群执行，读取/写入 HDFS 中的数据。
  8. 执行结果返回给 Driver，再通过 HiveServer2 返回给客户端。

3. Hive 基础概念

在开始编写 HiveQL 之前，我们需要理解一些关键概念：

3.1 数据库 (Databases)

类似于关系型数据库的数据库，用于组织和隔离表。

  • 默认数据库是 default。
  • 可以在不同的数据库中创建同名的表。

3.2 表 (Tables)

Hive 表的结构类似于 RDBMS 中的表，包含列和数据类型。但其数据本身存储在 HDFS 中。

  • Managed Table (内部表): Hive 完全管理表的数据和元数据。当你 DROP TABLE 时，Hive 会同时删除 Metastore 中的元数据和 HDFS 中的数据文件。
  • External Table (外部表): Hive 只管理表的元数据。数据存储在指定的 HDFS 位置，不受 Hive 的完全控制。当你 DROP TABLE 时，Hive 只会删除 Metastore 中的元数据，而保留 HDFS 中的数据文件。这对于数据源不由 Hive 独占管理或多个工具需要访问同一份 HDFS 数据时非常有用。

3.3 分区 (Partitions)

分区是 Hive 中最重要的优化手段之一。 它将表数据按照一个或多个列（分区列）的值进行物理上的划分，存储在 HDFS 的不同子目录中。

  • 例如，一个销售表可以按年份和月份分区：/warehouse/sales/year=2023/month=01/, /warehouse/sales/year=2023/month=02/ 等。
  • 优点： 查询时如果 WHERE 子句中包含了分区列过滤条件，Hive 可以只读取相关分区目录下的数据，避免全表扫描，显著提高查询性能（分区裁剪）。

3.4 桶 (Buckets)

分桶是将每个分区（或整个表，如果未分区）的数据按照一个或多个列的值进行哈希，将数据分散存储在固定数量的文件中。这些文件称为桶文件。

  • 例如，按用户ID进行分桶，相同用户ID的数据总是落入同一个桶文件。
  • 优点：
    • 便于抽样 (Sampling)：可以直接读取部分桶文件进行数据抽样。
    • 提高 JOIN 性能：如果两个表都按照相同的列分桶，并且分桶数量是倍数关系，那么 JOIN 时可以执行更高效的 桶连接 (Bucket Map Join)，显著减少 Map 端读取的数据量。

3.5 数据类型 (Data Types)

Hive 支持基本数据类型和复杂数据类型。

  • 基本类型:
    • 数值型: TINYINT, SMALLINT, INT, BIGINT, FLOAT, DOUBLE, DECIMAL
    • 布尔型: BOOLEAN
    • 字符串型: STRING, VARCHAR, CHAR
    • 日期/时间型: DATE, TIMESTAMP
    • 二进制: BINARY
  • 复杂类型:
    • ARRAY: 有序同类型元素的集合 (e.g., ARRAY<STRING>)
    • MAP: 无序键值对集合 (e.g., MAP<STRING, INT>)
    • STRUCT: 具有命名字段的结构 (e.g., STRUCT<name:STRING, age:INT>)
    • UNIONTYPE: 包含多种数据类型之一的值 (较少用)

4. HiveQL 数据定义语言 (DDL)

DDL 用于定义、修改和删除数据库对象（如数据库、表、视图）。

4.1 创建数据库

“`sql
— 创建一个名为 my_database 的数据库
CREATE DATABASE my_database;

— 如果数据库不存在则创建
CREATE DATABASE IF NOT EXISTS my_database;

— 创建数据库并指定 HDFS 存储位置和描述
CREATE DATABASE IF NOT EXISTS my_database
LOCATION ‘/user/hive/warehouse/my_db_data’
COMMENT ‘这是一个示例数据库’;
“`

4.2 使用数据库

“`sql
— 切换到 my_database 数据库
USE my_database;

— 后续的表操作都将在这个数据库下进行
“`

4.3 创建表

CREATE TABLE 是最重要的 DDL 命令。语法比较灵活，可以定义内部表或外部表、分区、分桶、文件格式等。

sql
-- 示例：创建一个简单的内部表 (文本文件格式)
CREATE TABLE IF NOT EXISTS employee (
emp_id INT COMMENT '员工ID',
name STRING COMMENT '姓名',
salary DOUBLE COMMENT '薪水',
hire_date DATE COMMENT '入职日期'
)
COMMENT '员工信息表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',' -- 指定字段之间的分隔符为逗号
LINES TERMINATED BY '\n' -- 指定行之间的分隔符为换行符
STORED AS TEXTFILE; -- 指定数据存储格式为文本文件

  • IF NOT EXISTS: 避免重复创建报错。
  • COMMENT: 为表或列添加描述。
  • ROW FORMAT DELIMITED: 指定行和字段的格式。
    • FIELDS TERMINATED BY: 字段分隔符。
    • COLLECTION ITEMS TERMINATED BY: 复杂类型（如 ARRAY、MAP）中元素之间的分隔符。
    • MAP KEYS TERMINATED BY: MAP 中键值对之间的分隔符。
    • LINES TERMINATED BY: 行分隔符。
  • STORED AS: 指定数据文件的存储格式。
    • TEXTFILE: 文本文件，数据通常以字符串形式存储，需要 SerDe 进行序列化/反序列化，查询时需解析字符串，性能较低。
    • SEQUENCEFILE: 二进制键值对格式，支持压缩。
    • RCFILE: 列式存储，读写性能优于 TEXTFILE 和 SEQUENCEFILE。
    • ORC: (Optimized Row Columnar) 优化的列式存储格式，支持高速压缩和索引，读写性能通常最优，强烈推荐用于分析型负载。
    • PARQUET: 另一种流行的列式存储格式，与 ORC 类似，跨平台兼容性好，也强烈推荐。

创建外部表：

sql
CREATE EXTERNAL TABLE IF NOT EXISTS logs (
timestamp BIGINT,
level STRING,
message STRING
)
COMMENT '应用程序日志表 (外部表)'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t' -- 假设日志是 Tab 分隔
STORED AS TEXTFILE
LOCATION '/data/app_logs/'; -- 指定数据所在的 HDFS 目录

  • EXTERNAL: 关键字，表示创建外部表。
  • LOCATION: 指定数据文件的 HDFS 路径。注意，Hive 不会管理这个路径下的文件删除。

创建分区表：

sql
CREATE TABLE IF NOT EXISTS daily_sales (
product_id INT,
amount DOUBLE,
region STRING
)
PARTITIONED BY (sale_date DATE, city STRING) -- 按日期和城市分区
COMMENT '每日销售数据 (分区表)'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS ORC; -- 分区表通常建议使用列式存储格式

  • PARTITIONED BY (column_name data_type, ...): 定义分区列。分区列不是表中实际的数据列，而是用于组织数据的元数据。

创建分桶表：

sql
CREATE TABLE IF NOT EXISTS user_behavior (
user_id BIGINT,
action STRING,
event_time TIMESTAMP
)
CLUSTERED BY (user_id) INTO 10 BUCKETS -- 按 user_id 分桶，分成 10 个桶
SORTED BY (event_time ASC) -- 每个桶内按 event_time 排序 (可选)
COMMENT '用户行为数据 (分桶表)'
STORED AS PARQUET;

  • CLUSTERED BY (column_name, ...): 定义分桶列。
  • INTO num_buckets BUCKETS: 指定桶的数量。
  • SORTED BY (column_name [ASC|DESC], ...): 可选，指定每个桶内的数据排序方式。

使用 AS SELECT 创建表 (CTAS)：

可以从现有查询结果创建新表。这常用于数据转换或创建中间表。

sql
-- 创建一个新表，包含所有来自 employee 表且工资大于 5000 的员工信息
CREATE TABLE high_salary_employee AS
SELECT emp_id, name, salary
FROM employee
WHERE salary > 5000;

4.4 显示数据库和表

“`sql
— 显示所有数据库
SHOW DATABASES;

— 显示当前数据库中的所有表
SHOW TABLES;

— 显示指定数据库中的所有表
SHOW TABLES IN my_database;
“`

4.5 查看表结构

“`sql
— 查看表的基本结构（列名、数据类型、分区信息等）
DESCRIBE employee;
DESC employee; — 简写

— 查看表的更详细信息（HDFS 路径、SerDe 信息、表属性等）
DESCRIBE EXTENDED employee;

— 查看表的格式化信息
DESCRIBE FORMATTED employee;
“`

4.6 修改表

ALTER TABLE 用于修改现有表的结构或属性。

  • 添加/删除/修改列 (请注意限制): 早期 Hive 版本对非分区表的列修改支持有限。使用 ORC/Parquet 等格式且开启 ACID 后，支持更灵活的列操作。
    “`sql
    — 添加新列 (Hive 2.1+ 对 ORC/Parquet 支持较好)
    ALTER TABLE employee ADD COLUMNS (department STRING COMMENT ‘部门’);

    — 修改列名和数据类型 (同样有格式和版本限制)
    ALTER TABLE employee CHANGE COLUMN name emp_name VARCHAR(100);
    * **重命名表：**sql
    ALTER TABLE employee RENAME TO staff;
    * **添加/删除分区：** 这是 `ALTER TABLE` 中最常用的功能之一，用于管理分区表的数据目录。sql
    — 添加一个分区 (如果 HDFS 路径已存在数据文件)
    ALTER TABLE daily_sales ADD PARTITION (sale_date=’2023-01-01′, city=’Shanghai’) LOCATION ‘/data/sales/2023/01/shanghai’;

    — 添加多个分区
    ALTER TABLE daily_sales ADD
    PARTITION (sale_date=’2023-01-02′, city=’Beijing’) LOCATION ‘/data/sales/2023/01/beijing’
    PARTITION (sale_date=’2023-01-03′, city=’Guangzhou’); — 如果不指定 LOCATION，使用默认路径规则

    — 删除一个分区 (同时删除元数据和 HDFS 数据)
    ALTER TABLE daily_sales DROP PARTITION (sale_date=’2023-01-01′, city=’Shanghai’);

    — 如果分区不存在，不报错
    ALTER TABLE daily_sales DROP PARTITION (sale_date=’2023-01-10′, city=’Shenzhen’) IF EXISTS;
    * **设置表属性：**sql
    ALTER TABLE employee SET TBLPROPERTIES (‘notes’=’这是一个重要的表’);
    “`

4.7 删除表

“`sql
— 删除表 (如果表不存在，会报错)
DROP TABLE employee;

— 如果表不存在，不报错
DROP TABLE IF EXISTS employee;

— 删除外部表只会删除元数据，保留 HDFS 数据。删除内部表会同时删除元数据和 HDFS 数据。
“`

4.8 删除数据库

“`sql
— 删除数据库 (如果数据库中有表，会报错)
DROP DATABASE my_database;

— 级联删除：删除数据库及其中的所有表
DROP DATABASE my_database CASCADE;

— 如果数据库不存在，不报错
DROP DATABASE IF EXISTS my_database;
“`

5. HiveQL 数据操纵语言 (DML)

DML 用于加载、插入、更新和删除数据。Hive 的 DML 操作与传统 RDBMS 有些不同，特别是历史版本。

5.1 加载数据 (LOAD DATA)

LOAD DATA 是将本地文件系统或 HDFS 中的数据文件移动/复制到 Hive 表或分区目录下的主要方式。这是一个文件系统级别的操作，不是解析文件内容插入行。

“`sql
— 将本地文件 /path/to/local/file.txt 加载到 employee 表
— 文件内容必须符合 employee 表的行和字段分隔符格式
LOAD DATA LOCAL INPATH ‘/path/to/local/file.txt’ INTO TABLE employee;

— 将本地文件加载到 employee 表，并覆盖现有数据
LOAD DATA LOCAL INPATH ‘/path/to/local/file.txt’ OVERWRITE INTO TABLE employee;

— 将 HDFS 路径 /user/data/new_employees.txt 下的文件移动到 employee 表目录
LOAD DATA INPATH ‘/user/data/new_employees.txt’ INTO TABLE employee; — 默认是 MOVE

— 将本地文件加载到分区表的指定分区
LOAD DATA LOCAL INPATH ‘/path/to/local/daily_data_shanghai.txt’
OVERWRITE INTO TABLE daily_sales
PARTITION (sale_date=’2023-01-01′, city=’Shanghai’);
“`

  • LOCAL INPATH: 源文件位于本地文件系统。执行时会复制文件到 HDFS 目标路径。
  • INPATH: 源文件位于 HDFS。默认行为是移动 (MOVE)，更快。
  • INTO TABLE: 加载数据到整个表。
  • INTO TABLE ... PARTITION (...): 加载数据到指定分区。
  • OVERWRITE: 如果目标路径下有数据，会先清空再加载。如果不加 OVERWRITE，默认是追加数据（对于分区表通常是追加，非分区表取决于 Hive 版本和配置）。通常建议使用 OVERWRITE 到新的分区，以避免数据混乱。

重要提示： LOAD DATA 操作不会验证数据是否符合表结构或数据类型。Hive 只是将文件放到 HDFS 路径下，只有在执行查询时才会尝试使用 SerDe 解析数据。如果数据格式不匹配，查询可能会返回 NULL 或错误。

5.2 插入数据 (INSERT)

INSERT 语句通过执行一个 SELECT 查询的结果来向表中插入数据。

“`sql
— 从另一个表 select 结果插入到 employee 表 (追加)
INSERT INTO TABLE employee
SELECT emp_id, name, salary, hire_date FROM temp_employee_data WHERE status = ‘active’;

— 从 select 结果插入到 employee 表 (覆盖)
INSERT OVERWRITE TABLE employee
SELECT emp_id, name, salary, hire_date FROM temp_employee_data WHERE status = ‘active’;

— 从 select 结果插入到分区表的指定分区 (覆盖)
INSERT OVERWRITE TABLE daily_sales
PARTITION (sale_date=’2023-01-05′, city=’Shanghai’)
SELECT product_id, amount, region FROM temp_sales_data WHERE sale_date = ‘2023-01-05’ AND city = ‘Shanghai’;

— 动态分区插入：根据 SELECT 结果自动确定分区值 (常用)
— 需要开启动态分区模式: SET hive.exec.dynamic.partition=true; SET hive.exec.dynamic.partition.mode=nonstrict;
INSERT OVERWRITE TABLE daily_sales — 或 INSERT INTO
PARTITION (sale_date, city) — 分区列不指定值
SELECT product_id, amount, region, sale_date, city — SELECT 结果中必须包含分区列
FROM temp_sales_data;

— 插入指定值 (适用于少量数据，通常不用于大数据)
INSERT INTO TABLE employee VALUES (101, ‘Alice’, 6000.0, ‘2024-01-15’); — 需要开启 ACID
“`

  • INSERT INTO: 追加数据。
  • INSERT OVERWRITE: 覆盖目标表或目标分区的数据。
  • 动态分区插入极大地简化了按查询结果将数据加载到不同分区的操作。注意需要配置相关的 Hive 参数。
  • 直接 VALUES 插入是 Hive 0.13+ 版本引入的，且需要开启 ACID 支持，通常效率不高，不适合批量插入。

5.3 更新和删除数据 (UPDATE, DELETE)

如前所述，Hive 历史版本对 UPDATE 和 DELETE 支持非常有限。从 Hive 0.14 版本开始，通过开启 ACID (Atomicity, Consistency, Isolation, Durability) 属性支持后，可以在 ORC 格式的 Managed Table 上执行行级别的 UPDATE 和 DELETE 操作。

要启用 ACID，需要在 hive-site.xml 中进行配置，并在创建表时指定格式为 ORC 并启用事务属性。

“`sql
— 示例 (需要开启 ACID 且表是 ORC 格式的 Managed Table):
— 更新 employee 表中 ID 为 101 的员工的薪水
UPDATE employee SET salary = 6500.0 WHERE emp_id = 101;

— 删除 employee 表中 ID 为 102 的员工记录
DELETE FROM employee WHERE emp_id = 102;
“`

重要提示： 在实际生产环境中，行级别的 UPDATE/DELETE 在 Hive 中不如传统 RDBMS 常见和高效。对于大批量的数据变更，更常见的方式是：

  1. 读取需要修改的数据。
  2. 在处理逻辑中进行修改。
  3. 将修改后的数据写入一个新的表或新的分区。
  4. 如果需要，切换表的指向（例如，通过 ALTER TABLE RENAME）或使用视图来屏蔽旧数据，暴露新数据。

5.4 清空表 (TRUNCATE TABLE)

清空表中的数据，但保留表结构。

“`sql
— 清空整个表的数据
TRUNCATE TABLE employee;

— 清空分区表指定分区的数据
TRUNCATE TABLE daily_sales PARTITION (sale_date=’2023-01-01′, city=’Shanghai’);
``TRUNCATE` 操作通常只支持 Managed Table。对于 External Table，清空操作只会删除元数据中指向的 HDFS 路径下的文件，但不会删除 LOCATION 指定的根目录本身（如果是分区表，可能只删除分区目录下的文件）。

6. HiveQL 数据查询语言 (DQL) – SELECT 语句

SELECT 语句是 HiveQL 中最常用和最强大的部分，用于从表中检索数据。其语法与标准 SQL 非常相似。

基本结构：

sql
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 BY col_list | DISTRIBUTE BY col_list | SORT BY col_list]
[LIMIT number]

6.1 SELECT 和 FROM 子句

  • SELECT: 指定要检索的列。* 表示所有列。
    sql
    SELECT emp_id, name, salary FROM employee;
    SELECT * FROM employee;
  • DISTINCT: 返回唯一的结果行。
    sql
    -- 查询所有不重复的员工姓名
    SELECT DISTINCT name FROM employee;
  • FROM: 指定从哪个表或视图查询数据。

6.2 WHERE 子句

用于过滤行。只返回满足条件的行。

“`sql
— 查询工资大于 5000 的员工
SELECT name, salary FROM employee WHERE salary > 5000;

— 查询部门为 ‘IT’ 或 ‘HR’ 的员工
SELECT name, department FROM employee WHERE department = ‘IT’ OR department = ‘HR’;
SELECT name, department FROM employee WHERE department IN (‘IT’, ‘HR’);

— 查询姓名以 ‘A’ 开头的员工
SELECT name FROM employee WHERE name LIKE ‘A%’;

— 查询姓名包含 ‘li’ 的员工 (RLIKE 支持正则表达式)
SELECT name FROM employee WHERE name LIKE ‘%li%’; — LIKE 模糊匹配
SELECT name FROM employee WHERE name RLIKE ‘.li.‘; — RLIKE 正则表达式匹配

— 查询 hire_date 在指定日期范围内的员工
SELECT name, hire_date FROM employee WHERE hire_date BETWEEN ‘2023-01-01’ AND ‘2023-12-31’;

— 查询部门为空的员工
SELECT name, department FROM employee WHERE department IS NULL;

— 利用分区裁剪优化查询 (重要!)
— 只查询 2023 年 1 月 1 日上海的销售数据
SELECT product_id, amount FROM daily_sales WHERE sale_date = ‘2023-01-01’ AND city = ‘Shanghai’; — Hive 会只扫描对应分区目录
“`

6.3 GROUP BY 子句

用于将具有相同值的行分组，常与聚合函数一起使用。

“`sql
— 计算每个部门的平均薪水
SELECT department, AVG(salary) FROM employee GROUP BY department;

— 计算每个部门和入职年份的员工数量
SELECT department, YEAR(hire_date) AS hire_year, COUNT(*)
FROM employee
GROUP BY department, YEAR(hire_date);
“`

6.4 HAVING 子句

用于在 GROUP BY 分组后过滤分组。WHERE 过滤原始行，HAVING 过滤分组后的结果（可以使用聚合函数）。

sql
-- 计算平均薪水，并只显示平均薪水大于 6000 的部门
SELECT department, AVG(salary) AS avg_salary
FROM employee
GROUP BY department
HAVING AVG(salary) > 6000;

6.5 ORDER BY, SORT BY, DISTRIBUTE BY, CLUSTER BY

这些子句用于控制输出结果的排序和数据在 Reduce 任务中的分布。

  • ORDER BY: 对最终结果进行全局排序。Hive 会将所有数据发送到一个或少数几个 Reduce 任务中进行排序，对于大数据集可能会导致性能瓶颈。
    sql
    -- 按薪水降序排列所有员工
    SELECT name, salary FROM employee ORDER BY salary DESC;
  • SORT BY: 对每个 Reduce 内部的数据进行排序。不能保证全局有序，但能保证每个 Reduce 的输出文件内部是有序的。比 ORDER BY 性能更高。
    sql
    -- 每个 Reduce 输出的员工数据按薪水排序
    SELECT name, salary FROM employee SORT BY salary DESC;
  • DISTRIBUTE BY: 控制 Map 输出如何分发到 Reduce。具有相同 DISTRIBUTE BY 字段值的行会被发送到同一个 Reduce。通常与 SORT BY 一起使用，实现每个分区内有序。
    sql
    -- 按部门分发到不同的 Reduce，每个 Reduce 内按薪水排序
    SELECT department, name, salary FROM employee DISTRIBUTE BY department SORT BY salary DESC;
    注意：DISTRIBUTE BY 的列必须出现在 SELECT 或 GROUP BY 中。DISTRIBUTE BY 列在 SELECT 结果中也会作为分组依据进行哈希分发。
  • CLUSTER BY: 是 DISTRIBUTE BY 和 SORT BY 的简写形式，它表示按指定的列进行分发，并且每个 Reduce 内的数据也按这些列进行排序。
    sql
    -- 相当于 DISTRIBUTE BY department SORT BY department
    SELECT department, name FROM employee CLUSTER BY department;
    如果 CLUSTER BY 列与分桶列相同，并且开启了优化，Hive 可以执行 桶连接 (Bucket Map Join)。

最佳实践: 对于大规模数据集，优先使用 SORT BY + DISTRIBUTE BY 或 CLUSTER BY 来代替 ORDER BY，以避免数据倾斜和性能问题。只有在结果集很小或者确实需要全局排序时，才使用 ORDER BY，并且通常结合 LIMIT 使用。

6.6 LIMIT 子句

限制返回的行数，常用于预览数据或配合 ORDER BY 获取 Top N 结果。

“`sql
— 返回前 10 条员工记录 (无序)
SELECT * FROM employee LIMIT 10;

— 返回薪水最高的 5 位员工
SELECT name, salary FROM employee ORDER BY salary DESC LIMIT 5;
“`

7. HiveQL 连接 (JOIN)

JOIN 用于根据相关列将两个或多个表的行组合起来。

  • INNER JOIN (内连接): 只返回两个表中都匹配的行。
  • LEFT [OUTER] JOIN (左外连接): 返回左表的所有行，以及右表中与左表匹配的行。如果右表没有匹配，则右表列返回 NULL。
  • RIGHT [OUTER] JOIN (右外连接): 返回右表的所有行，以及左表中与右表匹配的行。如果左表没有匹配，则左表列返回 NULL。
  • FULL [OUTER] JOIN (全外连接): 返回左表和右表中的所有行。如果某个表没有匹配，则另一表的列返回 NULL。
  • CROSS JOIN (交叉连接): 返回两个表的笛卡尔积，结果集行数是两表行数的乘积。通常应谨慎使用，除非数据量很小或确实需要此结果。

“`sql
— 示例：假设有一个 department 表 (dept_id, dept_name) 和 employee 表 (emp_id, name, dept_id)

— 内连接：查询员工姓名及其所在部门名称
SELECT e.name, d.dept_name
FROM employee e — 使用别名简化
JOIN department d — 默认为 INNER JOIN
ON e.dept_id = d.dept_id;

— 左外连接：查询所有员工及其所在部门名称，即使没有部门 (dept_id 为 NULL 或无匹配)
SELECT e.name, d.dept_name
FROM employee e
LEFT JOIN department d
ON e.dept_id = d.dept_id;

— 右外连接：查询所有部门及其员工姓名，即使部门没有员工
SELECT e.name, d.dept_name
FROM employee e
RIGHT JOIN department d
ON e.dept_id = d.dept_id;

— 全外连接：查询所有员工和所有部门，匹配的显示在一起，不匹配的显示 NULL
SELECT e.name, d.dept_name
FROM employee e
FULL OUTER JOIN department d
ON e.dept_id = d.dept_id;

— 交叉连接：非常规使用，谨慎！
— SELECT e.name, d.dept_name FROM employee e CROSS JOIN department d;
“`

JOIN 优化提示：

  • 小表在前: 如果一个 JOIN 操作中有一个表远小于另一个，将小表放在 JOIN 语句的左边（LEFT JOIN）或右边（RIGHT JOIN），有助于 Hive 执行 Map-side Join (或 Broadcast Join)，将小表加载到内存并在 Map 阶段直接与大表数据匹配，避免昂贵的 Reduce 端 Shuffle。可以通过设置 SET hive.auto.convert.join=true; 开启自动 Map-side Join 转换。
  • 使用分桶 JOIN: 如果两个表都按 JOIN 列分桶且分桶数匹配，可以使用 Bucket Map Join，性能更好。

8. HiveQL 内置函数

Hive 提供了丰富的内置函数，用于数据处理和转换。

  • 聚合函数 (Aggregate Functions): 用于对一组值进行计算，常与 GROUP BY 一起使用。
    • COUNT(col): 统计非 NULL 值的数量。
    • COUNT(*): 统计行数。
    • COUNT(DISTINCT col): 统计唯一非 NULL 值的数量（注意：在大数据集上性能可能较差）。
    • SUM(col): 求和。
    • AVG(col): 求平均。
    • MIN(col): 求最小值。
    • MAX(col): 求最大值。
  • 数学函数 (Math Functions): ROUND(), FLOOR(), CEIL(), ABS(), POWER(), SQRT() 等。
  • 字符串函数 (String Functions): LENGTH(), SUBSTR(str, pos, len), CONCAT(str1, str2, ...) , UPPER(), LOWER(), TRIM(), LPAD(), RPAD(), REPLACE(), SPLIT(str, regex) (返回 ARRAY) 等。
  • 日期/时间函数 (Date/Timestamp Functions): FROM_UNIXTIME(unix_timestamp, format), UNIX_TIMESTAMP([string date]), CURRENT_DATE, CURRENT_TIMESTAMP, DATE_ADD(start_date, days), DATE_SUB(start_date, days), DATEDIFF(end_date, start_date), YEAR(date), MONTH(date), DAY(date), HOUR(), MINUTE(), SECOND(), WEEKOFYEAR(), FROM_UTC_TIMESTAMP(), TO_UTC_TIMESTAMP() 等。
  • 条件函数 (Conditional Functions):
    • CASE WHEN condition1 THEN result1 [WHEN condition2 THEN result2 ...] ELSE result_default END
    • IF(condition, true_value, false_value)
  • 集合函数 (Collection Functions): SIZE(), MAP_KEYS(), MAP_VALUES(), ARRAY_CONTAINS(), SORT_ARRAY() 等。
  • 其他函数: ISNULL(), COALESCE(v1, v2, ...) (返回第一个非 NULL 值), GET_JSON_OBJECT(json_string, path) 等。

示例：

“`sql
— 计算总薪水
SELECT SUM(salary) FROM employee;

— 查询员工姓名的长度
SELECT name, LENGTH(name) FROM employee;

— 将员工姓名转换为大写
SELECT name, UPPER(name) FROM employee;

— 计算入职日期与当前日期的天数差
SELECT name, hire_date, DATEDIFF(CURRENT_DATE, hire_date) AS days_since_hire FROM employee;

— 使用 CASE WHEN 根据薪水等级
SELECT name, salary,
CASE
WHEN salary < 5000 THEN ‘Low’
WHEN salary >= 5000 AND salary < 8000 THEN ‘Medium’
ELSE ‘High’
END AS salary_level
FROM employee;

— 使用 IF 函数
SELECT name, salary, IF(salary >= 8000, ‘High’, ‘Normal’) AS salary_level FROM employee;

— 分割字符串
SELECT SPLIT(‘apple,banana,orange’, ‘,’) as fruit_list; — 返回 ARRAY [‘apple’, ‘banana’, ‘orange’]
“`

9. 视图 (Views)

视图是一个虚拟的表，它基于一个 SQL 查询的结果。视图本身不存储数据，每次查询视图时，都会重新执行其底层查询来生成结果。

  • 优点:
    • 简化复杂的查询 (将复杂查询定义为视图)。
    • 提高代码的可重用性。
    • 提供数据抽象和安全层 (可以只暴露部分列或行)。

“`sql
— 创建一个视图，显示高薪员工的基本信息
CREATE VIEW high_salary_view AS
SELECT emp_id, name, salary
FROM employee
WHERE salary >= 8000;

— 查询视图 (就像查询普通表一样)
SELECT * FROM high_salary_view;

— 删除视图
DROP VIEW high_salary_view;
“`

10. HiveQL 性能优化基础

虽然 Hive 会进行一些自动优化，但编写高效的 HiveQL 仍然需要一些技巧：

  • 利用分区裁剪: 在 WHERE 子句中尽可能使用分区列过滤数据。
  • 使用列式存储格式: 优先选择 ORC 或 Parquet 格式存储数据。
  • 选择合适的执行引擎: 配置和使用 Tez 或 Spark 引擎代替 MapReduce (通过 SET hive.execution.engine=tez; 或 spark;)。
  • 启用矢量化查询: 矢量化可以在 CPU 层面批量处理数据行，显著提高扫描、过滤、聚合等操作的速度。通过 SET hive.vectorized.execution.enabled=true; 启用。
  • 优化 JOIN: 理解 Map-side Join 和 Bucket Map Join，通过调整表顺序或分桶策略进行优化。对于大表 JOIN，考虑数据倾斜问题。
  • 只选择需要的列: 避免使用 SELECT *，只选择查询结果需要的列。
  • 使用 LIMIT 限制结果集: 在开发和测试阶段，使用 LIMIT 限制返回的行数，减少不必要的数据处理。
  • 避免 COUNT(DISTINCT): 对于大规模数据集上的 COUNT(DISTINCT col) 操作，开销很大。可以考虑使用近似去重函数（如 NDV(col)，由 Hive 提供的基于 HyperLogLog 算法的函数）或者先进行 GROUP BY col 再 COUNT(*) 的方式（如果数据量不是特别大）。
  • 使用 EXPLAIN 查看执行计划: 在执行耗时查询之前，使用 EXPLAIN 命令查看 Hive 将如何执行你的查询。这有助于你理解查询的瓶颈，并找到优化点。
    sql
    EXPLAIN SELECT department, COUNT(*) FROM employee GROUP BY department;
    EXPLAIN EXTENDED SELECT department, COUNT(*) FROM employee GROUP BY department; -- 查看更多详细信息
    EXPLAIN CBO SELECT department, COUNT(*) FROM employee GROUP BY department; -- 查看基于成本的优化器 (CBO) 计划
    EXPLAIN 输出会显示查询被拆分成的 MapReduce/Tez/Spark 阶段，以及每个阶段的操作（如 TableScan, Filter, GroupBy, Reduce 等），帮助你分析是否使用了分区、是否发生了数据倾斜等。

11. 超越基础：进一步学习方向

掌握了以上基础知识，你已经可以在 Hive 中进行基本的数据查询和分析了。如果你希望进一步深入，可以学习以下内容：

  • 高级函数: 窗口函数 (Window Functions)，自定义函数 (UDF, UDAF, UDTF)。
  • SerDe: 学习如何处理不同格式的数据（CSV, JSON, Avro, Protocol Buffers 等），以及如何编写自定义 SerDe。
  • 调优深入: 深入理解 MapReduce/Tez/Spark 的执行原理，学习更多 Hive 配置参数的调优。
  • Hive 事务 (ACID): 如果你的应用场景需要频繁的更新和删除，需要深入了解 Hive 的 ACID 特性及其使用限制。
  • 与其他工具集成: 学习如何将 Hive 与 Spark SQL, Presto/Trino, Impala, Zeppelin, Hue 等工具集成使用。
  • 元数据管理: 了解 Apache Atlas 或其他元数据管理工具与 Hive 的集成。

总结

Apache Hive 为我们提供了一个强大的、基于 SQL 的接口来处理存储在 Hadoop 中的海量数据。通过学习 Hive 的核心概念（表、分区、分桶、数据类型）、掌握基本的 DDL 和 DML 操作、熟练运用 SELECT 语句进行数据查询，并初步了解性能优化技巧，你就能快速入门 Hive SQL，为你的大数据分析之旅打下坚实的基础。

请记住，实践是最好的学习方式。在实际环境中搭建 Hive 或使用提供 Hive 服务的平台，动手创建表、加载数据、编写查询，不断尝试和探索，你将更快地精通 Hive SQL，在大数据的世界里游刃有余！祝你学习顺利！

发表评论

您的邮箱地址不会被公开。 必填项已用 * 标注

滚动至顶部