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

amazon-web-services amazon-athena
Guillermo Mirandes picture Guillermo Mirandes · Feb 15, 2018 · Viewed 13.9k times · Source

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.

Answer

Dhaval picture Dhaval · Feb 16, 2018

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.

Related questions

Amazon Athena: no viable alternative at input

While creating a table in Athena; it gives me following exception: no viable alternative at input

Read More
Athena greater than condition in date column

I have the following query that I am trying to run on Athena. SELECT observation_date, COUNT(*) AS count FROM db.table_name WHERE observation_date > '2017-12-31' GROUP BY observation_date However it is producing this …

Read More
AWS Athena and date_format

I have some issue while formatting a timestamp with Amazon Athena service. select date_format(current_timestamp, 'y') Returns just 'y' (the string). The only way I found to format dates in Amazon Athena is trough CONCAT + YEAR + MONTH + DAY …

Read More