八、HQL DML数据导入与操作

Apache Hive 作为一个强大的数据仓库工具，其核心价值在于对存储在分布式系统（如 HDFS）中的大规模数据进行查询和分析。但在进行分析之前，首先需要有效地将数据加载到 Hive 表中或在表之间进行数据流转。Hive 提供了多种数据导入和操纵的方式，主要通过其数据操纵语言 (DML) 来实现。本文将详细介绍这些常用的数据加载与写入方法，重点围绕 LOAD DATA 命令和功能丰富的 INSERT 语句展开。

一、LOAD DATA 命令：从文件系统直接加载

LOAD DATA 是 Hive 中最直接的数据导入命令，用于将文件系统（本地文件系统或 HDFS）中的数据文件加载到 Hive 表中。

1. 从本地加载 CSV 到非分区表（覆盖）

   CREATE TABLE employees_load (id INT, name STRING, department STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
   LOAD DATA LOCAL INPATH '/path/to/local/employees.csv' OVERWRITE INTO TABLE employees_load;
   

2. 从 HDFS 加载文本文件到分区表（追加）

   CREATE TABLE web_logs_load (ip STRING, url STRING) PARTITIONED BY (dt STRING) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
   ALTER TABLE web_logs_load ADD IF NOT EXISTS PARTITION (dt='2023-11-20');
   LOAD DATA INPATH '/hdfs/path/to/logs/2023-11-20/' INTO TABLE web_logs_load PARTITION (dt='2023-11-20');
   

二、INSERT 语句：多样化的数据写入与转换

INSERT 语句是 Hive 中更为灵活和强大的数据写入机制，它不仅能加载数据，还能在写入过程中进行数据转换、聚合和筛选。

(一) INSERT ... VALUES：直接插入少量行

这是最常用的 INSERT 形式，将一个 SELECT 查询的结果写入到目标表中。可以配合 OVERWRITE 或 INTO（追加）。

1. 从 employees_load 筛选数据覆盖写入 active_employees

   CREATE TABLE active_employees (id INT, name STRING, department STRING);
   INSERT OVERWRITE TABLE active_employees
   SELECT id, name, department FROM employees_load WHERE department != 'HR';
   

2. 动态分区加载日志数据

   CREATE TABLE daily_logs_partitioned (message STRING, severity STRING) PARTITIONED BY (log_date DATE, log_hour INT);
   SET hive.exec.dynamic.partition=true;
   SET hive.exec.dynamic.partition.mode=nonstrict;
   -- 假设 raw_logs 表有 message, severity, event_time (TIMESTAMP) 列
   INSERT OVERWRITE TABLE daily_logs_partitioned PARTITION (log_date, log_hour)
   SELECT message, severity, to_date(event_time) AS log_date, hour(event_time) AS log_hour
   FROM raw_logs;
   

(三) 多表插入 (Multiple Inserts)：一次查询，多目标写入

Hive 允许从一个源查询 (FROM ... SELECT ...) 的结果同时插入到多个表或分区中，这能有效减少对源表的扫描次数。

此功能允许将查询结果直接写入 HDFS 文件系统中的指定目录，而不是 Hive 表。这对于数据导出、与其他系统集成或生成报告文件非常有用。

CTAS 一步完成表的创建和数据填充，非常便捷。

Hive 的 LOAD DATA 和各种形式的 INSERT 语句共同构成了其强大的数据加载和操纵能力。从简单的文件加载到复杂的查询结果转换、多目标分发乃至数据导出，INSERT 语句几乎无所不能。理解并熟练运用这些命令，结合对静态与动态分区的认知，是每一位 Hive 用户进行高效数据处理的基石。

练习题

1. 场景：你有一个简单的日志级别表 log_levels，包含 level_id INT 和 level_name STRING 两列。
   要求：使用 INSERT ... VALUES 语句向 log_levels 表中插入以下三条记录：

   • (1, 'INFO')
   • (2, 'WARN')
   • (3, 'ERROR')
     请编写相应的 HQL 代码（包括建表语句）。

2. 场景：你有一个源表 raw_sales_data 包含 product_id STRING, sales_amount DECIMAL(10,2), sale_date STRING (格式 'YYYY-MM-DD')。你希望将2023年10月份的销售数据加载到一个按 month_partition STRING (格式 'YYYY-MM') 分区的表 october_sales 中，并覆盖该分区现有数据。
   要求：编写创建 october_sales 表（包含 product_id, sales_amount 列和分区列）的 HQL，以及加载数据的 HQL。

3. 场景：你有一个订单表 all_orders 包含 order_id STRING, customer_id STRING, order_status STRING, order_value DECIMAL(12,2)。你希望根据 order_status 将数据分别写入到两个新表：completed_orders (状态为 'COMPLETED') 和 pending_orders (状态为 'PENDING' 或 'PROCESSING')。
   要求：假设 completed_orders 和 pending_orders 表已创建且只包含 order_id 和 order_value 列。使用一次 FROM 子句和多表插入完成数据分发，覆盖目标表数据。

4. 场景：你有一个表 customer_feedback 包含 customer_id STRING, feedback_text STRING, rating INT。你希望将所有评分低于3星（即 rating < 3）的反馈导出到 HDFS 目录 /user/reports/low_rating_feedback 中，以制表符分隔的文本文件形式存储。
   要求：编写相应的 HQL 导出语句。

5. 场景：你有一个原始事件表 event_logs_source 包含 event_id STRING, user_id STRING, event_type STRING, event_timestamp TIMESTAMP。你希望将这些数据加载到一个新的分区表 categorized_events 中，该表按 event_day DATE (从 event_timestamp 提取) 和 event_category STRING (假设根据 event_type 的某种规则映射，例如 'TYPE_A' -> 'Category1', 'TYPE_B' -> 'Category2', 其他 -> 'OtherCategory') 进行双重动态分区。
   要求：编写创建 categorized_events 表（包含 event_id, user_id 列和两个分区列）的 HQL，以及使用动态分区加载数据的 HQL。

练习题答案

1. 题目一答案：
   ```sql
   CREATE TABLE log_levels (
   level_id INT,
   level_name STRING
   );

INSERT INTO TABLE log_levels VALUES (1, 'INFO'), (2, 'WARN'), (3, 'ERROR');

2.  **题目二答案：**
```sql
CREATE TABLE october_sales (
product_id STRING,
sales_amount DECIMAL(10,2)
)
PARTITIONED BY (month_partition STRING)
STORED AS ORC;

-- 假设要加载到 '2023-10' 分区
ALTER TABLE october_sales ADD IF NOT EXISTS PARTITION (month_partition='2023-10');

INSERT OVERWRITE TABLE october_sales PARTITION (month_partition='2023-10')
SELECT
product_id,
sales_amount
FROM
raw_sales_data
WHERE
SUBSTR(sale_date, 1, 7) = '2023-10';

1. 题目三答案：
   ```sql
   -- 假设表已创建：
   -- CREATE TABLE completed_orders (order_id STRING, order_value DECIMAL(12,2));
   -- CREATE TABLE pending_orders (order_id STRING, order_value DECIMAL(12,2));

FROM all_orders
INSERT OVERWRITE TABLE completed_orders
SELECT order_id, order_value WHERE order_status = 'COMPLETED'
INSERT OVERWRITE TABLE pending_orders
SELECT order_id, order_value WHERE order_status IN ('PENDING', 'PROCESSING');

4.  **题目四答案：**
```sql
INSERT OVERWRITE DIRECTORY '/user/reports/low_rating_feedback'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
STORED AS TEXTFILE
SELECT
customer_id,
feedback_text,
rating
FROM
customer_feedback
WHERE
rating < 3;

1. 题目五答案：
   ```sql
   CREATE TABLE categorized_events (
   event_id STRING,
   user_id STRING
   )
   PARTITIONED BY (event_day DATE, event_category STRING)
   STORED AS PARQUET;

SET hive.exec.dynamic.partition=true;
SET hive.exec.dynamic.partition.mode=nonstrict;
-- 假设最大动态分区数也已适当设置，例如 SET hive.exec.max.dynamic.partitions.pernode=1000;

INSERT OVERWRITE TABLE categorized_events PARTITION (event_day, event_category)
SELECT
event_id,
user_id,
to_date(event_timestamp) AS event_day_val, -- 对应第一个动态分区列 event_day
CASE
WHEN event_type = 'TYPE_A' THEN 'Category1'
WHEN event_type = 'TYPE_B' THEN 'Category2'
ELSE 'OtherCategory'
END AS event_category_val -- 对应第二个动态分区列 event_category
FROM
event_logs_source;
```