数据库、表和列名称使用 AWS Glue 爬网程序使用 CSV 文件 AWS Glue 分区索引和筛选使用地理空间数据将 AWS Glue ETL 任务与 Athena 结合使用

将 Athena 与 AWS Glue 结合使用时的最佳实践

在将 Athena 与 AWS Glue Data Catalog 配合使用时，可使用 AWS Glue 创建要在 Athena 中查询的数据库和表（架构），也可以使用 Athena 创建架构，然后将其用于 AWS Glue 和相关服务。本主题提供了在使用上述任一方法时的注意事项和最佳实践。

在后台，Athena 使用 Trino 来处理 DML 语句，使用 Hive 来处理创建和修改架构的 DDL 语句。借助这些技术，有几个可遵循的惯例，以使 Athena 和 AWS Glue 很好地协作。

本主题内容

数据库、表和列名称
使用 AWS Glue 爬网程序
安排爬网程序以保持 AWS Glue Data Catalog 和 Simple Storage Service (Amazon S3) 同步

将多个数据源和爬网程序结合使用

同步分区架构以避免“HIVE_PARTITION_SCHEMA_MISMATCH”

更新表元数据
使用 CSV 文件
用引号引起来的 CSV 数据

具有标题的 CSV 文件
AWS Glue 分区索引和筛选
使用地理空间数据
将 AWS Glue ETL 任务与 Athena 结合使用
将 Athena 用于 AWS Glue ETL 任务来创建表

使用 ETL 任务优化查询性能

当转换为 ORC 时将 SMALLINT 和 TINYINT 数据类型转换为 INT

自动执行 AWS Glue ETL 任务

数据库、表和列名称

当您在 AWS Glue 中创建架构以便在 Athena 中进行查询时，请考虑以下事项：

AWS Glue 中可接受的数据库名称、表名以及列名的字符必须是 UTF-8 字符串。字符串长度不得少于 1 个字节，也不得超过 255 个字节。其中可以使用的字符包括空格，并由以下单行字符串模式定义字符：
```
[\u0020-\uD7FF\uE000-\uFFFD\uD800\uDC00-\uDBFF\uDFFF\t]*
```
目前，AWS Glue 正则表达式模式允许在名称的开头添加前导空格。但这些前导空格可能难以检测且创建后可能导致可用性问题，所以请避免创建带有前导空格的对象名称。
如果您使用 AWS::Glue::Database AWS CloudFormation 模板创建 AWS Glue 数据库而未指定数据库名称，AWS Glue 会自动以与 Athena 不兼容的 resource_name–random_string 格式生成数据库名称。
您可以使用 AWS Glue Catalog Manager 重命名列，但不重命名表名称或数据库名称。要解决此限制，必须使用旧数据库的定义来创建具有新名称的数据库。然后，使用旧数据库中的表定义在新数据库中重新创建表。为此，您可以使用 AWS CLI 或 AWS Glue 软件开发工具包。要查看步骤，请参阅使用 AWS CLI 重新创建 AWS Glue 数据库及其表。

有关 AWS Glue 中数据库和表的更多信息，请参阅《AWS Glue Developer Guide》中的 Databases 和 Tables。

使用 AWS Glue 爬网程序

AWS Glue 爬网程序可帮助发现数据集的架构，并在 AWS Glue Data Catalog 中将其注册为表。爬网程序会遍历您的数据并确定架构。此外，爬网程序还可以检测并注册分区。有关更多信息，请参阅《AWS Glue 开发人员指南》中的定义爬网程序。可以从 Athena 查询成功抓取的数据中的表。

注意

Athena 不承认您为 AWS Glue 爬网程序指定的排除模式。例如，如果您有一个 Amazon S3 存储桶，其中包含 .csv 和 .json 文件，并且您从爬网程序中排除了 .json 文件时，Athena 会查询两组文件。要避免这种情况，请将要排除的文件放置在其他位置。

安排爬网程序以保持 AWS Glue Data Catalog 和 Simple Storage Service (Amazon S3) 同步

AWS Glue 爬网程序可以设置为按计划或按需运行。有关更多信息，请参阅《AWS Glue 开发人员指南》中的基于时间的任务和爬网程序安排。

如果您的数据在固定时间到达分区表，则可以设置 AWS Glue 爬网程序按计划运行以检测和更新表分区。这样就不需要运行耗时长且昂贵的 MSCK REPAIR 命令或手动运行 ALTER TABLE ADD PARTITION 命令。有关更多信息，请参阅《AWS Glue 开发人员指南》中的表分区。

将多个数据源和爬网程序结合使用

当 AWS Glue 爬网程序扫描 Amazon S3 并检测到多个目录时，它会使用启发法来确定表的根在目录结构中的位置，以及表的分区所在的目录。在某些情况下，如果在两个或更多目录中检测到的架构相似，则爬网程序可能将它们视为分区而不是单独的表。一种帮助爬网程序发现单个表的方法是将每个表的根目录添加为爬网程序的数据存储。

以下示例是 Amazon S3 中的分区：


s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition1/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition2/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table1/partition3/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table2/partition4/file.txt
s3://DOC-EXAMPLE-BUCKET/folder1/table2/partition5/file.txt

如果 table1 和 table2 的架构类似，并且单个数据源在 AWS Glue 中设置为 s3://DOC-EXAMPLE-BUCKET/folder1/，则爬网程序可能创建一个具有两个分区列的表：一个分区列包含 table1 和 table2，另一个分区列包含 partition1 到 partition5。

要让 AWS Glue 爬网程序创建两个单独的表，请将爬网程序设置为具有两个数据源 s3://DOC-EXAMPLE-BUCKET/folder1/table1/ 和 s3://DOC-EXAMPLE-BUCKET/folder1/table2，如以下过程所示。

向 AWS Glue 中的现有爬网程序中添加 S3 数据存储

登录 AWS Management Console，然后打开 AWS Glue 控制台，网址为：https://console.aws.amazon.com/glue/。
在导航窗格中，选择 爬网程序。
选择指向爬网程序的链接，然后选择 Edit（编辑）。
对于步骤 2：选择数据源和分类器，选择 Edit（编辑）。
对于 Data sources（数据来源），选择 Add a data source（添加数据来源）。
在 Add data source（添加数据来源）对话框中，对于 S3 path（S3 路径），选择 Browse（浏览）。
选择要使用的计划，然后选择 Choose（选择）。

您添加的数据来源显示在 Data sources（数据来源）列表中。
选择下一步。
在 Configure security settings（配置安全设置）页面中，创建或选择爬网程序的 IAM 角色，然后选择 Next（下一步）。
确保 S3 路径以斜杠结尾，然后选择 Add an S3 data source（添加 S3 数据来源）。
在 Set output and scheduling（设置输出和计划）页面中，对于 Output configuration（输出配置），选择目标数据库。
选择下一步。
在 Review and update（审核和更新）页面中，查看您所做的选择。要编辑步骤，请选择 Edit（编辑）。
选择更新。

同步分区架构以避免“HIVE_PARTITION_SCHEMA_MISMATCH”

对 AWS Glue Data Catalog 中每个具有分区列的表，架构都在表级别并且针对表中的每个单独分区存储。分区的架构由 AWS Glue 爬网程序根据它在分区中读取的数据样本进行填充。有关更多信息，请参阅将多个数据源和爬网程序结合使用。

当 Athena 运行查询时，它会验证表的架构和查询所需的任何分区的架构。验证会将列数据类型按顺序进行比较，确保它们对于重叠的列匹配。这可防止意外的操作，例如在表的中间添加或删除列。如果 Athena 检测到分区的架构与表的架构不同，则 Athena 可能无法处理查询，会因 HIVE_PARTITION_SCHEMA_MISMATCH 而失败。

可通过几种方式解决此问题。首先，如果意外添加了数据，您可以删除导致架构差异的数据文件，删除该分区，然后重新爬取数据。其次，您可以删除单个分区，然后在 Athena 中运行 MSCK REPAIR，以使用表的架构重新创建分区。只有当您确信应用的架构将继续正确读取数据时，此第二个选项才有效。

更新表元数据

在爬取之后，AWS Glue 爬网程序会自动分配某些表元数据，以帮助它与其他外部技术（如 Apache Hive、Presto 和 Spark）兼容。有时，爬网程序可能会错误地分配元数据属性。在使用 Athena 查询表之前，手动更正 AWS Glue 中的属性。有关更多信息，请参阅《AWS Glue 开发人员指南》中的查看和编辑表详细信息。

当 CSV 文件将每个数据字段都用引号引起来，使 serializationLib 属性错误时，AWS Glue 可能会错误分配元数据。有关更多信息，请参阅用引号引起来的 CSV 数据。

使用 CSV 文件

CSV 文件有时会将每个列所适用的数据值用引号引起来，并且 CSV 文件中可能包含标题值，而这不是要分析的数据的一部分。当您使用 AWS Glue 从这些文件创建架构时，请遵循本部分中的指导。

用引号引起来的 CSV 数据

您可能有一个 CSV 文件，该文件包含在双引号中的数据字段，如下例所示：


"John","Doe","123-555-1231","John said \"hello\""
"Jane","Doe","123-555-9876","Jane said \"hello\""

要在 Athena 中对使用具有引号值的 CSV 文件创建的表运行查询，必须修改 AWS Glue 中的表属性以使用 OpenCSVSerDe。有关 OpenCSV SerDe 的更多信息，请参阅用于处理 CSV 的 OpenCSVSerDe。

要在 AWS Glue 控制台中编辑表属性

在 AWS Glue 控制台中，选择导航窗格中的 Tables（表）。
选择您要编辑的表的链接，然后依次选择 Action（操作）、Edit table（编辑表）。
在 Edit table（编辑表）页面上，进行以下更改：
- 对于 Serialization lib（序列化库），输入 org.apache.hadoop.hive.serde2.OpenCSVSerde。
- 对于 Serde parameters（SerDe 参数），为键 escapeChar、quoteChar 和 separatorChar 输入以下值：
  - 对于 escapeChar，输入一个反斜杠 (\)。
  - 对于 quoteChar，输入一个双引号 (")。
  - 对于 separatorChar，输入一个逗号 (,)。
选择保存。

有关更多信息，请参阅《AWS Glue 开发人员指南》中的查看和编辑表详细信息。

以编程方式更新 AWS Glue 表属性

您可以使用 AWS Glue UpdateTable API 操作或更新表 CLI 命令修改 SerDeInfo 数据块，如以下示例 JSON 所示。


"SerDeInfo": {
   "name": "",
   "serializationLib": "org.apache.hadoop.hive.serde2.OpenCSVSerde",
   "parameters": {
      "separatorChar": ","
      "quoteChar": "\""
      "escapeChar": "\\"
      }
},

具有标题的 CSV 文件

当您在 Athena 中使用 CREATE TABLE 语句定义一个表时，可以使用 skip.header.line.count 表属性以忽略 CSV 数据中的标题，如下例所示。


...
STORED AS TEXTFILE
LOCATION 's3://DOC-EXAMPLE-BUCKET/csvdata_folder/';
TBLPROPERTIES ("skip.header.line.count"="1")

或者，您可以事先删除 CSV 标题，以便不将标题信息包含在 Athena 查询结果中。实现此操作的一种方法是使用 AWS Glue 任务，它执行提取、转换和加载 (ETL) 工作。您可以使用 PySpark Python 方言的扩展语言在 AWS Glue 中编写脚本。有关更多信息，请参阅《AWS Glue 开发人员指南》中的在 AWS Glue 中编写任务。

以下示例显示 AWS Glue 脚本中的一个函数，它使用 from_options 写出动态帧，并将 writeHeader 格式选项设置为 false，从而删除标题信息：


glueContext.write_dynamic_frame.from_options(frame = applymapping1, connection_type = "s3", connection_options = {"path": "s3://DOC-EXAMPLE-BUCKET/MYTABLEDATA/"}, format = "csv", format_options = {"writeHeader": False}, transformation_ctx = "datasink2")

AWS Glue 分区索引和筛选

在 Athena 查询分区表时，其会检索可用的表分区并筛选出与查询相关的子集。随着新数据和分区的添加，处理分区需要更多时间，查询运行时间可能会增加。如果您的表包含随着时间的推移而增长的大量分区，请考虑使用 AWS Glue 分区索引和筛选。使用分区索引，Athena 可以优化分区处理并提升高度分区表的查询性能。在表的属性中设置分区筛选包括两个步骤：

在 AWS Glue 中创建分区索引。
为表启用分区筛选。

创建分区索引

有关在 AWS Glue 中创建分区索引的步骤，请参阅《AWS Glue 开发人员指南》中的使用分区索引。有关 AWS Glue 中对分区索引的限制，请参阅该页面上的关于分区索引部分。

启用分区筛选

要为表启用分区筛选，必须在 AWS Glue 中设置新的表属性。有关如何在 AWS Glue 中设置表属性的步骤，请参阅设置分区投影页面。当您在 AWS Glue 中编辑表详细信息时，将以下键值对添加到 Table properties（表属性）部分：

对于 Key（键），请添加 partition_filtering.enabled
对于 Value（值），请添加 true

您可以随时通过将 partition_filtering.enabled 值设置为 false 来对此表禁用分区筛选。

完成上述步骤后，您可以返回 Athena 控制台查询数据。

有关使用分区索引和筛选的更多信息，请参阅 AWS 大数据博客中的使用 AWS Glue Data Catalog 分区索引提高 Amazon Athena 查询性能。

使用地理空间数据

AWS Glue 不内在支持已知文本 (WKT)、已知二进制 (WKB) 或其他 PostGIS 数据类型。AWS Glue 分类器解析地理空间数据并使用相应格式支持的数据类型对其进行分类，例如用于 CSV 的 varchar。与其他 AWS Glue 表一样，您可能需要更新从地理空间数据创建的表的属性，以允许 Athena 按原样解析这些数据类型。有关更多信息，请参阅使用 AWS Glue 爬网程序和使用 CSV 文件。Athena 可能无法按原样解析 AWS Glue 表中的某些地理空间数据类型。有关在 Athena 中使用地理空间数据的更多信息，请参阅查询地理空间数据。

将 AWS Glue ETL 任务与 Athena 结合使用

AWS Glue 任务执行 ETL 操作。AWS Glue 任务运行一个从源中提取数据、转换数据并将其加载到目标中的脚本。有关更多信息，请参阅《AWS Glue 开发人员指南》中的在 AWS Glue 中编写任务。

将 Athena 用于 AWS Glue ETL 任务来创建表

您在 Athena 中创建的表必须添加有名为 classification 的表属性，该属性标识数据的格式。这使 AWS Glue 能够将这些表用于 ETL 任务。分类值可以是 avro、csv、json、orc、parquet 或 xml。下面是 Athena 中的示例 CREATE TABLE 语句：


CREATE EXTERNAL TABLE sampleTable (
  column1 INT,
  column2 INT
  ) STORED AS PARQUET
  TBLPROPERTIES (
  'classification'='parquet')

如果在创建表时未添加表属性，则可以使用 AWS Glue 控制台添加它。

使用 AWS Glue 控制台添加分类表属性

登录 AWS Management Console，然后打开 AWS Glue 控制台，网址为：https://console.aws.amazon.com/glue/。
在控制台导航窗格中。选择 Tables（表）。
选择您要编辑的表的链接，然后依次选择 Action（操作）、Edit table（编辑表）。
向下滚动到 Table properties（表属性）部分。
选择添加。
对于键，输入 classification。
对于 Value（值），输入数据类型（例如 json)。
选择保存。

在 Table details（表详细信息）部分中，您输入的数据类型显示在表的 Classification（分类）字段中。

有关更多信息，请参阅《AWS Glue 开发人员指南》中的使用表。

使用 ETL 任务优化查询性能

AWS Glue 任务可帮助您将数据转换为一种可优化 Athena 中的查询性能的格式。数据格式会极大影响 Athena 中的查询性能和查询成本。

我们建议使用 Parquet 和 ORC 数据格式。AWS Glue 支持写入到这两种数据格式，从而使您可以更方便快捷地将数据转换为对 Athena 最佳的格式。有关这些格式的更多信息以及提高性能的其他方法，请参阅 Top 10 performance tuning tips for Amazon Athena。

当转换为 ORC 时将 SMALLINT 和 TINYINT 数据类型转换为 INT

要减少 Athena 无法读取 AWS Glue ETL 任务生成的 SMALLINT 和 TINYINT 数据类型的可能性，请在使用向导或为 ETL 编写脚本时，将 SMALLINT 和 TINYINT 转换为 INT。

自动执行 AWS Glue ETL 任务

您可以将 AWS Glue ETL 任务配置为基于触发器自动运行。当来自外部 AWS 的数据被以次优格式推送到 Amazon S3 存储桶，以用于在 Athena 中查询时，此功能非常适用。有关更多信息，请参阅《AWS Glue 开发人员指南》中的触发 AWS Glue 任务。

Javascript 在您的浏览器中被禁用或不可用。

要使用 Amazon Web Services 文档，必须启用 Javascript。请参阅浏览器的帮助页面以了解相关说明。

文档惯例

从另一个账户注册 AWS Glue Data Catalog

重新创建数据库