Why is my Athena query doing a full scan on a partitioned table with joins on the partition keys

0

Hi all,

I have this one query that started costing me way too much, it went unnoticed for almost a year, but now costing me $5k a month.

I have this rather large table, 750GB, with 3 partitions, let's call them colA, colB, colC. I counted the number of partitions for each of those columns and it goes like this respectively (2993, 14030, 16520). There's a hierarchy in those partitions, A -> B -> C. When I query this table, I always use those 3 partitions, in that order (even if that doesn't matter).

My data is HIVE partitioned in S3 and in parquet SNAPPY compression format.

Now, I have a little 3MB fact table that tells me the latest partition added since a certain timestamp. Normally this resolves to 1 or 2 partitions. I use this table to do a RIGHT JOIN with the table above so I can filter out the results just to the partitions of interest. Here goes my interesting experiment and results.

The following query uses the partitions correctly:

WITH filter_result AS(
	SELECT *
	FROM my_fact_table
	WHERE timestamp > 1671633935
)
SELECT *
FROM my_large_table l
RIGHT JOIN filter_result fr ON l.colA = fr.colA
	AND l.colB = fr.colB
	AND l.colC = fr.colC
WHERE l.colA IN ('ABC')
	AND l.colB IN ('DEF')
	AND l.colC IN ('GHI')

but this ones, does a full table scan every time.

WITH filter_result AS(
	SELECT *
	FROM my_fact_table
	WHERE timestamp > 1671633935
)
SELECT *
FROM my_large_table l
RIGHT JOIN filter_result fr ON l.colA = fr.colA
	AND l.colB = fr.colB
	AND l.colC = fr.colC

I have tried pretty much every order of operation possible. Using Athena Engine Version3. I cannot push the filter predicate no matter what I try. I always ends up scanning the 750GB of data, $4 a pop for querying.

I cannot do the version that works, because that's now how my query works, I need the fact table to filter out the results of the second table.

This to me seems like a pretty standard use of a SQL engine. If Athena is not capable of doing this, at the very least it is worrying and makes me rethink my choice of technology when it comes to push this sort of workloads.

  • Did you try using a WHERE condition in the final select statement as a DUMMY statement. eg.

    where 1=1

    or

    where fr.timestamp > 1671633935

  • Yes, dummy statements do not work.

asked a year ago2650 views
1 Answer
0

Hi alexandrec,

If you query a partitioned table and do not specify the partition in a WHERE clause then Athena will scan the entire table. If you do specify a partition with a WHERE clause then Athena will scan only the data in the specified partition. This is mentioned in Amazon Athena documentation [1].

This would be why the first query scans the partitions correctly but the second one does not.

I hope this answers your question about the behavior you experience with this query. You may also benefit from [2] which outlines some Athena performance tuning tips.

[1] https://docs.aws.amazon.com/athena/latest/ug/partitions.html#partitions-considerations-limitations

[2] https://aws.amazon.com/blogs/big-data/top-10-performance-tuning-tips-for-amazon-athena/

AWS
Joel_P
answered a year ago
profile picture
EXPERT
reviewed 19 days ago
  • JOIN clause conditions should be understood by the underlying engine as filter predicates. No where in the documentation it is said anything about that specifically. Otherwise, Athena becomes a huge money sink for very large tables with thousands of partitions. The only solution is to reduce the amount of partitions, so we scan less data, but that comes at the cost of losing data in the table. Where can I get help from an AWS Athena engineer?

  • Hi alexandrec,

    You can raise a technical support case at [1] to seek assistance from an AWS Athena engineer. Note that you will need a support plan that is Developer or above. See [2] for details on creating a support case with AWS.

    [1] https://console.aws.amazon.com/support/home#/case/create?issueType=technical [2] https://docs.aws.amazon.com/awssupport/latest/user/case-management.html

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions