Does anyone know if its possible to retrieve the creation date of a table in AWS Athena using SQL on the information_schema catalog? I know I can use show properties on an individual table basis but I want to get the data for 1000's of tables.
1 Answers
This can tell you when the data in a given table was populated.
1) list the s3 objects in the table
Some SQL to execute via Athena. I like knowing record counts as well, but really the "$PATH" thing is the important part.
select count(*) as record_cnt, "$PATH" as path
from myschema.mytable
group by "$PATH"
order by "$PATH"
record_cnt path
10000 s3://mybucket/data/foo/tables/01234567-890a-bcde/000000_00000-abcdef
..etc...
2) get the timestamp for the s3 objects
$ aws s3 ls s3://mybucket/data/foo/tables/01234567-890a-bcde/000000_00000-abcdef
2022-01-01 12:01:23 4456780 000000_00000-abcdef
$
You'll need to loop through the S3 objects & consolidate the timestamps.
I see +/- a few seconds on the backing s3 objects when I create tables, so you may want to consolidate that to the nearest 5 minutes... or hour.
shrug Whatever fits the tempo of your table creation.
I've also seen tables where data is appended over time, so maybe the closest you could to an actual table create timestamp would be the oldest s3 object's timestamp.
Ps. I haven't dug into the Glue table defn stuff enough to know if one can get useful metadata out of that.
Related