时间戳

首页 > 解决方案 > 如何从 S3 关联、连接和聚合多个文件

sql - 如何从 S3 关联、连接和聚合多个文件

问题描述

我在 S3 中有一个存储桶，包含数百个文件夹，每个文件夹都包含具有相同结构的文件，这些文件是关系数据库表的 csv 表示。不同的文件夹因数据内容而异，但可能会发生重叠。

在每个文件夹中，我想加入 3 个表，并将输出存储在一个专用表中。专用表最终应该保存来自所有不同文件夹的连接数据。不同文件夹之间可能会发生重复，但记录有一个唯一键，可以帮助进行聚合。

在所有文件中，特定文件夹的数据大小可以达到 5 GB 的磁盘空间。其中 2 个文件包含数十万条记录。第三个文件最多可以达到 20M 条记录。

结果应存储在 AWS RDS 的 postgresql 实例上。但是，我正在考虑切换到 Redshift。这种规模的数据会更好吗？

这3张表是：

所有这些都由连接中使用的键索引。我的方法是遍历 S3 存储桶，并为每个文件夹将 3 个文件加载到数据库中。然后，使用 sql 为 3 个表创建连接表，最后将连接数据添加到应包含所有文件夹数据的聚合表中。

我目前正在尝试处理 1 个文件夹，以便更好地了解如何在时间和空间上使流程优化。加载后我注意到数据库使用了大约 2 倍的磁盘空间，这是我预期的。为什么加入磁盘空间的成本如此之高？有没有一种以最低成本加载和加入的方法？最初为每个文件夹加载的数据用作临时表，直到我删除重复项并将其加载到聚合表中。所以它的寿命会比较短。我尝试使用CREATE UNLOGGED TABLE，但效果不大。

CREATE UNLOGGED TABLE agg_data AS SELECT * FROM
transactions t
INNER JOIN consumers c USING (consumer_id)
INNER JOIN providers p USING (provider_id);

这适用于 1 个文件夹，时间明智。它确实比我想象的要多得多的磁盘空间。对于数百个文件夹，这将如何大规模工作。随着时间的推移，聚合将如何表现，因为我需要在不断增长的表中搜索重复记录？

总结我的问题：

  1. 如何在 RDS 和 Redshift 之间进行选择？我担心的是目标表中的数千万条记录，以及在向目标表中添加新数据时需要删除重复项。
  2. 为什么加入数据需要这么多数据库存储？对于临时数据，有没有办法将其最小化？
  3. 在删除重复项的同时将新数据插入目标表的有效方法是什么？
  4. 使用 AWS Glue 将文件加入并存储在 S3 中，然后将它们加载到目标数据库会更好吗？目前它似乎不是一种选择，因为 Glue 需要永远加入数据。

标签： sqlpostgresqlamazon-web-servicesamazon-s3inner-join

解决方案

我建议使用Amazon Athena加入文件并生成所需的输出。

首先，每个目录都需要被识别为一个表。这可以通过CREATE EXTERNAL TABLE在 Athena 中手动运行命令并指向文件夹来完成。文件夹中的所有文件都将被视为包含表的数据，并且它们都应该具有相同的格式。

如果需要，可以改为使用AWS Glue 爬网程序来创建表定义。创建一个爬虫并将其指向该文件夹。Glue 将在 Athena 可访问的 AWS Glue 数据目录中创建表定义。

定义三个输入表后，您可以在 Amazon Athena 中运行一个查询，该查询连接三个表并使用CREATE TABLE AS.

请参阅：从查询结果创建表 (CTAS) - Amazon Athena

Glue 也可用于在 Python - AWS Glue 中编程 AWS Glue ETL 脚本，但我还没有尝试过，因此无法提供建议。但是，我使用 AWS Glue 爬虫创建表，然后通过 Amazon Athena 进行查询。

获得输出数据后，您可以将其加载到您选择的数据库中。您选择哪个数据库取决于您的用例。我建议从Amazon RDS for PostgreSQL开始，因为它是一个传统数据库，而且您似乎对它感到满意。如果您以后需要提高性能（例如数十亿或行而不是数百万），您可以迁移到Amazon Redshift。

一般评论：您希望加入这 3 个表是相当奇怪的，因为可能会有很多重复的数据（非常非规范化）。相反，您可以简单地将这些表加载到您想要的数据库中，然后在数据库中进行连接，可能会选择您希望包含哪些列。

推荐阅读