I'm struggling with this, let me describe the scenario:
- I have a PySpark process running on EMR, which write a big table into S3, using parquet files.
- The table is written using hive like partitions, ie:
s3://my-bucket/my_table/date=2021-12-16/event=user_created - Then I run a Glue crawler that successfully detects all the partitions and create a table on my database:
my_database.my_table - Last, I have this database "mounted" on Redshift by creating an external database like this:
create external schema my_database
from data catalog
database 'my_database'
iam_role 'arn:aws:iam::blabla'
create external database if not exists;
So, If I run
select *
from my_database
where date = '2021-12-16'
and event = 'user_created'
limit 500;
On Redshift, this finishes in 5s and scans 20MB of data :heart:
but if I run the exact same query from the query editor in the Athena console it'll take some minutes to complete or even be aborted because it reached a limt of 25GB that we set.
What is going on here? Shouldn't Athena benefit from partitioning as well? I feel like I'm missing something, but I can't figure it out.
EDIT:
adding more info, thanks Theo
Here's the table schema, sorry for the blurred columns:
date is a string in the format YYYY-MM-DD, I was using year=YYYY/month=MM/day=DD/ but resulted very difficult to query. Any other advice is welcomed.
Here's the output of three different explains on Redshift Spectrum
explain select * from datalake.mytable
explain select * from datalake.mytable
where event = 'a_valid_event'
explain select * from datalake.mytable
where event = 'a_valid_event'
and "date" = '2021-12-14'
Here's the output of the same queries ran on AWS Athena console:
note that this one took 3 minutes on explain! where spectrum took always less than 10s.
Output using only event filter
last output using event and date on Athena. This explain was blazing fast!
I've added partition indexes to this table because sometimes people will query it using only one of the partitions set up, this is how it looks like (qq: adding event_timestamp index wasn't necessary at all, right?)
Additionally I'd like to say that I've tried the speed of different combinations of filters using boto3 glue client method get_partitions, and it's fetching all partitions just fine!
My own diagnosis is: Athena isn't using partition indexes at all, so if you use one ONE partition field in the filter (instead of the total two) it will just scan a lot of data. Which would match what says here: https://docs.aws.amazon.com/glue/latest/dg/partition-indexes.html#partition-index-limitations
which is nonesense because filtering by event (partition) and then looking for one valid user_id (no partition) will hit the 25GB data scanned cap and abort.
