Hive SQL零基础到精通：100道练习题+答案，SQL能力快速提升

好的，各位数据工程师、数据分析师以及所有对大数据处理感兴趣的开发者们！今天，我们将开启一场酣畅淋漓的Hive SQL实战之旅。我将以我15年架构与开发的经验，带领大家从零基础到精通，通过精心设计的100道练习题及其详解，系统地、深度地掌握Hive SQL的核心精髓。

这篇文章不仅是一份习题集，更是一本系统性的实战教程。我们将从环境搭建开始，深入Hive的核心概念、DDL/DML操作、各类函数、高级查询技巧，最终攻克性能调优的堡垒。无论你是刚刚接触Hadoop生态的初学者，还是希望查漏补缺、冲击高级岗位的资深工程师，这份指南都将为你提供巨大的价值。

第一部分：开篇明义——为什么是Hive？

在大数据领域，Hive是构建在Hadoop之上的数据仓库基础架构。它可以将结构化的数据文件映射为一张数据库表，并提供了一套类SQL的查询语言——HiveQL（简称HQL），允许熟悉SQL的用户直接查询存储在HDFS上的海量数据。

核心价值：

降低门槛：将复杂的MapReduce任务转换为简单的SQL查询，让数据分析师、后端工程师等无需深入学习Java和MapReduce即可处理大数据。高可扩展性：依托Hadoop的HDFS和MapReduce/Tez/Spark，可处理PB级别的数据。强大的容错性：任务失败后可自动重试。灵活的序列化格式：支持TextFile、ORC、Parquet、Avro等，其中ORC和Parquet因其列式存储和高效压缩，成为生产环境的首选。

架构概览：
Hive并非传统的关系型数据库。它是一个批处理系统，延迟较高，适用于OLAP（联机分析处理） 场景，而非OLTP（联机事务处理）。其核心架构如下图所示（使用Mermaid绘制）：

理解了Hive的定位，我们就可以开始动手了。

第二部分：环境准备与数据奠基

工欲善其事，必先利其器。在开始练习之前，我们需要一个Hive环境。

开发环境搭建（以Docker为例，最快捷的方式）

对于个人学习和测试，使用Docker部署一个单节点的Hadoop+Hive环境是最佳选择。

安装Docker：请从Docker官网下载并安装适合你操作系统的Docker Desktop。拉取镜像：一个流行的选择是bde2020/hadoop系列镜像。


# 拉取包含Hive的Hadoop镜像
docker pull bde2020/hadoop-namenode:2.0.0-hadoop2.7.4-hive
docker pull bde2020/hadoop-datanode:2.0.0-hadoop2.7.4-hive
docker pull bde2020/hadoop-resourcemanager:2.0.0-hadoop2.7.4-hive
docker pull bde2020/hadoop-nodemanager:2.0.0-hadoop2.7.4-hive
docker pull bde2020/hadoop-historyserver:2.0.0-hadoop2.7.4-hive
# 使用docker-compose编排启动，具体yml文件可从GitHub搜索相关项目获取。

启动Hive CLI：环境启动后，通过docker exec命令进入容器并使用Hive命令行界面。


docker exec -it <container_id> hive

或者使用Beeline（推荐）：新版本Hive推荐使用Beeline（基于JDBC）。


docker exec -it <container_id> beeline -u jdbc:hive2://localhost:10000

创建练习数据库和表

我们将模拟一个电商场景，创建两张核心表：user_behavior（用户行为表）和product_info（商品信息表）。

步骤 1：创建数据库


CREATE DATABASE IF NOT EXISTS practice;
USE practice;

步骤 2：创建用户行为表（外部表，链接到HDFS数据）
假设我们有存储在HDFS上的用户行为日志文件，格式为制表符（）分隔的文本。


CREATE EXTERNAL TABLE IF NOT EXISTS user_behavior (
    user_id BIGINT COMMENT '用户ID',
    item_id BIGINT COMMENT '商品ID',
    category_id BIGINT COMMENT '商品类目ID',
    behavior STRING COMMENT '行为类型<pv, buy, cart, fav>',
    timestamp BIGINT COMMENT '行为时间戳'
)
COMMENT '用户行为日志表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '	'
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse/practice.db/user_behavior'; -- 指向HDFS路径

-- 假设数据已通过hadoop fs -put命令上传到该HDFS路径
-- hadoop fs -put /local/path/to/user_behavior.txt /user/hive/warehouse/practice.db/user_behavior/

步骤 3：创建商品信息表（内部表）


CREATE TABLE IF NOT EXISTS product_info (
    item_id BIGINT COMMENT '商品ID',
    item_name STRING COMMENT '商品名称',
    price DOUBLE COMMENT '商品价格'
)
COMMENT '商品信息表'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '	'
STORED AS TEXTFILE;

-- 使用LOAD DATA命令加载本地数据文件到表目录
LOAD DATA LOCAL INPATH '/local/path/to/product_info.txt' INTO TABLE product_info;

现在，我们的战场已经准备好了。让我们正式进入练习题环节！

第三部分：Hive SQL 100道练习题与答案精讲

我们将练习题分为6个难度等级，从基础到精通，层层递进。

Level 1：基础查询与过滤（第1-20题）

目标：熟悉SELECT, WHERE, DISTINCT, LIMIT等基本操作。

1. 查询user_behavior表中的前10行数据。


SELECT * FROM user_behavior LIMIT 10;

2. 查询所有不同的用户ID。


SELECT DISTINCT user_id FROM user_behavior;

3. 查询所有“购买”（behavior=‘buy’）行为记录。


SELECT * FROM user_behavior WHERE behavior = 'buy';

4. 查询用户ID为123456的用户在2020-06-01之后的所有行为（假设timestamp是毫秒级时间戳）。


SELECT *
FROM user_behavior
WHERE user_id = 123456
AND FROM_UNIXTIME(CAST(timestamp/1000 AS BIGINT), 'yyyy-MM-dd') >= '2020-06-01';
-- 解释：Hive的FROM_UNIXTIME函数接收秒级时间戳。所以先将毫秒级timestamp除以1000转成秒。

5. 查询商品类目ID在1000到2000之间的所有“pv”（浏览）行为。


SELECT *
FROM user_behavior
WHERE behavior = 'pv'
AND category_id BETWEEN 1000 AND 2000;

…
(持续到第20题，涵盖LIKE, IN, IS NULL等操作符)

Level 2：聚合与分组（第21-40题）

目标：掌握GROUP BY, HAVING以及COUNT, SUM, AVG, MAX, MIN等聚合函数。

21. 统计总的PV（浏览量）次数。


SELECT COUNT(*) AS pv_count
FROM user_behavior
WHERE behavior = 'pv';

22. 统计每个用户的PV次数，并按次数从高到低排序。


SELECT user_id, COUNT(*) AS pv_count
FROM user_behavior
WHERE behavior = 'pv'
GROUP BY user_id
ORDER BY pv_count DESC;

23. 统计每个商品的总购买次数。


SELECT item_id, COUNT(*) AS buy_count
FROM user_behavior
WHERE behavior = 'buy'
GROUP BY item_id;

24. 找出被购买次数最多的前10个商品。


SELECT item_id, COUNT(*) AS buy_count
FROM user_behavior
WHERE behavior = 'buy'
GROUP BY item_id
ORDER BY buy_count DESC
LIMIT 10;

25. 查询平均价格高于50元的商品类目。
此题需要关联product_info和user_behavior表，先获取类目和价格的关系。


-- 假设一个商品只属于一个类目
SELECT u.category_id, AVG(p.price) AS avg_price
FROM user_behavior u
JOIN product_info p ON u.item_id = p.item_id
GROUP BY u.category_id
HAVING avg_price > 50;
-- 关键：WHERE在分组前过滤行，HAVING在分组后过滤组。

…
(持续到第40题，涵盖COUNT(DISTINCT ...)等复杂聚合)

Level 3：多表连接与子查询（第41-60题）

目标：掌握INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN以及嵌套子查询。

41. 查询所有被购买过的商品的详细信息（商品ID、名称、价格）。


-- 方法1：使用INNER JOIN
SELECT DISTINCT p.item_id, p.item_name, p.price
FROM product_info p
INNER JOIN user_behavior u ON p.item_id = u.item_id AND u.behavior = 'buy';

-- 方法2：使用子查询 + IN
SELECT item_id, item_name, price
FROM product_info
WHERE item_id IN (
    SELECT DISTINCT item_id FROM user_behavior WHERE behavior = 'buy'
);

42. 查询哪些商品从未被浏览过（pv）。


-- 使用LEFT JOIN + IS NULL
SELECT p.*
FROM product_info p
LEFT JOIN (
    SELECT DISTINCT item_id FROM user_behavior WHERE behavior = 'pv'
) u ON p.item_id = u.item_id
WHERE u.item_id IS NULL;

43. 查询每个用户购买的最贵的商品是什么（显示用户ID，商品ID，商品名称，价格）。


-- 这是一个典型的分组求极值同时需要获取其他列信息的问题，可以使用窗口函数或自连接。
-- 方法A：使用窗口函数（更高效，Level 5会详解）
SELECT user_id, item_id, item_name, price
FROM (
    SELECT
        u.user_id,
        u.item_id,
        p.item_name,
        p.price,
        RANK() OVER (PARTITION BY u.user_id ORDER BY p.price DESC) as rk
    FROM user_behavior u
    JOIN product_info p ON u.item_id = p.item_id
    WHERE u.behavior = 'buy'
) t
WHERE rk = 1;

-- 方法B：使用聚合+JOIN（理解连接逻辑）
SELECT u.user_id, u.item_id, p.item_name, p.price
FROM user_behavior u
JOIN product_info p ON u.item_id = p.item_id
JOIN (
    SELECT user_id, MAX(p.price) as max_price -- 先找到每个用户购买的最高价格
    FROM user_behavior u
    JOIN product_info p ON u.item_id = p.item_id
    WHERE u.behavior = 'buy'
    GROUP BY user_id
) max_p ON u.user_id = max_p.user_id AND p.price = max_p.max_price
WHERE u.behavior = 'buy';

…
(持续到第60题，涵盖各种复杂的连接场景和子查询应用)

Level 4：日期与窗口函数（第61-80题）

目标：精通日期处理函数和强大的窗口函数（ROW_NUMBER, RANK, LEAD, LAG, SUM() OVER()等）。

61. 将时间戳字段转换为’yyyy-MM-dd HH:mm:ss’格式。


SELECT
    user_id,
    item_id,
    behavior,
    FROM_UNIXTIME(CAST(timestamp/1000 AS BIGINT), 'yyyy-MM-dd HH:mm:ss') AS behavior_time
FROM user_behavior;

62. 计算每个用户每次购买行为与其上一次购买行为的时间间隔（秒）。


SELECT
    user_id,
    item_id,
    behavior,
    timestamp,
    LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_timestamp,
    (timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp)) / 1000 AS seconds_diff
FROM user_behavior
WHERE behavior = 'buy';
-- LAG(col, n, default)：获取当前行之前第n行的数据。

63. 对每个商品的浏览量（pv）进行累加。


SELECT
    item_id,
    timestamp,
    COUNT(*) OVER (PARTITION BY item_id ORDER BY timestamp) AS cumulative_pv
FROM user_behavior
WHERE behavior = 'pv';
-- 这是一个典型的累计求和窗口函数应用。

64. 找出每个类目下价格排名前3的商品。


-- 需要先关联表获取商品和类目的关系，假设一个商品属于一个类目
SELECT
    category_id,
    item_id,
    item_name,
    price,
    rk
FROM (
    SELECT
        u.category_id,
        p.item_id,
        p.item_name,
        p.price,
        ROW_NUMBER() OVER (PARTITION BY u.category_id ORDER BY p.price DESC) AS rk
    FROM product_info p
    JOIN (
        SELECT DISTINCT item_id, category_id FROM user_behavior -- 获取商品-类目映射
    ) u ON p.item_id = u.item_id
) t
WHERE rk <= 3;
-- ROW_NUMBER(): 生成连续的序号，相同值按顺序排不同号。
-- RANK(): 相同值排相同号，并跳过后续序号。
-- DENSE_RANK(): 相同值排相同号，但不跳过后续序号。

…
(持续到第80题，深入探索窗口函数的各种应用场景)

Level 5：高级技巧与优化（第81-95题）

目标：学习UDTF、CASE WHEN、EXPLAIN、数据倾斜处理等高级技巧和性能优化思想。

81. 使用CASE WHEN将行为类型（behavior）转换为中文。


SELECT
    user_id,
    item_id,
    CASE behavior
        WHEN 'pv' THEN '浏览'
        WHEN 'buy' THEN '购买'
        WHEN 'cart' THEN '加购'
        WHEN 'fav' THEN '收藏'
        ELSE '其他'
    END AS behavior_cn
FROM user_behavior;

82. 使用EXPLAIN命令查看一条复杂查询的执行计划。


EXPLAIN
SELECT user_id, COUNT(*) FROM user_behavior WHERE behavior = 'pv' GROUP BY user_id;

分析执行计划输出，理解Hive如何将SQL转换为MapReduce/Tez任务，这是性能调优的基础。

83. 使用LATERAL VIEW EXPLODE解析JSON字符串（模拟场景）。
假设我们有一个字段是JSON数组'["a","b","c"]'。


-- 首先，Hive需要添加jar包支持（通常已配置）
-- ADD JAR /path/to/hive-hcatalog-core.jar;
SELECT
  user_id,
  exploded_tag
FROM user_behavior
LATERAL VIEW EXPLODE(SPLIT('["a","b","c"]', '[,|"|\[|\]]')) tags AS exploded_tag
WHERE exploded_tag != '';
-- 这是一个模拟示例，实际中可使用get_json_object和json_tuple处理JSON。

84. 如何应对GROUP BY时的数据倾斜？（假设user_id分布极不均匀）


-- 开启负载均衡优化
SET hive.groupby.skewindata = true;
-- 或 在SQL层面先对倾斜key进行随机打散，再做聚合
SELECT
    user_id,
    SUM(cnt)
FROM (
    SELECT
        user_id,
        COUNT(*) AS cnt
    FROM user_behavior
    GROUP BY user_id
    -- 如果知道某个特定user_id数据量巨大，可以单独处理
    UNION ALL
    SELECT
        user_id,
        COUNT(*) AS cnt
    FROM user_behavior
    WHERE user_id = 'skewed_user_id' -- 假设的倾斜key
    GROUP BY user_id
) t
GROUP BY user_id;
-- 这只是思路之一，具体方案需根据实际情况定制。

…
(持续到第95题，涵盖更多实战中的疑难杂症和解决方案)

Level 6：综合实战与应用（第96-100题）

目标：综合运用所有知识，解决复杂的业务分析需求。

96. 计算每个用户的复购率（购买过至少两次的比例）。


SELECT
    COUNT(CASE WHEN buy_count >= 2 THEN user_id END) / COUNT(user_id) AS repurchase_rate
FROM (
    SELECT user_id, COUNT(*) AS buy_count
    FROM user_behavior
    WHERE behavior = 'buy'
    GROUP BY user_id
) t;

97. 分析用户购买路径（例如：pv -> cart -> buy）。使用窗口函数LEAD/LAG模拟用户会话内的行为序列。
(此题较复杂，需要定义会话窗口，例如30分钟内无活动则为新会话)


-- 步骤1：为用户行为排序并标记会话开始
WITH ranked_events AS (
    SELECT
        user_id,
        behavior,
        timestamp,
        LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp) AS prev_ts,
        CASE
            WHEN (timestamp - LAG(timestamp) OVER (PARTITION BY user_id ORDER BY timestamp)) > (30 * 60 * 1000)
            THEN 1
            ELSE 0
        END AS is_new_session
    FROM user_behavior
),
-- 步骤2：为每个会话生成唯一ID
sessionized AS (
    SELECT
        user_id,
        behavior,
        timestamp,
        SUM(is_new_session) OVER (PARTITION BY user_id ORDER BY timestamp) AS session_id
    FROM ranked_events
)
-- 步骤3：分析会话内的行为序列（例如，使用COLLECT_LIST和CONCAT_WS）
SELECT
    user_id,
    session_id,
    CONCAT_WS(' -> ', COLLECT_LIST(behavior)) AS behavior_path -- 需要先按时间排序collect
FROM sessionized
GROUP BY user_id, session_id;
-- 这是一个简化模型，真实场景更复杂。

98. 使用Hive的ACID事务表（ORC格式）实现一个简单的更新操作。


-- 1. 首先确保Hive配置支持事务
SET hive.support.concurrency = true;
SET hive.txn.manager = org.apache.hadoop.hive.ql.lockmgr.DbTxnManager;
SET hive.compactor.initiator.on = true;
SET hive.compactor.worker.threads = 1;

-- 2. 创建支持事务的表（必须为ORC格式，并分桶）
CREATE TABLE transactional_table (
    id INT,
    value STRING
)
CLUSTERED BY (id) INTO 2 BUCKETS
STORED AS ORC
TBLPROPERTIES ('transactional'='true');

-- 3. 执行UPDATE操作
UPDATE transactional_table SET value = 'new_value' WHERE id = 1;

99. 对比TextFile、ORC、Parquet格式的查询性能。


-- 创建不同格式的相同数据表
CREATE TABLE user_behavior_textfile (...) STORED AS TEXTFILE;
CREATE TABLE user_behavior_orc (...) STORED AS ORC;
CREATE TABLE user_behavior_parquet (...) STORED AS PARQUET;

-- 将数据插入到这些表中
INSERT OVERWRITE TABLE user_behavior_orc SELECT * FROM user_behavior_textfile;

-- 分别执行相同的聚合查询，并观察执行时间
-- SET hive.compute.query.using.stats=false; -- 避免元数据缓存影响
-- 记录时间：SELECT COUNT(*) FROM user_behavior_textfile WHERE behavior='pv';
-- 记录时间：SELECT COUNT(*) FROM user_behavior_orc WHERE behavior='pv';
-- 记录时间：SELECT COUNT(*) FROM user_behavior_parquet WHERE behavior='pv';
-- ORC/Parquet因其列式存储和压缩，在聚合查询上通常有数量级的性能提升。

100. 设计一个简单的数据仓库分层模型（ODS -> DWD -> DWS -> ADS），并用Hive SQL实现一层到另一层的ETL脚本。


-- ODS层：原始数据层，与原始日志结构基本一致
CREATE TABLE ods_user_behavior (...) ... LOCATION '...';

-- DWD层：明细数据层，进行清洗、维度退化
CREATE TABLE dwd_user_behavior AS
SELECT
    user_id,
    item_id,
    category_id,
    behavior,
    FROM_UNIXTIME(CAST(timestamp/1000 AS BIGINT), 'yyyy-MM-dd') AS dt, -- 生成日期分区字段
    ...
FROM ods_user_behavior
WHERE dt >= '2020-01-01'; -- 数据清洗过滤

-- DWS层：服务数据层，按主题进行轻度聚合
CREATE TABLE dws_user_daily_behavior
PARTITIONED BY (dt STRING)
AS
SELECT
    user_id,
    dt,
    COUNT(CASE WHEN behavior='pv' THEN 1 END) AS pv_cnt,
    COUNT(CASE WHEN behavior='buy' THEN 1 END) AS buy_cnt,
    ...
FROM dwd_user_behavior
GROUP BY user_id, dt;

-- ADS层：应用数据层，面向具体业务需求（如用户画像报表）
CREATE TABLE ads_user_repurchase_analysis AS
SELECT
    dt,
    COUNT(CASE WHEN buy_cnt >= 2 THEN user_id END) / COUNT(user_id) AS repurchase_rate
FROM dws_user_daily_behavior
GROUP BY dt;

第四部分：超越练习题——Hive核心原理与调优深度解析

做完100题，你已经具备了强大的实战能力。但要真正“精通”，必须理解其底层原理。

1. Hive执行计划深度解读

使用EXPLAIN EXTENDED或EXPLAIN DEPENDENCY可以获得更详细的信息。关键要看：

Stage规划：Hive将查询切分成多少个MapReduce/Tez Stage。Operator树：查看每个Stage内部的操作符，如TableScan, Group By Operator, Reduce Output Operator等。数据流：理解数据如何在不同的Operator和Stage之间流动。

调优的第一步永远是查看并理解执行计划。

2. Join优化原理

Common Join（Reduce Side Join）：最普通的Join，在Reduce阶段进行。处理大规模数据时容易产生瓶颈。Map Join：优化重点！ 当一个小表和一个大表Join时，可以将小表完全加载到每个Map任务的内存中，直接在Map端完成Join，避免Reduce阶段。


SET hive.auto.convert.join = true; -- 默认开启
SET hive.mapjoin.smalltable.filesize = 25000000; -- 判断为小表的阈值（25MB）

Bucket Map Join：如果两个表都以相同方式（Join Key）分桶，且桶的数量相等，可以在Map端直接进行桶对桶的Join，效率极高。Skew Join：处理Join Key数据倾斜的优化。


SET hive.optimize.skewjoin = true;
SET hive.skewjoin.key = 100000; -- 认为key是倾斜key的阈值

3. 数据存储格式与压缩的数学权衡

选择ORC或Parquet不仅是趋势，更是性能要求。其优势源于列式存储的数学原理。

行式存储（如TextFile）：

{

}

Data_{row} = {R_1, R_2, R_3, …, R_n}

Datarow={R1,R2,R3,…,Rn}。适合OLTP，查询整行数据快。列式存储（如ORC）：

{

}

Data_{column} = {C_1, C_2, C_3, …, C_m}

Datacolumn={C1,C2,C3,…,Cm}。适合OLAP，查询少数列极快。

优势：

高效压缩：同一列的数据类型相同，熵更低，压缩比远高于行存储（通常可达5-10倍）。压缩算法如ZLIB、SNAPPY。
压缩率

R_c = frac{Size_{raw}}{Size_{compressed}}

Rc=SizecompressedSizeraw 谓词下推：在读取数据时，可以根据WHERE条件跳过不必要的列和行组（Stripe），极大减少I/O。例如，查询WHERE behavior = 'buy'，只需要读取behavior这一列和相关的偏移量数据，而不是整行数据。向量化查询：Hive的Vectorization特性允许一次处理1024行数据的一整批列，而不是一次处理一行，充分利用现代CPU的SIMD指令集，大幅提升计算效率。


SET hive.vectorized.execution.enabled = true;

4. 计算引擎的选择：Tez vs. MapReduce

MapReduce：经典但笨重。每个Hive查询阶段（Stage）都会触发MR任务，每个MR任务都需要将中间结果写入HDFS，磁盘I/O是巨大开销。

Tez：推荐使用。它将Job描绘为DAG（有向无环图），允许更灵活的任务编排，避免不必要的中间落盘，可以复用容器，从而大幅降低延迟。其性能通常比MR快数倍。


SET hive.execution.engine = tez;

第五部分：总结与展望

通过这100道练习题和背后的原理深度解析，你已经系统地走完了从Hive SQL零基础到精通的道路。我们覆盖了：

基础操作：查询、过滤、聚合、分组。核心技能：多表连接、子查询、日期处理。高级武器：窗口函数、复杂优化技巧。内功心法：执行计划、Join优化、存储格式、计算引擎。

未来趋势：

云原生：Hive on Spark、Hive on Kubernetes正在成为新趋势，资源调度更灵活。LLAP（Live Long and Process）：Hive 2.0引入的守护进程，可实现亚秒级查询响应，模糊了传统数据仓库和MPP数据库的界限。湖仓一体（Data Lakehouse）：以Hudi、Iceberg、Delta Lake为代表的表格式，在数据湖（HDFS/S3）上实现了类似数据仓库的ACID事务、增量更新等能力。Hive正在积极集成这些格式，未来Hive可能会更多地扮演查询引擎的角色，服务于底层不同的存储格式。

学习永无止境。最好的学习方法，就是在实践中不断遇到问题，然后深入研究并解决它。希望你以此文为起点，在广阔的大数据世界里继续遨游，成为一名真正的数据高手！

工具和资源推荐：

官方文档：Apache Hive官方文档永远是第一参考资料。书籍：《Hive编程指南》、《Apache Hive Essentials》。社区：Stack Overflow、Apache Hive JIRA、各类技术博客。

祝你编码愉快，数据之路越走越宽广！