AWS Athena: Delete partitions between date range

Question

I have an athena table with partition based on date like this:

20190218

I want to delete all the partitions that are created last year.

I tried the below query, but it didnt work.

ALTER TABLE tblname DROP PARTITION (partition1 < '20181231');

ALTER TABLE tblname DROP PARTITION (partition1 > '20181010'), Partition (partition1 < '20181231');

Answer 1

According to https://docs.aws.amazon.com/athena/latest/ug/alter-table-drop-partition.html, ALTER TABLE tblname DROP PARTITION takes a partition spec, so no ranges are allowed.

In Presto you would do DELETE FROM tblname WHERE ..., but DELETE is not supported by Athena either.

For these reasons, you need to do leverage some external solution.

For example:

1. list the files as in https://stackoverflow.com/a/48824373/65458
2. delete the files and containing directories
3. update partitions information (https://docs.aws.amazon.com/athena/latest/ug/msck-repair-table.html should be helpful)