Hive数仓操作（七）

一、 Hive动态分区表

1. 动态分区与静态分区的区别

1. 分区定义：

   • 静态分区：在插入数据时，需要手动指定分区字段的值。
   • 动态分区：分区字段的值是根据数据中的某个字段自动生成的，用户只需指定分区字段的类型。

2. 数据加载方式：

   • 静态分区：可以通过 LOAD DATA 和 INSERT ... SELECT 加载数据。
   • 动态分区：只能通过 INSERT ... SELECT 加载数据。

3. 查询要求：

   • 静态分区：在 INSERT ... SELECT 中，SELECT 和 WHERE 子句中不需要包含分区字段。
   • 动态分区：在 INSERT ... SELECT 中，必须查询出分区字段的值，并且这些字段需要放在 SELECT 和 WHERE 子句之间的最后。

2. 动态分区设置

1. 开启动态分区

   SET hive.exec.dynamic.partition = true;
   

2. 设置动态分区模式

   动态分区有两种模式：

   • strict: 需要与静态分区一起使用，不设置nonstrict的情况下创建动态分区表的同时必需要指定一个静态分区。

     -- 示例: 
     INSERT INTO TABLE students_pt PARTITION(dt='anhui', pt) SELECT ..., pt FROM students;
     

   • nonstrict: 允许单独使用动态分区。

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

3. 设置最大分区数量

   可以根据业务需求调整支持的最大分区数量。例如，设置为1000：

   SET hive.exec.max.dynamic.partitions.pernode = 1000;
   

示例

1. 动态分区示例

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

CREATE TABLE student_info_dynamic (
  id INT,
  name STRING,
  sex STRING,
  age INT
)
PARTITIONED BY (year STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

INSERT INTO TABLE student_info_dynamic
PARTITION (year)
SELECT id, name, sex, age, year FROM source_table;

2. 静态加动态分区示例

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

CREATE TABLE student_info_static_dynamic (
  id INT,
  name STRING,
  sex STRING
)
PARTITIONED BY (year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

INSERT INTO TABLE student_info_static_dynamic
PARTITION (year = '2023', month)
SELECT id, name, sex, month FROM source_table;

3. 多个动态分区示例

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

CREATE TABLE student_info_multi_dynamic (
  id INT,
  name STRING
)
PARTITIONED BY (year STRING, month STRING)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ',';

INSERT INTO TABLE student_info_multi_dynamic
PARTITION (year, month)
SELECT id, name, year, month FROM source_table;

---

二、 Hive外部分区表

假设在 HDFS 上已经存在日志目录，其结构如下：

hadoop fs -put log1.txt /logs/20210510
hadoop fs -put log2.txt /logs/20210511
hadoop fs -put log3.txt /logs/20210512

1. 创建外部分区表

CREATE EXTERNAL TABLE ext_logs_p (
  id INT,
  name STRING,
  operate STRING
)
PARTITIONED BY (date_time STRING)
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
LOCATION '/logs';

2. 挂载已有分区数据

创建分区并与已有目录绑定：

ALTER TABLE ext_logs_p ADD PARTITION (date_time='20210510')
LOCATION '/logs/20210510';

ALTER TABLE ext_logs_p ADD PARTITION (date_time='20210511')
LOCATION '/logs/20210511';

ALTER TABLE ext_logs_p ADD PARTITION (date_time='20210512')
LOCATION '/logs/20210512';

3. 自动修复分区

如果目录已经存在，可以使用以下命令自动修复分区：

MSCK REPAIR TABLE ext_logs_p;

为了让 Hive 自动识别分区，目录应该按以下格式命名：

• /logs/date_time=20210510/ 包含 log1.txt
• /logs/date_time=20210511/ 包含 log2.txt
• /logs/date_time=20210512/ 包含 log3.txt

这种方式会扫描目录结构并自动更新 Hive 元数据，使其与 HDFS 上的现有目录匹配，运行MSCK REPAIR TABLE命令后，Hive 会扫描 /logs/ 目录，自动识别并添加符合格式的分区：

• date_time='20210510'
• date_time='20210511'
• date_time='20210512

外部分区表关联数据的方式

• 创建分区后使用 LOAD DATA：适用于将新数据加载到指定分区中。
• 通过 ALTER TABLE ADD PARTITION：用于将已有目录绑定到分区。
• 使用 MSCK REPAIR TABLE：自动修复分区，适用于已有目录符合分区格式的情况。

---