Get the Last Modified date for all BigQuery tables in a BigQuery Project

Question

I have several databases within a BigQuery project which are populated by various jobs engines and applications. I would like to maintain a dashboard of all of the Last Modified dates for every table within our project to monitor job failures.

Are there any command line or SQL commands which could provide this list of Last Modified dates?

Answer 1

For a SQL command you could try this one:

#standardSQL
SELECT *, TIMESTAMP_MILLIS(last_modified_time)
FROM `dataset.__TABLES__` where table_id = 'table_id'

I recommend you though to see if you can log these errors at the application level. By doing so you can also understand why something didn't work as expected.

If you are already using GCP you can make use of Stackdriver (it works on AWS as well), we started using it in our projects and I recommend giving it a try (we tested for python applications though, not sure how the tool performs on other clients but it might be quite similar).