DELETE FROM does not work on AWS Data Catalog table

Question

Hello,

While trying to run this command `DELETE FROM "datasets"."us_spending"` in Athena, on a table from AWS Data Catalog, I had this error:

```
NOT_SUPPORTED: Cannot delete from non-managed Hive table
This query ran against the ‘datasets’ database, unless qualified by the query. Please post the error message on our forum  or contact customer support  with Query ID: 5c140e60-7534-44b5-a693-85ec1d645d4b
```
Does this means that I can't delete from the AWS Data Catalog tables, and only delete the entire table, and then recreate it ?  Is there any method to delete rows based on where condition ?

Thank you,  
Mihai

Accepted Answer

Hi,

as described in the [Athena Documentation](https://docs.aws.amazon.com/athena/latest/ug/delete-statement.html) Delete works only with [Iceberg](https://iceberg.apache.org/) tables.

Tables catalogued in the AWS Glue Catalog from file formats as csv, parquet, orc, json, etc. are append only; a data lake it is not transactional unless you start using an open table format as Iceberg, Hudi or Delta. Currently Athena supports only Iceberg for deletes.

To learn more on how to create and work with a Iceberg table in the AWS Glue Catalog refer to[ this documentation page from LakeFormation](https://docs.aws.amazon.com/lake-formation/latest/dg/creating-iceberg-tables.html) and the following blog posts:
[Introducing AWS Glue crawler and create table support for Apache Iceberg format](https://aws.amazon.com/blogs/big-data/introducing-aws-glue-crawler-and-create-table-support-for-apache-iceberg-format/) , [AWS Glue Data Catalog now supports automatic compaction of Apache Iceberg tables](https://aws.amazon.com/blogs/aws/aws-glue-data-catalog-now-supports-automatic-compaction-of-apache-iceberg-tables/), [Perform upserts in a data lake using Amazon Athena and Apache Iceberg](https://aws.amazon.com/blogs/big-data/perform-upserts-in-a-data-lake-using-amazon-athena-and-apache-iceberg/)

hope this helps

DELETE FROM does not work on AWS Data Catalog table

相關內容