CODEWITHSUNDEEP
amazon-web-servicesaws-glueamazon-athena
micah
micah

Reputation: 8096

Athena Partition Projection Not Working As Expected

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?

Upvotes: 6

Views: 3745

Answers (2)

Theo
Theo

Reputation: 132862

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.

Upvotes: 6

Nicolas Busca
Nicolas Busca

Reputation: 1305

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.

Upvotes: -3

Related Questions