By using AWS re:Post, you agree to the Terms of Use

Athena views can't include Athena table partitions

0

Hi,

I found out this weird bug in Athena when showing table partitions using Athena engine version 2. When retrieving the most recent partition for a table, I can easily create this simple query which picks the latest year according to the partition. In my example I have a table which is partitioned by year e.g. year=2021/file.csv , year=2022/file.csv. If I run this query in Athena

select
	*
from
	"my_year_table$partitions"
order by
	year desc
limit 1

I get 2022 back

However, If I try to create a view that does this automatically for me

create or REPLACE view HURR_DURR as 
select
	*
from
	"my_year_table$partitions"
order by
	year desc
limit 1

I'm greeted with the following error

line 5:2: Table [aws-account-number].my_database.my_year_table$partitions does not exist. This query ran against the "my_database" database, unless qualified by the query. Please post the error message on our forum or contact customer support with Query Id: 266fb6e9-4a14-47de-8760-a9a21b88387e

The reason why I want to create a view is because I have tables with much more partitioning and I want some end users that query athena to simply run the view to retrieve the most recent partition key. Is there perhaps another way to go on about it?

Best regards

  • Bump Up - does such a feature work here?

asked 2 months ago44 views
No Answers

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