CODEWITHSUNDEEP
amazon-web-serviceshiveamazon-athena
micah
micah

Reputation: 8096

Unable to Delete Partition in Athena

I have a poorly formatted partition in Athena. I partition on year, month, day, and hour as integer columns, but mistakenly created partitions as floats.

i.e

/year=2019.0/month=4.0/day=22.0/hour=6.0

instead of

/year=2019/month=4/day=22/hour=6

I removed the s3 files responsible and ran a MSCK REPAIR TABLE but the partition wasn't removed. I tried removing the partition manually with-

ALTER TABLE my_table DROP PARTITION (year=2019.0)
ALTER TABLE my_table DROP PARTITION (year='2019.0')

But I got the error

FAILED: SemanticException [Error 10006]: Partition not found (year = null)

Notice year = null. It seems Athena doesn't know what to do with decimals.

How do I get rid of this faulty partition?

EDIT:

The only way I was able to resolve this was to recreate the table and repair it. Still looking for another solution because that would be a bummer in prod.

Upvotes: 3

Views: 3439

Answers (1)

notNull
notNull

Reputation: 31460

Could you once try with these methods to drop all partitions:

ALTER TABLE my_table DROP PARTITION (year > 0.0);
(or)
ALTER TABLE my_table DROP PARTITION (year > 0);

(or)

Change the datatype of year to String then try to drop the partitions

ALTER TABLE my_table DROP PARTITION (year='2019.0')

Upvotes: 3

Related Questions