I am uploading a .CSV file into AWS S3 and then this is pulled into an AWS Athena table.

Is it possible to automatically add a column at the end of the Athena table that shows the time that the CSV file was uploaded?

The process is that I receive external data in regular intervals and will always be uploading this data using S3. It would be great if the upload time can be included for every CSV.

Is this possible?

CodePudding user response：

There is a special $PATH column that provides the "Amazon S3 file location for the data in a table row":

SELECT "$path" FROM "my_database"."my_table" WHERE year=2019;

Therefore, if the filename of the CSV file contained a date/time, you could extract it in the query.

However, there is no special column for showing the upload date of the file.

See: Getting the file locations for source data in Amazon S3 - Amazon Athena