sql - 如何使用基于分区的 INSERT INTO 覆盖表？- 雅典娜

Question

我目前正在使用INSERT INTOAthena 命令通过execution_date自动作业更新我每天分区的表。

我现在想通过每天两次更新表来配置此作业，但仍按execution_date. 所以我早上的工作应该像往常一样工作，但我下午的工作execution_date应该会覆盖同样execution_date从早上开始分区的记录。

我已经调查过了MSCK REPAIR TABLE，但我不确定它将如何适用于我的用例。

如何使用 Athena 的INSERT INTO命令覆盖按相同值分区的记录？

标签： sqlamazon-web-servicessql-insertamazon-athenadatabase-partitioning

Answer 1

Athena 永远不会覆盖数据。如果你想删除或替换数据，你必须自己做。INSERT INTO如果输出位置有任何东西，CREATE TABLE AS（CTAS）将拒绝运行。

您可以做什么取决于您的要求。INSERT INTO最简单的方法是在运行命令之前删除数据。这样做的缺点是在命令运行之前分区中没有数据。即在同一时间窗口中运行的查询将看不到当天的任何数据。您可以确保在重新处理期间不运行任何查询并避免不一致，但如果这不是一个选项，它会变得更加复杂。

我过去采用的另一种方法是使用 CTAS 作为转换机制，然后将转换后的数据移动到位。有了新UNLOAD功能，这变得容易一些，因为没有创建表。您可以做的是运行UNLOAD命令而不是您的命令INSERT INTO并使用临时输出位置。如果这是一天中的第一次运行，您只需将文件复制到正确的位置并使用 Glue 数据目录 API 创建一个分区- 或者更好地使用分区投影来避免完全处理分区。如果这是当天的第二次运行，则删除现有分区中的文件并将新文件复制到它们的位置。然后删除UNLOAD命令输出位置中的所有内容。

当分区中没有数据时，以及分区中有部分数据时，该替代方案仍然具有较短的时间窗口。查询可以在您开始删除旧文件和将所有新文件复制到分区位置之前的任何时间点运行。窗口最多不应超过几秒钟，特别是如果您并行执行所有删除操作，然后并行执行所有复制操作，但它永远不会为零。

如果您真的想避免不一致，还有另一种选择，它使用分区位置不必与分区键值相对应的事实。您可以UNLOAD像以前一样运行相同的命令，但设置输出位置，以便输出与其他分区在同一位置结束，但具有唯一的后缀。如果您的表按日期分区，并且您的分区具有类似的 URI，s3://example-bucket/data/2021-08-16/并且s3://example-bucket/data/2021-08-17/您将命令的输出位置设置为UNLOAD类似 的 URI s3://example-bucket/data/2021-08-17_asdf1234/，其中“asdf1234”是每次运行的新随机字符串。转换完成后，您可以使用 Glue 数据目录 API 更新分区的位置指向新位置，但使用相同的分区键值，例如“2021-08-17”。更新分区的位置后，您将删除旧位置的文件。这将确保运行的任何查询都将看到旧数据或新数据，但绝不会看不到数据或部分数据。

没有办法让 Athena 原子地替换分区。哪种选择适合您取决于查询在更新期间看不到数据或看到部分数据的问题。