练习

下载数据

Hive 分区、分桶、导入与导出实验

实验目标

实验目标

本实验旨在掌握 Hive 数据分区、分桶、数据导入与导出 的核心概念与操作，通过实践不同的 Hive 数据管理方式，提高数据存储与查询效率。具体目标如下：

实验目标

本实验旨在掌握 Hive 分区、分桶、数据导入与导出 的基本操作，提高数据管理和查询效率。具体目标如下：

1. 掌握 Hive 分区与分桶

• 了解 单级分区 和 多级分区（如按年份+地区）。

• 掌握 分桶表 的创建及查询优化方法。

2. 学习数据导入方法

• 通过 LOAD DATA、INSERT INTO 等方式导入数据。

• 了解 外部表 及 动态分区 机制。

3. 提升查询与优化能力

• 使用 SHOW PARTITIONS、TABLESAMPLE 进行高效查询。

• 通过 DISTRIBUTE BY 提高查询性能。

4. 掌握数据导出

• 使用 INSERT OVERWRITE DIRECTORY 导出数据。

• 了解 EXPORT TABLE 备份数据的方法。

通过实验，掌握 Hive 数据存储与优化，提高数据分析处理能力。

已知数据表：sales_data.csv，其数据格式如下：

实验内容

实验任务一：Hive 分区实验

1、创建 Hive 数据库

-- 删除已有数据库
DROP DATABASE IF EXISTS sales_db CASCADE;

-- 创建数据库
CREATE DATABASE IF NOT EXISTS sales_db;

-- 选择数据库
USE sales_db;

2、创建原始表（用于数据导入）

方法1：创建表

CREATE TABLE IF NOT EXISTS sales_raw (
    order_id INT,
    customer_id STRING,
    product_id INT,
    product_name STRING,
    sale_date DATE,      -- 格式：YYYY-MM-DD
    quantity INT,
    unit_price DOUBLE,
    payment_method STRING,
    region STRING,
    customer_gender STRING,
    customer_age INT,
    customer_register_date DATE  -- 格式：YYYY-MM-DD
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

📌 说明 sales_raw 用于存放原始 CSV 数据，无分区。

方法2：复制表结构

-- 创建新表sales_raw_2018，并复制sales_raw表结构
CREATE TABLE IF NOT EXISTS sales_raw_2018 LIKE sales_raw;

方法3：创建外部表

CREATE EXTERNAL TABLE IF NOT EXISTS sales_raw_2017_external (
    order_id INT,
    customer_id STRING,
    product_id INT,
    product_name STRING,
    sale_date DATE,
    quantity INT,
    unit_price DOUBLE,
    payment_method STRING,
    region STRING,
    customer_gender STRING,
    customer_age INT,
    customer_register_date DATE
)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE 
LOCATION 'hdfs://master:9820/user/hive/external/sales_raw_2017/';

#  语法小知识
-- 修改表名sales_raw_2017 为 sales_raw_2018
ALTER TABLE sales_raw_2017 rename to sales_raw_2018;

-- 修改字段与字段类型（把sale_data 更改为 sale_date）
ALTER TABLE sales_data CHANGE COLUMN sale_data sale_date DATE;

3、数据导入方法

方法 1：LOAD DATA（直接加载本地或 HDFS 文件）

-- 从本地文件系统导入数据到sales_raw表
LOAD DATA LOCAL INPATH '/opt/data/sales_data.csv' INTO TABLE sales_raw;
-- 查看表数据
select * from sales_raw;

-- 从本地文件系统导入数据到sales_raw_2018表
LOAD DATA LOCAL INPATH '/opt/data/sales_data_2018.csv' INTO TABLE sales_raw_2018;

-- 在master节点创建目录/user/hive/data/
hdfs dfs -mkdir -p /user/hive/data/
-- 在master节点把sales_data_2019.csv上传到HDFS中的/user/hive/data/ 目录中
hdfs dfs -put /opt/data/sales_data_2019.csv /user/hive/data/
-- 从 HDFS 导入数据到sales_raw表
LOAD DATA INPATH 'hdfs://master:9820/user/hive/data/sales_data_2019.csv' INTO TABLE sales_raw;
-- 查看2019年的数据
SELECT * FROM sales_raw WHERE sale_date < '2020-01-01';

✅ 适用场景：当数据已存储在本地或 HDFS，且无需转换格式时。

方法2：hsfs dfs -put （直接上传本地数据到外部表的目录）

-- 在master节点上操作
hdfs dfs -put /opt/data/sales_data_2017.csv /user/hive/external/sales_raw_2017/

-- 查看2017年的数据
SELECT * FROM sales_raw_2017_external;

方法 3：INSERT INTO（逐行插入数据）

INSERT INTO TABLE sales_raw VALUES (10001,'2019_10001', 1008, '手机', '2019-12-30', 1, 6666, '信用卡', 'West', '女', 41, '2018-10-7');

-- 查看插入的数据
select * from sales_raw where customer_id='2019_10001';

✅ 适用场景：小批量插入单条数据，不适用于大规模数据。

方法 4：INSERT INTO + SELECT（从查询结果导入数据）

-- 插入sales_raw_2018年的数据到原始表
INSERT INTO TABLE sales_raw
SELECT * FROM sales_raw_2018
WHERE sale_date >= '2018-01-01' AND sale_date <= '2018-12-31';

-- 查看2018年的数据
SELECT * FROM sales_raw 
WHERE sale_date >= '2018-01-01' AND sale_date <= '2018-12-31';

-- 插入sales_raw_2017年的数据到原始表
INSERT INTO TABLE sales_raw
SELECT * FROM sales_raw_2017_external
WHERE sale_date >= '2017-01-01' AND sale_date <= '2017-12-31';

-- 查看2017年的数据
SELECT * FROM sales_raw
WHERE sale_date >= '2017-01-01' AND sale_date <= '2017-12-31';

✅ 适用场景：当需要从其他表导入数据。

-- 查看sales_raw表中有哪些年份的数据
select distinct year(sale_date)
from sales_raw;

-- 查看总数据
select count(*) from sales_raw;

4、创建分区表（按年份分区）

CREATE TABLE IF NOT EXISTS sales_partitioned_by_year (
    order_id INT,
    customer_id STRING,
    product_id INT,
    product_name STRING,
    sale_date DATE,
    quantity INT,
    unit_price DOUBLE,
    payment_method STRING,
    region STRING,
    customer_gender STRING,
    customer_age INT,
    customer_register_date DATE
)
PARTITIONED BY (sale_year string)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

✅ 适用场景：数据按年份存储，提高查询效率。

5、启用动态分区并插入数据

SET hive.exec.dynamic.partition = true;
SET hive.exec.dynamic.partition.mode = nonstrict;

INSERT INTO TABLE sales_partitioned_by_year PARTITION (sale_year)
SELECT
    order_id,
    customer_id,
    product_id,
    product_name,
    sale_date,
    quantity,
    unit_price,
    payment_method,
    region,
    customer_gender,
    customer_age,
    customer_register_date,
    year(sale_date) AS sale_year
FROM sales_raw;

✅ 适用场景：数据自动按年分区，无需手动指定。

6、查询 Hive 分区表

6.1 查询所有数据

SELECT * FROM  sales_partitioned_by_year;

6.2 查询 2020 年销售数据

SELECT * FROM sales_partitioned_by_year WHERE sale_year='2020';

6.3 统计每年销售总额

SELECT sale_year, SUM(quantity * unit_price) AS total_sales 
FROM sales_partitioned_by_year 
GROUP BY sale_year;

7、验证 Hive 分区存储

hdfs dfs -ls /user/hive/warehouse/sales_db/sales_partitioned_by_year/

✅ 说明：HDFS 目录按 年份 分区，确保数据按年存储，提高查询效率。

8、查看所有分区

SHOW PARTITIONS sales_partitioned_by_year;

Hive 二级分区实验

目标： 在现有 sales_raw 表的基础上，创建一个 二级分区表，按 sale_year（年）+ region（地区） 进行分区。

1、创建按“年 + 地区”二级分区的 Hive 表

CREATE TABLE IF NOT EXISTS sales_partitioned_by_year_region (
    order_id INT,
    customer_id STRING,
    product_id INT,
    product_name STRING,
    sale_date DATE,
    quantity INT,
    unit_price DOUBLE,
    payment_method STRING,
    customer_gender STRING,
    customer_age INT,
    customer_register_date DATE
)
PARTITIONED BY (sale_year STRING, region STRING)  -- 一级分区: 年，二级分区: 地区
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE;

📌 说明

• sale_year：一级分区（存储 YYYY 格式，如 2020）。

• region：二级分区（存储 East、West、South、North）。

• 存储结构示例：

/user/hive/warehouse/sales_partitioned_by_year_region/sale_year=2020/region=East
/user/hive/warehouse/sales_partitioned_by_year_region/sale_year=2020/region=West

2、启用 Hive 动态分区

SET hive.exec.dynamic.partition = true;      -- 启用动态分区
SET hive.exec.dynamic.partition.mode = nonstrict;  -- 允许 Hive 自动创建分区

3、将数据从 sales_raw 迁移到 sales_partitioned_by_year_region

INSERT INTO TABLE sales_partitioned_by_year_region PARTITION (sale_year, region)
SELECT 
    order_id,
    customer_id,
    product_id,
    product_name,
    sale_date,
    quantity,
    unit_price,
    payment_method,
    customer_gender,
    customer_age,
    customer_register_date,
    year(sale_date) AS sale_year,  -- 使用 sale_year 作为一级分区
    region    -- 使用 region 作为二级分区
FROM sales_raw;

📌 说明

• year(sale_date) AS sale_year → 提取年份（YYYY）作为 一级分区。

• region → 直接作为 二级分区，无需转换。

• Hive 自动创建分区目录，并存储数据。

4、 查询二级分区表

4.1 查询所有数据

SELECT * FROM sales_partitioned_by_year_region;

4.2 查询 2020 年 East 地区的销售数据

SELECT * FROM sales_partitioned_by_year_region 
 WHERE sale_year='2020' AND region='East';

4.3 统计每年每个地区的销售总额

SELECT sale_year, region, SUM(quantity * unit_price) AS total_sales 
FROM sales_partitioned_by_year_region 
GROUP BY sale_year, region;

5、验证 Hive 分区存储

hdfs dfs -ls /user/hive/warehouse/sales_partitioned_by_year_region/

✅ 说明：

• HDFS 目录结构应类似于：

/user/hive/warehouse/sales_partitioned_by_year_region/sale_year=2020/region=East
/user/hive/warehouse/sales_partitioned_by_year_region/sale_year=2020/region=West

6、维护 & 优化

6.1 查看所有分区

SHOW PARTITIONS sales_partitioned_by_year_region;

Hive 导出数据实验

方法 1：INSERT OVERWRITE DIRECTORY（导出到 HDFS）

-- 1.使用sales_partitioned_by_year
insert overwrite directory '/user/hive/export/sales_data_2024'
row format delimited
fields terminated by ','
select * from sales_partitioned_by_year where sale_year="2024";

-- 2.使用sales_raw
insert overwrite directory '/user/hive/export/sales_data_2023'
    row format delimited
    fields terminated by ','
select * from sales_raw where year(sale_date)="2023";

✅ 适用场景：导出数据到 HDFS 目录。

方法 2：INSERT OVERWRITE LOCAL DIRECTORY（导出到本地）

INSERT OVERWRITE LOCAL DIRECTORY '/opt/data/export/export_data_2021'
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
SELECT * FROM sales_partitioned_by_year where sale_year="2021";

✅ 适用场景：当需要将数据存储在本地。

方法 3：EXPORT TABLE（导出 Hive 表）

EXPORT TABLE sales_partitioned_by_year TO 'hdfs://namenode:9000/user/hive/exported_table';

✅ 适用场景：备份 Hive 表数据。

实验任务二：Hive 分桶实验

本实验涵盖 Hive 分桶表（Bucket Table）的完整流程，包括：

1. 创建 Hive 分桶表

2. 启用 Hive 分桶

3. 将数据插入分桶表

4. 验证分桶存储

5. 优化查询性能

6. 维护与数据管理

1、创建 Hive 分桶表

CREATE TABLE IF NOT EXISTS sales_bucketed (
    order_id INT,
    customer_id INT,
    product_id INT,
    product_name STRING,
    sale_date DATE,      -- 格式：YYYY-MM-DD
    quantity INT,
    unit_price DOUBLE,
    payment_method STRING,
    region STRING,
    customer_gender STRING,
    customer_age INT,
    customer_register_date DATE  -- 格式：YYYY-MM/-DD
)
CLUSTERED BY (customer_id) INTO 4 BUCKETS
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ',' 
STORED AS TEXTFILE;

📌 说明：

• CLUSTERED BY (customer_id) INTO 4 BUCKETS：基于 customer_id 进行 分桶，每个桶存储不同 customer_id 的数据，提高查询效率。

• STORED AS ORC：ORC 是 Hive 最优存储格式，支持高效压缩和索引优化。

2、启用 Hive 分桶

SET hive.enforce.bucketing = true;   -- 强制启用分桶
SET hive.exec.dynamic.partition = true; -- 启用动态分区（如需要）
SET hive.exec.dynamic.partition.mode = nonstrict;

3、将数据插入分桶表

INSERT OVERWRITE TABLE sales_bucketed
SELECT 
    order_id, 
    customer_id, 
    product_id, 
    product_name, 
    sale_date, 
    quantity, 
    unit_price,
    payment_method, 
    region,   
    customer_gender, 
    customer_age, 
    customer_register_date
FROM sales_raw
DISTRIBUTE BY customer_id;  -- 确保数据按照 customer_id 进行分桶

📌 说明：customer_id 作为 分桶键（Bucket Key），分散存储不同的用户数据，提高查询效率。

4、验证 Hive 分桶存储

hdfs dfs -ls /user/hive/warehouse/sales_db/sales_bucketed/

✅ 说明：该目录下应包含 000000_0 ~ 000003_0 4 个文件（对应 4 个分桶）。

5、分桶查询优化

5.1 直接查询

SELECT * FROM sales_bucketed WHERE customer_id = 2163;

📌 说明：未优化查询，可能扫描整个数据集，性能一般。

5.2 使用 TABLESAMPLE 提高查询效率

SELECT * FROM sales_bucketed TABLESAMPLE(BUCKET 2 OUT OF 4);

📌 说明：TABLESAMPLE(BUCKET 2 OUT OF 4) 仅查询 第 2 号桶 的数据，提高查询速度。

6、维护 & 数据管理

6.1 查看所有分桶

-- 查看分桶数据的分布情况
SELECT DISTINCT customer_id, COUNT(*) 
FROM sales_bucketed 
GROUP BY customer_id;

-- 在master中运行下面的命令，查看 底层 HDFS 目录，看看数据如何分桶：
hdfs dfs -ls /user/hive/warehouse/sales_bucketed/

📌 总结

• 分桶 vs 分区

• 分区 适用于 大范围数据分类（如 sale_year）。

• 分桶 适用于 小范围数据优化查询（如 customer_id）。

•