Are there any built-in athena functions that will show the underlying S3 file create / modify date for a given row in a query result? We have a large data lake with many different file formats and it would be hard to add this information to them all.
2 Answers
Amazon Athena supports the Presto $path pseudocolumn, which shows the name of the file from which the row was obtained, eg:
SELECT DISTINCT "$path" FROM table
There are requests to add more pseudocolumns to Presto, such as Last Modified and File Size, but (at the time of writing this answer) they are not available in Presto. Once they are in Presto, they might find their way to Athena after some time.
See:
According the PrestoDB documentation here https://prestodb.io/docs/current/connector/hive.html#extra-hidden-columns the fields are added but at the time of this post they are not yet available in Athena.
According to the documentation the answer to your question would be the modification date of the file, that can be retrieved with the $file_modified_time variable.
Related