Hive 课程复习

2. 服务启动

课堂环境使用 Hadoop 2.6.5 与 Hive 1.2.1，顺序是 Hadoop → Metastore → HiveServer2 → 客户端。

# 1. 启动 Hadoop（课堂环境）
cd /usr/local/hadoop-2.6.5/sbin
./start-all.sh

# 2. 启动 Hive Metastore
cd /usr/local/apache-hive-1.2.1-bin/bin
./hive --service metastore &

# 3. 启动 HiveServer2
./hive --service hiveserver2 &

# 4. 启动 Hive CLI
./hive

版本提示：start-all.sh 与 Hive CLI 已是旧式用法。新环境通常分别使用 start-dfs.sh、start-yarn.sh，客户端优先使用 beeline。考试按课件，实际部署按当前版本文档。

8. 下面关于 Hive 内部表和外部表描述正确的是（　）。

A. Hive 内部表的元数据和数据都由 Hive 自己管理。
B. Hive 不会管理外部表的元数据。
C. 当 Hive 内部表的元数据发生变化时，内部表的修改不会同步给元数据。
D. 对外部表的表结构和分区进行修改，不需要修复。

答案：A. Hive 管理内部表的元数据与数据。Hive 同样管理外部表元数据，所以 B 错；修改表结构会同步更新元数据，所以 C 错；结构或分区发生改变仍须维护元数据，所以 D 错。

9. 以下关于 HiveSQL 基本操作描述正确的是（　）。

A. 创建外部表必须要指定 LOCATION 信息。
B. 创建外部表使用 EXTERNAL 关键字，创建普通表需要指定 INTERNAL 关键字。
C. 创建表时可以指定列分隔符。
D. 加载数据到 Hive 时，源数据必须是 HDFS 的一个路径。

答案：C. 外部表不一定必须显式指定 LOCATION；普通表没有 INTERNAL 关键字；使用 LOCAL 可以从本地文件系统加载数据。

10. 关于 Hive 中的桶说法不正确的是（　）。

A. 每个桶是一个目录。
B. 建表时指定桶个数，桶内可排序。
C. 数据按照某个字段的值 Hash 后放入某个桶中。
D. 对于数据抽样、特定 Join 的优化很有意义。

答案：A. 桶通常对应数据文件，而不是目录。指定桶数、按字段 Hash 分桶，以及用于抽样和特定 Join 优化的描述均成立。

9. Test1 数据库与复杂类型综合练习

本章内容来自原资料中的 test1.txt，题目与标准答案逐项对应。

9.1 题目

创建名为 ecommerce 的数据库，如果已存在则不报错。
为该数据库添加注释 This is an ecommerce database。
修改数据库属性，增加键值对 owner=analyst。
切换到 ecommerce 数据库，并设置 Hive CLI 显示当前数据库名称。
假设已存在用户表 dim_users，复杂类型字段如下： | 字段 | 类型 | |-----------|-------------------------------------------------| | tags | array<string> | | profile | map<string,string> | | address | struct<street:string,district:string,zip:int> |
1. 查询 tags 中包含 VIP 的用户，并显示其第一个标签。
2. 提取 profile 中的年龄和性别，年龄转换为整数。
3. 查询用户所在区和邮编，只筛选“朝阳区”或“浦东新区”。

9.2 数据库操作答案

-- 第 1、2 题：IF NOT EXISTS 避免数据库已存在时报错
CREATE DATABASE IF NOT EXISTS ecommerce
COMMENT 'This is an ecommerce database';

-- 第 3 题：增加数据库属性
ALTER DATABASE ecommerce
SET DBPROPERTIES ('owner' = 'analyst');

-- 第 4 题：切换数据库并让 CLI 提示符显示当前数据库
USE ecommerce;
SET hive.cli.print.current.db = true;

说明：COMMENT 是 CREATE DATABASE 的子句，应放在语句分号之前；DBPROPERTIES 的键和值均使用字符串。

9.3 ARRAY 查询答案

SELECT user_id,
       tags[0] AS first_tag
FROM dim_users
WHERE array_contains(tags, 'VIP');

array_contains 判断数组是否包含指定值；Hive 数组下标从 0 开始，因此 tags[0] 是第一个标签。

9.4 MAP 查询答案

SELECT user_id,
       CAST(profile['age'] AS INT) AS age,
       profile['gender'] AS gender
FROM dim_users;

Map 使用 profile['key'] 取值；由于 profile 的值类型是 STRING，年龄需要用 CAST 转成 INT。

9.5 STRUCT 查询答案

SELECT user_id,
       address.district AS district,
       address.zip AS zip
FROM dim_users
WHERE address.district IN ('朝阳区', '浦东新区');

Struct 使用点号访问成员；IN 用于匹配给定的两个行政区。

ARRAY

下标访问：tags[0]

MAP

键访问：profile['age']

STRUCT

成员访问：address.district

11. 图片课件补充：Hive 查询与综合练习

4.1 装载数据到表中

Hive 没有常规的行级数据插入、更新和删除操作。向表中写入数据，通常使用批量装载，或者直接将数据文件写入表对应的正确目录。

`LOAD DATA` 基本语法

LOAD DATA LOCAL INPATH '/root/file1.txt'
INTO TABLE logs
PARTITION (dt='2018-02-07', country='China');

LOCAL 表示源文件位于本地文件系统；不写 LOCAL 时，源路径通常指 HDFS。
LOAD DATA 可以把数据文件装载到 Hive 表对应的分区目录。
如果目标分区目录不存在，命令会先创建分区目录，再将数据复制或移动到该目录。
如果目标表不是分区表，应省略 PARTITION 子句。

`OVERWRITE` 与追加

LOAD DATA LOCAL INPATH '/root/file1.txt'
OVERWRITE INTO TABLE logs
PARTITION (dt='2018-02-07', country='China');

指定 OVERWRITE：先删除目标目录中原有的数据，再装载新数据。
不指定 OVERWRITE：保留原数据，将新文件加入目标目录。
如果目标目录中已有与待装载文件同名的文件，旧的同名文件会被覆盖。

INSERT INTO 和 INSERT OVERWRITE 的区别同样是追加与覆盖：

INSERT INTO 向表或分区追加数据，不会因为数据内容重复而报错。
INSERT OVERWRITE 覆盖目标表或目标分区中原有的数据。

5.1 基础查询与选择

`SELECT ... FROM ...`

SELECT 决定输出列，FROM 指定数据来源，数据来源可以是表、视图或子查询。

SELECT *
FROM employees;

SELECT * 查询全部列，适合快速查看数据。
表别名为表指定临时名称，在自连接或多表连接中必须使用。
可以直接在 SELECT 中对列做算术运算。

SELECT
  e.employee_id,
  e.salary * 12 AS annual_salary
FROM employees e;

常见算术和位运算符包括：+、-、*、/、%、&、|、^、~。

列别名

使用 表达式 AS 别名 为计算结果或复杂表达式指定有意义的名称：

SELECT salary * 12 AS annual_salary
FROM employees;

WHERE 子句不能直接使用同一层 SELECT 中定义的列别名，因为 SQL 的逻辑执行顺序是 FROM → WHERE → SELECT。需要按别名过滤时，可使用嵌套子查询；聚合后的别名也可在 HAVING 中按实际语法使用。

复杂数据类型访问

SELECT
  subordinates[0] AS first_subordinate,
  deductions['tax'] AS tax,
  address.city AS city
FROM employees;

ARRAY：通过下标访问，下标从 0 开始，例如 subordinates[0]。
MAP：通过字符串键访问，例如 deductions['tax']。
STRUCT：通过点号访问字段，例如 address.city。

5.4 排序与数据分发

`ORDER BY`、`SORT BY`、`DISTRIBUTE BY`、`CLUSTER BY`

对比项	`ORDER BY`	`SORT BY`	`DISTRIBUTE BY`	`CLUSTER BY`
核心作用	全局排序	每个 Reducer 内部局部排序	控制数据分发，类似分区	同一列上的 `DISTRIBUTE BY` + `SORT BY`，默认升序
Reducer 数量	强制 1 个	多个	多个	多个
输出有序性	单个文件，全局有序	多个文件，文件内有序，文件间无序	只保证相同键进入同一 Reducer，不保证顺序	多个文件，每个文件内部按分发列升序
是否全局有序	是	否	否	否
典型场景	最终报表、Top N、必须全局有序的小结果集	相同 `a` 进入同一 Reducer 后，再按 `b` 排序	相同 `a` 进入同一 Reducer	相同 `a` 进入同一 Reducer，内部按 `a` 升序

限制行数

SELECT *
FROM employees
LIMIT 10;

LIMIT n 返回前 n 行。

类型转换

SELECT CAST(value AS INT)
FROM source_table;

使用 CAST(value AS type) 进行类型转换。无法转换的值通常得到 NULL。

抽样查询

SELECT *
FROM source_table
TABLESAMPLE(BUCKET 1 OUT OF 4 ON user_id);

课件给出的抽样语法为 TABLESAMPLE(BUCKET x OUT OF y ON 列名)。

练习题 12—16

练习题 12

下面关于 HiveQL 中 INSERT INTO 和 INSERT OVERWRITE 的区别，说法不正确的是（　）。

A. INSERT INTO 不会覆盖已经存在的数据。
B. INSERT OVERWRITE 会先将原始表的数据删除，再插入新数据。
C. INSERT INTO 不考虑原始数据，直接追加到表中。
D. INSERT INTO 重复的数据会报错。

答案：D。 Hive 表默认不会因为追加了重复内容而报错；是否需要去重由查询或数据处理逻辑决定。

练习题 13

Hive 中分组的关键字是（　）。

A. GROUP BY
B. ORDER BY
C. DISTRIBUTE BY
D. SORT BY

答案：A。 GROUP BY 用于按照一个或多个字段分组。

练习题 14

已知数组 trans_cnt 的值为 [1, 2, 3, 4]，trans_cnt[2] 的结果为（　）。

A. 1
B. 2
C. 3
D. 4

答案：C。 Hive 的数组下标从 0 开始，下标 2 对应第三个元素 3。

练习题 15

在 Hive 中，不可以作为去重命令直接使用的是（　）。

A. DISTINCT
B. GROUP BY
C. ROW_NUMBER
D. HAVING

答案：D。 HAVING 用于过滤分组后的结果，本身不是去重方法。DISTINCT 和 GROUP BY 可直接去重，ROW_NUMBER() 可配合分区和序号筛选实现按规则去重。

练习题 16

外连接进行 Join 默认在（　）。

A. Map 端
B. Reduce 端
C. External 端
D. Shuffle 端

答案：B。 Hive 的普通 Join 默认在 Reduce 端完成；满足特定条件时可以使用 Map Join 优化。

练习题 17：在线音乐平台综合分析

某在线音乐平台“乐享音乐”希望分析用户的付费订阅和退订行为，以优化内容推荐和定价策略。平台维护用户信息、歌曲信息、订阅支付记录和退订记录四张核心表。

数据规模：用户表约 3000 万行，订阅支付记录每日新增约 200 万行，歌曲表约 60 万行，退订记录每日新增约 5 万行。

表结构

用户表 `dim_users`

字段	类型	含义
`user_id`	`string`	用户编号
`user_name`	`string`	用户名
`register_date`	`string`	注册日期
`country`	`string`	国家
`tags`	`array<string>`	标签，如 `['premium', 'family']`
`profile`	`map<string,string>`	扩展属性，如 `{'age':'22','gender':'F'}`
`vip_level`	`int`	VIP 等级，范围 0—5

歌曲表 `dim_songs`

字段	类型	含义
`song_id`	`string`	歌曲编号
`song_name`	`string`	歌曲名称
`genre_l1`	`string`	一级流派
`genre_l2`	`string`	二级流派
`price`	`double`	单曲订阅价格
`singer`	`string`	歌手

订阅支付记录表 `fact_subscriptions`

字段	类型	含义
`sub_id`	`string`	订阅记录号
`user_id`	`string`	用户编号
`song_id`	`string`	歌曲编号
`sub_date`	`string`	订阅支付日期
`duration`	`int`	订阅时长，单位为天
`pay_amount`	`double`	实际支付金额
`status`	`string`	状态：`completed`、`expired`

退订表 `fact_unsubscribes`

字段	类型	含义
`unsub_id`	`string`	退订单号
`sub_id`	`string`	原订阅记录号
`unsub_date`	`string`	退订申请日期
`reason`	`string`	退订原因

任务 1：查询高等级 VIP 用户

查询 VIP 等级大于等于 4 的用户编号、用户名、国家，以及 profile 中的年龄。年龄转换为整数，无法转换时显示 NULL。

SELECT
  user_id,
  user_name,
  country,
  CAST(profile['age'] AS INT) AS age
FROM dim_users
WHERE vip_level >= 4;

任务 2：统计 Pop 歌手订阅金额 Top 5

查询二级流派为 pop 的歌曲，统计每位歌手的订阅支付总金额和订阅记录数，按总金额降序取前 5 名。

SELECT
  ds.singer,
  SUM(fs.pay_amount) AS total_pay,
  COUNT(*) AS subscription_count
FROM fact_subscriptions fs
JOIN dim_songs ds
  ON fs.song_id = ds.song_id
WHERE ds.genre_l2 = 'pop'
GROUP BY ds.singer
ORDER BY total_pay DESC
LIMIT 5;

任务 3：统计发生过退订的歌曲

通过订阅记录关联退订表，找出曾被退订过至少一次的歌曲，输出歌曲编号、歌曲名称和退订次数。

SELECT
  ds.song_id,
  ds.song_name,
  COUNT(fu.unsub_id) AS unsubscribe_count
FROM fact_subscriptions fs
JOIN fact_unsubscribes fu
  ON fs.sub_id = fu.sub_id
JOIN dim_songs ds
  ON fs.song_id = ds.song_id
GROUP BY ds.song_id, ds.song_name
HAVING COUNT(fu.unsub_id) >= 1;

任务 4：找出首单支付超过 50 元的用户

首单定义为每位用户按 sub_date 升序排列后的第一条订阅支付记录。假设同一天只有一条记录。

WITH ranked_subscriptions AS (
  SELECT
    user_id,
    pay_amount,
    sub_date,
    ROW_NUMBER() OVER (
      PARTITION BY user_id
      ORDER BY sub_date ASC
    ) AS rn
  FROM fact_subscriptions
)
SELECT
  u.user_id,
  u.user_name,
  r.pay_amount AS first_pay_amount,
  r.sub_date AS first_sub_date
FROM ranked_subscriptions r
JOIN dim_users u
  ON r.user_id = u.user_id
WHERE r.rn = 1
  AND r.pay_amount > 50;

Hive 课程复习

目录

1. Hive 是以（ ）技术为基础的数据仓库。

2. Hive 是基于（ ）为计算引擎的。

3. Hive 存储元数据的服务是（ ）。

4. 以下不属于 Hive 文件格式的是（ ）。

5. Hive 的元数据存储在 Derby 和 MySQL 中的区别是（ ）。

6. 下面不属于 Hive 中的元数据信息的是（ ）。

2. 服务启动

8. 下面关于 Hive 内部表和外部表描述正确的是（ ）。

9. 以下关于 HiveSQL 基本操作描述正确的是（ ）。

10. 关于 Hive 中的桶说法不正确的是（ ）。