Athena Partition Projection Not Working As Expected

user_1028039 0
Viewed 1895

I am moving from registered partitions to partition projection.

Previously my data was partitioned by p_year={yyyy}/p_month={MM}/p_day={dd}/p_hour={HH}/... and I am moving these to p_date={yyyy}-{MM}-{dd} {HH}:00:00/..

I have a recent events table that stores the last 2 days worth of events. And so my p_date range is NOW-2DAYS,NOW. The full table parameters are-

projection.enabled: 'True'
projection.p_date.type: 'date'
projection.p_date.range: NOW-2DAYS,NOW
projection.p_date.format: 'yyyy-MM-dd HH:mm:ss'
projection.p_date.interval: 1
projection.p_date.interval.unit: 'HOURS'

But when I try to query this, I get no results.

SELECT COUNT(*) FROM recent_events_2d_v2
> 0

However, If I change the date range to 2020-09-01 00:00:00,NOW I do get results.

Something seems off with the relative date ranges with partition projection. Can anyone see what I may be doing wrong, or is this a bug?

amazon-athena aws-glue amazon-web-services
2 Answers

You need to change your date format to 'yyyy-MM-dd HH:\'00:00\'' (i.e. literal "00:00" instead of minutes and seconds placeholders).

The way partition projection deals with dates leaves some things to be desired. It seems reasonable that if you say the interval is one hour that the timestamps get rounded to the nearest hour, but that's not what happens. Athena will use the actual "now" to generate the partition values, and if your date format contains fields for minutes and seconds, those will be filled in too.

I assume the reason why it worked when you used a hard coded timestamp is that Athena uses that value as the seed for the sequence, and all other timestamps will also be aligned to the hour.

user_11090

If you are sure your bucket p_date={yyyy}-{MM}-{dd} {HH}:00:00/.. contains data, then you need to make sure that partitions are correctly loaded. Try running

MSCK REPAIR TABLE recent_events_2d_v2

and rerun the query.

user_67745380
Related