Subquery in partitioned Athena tables

user_5495304 0
Viewed 724

I am using partitions in Athena. I have a partition called snapshot, and when I call a query as such:

select * from mytable where snapshot = '2020-06-25'

Then, as expected only the specified partition is scanned and my query is fast. However, if I use a subquery which returns a single date, it is slooow:

select * from mytable where snapshot = (select '2020-06-25')

The above actually scans all partitions and not only the specified date, and results in very low performance.

My question is can I use a subquery to specify partitions and increase performance. I need to use a subsquery to add some custom logic which returns a date based on some criteria.

trino presto amazon-athena hive sql
2 Answers

Edit:

Trino 356 is able to inline such queries, see https://github.com/trinodb/trino/issues/4231#issuecomment-845733371

Older answer:

Presto still does not inline trivial subquery like (select '2020-06-25'). This is tracked by https://github.com/trinodb/trino/issues/4231. Thus, you should not expect Athena to inline, as it's based on Presto .172.

I need to use a subsquery to add some custom logic which returns a date based on some criteria.

If your query is going to be more sophisticated, not a constant expression, it will not be inlined anyway. If snapshot is a partition key, then you could leverage a recently added feature -- dynamic partition pruning. Read more at https://trino.io/blog/2020/06/14/dynamic-partition-pruning.html. This of course assumes you can choose Presto version.

If you are constraint to Athena, your only option is to evaluate the subquery outside of the main query (separately), and pass it back to the main query as a constant (e.g. literal).

user_654580

The Athena 2.0 released in late 2020 seems to have improved their push_down_predicate handling to support subquery.

Here is their related statement from https://docs.aws.amazon.com/athena/latest/ug/engine-versions-reference.html#engine-versions-reference-0002

Predicate inference and pushdown – Predicate inference and pushdown extended for queries that use a <symbol> IN <subquery> predicate.

My test with our own Athena table indicates this is indeed the case. My test query is roughly as below

SELECT *
FROM table_partitioned_by_scrape_date
WHERE scrape_date = (
  SELECT max(scrape_date) 
  FROM table_partitioned_by_scrape_date
)

From the bytes scanned by the query, I can tell Athena indeed only scanned the partition with the latest scrape_date.

Moreover, I also tested support of push_down_predicate in JOIN clause where the join_to value is result of another query. Even though it is not mentioned in the release note, apparently Athena 2.0 now is smart enough also to support this scenario and only scan the latest scrape_date partition. I have tested similar query in Athena 1.0 before, it would scan all the partitions instead. My test query is as below

WITH l as (
  SELECT max(scrape_date) as latest_scrape_date
  FROM table_partitioned_by_scrape_date
)
SELECT deckard_id
FROM table_partitioned_by_scrape_date as t
JOIN l ON t.scrape_date = l.latest_scrape_date
user_8258600
Related