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?
Same problem. It's a pain because I have a complicated subquery that I want to use in several different ways, so it's ideal for a view, but it is based on the most recent partition key. I have to copy-paste.