将 Athena 与 Amazon Glue 结合使用时的最佳实践 - Amazon Athena
数据库、表和列名称使用 Amazon Glue 爬网程序使用 CSV 文件Amazon Glue 分区索引和筛选使用地理空间数据将 Amazon Glue ETL 任务与 Athena 结合使用
Amazon Web Services 文档中描述的 Amazon Web Services 服务或功能可能因区域而异。要查看适用于中国区域的差异，请参阅 中国的 Amazon Web Services 服务入门 (PDF)。

本文属于机器翻译版本。若本译文内容与英语原文存在差异，则一律以英文原文为准。

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

将 Athena 与... 一起使用时Amazon Glue Data Catalog，你可以使用Amazon Glue创建要在 Athena 中查询的数据库和表（架构），或者您可以使用 Athena 创建架构然后在中使用它们Amazon Glue和相关服务。本主题提供了在使用上述任一方法时的注意事项和最佳实践。

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

本主题内容

数据库、表和列名称

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

  • 数据库名称不得超过 255 个字符。

  • 表名称不得超过 255 个字符。

  • 列名称不得超过 255 个字符。

  • 数据库名称、表名称和列名称仅可接受小写字母、数字和下划线字符。

有关更多信息，请参阅《Amazon Glue 开发人员指南》中的数据库和表。

注意

如果你使用AWS::Glue::Database Amazon CloudFormation用于创建的模板Amazon Glue数据库，不要指定数据库名称，Amazon Glue自动生成格式为的数据库名称资源名称—随机字符串这与雅典娜不兼容。

您可以使用 Amazon Glue Catalog Manager 重命名列，但不重命名表名称或数据库名称。要解决此限制，必须使用旧数据库的定义来创建具有新名称的数据库。然后，使用旧数据库中表的定义在新数据库中重新创建表。为此，你可以使用Amazon CLI要么Amazon GlueSDK。要查看步骤，请参阅 使用Amazon CLI重新创建Amazon Glue数据库及其表。

使用 Amazon Glue 爬网程序

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

注意

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

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

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

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

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

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

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

s3://bucket01/folder1/table1/partition1/file.txt
s3://bucket01/folder1/table1/partition2/file.txt
s3://bucket01/folder1/table1/partition3/file.txt
s3://bucket01/folder1/table2/partition4/file.txt
s3://bucket01/folder1/table2/partition5/file.txt

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

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

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

  1. 登录 Amazon Web Services Management Console，然后打开 Amazon Glue 控制台，网址为：https://console.aws.amazon.com/glue/。

  2. 在导航窗格中，选择 Crawlers (爬网程序)。

  3. 选择指向爬网程序的链接，然后选择 Edit（编辑）。

  4. 对于步骤 2：选择数据源和分类器，选择 Edit（编辑）。

  5. 对于 Data sources（数据来源），选择 Add a data source（添加数据来源）。

  6. 在 Add data source（添加数据来源）对话框中，对于 S3 path（S3 路径），选择 Browse（浏览）。

  7. 选择要使用的计划，然后选择 Choose（选择）。

    您添加的数据来源显示在 Data sources（数据来源）列表中。

  8. 选择下一步。

  9. 在 Configure security settings（配置安全设置）页面中，创建或选择爬网程序的 IAM 角色，然后选择 Next（下一步）。

  10. 确保 S3 路径以斜杠结尾，然后选择 Add an S3 data source（添加 S3 数据来源）。

  11. 在 Set output and scheduling（设置输出和计划）页面中，对于 Output configuration（输出配置），选择目标数据库。

  12. 选择下一步。

  13. 在 Review and update（审核和更新）页面中，查看您所做的选择。要编辑步骤，请选择 Edit（编辑）。

  14. 选择更新。

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

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

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

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

更新表元数据

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

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

使用 CSV 文件

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

用引号引起来的 CSV 数据

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

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

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

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

  1. 在 Amazon Glue 控制台中，选择导航窗格中的 Tables（表）。

  2. 选择您要编辑的表的链接，然后依次选择 Action（操作）、Edit table（编辑表）。

  3. 在 Edit table（编辑表）页面上，进行以下更改：

    • 对于 Serialization lib（序列化库），输入 org.apache.hadoop.hive.serde2.OpenCSVSerde。

    • 对于 Serde parameters（SerDe 参数），为键 escapeChar、quoteChar 和 separatorChar 输入以下值：

      • 对于 escapeChar，输入一个反斜杠 (\)。

      • 对于 quoteChar，输入一个双引号 (")。

      • 对于 separatorChar，输入一个逗号 (,)。

  4. 选择保存。

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

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

你可以使用Amazon Glue UpdateTableAPI 操作或更新表用于修改 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://my_bucket/csvdata_folder/';
TBLPROPERTIES ("skip.header.line.count"="1")

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

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

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

Amazon Glue 分区索引和筛选

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

  1. 在 Amazon Glue 中创建分区索引。

  2. 为表启用分区筛选。

创建分区索引

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

启用分区筛选

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

  • 对于 Key（键），请添加 partition_filtering.enabled

  • 对于 Value（值），请添加 true

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

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

有关使用分区索引和筛选的更多信息，请参见使用以下方法提高亚马逊 Athena 查询性能Amazon Glue Data Catalog分区索引在里面Amazon大数据博客。

使用地理空间数据

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

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

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

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

您在 Athena 中创建的表必须添加有名为 classification 的表属性，该属性标识数据的格式。这使 Amazon 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')

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

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

  1. 登录 Amazon Web Services Management Console，然后打开 Amazon Glue 控制台，网址为：https://console.aws.amazon.com/glue/。

  2. 在控制台导航窗格中。选择 Tables（表）。

  3. 选择您要编辑的表的链接，然后依次选择 Action（操作）、Edit table（编辑表）。

  4. 向下滚动到 Table properties（表属性）部分。

  5. 选择 Add（添加）。

  6. 对于 Key（键），输入 classification。

  7. 对于 Value（值），输入数据类型（例如 json)。

  8. 选择保存。

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

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

使用 ETL 任务优化查询性能

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

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

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

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

自动执行 Amazon Glue ETL 任务

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