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

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

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

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

本主题内容

数据库、表和列名称

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

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

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

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

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

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

注意

如果您使用 AWS::Glue::Database Amazon CloudFormation 模板创建 Amazon Glue 数据库而未指定数据库名称,Amazon Glue 会自动以与 Athena 不兼容的 resource_name–random_string 格式生成数据库名称。

您可以使用 Amazon Glue Catalog Manager 重命名列,但不重命名表名称或数据库名称。要更改数据库名称,您必须创建一个新的数据库,并将表从旧数据库复制到新数据库(换言之,将元数据复制到新实体中)。对于表,您可以遵循类似的流程。您可以使用 Amazon Glue 软件开发工具包或 Amazon CLI 执行此操作。

使用 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

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

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

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

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

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

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

  4. Specify crawler source type(指定爬网程序源类型)下,根据需要选择其他设置,然后选择 Next(下一步)。

  5. Add a data store 下,将 Include path 更改为表级别目录。例如,对于上述示例,您需要将其从 s3://bucket01/folder1 to s3://bucket01/folder1/table1/ 进行更改。

  6. 选择 Next (下一步)

  7. 对于 Add another data store(添加其他数据存储),依次选择 Yes(是)、Next(下一步)。

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

  9. 重复相应步骤以添加任何其他表级别目录,并完成爬网程序配置。

    在最后一个屏幕上,Data stores(数据存储)下会出现 Include locations(包括位置)的新值,如下图所示:

  10. 选择 Finish (结束)

同步分区架构以避免“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 的更多信息,请参阅 用于处理 CSV 的 OpenCSVSerDe

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

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

  2. 选择您要编辑的表,然后依次选择 Action(操作)、Edit table details(编辑表详细信息)。

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

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

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

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

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

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

  4. 选择 Apply(应用)。

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

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

您可以使用 Amazon 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://my_bucket/csvdata_folder/'; TBLPROPERTIES ("skip.header.line.count"="1")

或者,您可以事先删除 CSV 标题,以便不将标题信息包含在 Athena 查询结果中。实现此操作的一种方法是使用 Amazon Glue 任务,它执行提取、转换和加载 (ETL) 工作。您可以使用 PySpark Python 方言的扩展语言在 Amazon Glue 中编写脚本。有关更多信息,请参阅《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 控制台查询数据。

使用地理空间数据

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 任务。分类值可以是 csvparquetorcavrojson。下面是 Athena 中的示例 CREATE TABLE 语句:

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

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

使用控制台更改分类属性

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

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

使用 ETL 任务优化查询性能

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

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

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

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

自动执行 Amazon Glue ETL 任务

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