CODEWITHSUNDEEP
amazon-web-servicesamazon-s3aws-glueamazon-athenaazure-data-catalog
Surya
Surya

Reputation: 41

How to use max, min functions in projection.columnName.range to the AWS Glue Table Property

I use the below table property to set range to date column

'projection.date.range' = 'NOW-365DAYS,NOW+1DAYS'

The table has no data from NOW-365DAYS as it is a new table. While querying from Athena on this table results in a high volume of listbucket requests. I don't want to happen this. SO thought to set a range like below

'projection.date.range' = 'MAX(2022/01/12, NOW-365DAYS), NOW+1DAYS' so that i can avoid empty partitions. But it is throwing me an error.

Is there a way to use MAX/MIN functions in projection.date.range?

Upvotes: 0

Views: 368

Answers (1)

Theo
Theo

Reputation: 132872

It's not possible to qualify the partition projection range like that, unfortunately. I suggest setting the lower bound to the actual first date with data until the relative range makes sense.

Since you say Athena is making a lot of S3 list requests I assume you are querying the table without filters on this partition key. This will always result in a lot of S3 listings, at least 365 of them, regardless of whether there is data or not. Why aren't your queries filtering on the date partition key?

Is the reason why you want the range to be the last 365 days that you will remove data after one year, or is there another reason?

Upvotes: 0

Related Questions