Amazon Athena: How to drop all partitions at once

Question

My partitions look like these

event_year=2019/event_week=37/event_date=2019-09-10
event_year=2019/event_week=42/event_date=2019-10-13
event_year=2019/event_week=8/event_date=2019-02-20
event_year=2020/event_week=24/event_date=2020-06-15

There are 1500 partitions like this how do I drop all the partitions at once?

Check https://stackoverflow.com/a/68249507/7407116. That worked for me. — Piyush, Jan 23 '22 at 20:56

score 5 · Answer 1 · answered Jul 31 '20 at 12:53

The easiest and quickest way is to drop the table and recreate it. You can get the DDL with SHOW CREATE TABLE table_name if you don't have it.

If you really need to drop partitions and not the table the most efficient way is to use the Glue Data Catalog APIs to first list all partitions and then delete partitions in batches of 25.

Crazometer · Answer 2 · 2023-02-07T23:52:24.133

1

It's not documented anywhere but sometimes in athena you can drop all partitions with

ALTER TABLE table_name DROP PARTITION (not_a_column=NULL)

This appears to be a side effect of being able to only specify one partition if you have a table partitioned on multiple dimensions.

If the above doesn't work then I fallback to using the awswrangler python library https://aws-sdk-pandas.readthedocs.io/en/stable/stubs/awswrangler.catalog.delete_partitions.html

edited Feb 07 '23 at 23:52

answered Jan 30 '23 at 00:48

Crazometer

457
7
12

1

Seems like this answer is sent just for me. Thanks for your contribution. – Ankit Mahajan Jan 30 '23 at 14:56

Amazon Athena: How to drop all partitions at once

2 Answers2