CODEWITHSUNDEEP
sqlgoogle-cloud-platformgoogle-bigquery
unique_beast
unique_beast

Reputation: 1470

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

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.

enter image description here

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

Upvotes: 29

Views: 38983

Answers (2)

KatieK
KatieK

Reputation: 1

I've just queried stacked GA4 data using the following code:

FROM analytics_#########.__TABLES__
where table_id LIKE 'events_2%'

I have kept the 2 on the events to ensure my intraday tables do not pull through also.

Upvotes: 0

Willian Fuks
Willian Fuks

Reputation: 11777

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).

Upvotes: 50

Related Questions