将 Athena 搭配使用时的最佳实践 Amazon Glue - Amazon Athena
数据库、表和列名称使用 Amazon Glue 爬虫使用 CSV 文件Amazon Glue 分区索引和筛选使用地理空间数据在 Athena 上使用 Amazon Glue 作业 ETL
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 和Athena才能很好地协同工作。

本主题内容

数据库、表和列名称

在中创建架构 Amazon Glue 以在 Athena 中进行查询时，请考虑以下几点：

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

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

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

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

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

注意

如果您使用AWS::Glue::Database Amazon CloudFormation 模板创建数据库但未指定 Amazon Glue 数据库名称，则会 Amazon Glue 自动生成一个与 Athena 不兼容的 resource_name—random_string 格式的数据库名称。

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

使用 Amazon Glue 爬虫

Amazon Glue Crawlers 可帮助发现数据集的架构并将其注册为 Amazon Glue 数据目录中的表。爬网程序会遍历您的数据并确定架构。此外，爬网程序还可以检测并注册分区。有关更多信息，请参阅《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的架构相似，并且将单个数据源设置为s3://bucket01/folder1/中 Amazon Glue，则 Crawler 可以创建包含两个分区列的单个表：一个分区列包含table1和table2，另一个分区列包含partition1直partition5到。

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

要将 S3 数据存储添加到现有的爬虫中 Amazon Glue

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

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

  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 数据目录中每个具有分区列的表，架构存储在表级别，以及表中的每个单独分区的架构。分区的架构由 Amazon Glue Crawler 根据其在分区内读取的数据样本进行填充。有关更多信息，请参阅将多个数据源和爬网程序结合使用。

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

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

更新表元数据

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

Amazon Glue 当 CSV 文件的每个数据字段周围都有引号时，可能会错误分配元数据，从而弄错了serializationLib属性。有关更多信息，请参阅用引号引起来的 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 中的表属性才能使用 OpenCSV。SerDe有关 OpenCSV 的更多信息 SerDe，请参阅。用于处理 CSV 的 OpenCSVSerDe

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

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

  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 操作或 update-tab le 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 查询结果中。实现这一目标的一种方法是使用执行提取、转换和加载 (ETL) 工作的作 Amazon Glue 业。 Amazon Glue 您可以使用一种是 PySpark Python 方言扩展的语言来编写脚本。有关更多信息，请参阅《Amazon Glue 开发者指南》中的 “在 Glue Amazon 中创作作作业”。

以下示例显示了 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，请将以下键值对添加到表属性部分：

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

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

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

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

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

使用地理空间数据

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

在 Athena 上使用 Amazon Glue 作业 ETL

Amazon Glue 作业执行 ETL 操作。 Amazon Glue 作业运行一个脚本，该脚本从源中提取数据，转换数据，然后将其加载到目标中。有关更多信息，请参阅《Amazon Glue 开发者指南》中的 “在 Glue Amazon 中创作作作业”。

使用 Athena 为 ETL 作业 Amazon Glue 创建表

您在 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. 选择 添加。

  6. 对于键，输入 classification。

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

  8. 选择保存。

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

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

使用 ETL 任务优化查询性能

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

我们建议使用 Parquet 和 ORC 数据格式。 Amazon Glue 支持写入这两种数据格式，这使您可以更轻松、更快地将数据转换为 Athena 的最佳格式。有关这些格式和其他提高性能的方法的更多信息，请参阅 Amazon Athena 的十大性能调整技巧。

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

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

自动化 ETL 的 Amazon Glue 作业

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