AWS Athena - Query data from different years in partitions

user_15079940 0
Viewed 620

We have large datasets partitioned in S3 like s3://bucket/year=YYYY/month=MM/day=DD/file.csv.

What would be the best way to query the data in Athena from different years and take advantage of the partitioning ?

Here's what I tried for data from 2018-03-07 to 2020-03-06:

Query 1 - running for 2min 45s before I cancel

SELECT dt, col1, col2
FROM mytable
WHERE year BETWEEN '2018' AND '2020'
AND dt BETWEEN '2018-03-07' AND '2020-03-06'
ORDER BY dt

Query 2 - run for about 2min. However I don't think it would be efficient if the period were from for example 2005 to 2020

SELECT dt, col1, col2
FROM mytable
WHERE (year = '2018' AND month >= '03' AND dt >= '2018-03-07')
OR year = '2019' OR (year = '2020' AND month <= '03' AND dt <= '2020-03-06')
ORDER BY dt
amazon-athena partitioning hive amazon-web-services sql
2 Answers

I would suggest to repartition table by dt only (yyyy-MM-dd) instead of year, month, day, this is simple and partition pruning will work, though queries using year only filter like where year>'2020' should be rewritten as dt>'2020-01-01' and so on.

Also BTW in Hive partition pruning works fine with queries like this:

where concat(year, '-', month, '-', day) >= '2018-03-07'
      and 
      concat(year, '-', month, '-', day) <= '2020-03-06'

I cant check does the same works in Presto or not but it worth trying. You can use || operator instead of concat().

user_27003440

Slightly modified your second query (year > '2018' AND year < '2020') instead of year='2019' and having a condition on day rather than dt(I don't see date in partitions)?

SELECT *
FROM award WHERE 
(year = '2018' AND ((month = '03' AND day >= '07') OR month > '03'))
OR 
(year > '2018' AND  year < '2020') 
OR 
(year = '2020' AND (month < '03' OR (month = '03' OR day <= '06')))
user_101868080
Related