将 Athena 与 AWS Glue 结合使用时的最佳实践 - Amazon Athena
AWS 文档中描述的 AWS 服务或功能可能因区域而异。要查看适用于中国区域的差异,请参阅中国的 AWS 服务入门

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

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

在将 Athena 与 AWS Glue 数据目录结合使用时,您可以使用 AWS Glue 来创建要在 Athena 中查询的数据库和表(架构),也可以使用 Athena 来创建架构,然后在 AWS Glue 和相关服务中使用它们。本主题提供了在使用上述任一方法时的注意事项和最佳实践。

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

本主题内容

数据库、表和列名称

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

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

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

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

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

您可以使用 AWS Glue 目录管理器来重命名列,但此时不能使用 AWS Glue 控制台更改表名称和数据库名称。要更正数据库名称,您需要创建一个新的数据库并将表复制到其中 (换言之,就是将元数据复制到新实体中)。对于表,您可以遵循类似的流程。您可以使用 AWS Glue SDK 或 AWS CLI 来完成此操作。

使用 AWS Glue 爬网程序

AWS Glue 爬网程序可帮助发现并注册 AWS Glue 数据目录中的数据集的架构。爬网程序会遍历您的数据,并检查其中的各个部分以确定架构。此外,爬网程序还可以检测并注册分区。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/add-crawler.html 开发人员指南 中的AWS Glue使用爬网程序编录数据

注意

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

安排爬网程序以保持 AWS Glue 数据目录和 Amazon S3 同步

AWS Glue 爬网程序可以设置为按计划或按需运行。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/monitor-data-warehouse-schedule.html 开发人员指南 中的AWS Glue基于时间的任务和爬网程序日程

如果您的数据在固定时间到达分区表,则可以设置 AWS Glue 爬网程序按计划运行以检测和更新表分区。这样就无需运行耗时长且昂贵的 MSCK REPAIR 命令或手动运行 ALTER TABLE ADD PARTITION 命令。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/tables-described.html#tables-partition 开发人员指南 中的AWS Glue表分区

将多个数据源和爬网程序一起使用

当 AWS 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

如果 table1table2 的架构类似,并且单个数据源在 AWS Glue 中设置为 s3://bucket01/folder1/,则爬网程序可能创建一个具有两个分区列的表:一个分区列包含 table1table2,另一个分区列包含 partition1partition5

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

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

  1. 登录 AWS 管理控制台并通过以下网址打开 AWS Glue 控制台:https://console.amazonaws.cn/glue/

  2. 选择 Crawlers (爬网程序),选择您的爬网程序,然后选择 Action (操作)Edit crawler (编辑爬网程序)

  3. Add information about your crawler 下,根据需要选择附加设置,然后选择 Next

  4. Add a data store 下,将 Include path 更改为表级别目录。例如,对于上述示例,您可以从 s3://bucket01/folder1 to s3://bucket01/folder1/table1/ 中进行更改。 选择 Next (下一步)

  5. 对于 Add another data store,选择 YesNext

  6. 对于 Include path (包含路径),输入您的其他表级别目录(例如,s3://bucket01/folder1/table2/),然后选择 Next (下一步)

    1. 对任何其他表级别目录重复步骤 3-5,并完成爬网程序配置。

Include locations (包含位置) 的新值显示在数据存储下,如下所示:

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

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

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

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

更新表元数据

在爬取之后,AWS Glue 爬网程序会自动分配某些表元数据,以帮助它与其他外部技术(如 Apache Hive、Presto 和 Spark)兼容。有时,爬网程序可能会错误地分配元数据属性。在使用 Athena 查询表之前,手动更正 AWS Glue 中的属性。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/console-tables.html#console-tables-details 开发人员指南 中的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 的更多信息,请参阅OpenCSVSerDe 用于处理 CSV

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

  1. 在 AWS Glue 控制台导航窗格中,选择 Tables (表)

  2. 选择要编辑的表,然后选择 Edit table (编辑表)

  3. Edit table details (编辑表详细信息) 对话框中,进行以下更改:

    • 对于 Serde serialization lib (Serde 序列化库),输入 org.apache.hadoop.hive.serde2.OpenCSVSerde

    • 对于 Serde parameters (Serde 参数),为键 escapeCharquoteCharseparatorChar 输入以下值:

      • 对于 escapeChar,输入反斜杠 (\)。

      • 对于 quoteChar,输入双引号 (")。

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

有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/console-tables.html#console-tables-details 开发人员指南 中的AWS Glue查看和编辑表详细信息

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

您可以使用 AWS Glue UpdateTable API 操作或 update-table CLI 命令在表定义中修改 块,如以下示例 JSON 中所示。SerDeInfo

"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 查询结果中。实现此操作的一种方法是使用 AWS Glue 任务,它执行提取、转换和加载 (ETL) 工作。您可以使用 AWS Glue Python 方言的扩展语言在 PySpark 中编写脚本。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/busisadd-job.html 开发人员指南 中的AWS Glue在 Glue 中编写任务

以下示例显示 AWS 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")

使用地理空间数据

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

将针对 ETL 的 AWS Glue 任务与 Athena 一起使用

AWS Glue 任务执行 ETL 操作。AWS Glue 任务运行一个从源中提取数据、转换数据并将其加载到目标中的脚本。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/busisadd-job.html 开发人员指南 中的AWS Glue在 Glue 中编写任务

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

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

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

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

使用控制台更改分类属性

  1. 选择 Edit Table
  2. 对于 Classification,选择文件类型,然后选择 Apply

有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/console-tables.html 开发人员指南 中的AWS Glue使用表

使用 ETL 任务优化查询性能

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

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

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

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

自动化针对 ETL 的 AWS Glue 任务

您可以将 AWS Glue ETL 任务配置为基于触发器自动运行。当来自外部 AWS 的数据被以次优格式推送到 Amazon S3 存储桶,以用于在 Athena 中查询时,此功能非常适用。有关更多信息,请参阅 https://docs.amazonaws.cn/glue/latest/dg/trigger-job.html 开发人员指南 中的AWS Glue触发 AWS Glue 任务