你遇到的“经典问题”变体：这是一个“模板迁移式课程设计”问题——给定一个样例（NumPy 的 Exercism 风格分步练习），要求在同一体例下为新领域（Hive / 大数据技术栈）构建练习驱动课程，包含小步迭代、代码框架与可验证结果。
---
# Hive 与大数据技术栈入门（Exercism 风格 · 约 10 小时）
> 面向已有 SQL 基础的学习者。每个步骤包含理论介绍 → 编程任务 → 验证机制 → 参考代码。
> 为便于自测，文中提供小型数据集与期望结果（多为行数或取前若干行的值）。
> 建议运行环境：Apache Hive 3.x，Hadoop 3.x；执行引擎可使用 MR/Tez/Spark 任一（默认 MR/Tez 皆可）。客户端建议 `beeline`。
## 目录
0. 练习准备：数据与目录
1. [[HDFS]] 基础：放数与查看
2. 数据库与表：Managed vs External
3. 分区表：静态/动态分区与元数据修复
4. 查询基础：筛选、排序、限制
5. 聚合与去重：按月营收与城市排行
6. 窗口函数：找出用户最近一单与生命周期消费
7. 连接与半连接：数据对齐、反连接挖“脏数据”
8. 半结构化处理：JSON 提取与展开
9. 存储格式与压缩：Text vs ORC/Parquet、统计信息
10. 分桶与采样、EXPLAIN：性能与可解释性
—附录：大数据技术栈鸟瞰与延伸练习
---
## Step 0（准备 · 30min）—练习目录与数据
目标：在本地创建小样本数据并放到 HDFS 目录，后续步骤复用。
### 0.1 建议目录
```bash
# 本地（存放样本文件）
mkdir -p ~/ex_hive_data/{users,orders,events,events_json}
# HDFS（练习区）
hdfs dfs -mkdir -p /user/$USER/ex_hive/raw/{users,orders,events,events_json}
```
### 0.2 写入本地样本文件
users.csv（8 行）→ `~/ex_hive_data/users/users.csv`
```Java
1,Alice,Beijing,2023-12-05
2,Bob,Shanghai,2023-12-10
3,Carol,Beijing,2024-01-02
4,David,Shenzhen,2024-02-14
5,Eva,Chengdu,2024-03-01
6,Frank,Beijing,2024-03-15
7,Grace,Shanghai,2024-04-09
8,Heidi,Beijing,2024-05-20
```
orders.csv（12 行）→ `~/ex_hive_data/orders/orders.csv`
```Java
1001,1,2024-01-03 10:23:00,paid,120.50
1002,2,2024-01-15 17:05:10,cancelled,0.00
1003,3,2024-02-08 09:10:22,paid,310.00
1004,1,2024-03-12 12:01:05,paid,89.90
1005,4,2024-03-20 19:22:31,paid,450.00
1006,5,2024-04-05 08:00:00,paid,39.00
1007,6,2024-04-18 21:15:45,refunded,99.00
1008,2,2024-05-01 11:11:11,paid,560.00
1009,7,2024-05-02 14:55:00,paid,230.00
1010,8,2024-05-20 07:30:30,paid,120.00
1011,3,2024-06-03 13:00:00,paid,45.00
1012,1,2024-06-30 20:20:20,paid,300.00
```
events（按分区日期存放的 tsv）
将下列内容分别保存为：
`~/ex_hive_data/events/dt=2024-05-01/events.tsv`
```Java
2024-05-01 10:00:00 2 /home 2024-05-01
2024-05-01 10:05:00 2 /product/sku_123 2024-05-01
2024-05-01 10:07:20 2 /cart 2024-05-01
2024-05-01 11:11:11 2 /checkout 2024-05-01
2024-05-01 12:00:00 9 /home 2024-05-01
```
`~/ex_hive_data/events/dt=2024-05-02/events.tsv`
```Java
2024-05-02 09:00:00 7 /home 2024-05-02
2024-05-02 09:10:00 7 /search?q=shoes 2024-05-02
2024-05-02 09:12:00 7 /product/sku_999 2024-05-02
```
`~/ex_hive_data/events/dt=2024-05-20/events.tsv`
```Java
2024-05-20 07:29:50 8 /home 2024-05-20
```
`~/ex_hive_data/events/dt=2024-06-03/events.tsv`
```Java
2024-06-03 12:45:00 3 /home 2024-06-03
```
`~/ex_hive_data/events/dt=2024-06-30/events.tsv`
```Java
2024-06-30 20:05:00 1 /home 2024-06-30
```
events\_json（行式 JSON）→ `~/ex_hive_data/events_json/events.json`
```Java
{"ts":"2024-05-01T10:05:00","uid":2,"event":"view","props":{"sku":"sku_123","price":560.0}}
{"ts":"2024-05-02T09:10:00","uid":7,"event":"search","props":{"q":"shoes"}}
{"ts":"2024-06-03T13:00:00","uid":3,"event":"purchase","props":{"order_id":1011,"amount":45.0}}
```
### 0.3 上传到 HDFS
```bash
hdfs dfs -put -f ~/ex_hive_data/users/users.csv /user/$USER/ex_hive/raw/users/
hdfs dfs -put -f ~/ex_hive_data/orders/orders.csv /user/$USER/ex_hive/raw/orders/
hdfs dfs -put -f ~/ex_hive_data/events/dt=2024-05-01 /user/$USER/ex_hive/raw/events/
hdfs dfs -put -f ~/ex_hive_data/events/dt=2024-05-02 /user/$USER/ex_hive/raw/events/
hdfs dfs -put -f ~/ex_hive_data/events/dt=2024-05-20 /user/$USER/ex_hive/raw/events/
hdfs dfs -put -f ~/ex_hive_data/events/dt=2024-06-03 /user/$USER/ex_hive/raw/events/
hdfs dfs -put -f ~/ex_hive_data/events/dt=2024-06-30 /user/$USER/ex_hive/raw/events/
hdfs dfs -put -f ~/ex_hive_data/events_json/events.json /user/$USER/ex_hive/raw/events_json/
```
---
## Step 1（30min）—HDFS 基础：放数与查看
理论：学会 `-ls/-cat/-du` 等操作，理解路径与权限。
任务
1. 列出练习根目录与子目录。
2. 查看 `orders.csv` 前几行。
3. 统计 `events` 目录下的总文件大小。
验证
`hdfs dfs -ls -R /user/$USER/ex_hive/raw | wc -l` 行数应 $\ge 8$（各目录与文件）。
`hdfs dfs -cat /user/$USER/ex_hive/raw/orders/orders.csv | head -3` 能看到 3 行订单。
`hdfs dfs -du -h /user/$USER/ex_hive/raw/events` 能显示每个分区目录大小（非空）。
参考命令
```bash
hdfs dfs -ls -R /user/$USER/ex_hive/raw
hdfs dfs -cat /user/$USER/ex_hive/raw/orders/orders.csv | head -3
hdfs dfs -du -h /user/$USER/ex_hive/raw/events
```
---
## Step 2（45min）—数据库与表：Managed vs External
理论
Managed 表：数据受 Hive 管理，删表会删数据。
External 表：仅管理元数据，删表不删 HDFS 中的数据。
行存储 Text，字段分隔符用 `FIELDS TERMINATED BY`。
任务
1. 建库并切换：`ex_hive`。
2. 建 managed 表 `users_text`（TextFile）。
3. 建 external 表 `orders_ext` 指向 HDFS 路径。
参考代码（HQL）
```sql
CREATE DATABASE IF NOT EXISTS ex_hive;
USE ex_hive;
-- Managed: users
DROP TABLE IF EXISTS users_text;
CREATE TABLE users_text(
user_id INT,
name STRING,
city STRING,
signup_dt DATE
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE;
LOAD DATA INPATH '/user/${hiveconf:user}/ex_hive/raw/users/users.csv'
INTO TABLE users_text;
-- External: orders
DROP TABLE IF EXISTS orders_ext;
CREATE EXTERNAL TABLE orders_ext(
order_id INT,
user_id INT,
order_ts TIMESTAMP,
status STRING,
total_amount DECIMAL(10,2)
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
STORED AS TEXTFILE
LOCATION '/user/${hiveconf:user}/ex_hive/raw/orders';
```
> 运行 beeline 前可带 `--hiveconf user=$USER`，或在会话里执行：`SET user=$USER;`
验证
```sql
SELECT COUNT() FROM users_text; -- 期望：8
SELECT COUNT() FROM orders_ext; -- 期望：12
DESCRIBE FORMATTED orders_ext; -- 期望：Table Type: EXTERNAL_TABLE
```
---
## Step 3（45min）—分区表与元数据修复
理论
分区能让查询裁剪特定目录，减少扫描量。
使用 `MSCK REPAIR TABLE` 扫描分区目录并修复元数据。
任务
1. 基于 events 按 `dt` 分区建 external 表 `events_log`。
2. 使用 `MSCK REPAIR TABLE` 自动加载已有分区。
3. 查看分区与总行数。
参考代码
```sql
USE ex_hive;
DROP TABLE IF EXISTS events_log;
CREATE EXTERNAL TABLE events_log(
ts STRING,
user_id INT,
url STRING
)
PARTITIONED BY (dt STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
LOCATION '/user/${hiveconf:user}/ex_hive/raw/events';
MSCK REPAIR TABLE events_log; -- 扫描 dt=... 目录并登记分区
```
验证
```sql
SHOW PARTITIONS events_log; -- 期望有 5 个分区：2024-05-01, 2024-05-02, 2024-05-20, 2024-06-03, 2024-06-30
SELECT COUNT() FROM events_log; -- 期望：11
SELECT COUNT() FROM events_log WHERE dt='2024-05-01'; -- 期望：5
```
---
## Step 4（45min）—查询基础：WHERE / ORDER BY / LIMIT
理论：SARGable 条件、隐式转换与时间函数（此处 TS 已为 TIMESTAMP）。
任务
1. 统计 已支付 订单条数与总金额。
2. 取金额最高的 Top-3 已支付订单（展示 `order_id,total_amount`）。
3. 统计 `users_text` 中不同城市数。
参考代码
```sql
USE ex_hive;
-- 1) 已支付
SELECT COUNT() AS paid_cnt,
SUM(total_amount) AS paid_sum
FROM orders_ext
WHERE status='paid';
-- 2) Top-3 paid
SELECT order_id, total_amount
FROM orders_ext
WHERE status='paid'
ORDER BY total_amount DESC
LIMIT 3;
-- 3) 城市去重
SELECT COUNT(DISTINCT city) AS city_cnt
FROM users_text;
```
验证（期望）
`paid_cnt = 10`，`paid_sum = 2264.40`
Top-3 `total_amount`：`560.00, 450.00, 310.00`（对应 `1008,1005,1003`）
`city_cnt = 4`
---
## Step 5（60min）—聚合与去重：按月营收与城市排行
理论：`GROUP BY`、`DATE_FORMAT`/`from_unixtime`、`HAVING`。
任务
1. 统计 2024-01 \~ 2024-06 各月已支付营收（`yyyy-MM`）。
2. 计算各城市（按用户归属）已支付营收并排序（降序）。
参考代码
```sql
-- 1) 月营收
WITH paid AS (
SELECT order_id, user_id, order_ts, total_amount
FROM orders_ext
WHERE status='paid'
)
SELECT DATE_FORMAT(order_ts,'yyyy-MM') AS ym,
ROUND(SUM(total_amount),2) AS revenue
FROM paid
GROUP BY DATE_FORMAT(order_ts,'yyyy-MM')
ORDER BY ym;
-- 2) 城市营收
SELECT u.city,
ROUND(SUM(o.total_amount),2) AS revenue
FROM orders_ext o
JOIN users_text u ON o.user_id=u.user_id
WHERE o.status='paid'
GROUP BY u.city
ORDER BY revenue DESC, u.city;
```
验证（期望）
月营收（ym → revenue）
`2024-01 → 120.50`，`2024-02 → 310.00`，`2024-03 → 539.90`，`2024-04 → 39.00`，`2024-05 → 910.00`，`2024-06 → 345.00`
城市营收（降序）
`Beijing → 985.40`，`Shanghai → 790.00`，`Shenzhen → 450.00`，`Chengdu → 39.00`
---
## Step 6（60min）—窗口函数：最近一单与生命周期消费
理论
`ROW_NUMBER()/RANK()`：排序内分组。
`FIRST_VALUE()/LAST_VALUE()` 与累积 `SUM(...) OVER (PARTITION BY...)`。
任务
1. 为每个用户找出最近一单（`last_order_ts,last_status,last_amount`）。
2. 统计每个用户生命周期已支付总额（含退款不计入）。
3. 计算“最近一单状态为 paid”的用户数。
参考代码
```sql
WITH ord AS (
SELECT o.,
CASE WHEN status='paid' THEN total_amount ELSE 0 END AS paid_amt
FROM orders_ext o
),
ranked AS (
SELECT user_id, order_id, order_ts, status, total_amount,
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_ts DESC) AS rn,
SUM(paid_amt) OVER (PARTITION BY user_id) AS lifetime_paid
FROM ord
)
SELECT user_id,
MAX(CASE WHEN rn=1 THEN order_ts END) AS last_order_ts,
MAX(CASE WHEN rn=1 THEN status END) AS last_status,
MAX(CASE WHEN rn=1 THEN total_amount END) AS last_amount,
MAX(lifetime_paid) AS lifetime_paid
FROM ranked
GROUP BY user_id
ORDER BY user_id;
```
验证（期望）
“最近一单状态为 paid”的用户数 $= 7$（仅 `user_id=6` 最近一单为 `refunded`）。
---
## Step 7（45min）—连接与半连接：数据对齐与脏数据定位
理论
`LEFT/RIGHT/INNER JOIN` 与 `LEFT SEMI/ANTI`。
通过反连接查“游离记录”。
任务
1. 检查订单中是否存在无用户档案的记录（订单表的 user\_id 不在用户表）。
2. 检查访问日志中是否存在未建档用户（events 有 user\_id，但用户表无此人）。
参考代码与验证
```sql
-- 1) 订单是否有“孤儿用户”（期望：0）
SELECT COUNT() AS orphan_orders
FROM orders_ext o
LEFT ANTI JOIN users_text u
ON o.user_id = u.user_id;
-- 2) 访问日志中的“未知用户”（期望：1个 user_id=9）
SELECT DISTINCT e.user_id
FROM events_log e
LEFT ANTI JOIN users_text u
ON e.user_id = u.user_id
ORDER BY e.user_id;
```
期望结果：`orphan_orders = 0`；未知用户列表仅 9。
---
## Step 8（60min）—半结构化处理：JSON 提取与展开
理论
使用 `get_json_object`/`json_tuple` 从 JSON 字符串提取字段。
`LATERAL VIEW` 配合 `explode/json_tuple` 进行列展开。
任务
1. 创建原始 JSON 行表并读取。
2. 解析 `ts, uid, event` 与 `props` 中的键值。
3. 统计 `event='purchase'` 的条数与对应 `order_id, amount`。
参考代码
```sql
USE ex_hive;
DROP TABLE IF EXISTS events_json_raw;
CREATE EXTERNAL TABLE events_json_raw(line STRING)
LOCATION '/user/${hiveconf:user}/ex_hive/raw/events_json';
-- 解析（使用 get_json_object）
WITH parsed AS (
SELECT
get_json_object(line,'$.ts') AS ts,
CAST(get_json_object(line,'$.uid') AS INT) AS uid,
get_json_object(line,'$.event') AS event,
get_json_object(line,'$.props.order_id') AS order_id,
get_json_object(line,'$.props.amount') AS amount,
get_json_object(line,'$.props.q') AS q,
get_json_object(line,'$.props.sku') AS sku
FROM events_json_raw
)
SELECT FROM parsed;
-- 统计 purchase
SELECT COUNT() AS purchase_cnt
FROM (
SELECT
FROM events_json_raw
) t
WHERE get_json_object(line,'$.event')='purchase';
-- 查看 purchase 明细（期望 order_id=1011, amount=45.0）
SELECT
get_json_object(line,'$.uid') AS uid,
get_json_object(line,'$.props.order_id') AS order_id,
get_json_object(line,'$.props.amount') AS amount
FROM events_json_raw
WHERE get_json_object(line,'$.event')='purchase';
```
验证（期望）
`purchase_cnt = 1`；对应 `(uid=3, order_id=1011, amount=45.0)`。
---
## Step 9（60min）—存储格式与压缩：Text vs ORC/Parquet、统计信息
理论
列式存储（ORC/Parquet）在扫描少量列、聚合/压缩时通常优于 Text。
`ANALYZE TABLE... COMPUTE STATISTICS` 让优化器更聪明。
可调整压缩：`SET hive.exec.compress.output=true;`，ORC 常用 `SNAPPY`。
任务
1. 用 CTAS 生成 ORC 表 `orders_orc` 与 Parquet 表 `orders_parq`（二选一或都做）。
2. 收集表与列统计信息。
3. 对比 `DESCRIBE FORMATTED` 观察 InputFormat/SerDe。
参考代码
```sql
USE ex_hive;
SET hive.exec.compress.output=true;
SET orc.compress=SNAPPY;
DROP TABLE IF EXISTS orders_orc;
CREATE TABLE orders_orc
STORED AS ORC
AS
SELECT FROM orders_ext;
DROP TABLE IF EXISTS orders_parq;
CREATE TABLE orders_parq
STORED AS PARQUET
AS
SELECT FROM orders_ext;
-- 统计信息
ANALYZE TABLE orders_orc COMPUTE STATISTICS;
ANALYZE TABLE orders_orc COMPUTE STATISTICS FOR COLUMNS order_id, user_id, status;
DESCRIBE FORMATTED orders_orc; -- 期望看到 OrcInputFormat / OrcSerde
DESCRIBE FORMATTED orders_parq; -- 期望看到 ParquetInputFormat / ParquetHiveSerDe
```
验证（期望）
`SELECT COUNT() FROM orders_orc;` → 12
`DESCRIBE FORMATTED` 中的 InputFormat/SerDe 分别为 ORC/Parquet 对应实现。
---
## Step 10（75min）—分桶与采样、EXPLAIN：性能与可解释性
理论
分桶（Bucketing）在按桶键连接或抽样时更高效；`TABLESAMPLE (BUCKET x OUT OF y)`。
需要 `SET hive.enforce.bucketing=true;` 插入时按桶写出。
`EXPLAIN` 查看计划（Map Join / Bucket Map Join / 分区裁剪等）。
任务
1. 创建按 `user_id` 分 4 桶的 ORC 表 `orders_bkt_orc`，从 `orders_orc` 写入。
2. 用 `TABLESAMPLE` 抽取 $1/4$ 数据（近似）。
3. `EXPLAIN` 一条带 `WHERE status='paid'` 的查询，确认分区裁剪/谓词下推等优化（至少看到扫描的表与过滤条件）。
参考代码
```sql
USE ex_hive;
SET hive.enforce.bucketing=true;
DROP TABLE IF EXISTS orders_bkt_orc;
CREATE TABLE orders_bkt_orc(
order_id INT,
user_id INT,
order_ts TIMESTAMP,
status STRING,
total_amount DECIMAL(10,2)
)
CLUSTERED BY (user_id) INTO 4 BUCKETS
STORED AS ORC;
INSERT OVERWRITE TABLE orders_bkt_orc
SELECT FROM orders_orc;
-- 采样（1/4 桶）
SELECT COUNT() AS sample_cnt
FROM orders_bkt_orc TABLESAMPLE(BUCKET 1 OUT OF 4);
-- 计划分析
EXPLAIN
SELECT user_id, SUM(total_amount) AS rev
FROM orders_bkt_orc
WHERE status='paid'
GROUP BY user_id;
```
验证（期望）
`SELECT COUNT() FROM orders_bkt_orc;` → 12
`sample_cnt` $\approx$ 3（接近 $1/4$，总数小可能偏差）
`EXPLAIN` 输出中应包含对 `status='paid'` 的过滤说明（不同引擎/版本呈现略有差异，重点是能看到扫描对象与过滤条件）。
---
# 附录 A—大数据技术栈鸟瞰（把 Hive 放在地图上）
存储层：HDFS / 对象存储（S3/OSS）
资源调度：YARN / Kubernetes
执行引擎：MapReduce（基线）、Tez（Hive 常用）、Spark（Hive on Spark / Spark SQL）
SQL 引擎：Hive（批/ETL 强项）、Presto/Trino（交互式查询）、Impala（MPP）
数据格式：ORC/Parquet/Avro（列式首选）、Text/CSV（落地/对接方便）
元数据：Hive Metastore / AWS Glue
采集/对接：Kafka（实时）、Flume、Sqoop（关系库导入）
调度编排：Oozie / Airflow
治理与质量：Ranger（权限）、Atlas（血缘）、Deequ/Great Expectations（质量）
> 经验法则：ODS（原始层）→ DWD（明细层）→ DWS（汇总层）→ ADS（应用层）的分层建模，在 Hive 中常以External 表 + 分区/列式配合落地。
---
# 附录 B—延伸练习（选做）
1. 动态分区插入
```sql
SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
-- 构造 stage 表（含 dt 字段），然后动态插入到按 dt 分区表中
-- 这里可将 events_log 的数据转入一个新的分区模型表
```
2. ACID 表与事务（Hive 3.x）：创建 `transactional=true` 的 ORC 表，尝试 `INSERT/UPDATE/DELETE`，并观察 compaction。
3. Map Join / Bucket Map Join：在小表侧启用 `SET hive.auto.convert.join=true;`，观察 `EXPLAIN` 的 join 策略变化。
4. 成本对比：同一查询在 Text/ORC/Parquet 上测试 `elapsed time`，理解列式优势。
---
## 提交与组织（仿 Exercism）
建议每一小节建一个目录，放入：
`instructions.md`（本节“理论/任务/验证”的摘录）
`starter.hql`（留有 `-- TODO:` 注释位置）
`verify.hql`（验证查询）
`README.md`（记录你运行的命令与观察）
---
### 小结
通过上述 10 个练习，你已完成从 HDFS 基操 → Hive 表管理 → 分区/聚合/窗口/连接 → 半结构化 JSON → 列式/统计/分桶/EXPLAIN 的闭环实践，并在大数据技术栈中定位了 Hive 的位置。
如果你愿意，我可以把这些步骤打包为一组文件骨架（目录 + `starter.hql/verify.hql` 模板 + `instructions.md`），方便你直接开始做题。