智警杯备赛--数据应用技术2

数据分区

简介

任务（一）

介绍

分区表实际上就是对应一个 HDFS 文件系统上的独立的文件夹，该文件夹下是该分区所有的数据文件，每个表可以拥有一个或者多个分区。

　　分区是以字段的形式在表结构中存在，通过 desc table_name; 命令可以查看到字段存在，但该字段不存放实际的数据内容，仅仅是分区的表示。

　　Hive 中的分区就是分目录，把一个大的数据集根据业务需要分割成小的数据集。在查询时通过 where 子句中的表达式选择查询所需要的指定的分区，这样的查询效率会提高很多。

　　分区建表分为 2 种，一种是单分区，也就是说在表文件夹目录下只有一级文件夹目录。另外一种是多分区，表文件夹下出现多文件夹嵌套模式。

　　由于分区表相对于没有分区的表有明显的性能优势，所以在实际生产环境中，分区表的使用是非常普遍的。本次实训主要训练在指定数据库下创建分区表。

涉及到的知识点

1.创建分区表

//在哪里定义行的格式
CREATE [EXTERNAL] TABLE [IF NOT EXISTS] table_name
[(col_name data_type [COMMENT col_comment], ...)]
[COMMENT table_comment]
[PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
[CLUSTERED BY (col_name, col_name, ...)
[SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
[ROW FORMAT row_format]
[STORED AS file_format]
[LOCATION hdfs_path]

//如何定义行的格式
ROW FORMAT
DELIMITED [FIELDS TERMINATED BY char]
[COLLECTION ITEMS TERMINATED BY char]
[MAP KEYS TERMINATED BY char]
[LINES TERMINATED BY char]
| SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value,
operty_name=property_value, ...)]

create table：创建一个指定名字的表。如果相同名字的表已经存在，则抛出异常；用户可以用 if not exists 选项来忽略这个异常。

external：可以让用户创建一个外部表，在建表的同时指定一个指向实际数据的路径（location）。 Hive创建内部表时，会将数据移动到数据仓库指向的路径；若创建外部表，仅记录数据所在的路径，不对数据的位置做任何改变。在删除表的时候，内部表的元数据和数据会被一起删除，而外部表只删除元数据，不删除数据。

comment：为表和列添加注释。

partitioned by：创建分区表。在 Hive Select 查询中一般会扫描整个表内容，会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据，因此建表时引入了 partition 概念。

clustered by：创建分桶表。对于每一个表（table）或者分区， Hive 可以进一步组织成桶，也就是说桶是更为细粒度的数据范围划分。

sorted by ：局部排序，保证每个 reducer 的输出文件是有序的。

row format ：用户在建表的时候可以自定义 SerDe 或者使用自带的 SerDe。如果没有指定 row format 或者 row format delimited，将会使用自带的 SerDe。在建表的时候，用户还需要为表指定列，用户在指定表的列的同时也会指定自定义的 SerDe，Hive 通过 SerDe 确定表的具体的列的数据。

stored as：指定存储文件类型。常用的存储文件类型：textfile：普通的文本文件格式；

sequencefile：二进制序列文件；

rcfile：列式存储格式文件。

location ：指定表在 HDFS 上的存储位置。

第一段中的ROW FORMAT row_format是一个“插槽”，第二段提供了可以“插入”到这个插槽中的两种主要“插头”类型：DELIMITED（用于简单分隔符文件）或 SERDE（用于复杂或自定义格式文件）。共同构成了Hive表定义中至关重要的一环，明确告诉Hive原始数据文件是什么样的，以及Hive中的每一行和每一个字段。没有ROW FORMAT的正确定义，Hive就无法正确地从HDFS文件中读取数据，或者将数据正确地写入到HDFS文件中

2.加载数据

LOAD DATA [LOCAL] INPATH 'filepath' [OVERWRITE] INTO TABLE tablename 
[PARTITION(partcol1=val1, partcol2=val2 ...)]

load 操作：只是单纯的复制/移动操作。

local 关键字：

如果指定了关键字 local，load 命令会去查找本地文件系统中的 filepath。

如果没有指定 local 关键字，则根据 inpath 中的 URI 查找文件。注意： URI 是指 HDFS 上的路径，分简单模式和完整模式两种，例如：简单模式： /user/hive/project/data1

完整模式： hdfs://namenode:9000/user/hive/project/data1

filepath：数据文件路径。

overwrite 关键字：如果使用了 overwrite 关键字，则目标表或者分区（HDFS 的数据）中的内容会被删除，然后再将 filepath 指向的文件/目录中的内容添加到表/分区中。

LOAD DATA命令是Hive中用于将数据导入表的一种直接方式。可以控制数据来源（本地或 HDFS）、加载模式（覆盖或追加），以及对于分区表，精确地指定数据应该落入哪个分区。是一个非常实用的ETL（Extract, Transform, Load）工具，用于将原始数据快速导入到Hive中进行后续的查询和分析 

3.查看表

1>显示分区

SHOW PARTITIONS [db_name.]table_name [PARTITION(partition_spec)];

SHOW PARTITIONS是核心命令，告诉Hive想查看表的分区

[db_name.]table_name

db_name. (可选)：如果要查询的表不在当前活跃的数据库中，需要指定数据库名称，后面跟一个点号 (.)

table_name (必填)：这是想要列出分区的分区表的名称，这个命令只适用于分区表。，如果尝试在一个非分区表上使用它，将会收到错误

[PARTITION(partition_spec)]：可选子句，允许过滤分区列表

PARTITION(...): 可以指定一个部分分区规范来缩小结果范围

partition_spec: 由一个或多个 分区列 = 值 对组成，用逗号分隔，不必指定所有的分区列

列出了指定表的所有现有分区，分区按字母顺序列出

2>查看表的信息语法结构

DESCRIBE [EXTENDED | FORMATTED]
    [db_name.]table_name [PARTITION partition_spec] [col_name ( [.field_name] | [.'$elem$'] | [.'$key$'] | [.'$value$'] )* ];

DESCRIBE 命令是 Hive 中用于检查表或列元数据的基本命令

基本用途是查看表的基本列结构（列名、数据类型）

EXTENDED / FORMATTED：提供更深入的表级元数据，如存储格式、SerDe 配置、位置等，对于排查问题和理解表如何存储数据至关重要

PARTITION：允许针对特定分区的存储属性进行查看

col_name ...：能够探索复杂数据类型（如结构体、数组、映射）的内部结构，这在大数据场景中处理半结构化数据时非常有用

显示给定包括分区列在内的所有列。describe 的简写是 desc

任务（二）

介绍

　　在 Hive 中，可以使用 alter table 子句来修改表的属性，实际上也就是修改表的元数据，而不会修改表中实际的数据。

　　本次实训主要考核大家对修改表操作的熟练程度，主要有对表和分区重命名、增加指定列和分区、修改指定列和分区以及删除指定列和分区。

涉及到的知识点

1.重命名表/分区

1>重命名表语法结构

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE是sql中用于修改表结构的通用关键字，后面可以跟很多子句来执行不同的修改操作（例如添加/删除列、更改列类型、添加/删除分区等），在这里，是执行“重命名”操作的前缀

table_name是重命名的表的当前名称

RENAME TO是ALTER TABLE语句中专门用于执行重命名操作的子句

new_table_name是将表重命名为的新名称

允许用户对表进行重命名。从版本 0.6 开始，表的重命名会移动其在 HDFS 的位置（条件是仅当创建的表没有 location 子句时，才会移动表的 HDFS 位置）。

2>重命名分区语法结构

ALTER TABLE table_name PARTITION partition_spec RENAME TO PARTITION partition_spec;

table_name是想要重命名其中分区的分区表的名称，这个命令只适用于分区表

PARTITION partition_spec这个子句用于识别你想要重命名的现有分区

PARTITION：关键字，表示正在指定一个分区

partition_spec是当前分区的完整且准确的规范，必须包含所有分区列及其当前值，并按正确的顺序排列。示例：如果表按year和month分区，而要重命名year=2023, month=01这个分区，就写 PARTITION(year=2023, month=01)

RENAME TO PARTITION partition_spec是执行重命名操作的子句

RENAME TO PARTITION：关键字，表示对分区执行重命名操作

partition_spec是该分区重命名为的新规范

允许更改分区列的值

2.增加列/分区

1>增加列语法结构

ALTER TABLE table_name ADD COLUMNS (col_name data_type[COMMENT col_comment], ...)

ADD COLUMNS是ALTER TABLE语句中专门用于执行添加新列操作的子句

(col_name data_type[COMMENT col_comment], ...)是要添加的新列的定义列表，可以添加一个或多个列，列之间用逗号分隔

col_name是新列的名称

data_type是新列的数据类型，Hive支持各种数据类型（如 INT, STRING, BIGINT, BOOLEAN, DOUBLE, ARRAY, MAP, STRUCT 等）。

[COMMENT col_comment] (可选): 你可以为新列添加一个描述性的注释。

add 是代表新增一列，列的位置在所有列之后，partition 列之前

2>增加分区位置语法结构

ALTER TABLE table_name ADD [IF NOT EXISTS] 
PARTITION partition_spec [LOCATION 'location']
[PARTITION partition_spec [LOCATION 'location'] ...];

partition_spec:
  : (partition_column = partition_col_value, partition_column = partition_col_value, ...)

[IF NOT EXISTS] (可选)如果包含 IF NOT EXISTS：当要添加的分区已经存在时，Hive不会报错，而是静默地跳过这个操作，可以防止因重复添加分区而导致的错误，相反如果省略IF NOT EXISTS：当要添加的分区已经存在时，Hive会抛出错误

PARTITION partition_spec [LOCATION 'location']是定义要添加的分区及其可选存储位置的语法，可以添加一个或多个分区，多个分区之间用空格分隔

PARTITION: 表示正在定义一个分区

[LOCATION 'location'] (可选):

LOCATION: 关键字，可以为这个新分区指定一个非默认的 HDFS 存储路径

'location'：这个分区数据实际存储在HDFS上的完整路径

默认行为：如果你不指定 LOCATION，Hive 会根据表的 LOCATION 和分区的 partition_spec自动生成默认的分区目录路径

用途：当数据已经存在于HDFS上的特定目录中，并且要将这个HDFS目录注册为Hive的一个分区时，就会使用LOCATION，这对于外部表尤为常见，因为外部表的数据通常由外部进程生成

[PARTITION partition_spec [LOCATION 'location'] ...]

这个部分表示可以通过在一个ALTER TABLE ADD PARTITION语句中一次性添加多个分区，每个分区定义都遵循PARTITION partition_spec [LOCATION 'location']的格式，并用空格分隔

使用alter table ... add partition ...将分区添加到表中。只有在分区值是字符串时才应使用引号。location 后写的是数据文件所在的目录

3.修改列/分区

1>修改列语法结构

ALTER TABLE table_name CHANGE [COLUMN] col_old_name col_new_name column_type [COMMENT col_comment] [FIRST|AFTER column_name];

col_new_name column_type [COMMENT col_comment]是新列的定义，会替换旧列的定义，必须提供新名称和新数据类型，即使它们与旧的相同

col_new_name是列的新名称，如果只是想改变数据类型或位置而不改变名称，这里也要写上旧的名称

column_type是列的新数据类型，必须指定数据类型，即使它与旧的相同

数据类型变更注意事项：从较窄的类型变更为较宽的类型（例如 INT 到 BIGINT，或 STRING 到 VARCHAR(N)）通常是安全的

从较宽的类型变更为较窄的类型可能会导致数据截断或转换错误

某些类型转换（如 STRING 到 INT）如果数据不兼容，可能会在查询时导致NULL或错误

[COMMENT col_comment] (可选): 可以为列添加或修改其注释

[FIRST|AFTER column_name]是一个可选的子句，用于调整列在表中的物理顺序

FIRST: 将当前修改的列移动到表的第一列

AFTER column_name: 将当前修改的列放置在指定 column_name 的后面

如果省略此子句：列的物理位置通常保持不变（这在Hive中通常不那么重要，因为查询是基于列名而不是物理顺序）

允许用户更改列的名称、数据类型、注释、位置或它们的任意组合。first 将列放在第一列， after col_name 将列放在 col_name 后面一列

2>修改分区语法结构

ALTER TABLE table_name [PARTITION partition_spec] SET LOCATION "new location";

修改名为 table_name 的表，将整个表（如果未指定分区）或其特定分区（如果指定了分区）的 HDFS 存储位置更改为 "new location" 指定的新路径

修改分区位置，一般来说是指修改分区的数据存储目录

4.删除列/分区

1>删除列语法结构

ALTER TABLE table_name REPLACE COLUMNS (col_name data_type[COMMENT col_comment], ...)

REPLACE COLUMNS是ALTER TABLE语句中专门用于执行替换列定义的操作子句，与ADD COLUMNS只在末尾追加不同，REPLACE COLUMNS会完全删除旧的列定义，然后用新的定义取代它们

replace columns 是先删除现存列，然后再增加新列。替换列只能在表使用自带 SerDe 时使用。删除列 drop 操作版本不支持，可以使用 replace 操作替代

2>删除分区语法结构

ALTER TABLE table_name DROP [IF EXISTS] PARTITION partition_spec[, PARTITION partition_spec, ...]

DROP表示要删除一个或多个分区

修改名为table_name的分区表，从其中删除一个或多个指定的分区，如果使用了 IF EXISTS，则即使指定的分区不存在也不会报错

可以使用alter table ... drop partition ... 删除表的分区。这将删除该分区的数据和元数据。同时删除多个分区时，分区之间用逗号隔开