Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Can I delete data (rows in tables) from Athena?

Is it possible to delete data stored in S3 through an Athena query? I have some rows I have to delete from a couple of tables (they point to separate buckets in S3).

I couldn't find a way to do it in the Athena User Guide: https://docs.aws.amazon.com/athena/latest/ug/athena-ug.pdf and DELETE FROM isn't supported, but I'm wondering if there is an easier way than trying to find the files in S3 and deleting them.

like image 913
Guillermo Mirandes Avatar asked Feb 15 '18 20:02

Guillermo Mirandes


People also ask

How do I delete Athena AWS records?

You can leverage Athena to find out all the files that you want to delete and then delete them separately. There is a special variable "$path". To automate this, you can have iterator on Athena results and then get filename and delete them from S3.

Can we alter table in Athena?

To see a new table column in the Athena Query Editor navigation pane after you run ALTER TABLE ADD COLUMNS , manually refresh the table list in the editor, and then expand the table again. ALTER TABLE ADD COLUMNS does not work for columns with the date datatype.

Can Athena modify data?

In Normal practise using Athena we can insert or query data in the table, but the option to update and delete does not exist. With Apache Iceberg integration with Athena, the users can run CRUD operations and also do time-travel on data to see the changes before and after a timestamp of the data.

How do I delete multiple tables in Athena?

Your answer An alternative is to create the tables in a specific database. Dropping the database will then delete all the tables. CREATE DATABASE db1; CREATE EXTERNAL TABLE table1 ...; CREATE EXTERNAL TABLE table2 ...; DROP DATABASE db1 CASCADE; The DROP DATABASE command will delete the table1 and table2 tables.


1 Answers

You can leverage Athena to find out all the files that you want to delete and then delete them separately. There is a special variable "$path".

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

To automate this, you can have iterator on Athena results and then get filename and delete them from S3.

like image 184
Dhaval Avatar answered Oct 13 '22 05:10

Dhaval