时间戳

首页 > 解决方案 > AWS Athena 查询分区

amazon-web-services - AWS Athena 查询分区

问题描述

我正在尝试使用AWS Athena为现有平台提供分析。目前流程如下所示：

  1. 数据作为 JSON 事件泵入 Kinesis Firehose。
  2. Firehose 使用AWS Glue中的表将数据转换为镶木地板，并每 15 分钟或在流达到 128 MB（最大支持值）时写入 S3。
  3. 当数据写入 S3 时，它使用路径进行分区/year=!{timestamp:yyyy}/month=!{timestamp:MM}/day=!{timestamp:dd}/...
  4. AWS Glue 爬虫每 24 小时使用最新的分区数据更新表，并使其可用于查询。

基本流程有效。然而，这有几个问题......

第一个（也是最重要的）是此数据是多租户应用程序的一部分。每个事件内部都有一个名为 的属性account_id。将发出的每个查询都将由特定帐户发出，我不想为每个查询扫描所有帐户数据。我需要找到一种可扩展的方式只查询相关数据。我确实考虑过尝试让我们 Kinesis 提取account_id并将其用作分区。但是，目前不支持此功能，并且对于超过 10,000 个帐户，AWS 20k 分区限制很快就会成为一个问题。

第二个问题是文件大小！AWS 建议文件不要小于 128 MB，因为这会对查询时间产生不利影响，因为执行引擎可能会花费额外的时间来处理打开 Amazon S3 文件的开销。鉴于 Firehose 的性质，我只能达到每个文件 128 MB 的最大大小。

标签： amazon-web-servicesamazon-s3amazon-athenaamazon-kinesis-firehose

解决方案

account_id有了这么多帐户，您可能出于多种原因不想将其用作分区键。我认为您在限制方面很好，每个表的分区限制是 1M，但这并不意味着这是一个好主意。

不过，您可以通过对部分帐户 ID 进行分区来显着减少扫描的数据量。如果您的账户 ID 是均匀分布的（如 AWS 账户 ID），您可以按前缀进行分区。如果您的帐户 ID 是第一个数字上的数字分区，则每个查询将扫描的数据量减少 90%，而两位数则减少 99%——同时仍将分区数量保持在非常合理的水平。

不幸的是，我也不知道如何使用 Glue 来做到这一点。在进行 ETL 时，我发现 Glue 通常非常无用。根据我的经验，即使是简单的事情也很难。使用 Athena 的 CTAS 功能与一些简单的 S3 操作相结合，将 CTAS 操作生成的数据添加为现有表中的分区，我取得了更大的成功。

如果您找到一种提取帐户 ID 的方法，您还可以尝试为每个帐户使用单独的表，您可以在数据库中拥有 100K 表。它与表中的分区没有太大区别，但可能会更快，具体取决于 Athena 如何确定要查询的分区。

不要太担心 128 MB 文件大小的经验法则。拥有大量小文件确实比拥有少量大文件更糟糕——但扫描大量数据以过滤掉一小部分对性能和成本非常不利也是事实。Athena 可以在一秒钟内提供结果，即使是对数百个只有几 KB 大小的文件的查询也是如此。我会担心确保 Athena 先读取正确的数据，然后再考虑理想的文件大小。

如果您告诉我更多关于每个帐户的数据量和帐户的预期寿命，我可以就目标提供更详细的建议。

更新：鉴于 Firehose 不允许您更改输入数据的目录结构，并且 Glue 通常非常糟糕，以及您在评论中提供的附加上下文，我会这样做：

  • 创建一个 Athena 表，其中包含数据中所有属性的列，并将日期作为分区键。这是您的输入表，只会针对此表运行 ETL 查询。不用担心输入数据有单独的年月日目录，你只需要一个分区键。将这些作为单独的分区键只会使事情变得复杂，并且拥有一个意味着它可以是 type ，而不是每次要进行日期计算时都必须将DATE三个单独的列组合成一个日期。STRING

  • 创建另一个具有相同列的 Athena 表，但按account_id_prefix日期或月份进行分区。这将是您对其运行查询的表。account_id_prefix将是您帐户 ID 中的一两个字符 - 您必须测试最有效的方法。您还必须决定是按日期还是更长的时间跨度进行分区。日期将使 ETL 更容易和更便宜，但更长的时间跨度会产生更少和更大的文件，这可以使查询更有效（但可能更昂贵）。

  • 创建执行以下操作的 Step Functions 状态机（在 Lambda 函数中）：

    • 将新分区添加到输入表。如果您安排您的状态机每天运行一次，它只需添加与当前日期对应的分区即可。使用 Glue CreatePartitionAPI 调用来创建分区（不幸的是，这需要大量信息才能工作，但您可以运行GetTable调用来获取它。例如使用["2019-04-29"]asValues和"s3://some-bucket/firehose/year=2019/month=04/day=29"as StorageDescriptor.Location。这相当于运行ALTER TABLE some_table ADD PARTITION (date = '2019-04-29) LOCATION 's3://some-bucket/firehose/year=2019/month=04/day=29'- 但通过 Glue 进行比在 Athena 中运行查询更快，更适合 Lambda。
    • 使用当前日期的过滤器对输入表启动CTAS 查询，按第一个字符或帐户 ID 和当前日期进行分区。使用低于查询表位置的 CTAS 输出位置。为 CTAS 操作创建的表生成一个随机名称，该表将在后面的步骤中删除。使用 Parquet 作为格式。
    • 查看Poll for Job Status示例状态机，了解如何等待 CTAS 操作完成。
    • 当 CTAS 操作完成时，列出在使用 Glue 创建的临时表中创建GetPartitions的分区，并在查询表中使用BatchCreatePartitions.
    • 最后删除属于您删除的查询表分区的所有文件，并删除由 CTAS 操作创建的临时表。

如果你决定对比日期更长的东西进行分区，你仍然可以使用上面的过程，但你还需要删除查询表中的分区和 S3 上的相应数据，因为每次更新都会替换现有数据（例如，按月分区，我建议您尝试，每天您都会为整个月创建新文件，这意味着需要删除旧文件）。如果您想每天多次更新查询表，那将是相同的。

这看起来很多，看起来就像 Glue Crawlers 和 Glue ETL 所做的那样——但根据我的经验，它们并没有让它变得这么容易。

在您的情况下，数据是使用 Glue Crawlers 理解的 Hive 样式分区进行分区的，但在许多情况下，您没有得到 Hive 样式的分区，而只是 Y/M/D（我实际上并不知道 Firehose 可以这样传递数据方式，我认为它只做了 Y/M/D）。Glue Crawler 每次运行时也会做很多额外的工作，因为它无法知道数据添加到了哪里，但你知道从昨天开始添加的唯一分区是昨天的分区，因此减少了爬取一步交易。

Glue ETL 也使事情变得非常困难，与 Lambda 和 Step Functions 相比，它是一项昂贵的服务。您要做的就是将原始数据从 JSON 转换为 Parquet 并重新分区。据我所知，使用比 Athena CTAS 查询更少的代码是不可能的。即使您可以使用 Glue ETL 以更少的代码进行转换操作，您仍然需要编写大量代码来替换目标表中的分区 - 因为这是 Glue ETL 和 Spark 根本不支持的。

Athena CTAS 并不是真的用来做 ETL，我认为我上面概述的方法比它应该的复杂得多，但我相信它比尝试做同样的事情更简单（即不断更新并可能根据另一个表中的数据替换表中的分区，而无需每次都重建整个表）。

通过这个 ETL 过程，您得到的好处是，您的摄取不必担心分区超过时间，但您仍然可以获得针对查询进行优化的表。

推荐阅读