Hive数仓操作（六）

一、 Hive 分区表

Hive 的分区表通过在 HDFS 中以不同的目录存储不同的分区数据，来提高查询性能并减少数据扫描量。分区表可以根据特定的列（如 性别 列的男/女）将数据划分为多个部分，使得查询时只需要扫描相关的分区，而不是整个表，下文讲解一下静态分区表。

1. 创建分区表

创建分区表的语法如下：

CREATE TABLE stu_info (
    id INT,
    name STRING,
    age INT
)
PARTITIONED BY (class STRING)  -- 分区列不能提前存在
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
STORED AS TEXTFILE;

2. 查看分区信息

要查看表的分区信息，可以使用以下命令：

DESC stu_info ;  -- 查看表结构，包括分区信息

SHOW PARTITIONS stu_info ;  -- 查看所有分区

3. 分区表的创建

分区表有两种使用情况，一种是创建分区表并加载数据LOAD DATA，另一种是将已有表转化为分区表并插入数据INSERT INTO。

示例一：创建分区表并加载数据

1. 创建分区表

我们首先创建一个分区表 stu_info，用于存储学生信息，并根据 class 列进行分区。

CREATE TABLE stu_info (
    id INT,
    name STRING,
    age INT
)
PARTITIONED BY (class STRING)  -- 分区列
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 字段分隔符
STORED AS TEXTFILE;  -- 存储格式

2. 准备数据文件

假设我们有一个数据文件 class_one.txt，内容如下：

1,张三,20
2,李四,22
3,王五,21

3. 加载数据到分区表

将数据文件加载到 stu_info 表中，指定 class 为 'one'。

LOAD DATA LOCAL INPATH '/opt/datas/class_one.txt' 
INTO TABLE stu_info PARTITION (class='one');

示例二：将已有表转化为分区表

1. 创建已有表

假设我们已经有一个表 stu_info2，其结构如下：

CREATE TABLE stu_info2 (
    id INT,
    name STRING,
    age INT,
    sex STRING
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 字段分隔符
STORED AS TEXTFILE;  -- 存储格式

2. 创建新的分区表

接下来，我们将 stu_info2 表转化为分区表 stu_info2_p。首先创建新的分区表：

CREATE TABLE stu_info2_p (
    id INT,
    name STRING,
    age INT
)
PARTITIONED BY (sex STRING)  -- 根据 sex 列分区
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','  -- 字段分隔符
STORED AS TEXTFILE;  -- 存储格式

3. 插入数据到分区表

我们使用 INSERT 语句将 stu_info2 表的数据插入到 stu_info2_p 表中，并按 sex 列进行分区。

INSERT INTO TABLE stu_info2_p PARTITION (sex='male') 
SELECT id, name, age FROM stu_info2 WHERE sex='男';

---

二、 Hive 分区操作

1. 增加单个分区

可以使用 ALTER TABLE ... ADD PARTITION 语句来添加一个新的分区。

ALTER TABLE stu_info ADD PARTITION (class='three');

2. 同时创建多个分区

可以在同一条语句中添加多个分区，但需要使用不同的子句。

ALTER TABLE stu_info ADD PARTITION (class='four') PARTITION (class='five');

3. 删除分区

对于内表，使用 ALTER TABLE ... DROP PARTITION 语句可以删除指定的分区及其数据。

ALTER TABLE stu_info DROP PARTITION (class='five'); 

注意: 删除分区时，该分区对应的目录及数据会被删除。

4. 同时删除多个分区

可以在同一条语句中同时删除多个分区。

ALTER TABLE stu_info DROP PARTITION (class='four'), PARTITION (class='five');

---

三、 Hive脚本化运行

实际工作中，使用交互式 shell 进行输入可能效率较低，因此可以使用脚本化方式来执行 Hive 查询。

1. 使用命令行执行简单查询

可以在本地Linux创建一个 Shell 脚本（例如 h1.sh），并在其中写入 Hive 查询命令。

#!/bin/bash
hive -e "SELECT * FROM bigdata.dept" > dept2.txt

• 说明:
  • hive -e 允许你在命令行中直接执行 Hive SQL 查询。
  • 查询结果会被重定向到 dept2.txt 文件中。

2. 执行复杂查询

如果需要执行复杂的 Hive 查询，可以将 SQL 语句写入后缀为 .hql 的文件，然后使用 hive -f 命令执行。

创建 HQL 文件

创建文件 h1.hql，写入以下内容：

SELECT * FROM bigdata.dept;

执行 HQL 文件

然后在Linux命令行中执行以下命令：

hive -f h1.hql > dept2.txt

• 说明:
  • hive -f h1.hql 会读取 h1.hql 文件中的所有 SQL 语句并执行。
  • 查询结果同样会被重定向到 dept2.txt 文件中。

---