我能删除Athena中的数据（表中的行）吗？

Question

有没有可能通过Athena查询删除存储在S3中的数据？我需要从几个表中删除一些行（它们指向S3中的不同桶）。

我在Athena用户指南中找不到方法：https://docs.aws.amazon.com/athena/latest/ug/athena-ug.pdf，并且不支持DELETE FROM，但我想知道是否有比查找S3中的文件并将其删除更简单的方法。

- Guillermo Mirandes

---

你能找到解决这个问题的方法吗，比如自定义解决方案？ - Aravind Krishnakumar

Answer 1

你可以利用Athena查找你想要删除的所有文件，然后逐个将它们删除。这里有一个特殊变量"$path"。

Select "$path" from <table> where <condition to get row of files to delete>

为了自动化这个过程，您可以在 Athena 结果上使用迭代器，然后获取文件名并从 S3 中删除它们。

- Dhaval

---

这很酷，谢谢分享，但我不能删除整个文件，我需要删除文件中有问题数据的特定行。 - Guillermo Mirandes

目前 Athena 并没有这样的支持。同时，我认为它并不符合 Athena 的宪章，因为它只是一个在某个地方存储数据的分析引擎。 - Dhaval

这与Python Lambda并行工作，使用Python查询Athena检索这些位置，然后假设文件符合Lambda 10GB限制，使用Pandas读取CSV / JSON / Parquet文件并将它们重写回相同的位置，减去要删除的记录。如果您可以处理单个项目记录，高分区设置一个软删除列在末尾，然后更新该标志也可以使用类似的过程。 - vfrank66

Answer 2

此外，我想补充一点，在您找到要更新的文件之后，您可以过滤掉您想要删除的行，并使用CTAS创建新文件：

https://docs.aws.amazon.com/athena/latest/ug/ctas.html

然后，您可以用由CTAS创建的新文件替换旧文件。我认为这是最简单的方法。

- Ori N

Answer 3

答案是肯定的，现在你可以从Athena删除数据，AWS最近推出了支持ACID属性的ICEBERG表格。

您需要按以下步骤创建一个Iceberg表格，该表格将具有与Athena表格相同的数据（您希望删除/更新记录的表格）。

创建ICEBERG表格

    Create table new_iceberg_table 
    (id double, name string)
    LOCATION 'S3://path/where/you/want/to_save/'
    TBLPROPERTIES (table_type='iceberg')

从数据目录加载数据到新的“冰山”表中。

   Insert into datasource.new_iceberg_table
   Select * from datasource.main_athena_table.

main_athena_table = 您想要执行删除/更新或ACID操作的表格。 new_iceberg_table = 新创建的表格。

现在，您可以向Iceberg表插入、更新和删除数据。

您还可以使用SYSTEM_TIME进行时间旅行。

相关的SQL语句

#更新SQL =

UPDATE from datasource.new_iceberg_table set id = 04 where name='ABC' ;

#删除SQL =

DELETE from datasource.new_iceberg_table where name='ABC' ;

#时间旅行SQL（如果你想要穿越时空并查看旧数据）

SELECT * from datasource.new_iceberg_table for SYSTEM_TIME as of (current_timestamp - interval '10' minutes) where name='ABC'

谢谢您的选择。

- Shivendra singh

Answer 4

我只想在Dhaval的回答中加一句话。

您可以找到包含要删除行的文件的路径，而不是删除整个文件，您可以从S3文件中仅删除行，我假设该文件以Json格式存在。

该过程是下载具有这些行的特定文件，从该文件中删除行，并将同一文件上传到S3。

这只是用修改后的数据（在您的情况下，没有被删除的行）替换原始文件。上传后，Athena会再次转换数据，已删除的行将不会显示。

- the_minimalist

Answer 5

使用 AWS Glue 来执行此操作。 加载数据，删除所需的部分，然后保存数据。 现在，您还可以从 S3 中删除文件并合并数据：https://aws.amazon.com/about-aws/whats-new/2020/01/aws-glue-adds-new-transforms-apache-spark-applications-datasets-amazon-s3/

- Stefan

Answer 6

Shivendra Singh的关于ICEBERG的回答应该被接受，因为ICEBERG似乎现在可以满足所有需求。但是，如果您需要保留Hive表，或者如果您的文件格式为JSON并且需要保持这种方式，您有以下选项：

1. 使用CTAS创建新表，并保留您想要保留的值。如果很难以这种方式构建查询，您始终可以执行类似于where id not in (select id ...)或select * from ... except select * from ...的操作。如果您的表已分区，并且在删除后应该有超过100个分区，则需要使用“insert into”技术来每次查询创建多达100个分区（https://docs.aws.amazon.com/athena/latest/ug/ctas-insert-into.html）。
2. 将与删除相关的分区的原始数据从S3中移动（以防万一）
3. 移动由（1）创建的数据

- Kaplan Ilya

Answer 7

AWS宣布了Iceberg与Athena的整合已经正式推出，现在Athena支持对Iceberg表进行原始级别的DML操作。

可以使用以下SQL语句来执行UPDATE和DELETE行：

DELETE FROM [db_name.]table_name [WHERE predicate]

UPDATE [db_name.]table_name SET xx=yy[,...] [WHERE predicate]

更多详情请参考AWS DOCUMENT。

请注意，Athena Iceberg整合现在非常严格 - 不支持嵌套SQL进行删除和更新。以下查询将无法工作：

Delete from table1 where uniqueid in (select b.uniqueid from delete_staging b)

- faisal_kk