****你遇到的****“****经典问题****”****变体：****这是一个****“****模板迁移式课程设计****”****问题——给定一个样例（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`），方便你直接开始做题。