如何在 Amazon Athena 中创建和使用分区表？

2 分钟阅读

我想在 Amazon Athena 中创建分区表并使用它们来改进查询。

简短描述

通过对 Athena 表进行分区，可以限制每次查询扫描的数据量，从而提高性能并降低成本。分区将表划分为几个部分，并根据列值将相关数据保存在一起。分区充当虚拟列，并有助于减少每次查询扫描的数据量。

创建表并对数据进行分区时，需考虑以下事项：

必须将数据作为一个分区存储在 Simple Storage Service (Amazon S3) 存储桶中。
创建表时，需包括分区列和分区数据的根位置。
选择适当的方法将分区加载到 AWS Glue 数据目录中。运行查询时，表引用数据目录。
对 Amazon S3 中高度分区的数据使用分区投影。

解决方法

在创建包含分区的表时，需要记住以下几点。

存储在 Amazon S3 上

必须对数据进行分区并存储在 Amazon S3 上。分区数据可采用以下任一格式：

Hive 样式格式（例如：s3://doc-example-bucket/example-folder/year=2021/month=01/day=01/myfile.csv）
**注意：**路径包括分区键的名称及其值（例如：year=2021）
非 Hive 样式格式（例如：s3://doc-example-bucket/example-folder/2021/01/01/myfile.csv）

创建表时需包括分区信息

CREATE TABLE 语句必须包含分区详细信息。使用 PARTITIONED BY 定义分区列，使用 LOCATION 指定分区数据的根位置。运行类似以下内容的查询：

CREATE EXTERNAL TABLE doc-example-table (
first string,
last string,
username string
)
PARTITIONED BY (year string, month string, day string)
STORED AS parquet
LOCATION 's3://doc-example-bucket/example-folder'

替换查询中的以下内容：

将 doc-example-table 替换为您正在创建的表的名称
将 doc-example-bucket 替换为存储表的 S3 存储桶的名称
将 example-folder 替换为 S3 文件夹的名称
将 first、last 和 username 替换为列的名称
将 year、month 和 day 替换为分区列的名称

使用适合您的使用案例的方法将分区加载到数据目录中

创建表后，将分区添加到数据目录中。您可以使用以下任一方法来执行此操作：

**对 Hive 样式格式数据使用 MSCK REPAIR TABLE 查询：**MSCK REPAIR TABLE 命令扫描文件系统（例如 Amazon S3），来查找兼容 Hive 的分区。该命令将它们与表中已存在的分区进行比较，然后将新分区添加到数据目录中。运行与以下类似的命令：
**注意：**如果您具有数千个分区，则此方法并非最佳做法。您的 DDL 查询可能会面临超时问题。有关更多信息，请参阅为什么我的 MSCK REPAIR TABLE 查询没有将分区添加到 AWS Glue 数据目录？

MSCK REPAIR TABLE doc-example-table

**对 Hive 样式和非 Hive 样式格式数据使用 ALTER TABLE ADD PARTITION 查询：**ALTER TABLE ADD PARTITION 命令将一个或多个分区添加到数据目录中。在该命令中，指定分区列名称和值对以及存储该分区数据文件的 Amazon S3 路径。通过运行类似于以下内容的命令，为每个查询添加一个分区或一批分区：

ALTER TABLE doc-example-table ADD PARTITION (year='2021', month='01', day='01') LOCATION 's3://doc-example-bucket/example-folder/2021/01/01/'

ALTER TABLE doc-example-table ADD
PARTITION (year='2021', month='01', day='01') LOCATION 's3://doc-example-bucket/example-folder/2021/01/01/'
PARTITION (year='2020', month='06', day='01') LOCATION 's3://doc-example-bucket/example-folder/2020/06/01/'

**对 Hive 和非 Hive 样式格式数据使用 AWS Glue 爬网程序：**您可以使用 Glue 爬网程序通过数据集自动推断表架构，并创建表，然后将分区添加到数据目录中。或者，您也可以使用爬网程序仅将分区添加到使用 CREATE TABLE 语句手动创建的表中。要使用爬网程序添加分区，在定义爬网程序时，请指定一个或多个现有数据目录表（而非数据存储）作为爬取的源。爬网程序对数据目录表指定的数据存储进行爬取。不会创建新表。而是会更新手动创建的表，并添加新分区。有关详细信息，请参阅设置爬网程序配置选项。
**对 Amazon S3 中高度分区的数据使用分区投影：**在 Amazon S3 中拥有高度分区的数据时，将分区添加到数据目录可能不切实际而且耗时。对高度分区的表进行查询时，不能快速完成。在这种情况下，您可以使用分区投影功能来加快高度分区表的查询处理速度并自动进行分区管理。在分区投影中，分区值和位置是根据配置计算的，而不是从存储库（如数据目录）中读取的。这意味着使用分区投影时无需将分区添加到数据目录中。由于内存中操作的速度通常比远程操作更快，因此分区投影减少了对高度分区的表进行查询的运行时间。分区投影功能目前适用于以下分区类型：枚举值、整数、日期或注入。有关更多信息，请参阅使用 Amazon Athena 进行分区投影。