Athena not adding partitions after msck repair table

Question

I have a firehose that stores data in s3 in the default directory structure: "YY/MM/DD/HH" and a table in athena with these columns defined as partitions:

year: string, month: string, day: string, hour: string

after running

msck repair table clicks

I only receive:

Partitions not in metastore:    clicks:2017/08/26/10

I can add these partitions manually and everything works however, I was wondering why msck repair does not add these partitions automatically and update the metastore?

Possible duplicate of Hive: Partitioning by part of integer column — David דודו Markovitz
Possible duplicate of stackoverflow.com/questions/42785633/… — David דודו Markovitz
@DuduMarkovitz my issue is after running msck repair new partitions are not added automatically, like the posts above show — Sam
... and like my answers show, only a specific directory naming convention, which you are not using, is supported — David דודו Markovitz
thanks Ill add year, month , day , hour specifically in my directories — Sam

Otto Otto · Accepted Answer · 2019-06-28T11:32:02

To use Athena MSCK REPAIR with S3 you need to use key-value pairs as path prefix:

clicks/year=2017/month=08/day=26/hour=10/

instead of: clicks/2017/08/26/10/

Alternatively, update the partitions directly in Glue (manually or use a crawler).

Found this here: https://forums.aws.amazon.com/message.jspa?messageID=789078

Athena not adding partitions after msck repair table

2 Answers