accessing Athena pseudo columns in Glue ETL job custom query

Question

background:

Users upload files to an S3 bucket containing their predictions for events under certain circumstances. I want my query results to only show the most recently made prediction for the circumstances.

Example: on 11/22/2023 a file is uploaded. 
| condition 1| condition 2| prediction|
| --- | --- | --- |
| red| blue| 12|
| yellow| green| 4|

then on 11/23/2023 another file is uploaded

| condition 1| condition 2 | prediction|
| --- | --- | --- |
| red | yellow| 9|
| purple| orange| 11|
| yellow| green| 7|

when I run my query I should see the following results
| condition 1| condition 2| prediction|
| --- | --- | --- |
| red| blue| 12|
| red | yellow| 9|
| purple| orange| 11|
| yellow| green| 7|

In Athena I have been able to accomplish this through the use of the pseudo column $file_modified_time. Which allows me to query the S3 object metadata telling me when the file containing each record was last updated.

However now I'm trying to run the query as a part of a Glue ETL job, so instead of using Presto like Athena, I have to use Spark SQL. My problem is Spark SQL doesn't recognize $file_modified_time as a column so I am trying to figure out if there is an alternative I can use?  (unless I can just run an Athena query within a Glue ETL job and I just don't know it).

here is Presto's documentation on the pseudo columns if that is any help.
https://prestodb.io/docs/current/connector/hive.html#extra-hidden-columns

has anybody else ran into this before and found a solution?

Thanks!

Accepted Answer

Just to add on to Gonzolo's response, Glue itself doesn’t provide direct functionality for retrieving the timestamp of when a file was saved in a S3 bucket. To retrieve such information, you can use Boto client methods to achieve this. Specifically you can use the:

* head_object() method [1]
* list_objects_v2() method [2]

Please see the external resource below to see example code of how this can be achieved.

Alternatively, you can save your Athena query and execute the saved query through a Glue job. Please see example code of this in this AWS Blog post [3].

References:
* [1] - https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/s3/client/head_object.html#

* [2] - https://boto3.amazonaws.com/v1/documentation/api/latest/reference/services/s3/client/list_objects_v2.html#

* [3] - https://repost.aws/questions/QUE8WYRbMZSQWopvUbBYerFw/can-i-execute-an-athena-saved-query-from-lambda
* [external resource] - https://stackoverflow.com/questions/9679344/getting-s3-objects-last-modified-datetimes-with-boto

Answer

It would be better if the predictions has a timestamp, instead of relying on the file modification date, which could be affected by other things.  
Otherwise, I don't think there is a way in Glue/Spark but you could invoke Athena from Glue and read the results (it's a bit wasteful since the cluster would be waiting while Athena is running) and then ask Glue to read the Athena query results and continue from there.

condition 1	condition 2	prediction
red	yellow	9
purple	orange	11
yellow	green	7

condition 1	condition 2	prediction
red	blue	12
red	yellow	9
purple	orange	11
yellow	green	7

accessing Athena pseudo columns in Glue ETL job custom query

Relevant content