CODEWITHSUNDEEP
google-bigquery
MarcinSzaleniec
MarcinSzaleniec

Reputation: 2256

BigQuery - query for metadata like 'last modified'

Is there any way to query for tables details in BigQuery? What do I mean? Running query like

SELECT * FROM region-eu.INFORMATION_SCHEMA.TABLES

I get the name of the billing project, data set name, table name, table type, if is insertable, typed, and creation time. However, looking into the details tab in the BQ console, I can find table size, last modified, number of rows, partitioned by, and so on. Is there any way to query for those metadata as well?

Your help would be appreciated.

Upvotes: 0

Views: 3869

Answers (1)

Ricco D
Ricco D

Reputation: 7277

You can view the metadata by exporting BigQuery usage logs.

Setup logs export using Cloud Logging. See creating a sink for a more detailed take.

  1. Open Logging
  2. Click Logs Router
  3. Click Create Sink
  4. Enter "Sink Name"
  5. For "Sink service" choose "BigQuery dataset"
  6. Select your BigQuery dataset to monitor
  7. Create sink

When the sink is created, all operations to be executed will store data usage logs in table "cloudaudit_googleapis_com_data_access_YYYYMMDD" and activity logs in table "cloudaudit_googleapis_com_activity_YYYYMMDD" under the BigQuery dataset you selected in your sink. Keep in mind that you can only track the usage starting at the date when you set up the logs export tables.

Created logging tables:

enter image description here

Using this simple query I checked the latest update date of my table:

SELECT  
JSON_EXTRACT(protopayload_auditlog.metadataJson, "$.tableChange.table.updateTime") as lastUpdate
FROM `project_id.dataset.cloudaudit_googleapis_com_activity*`
where protopayload_auditlog.metadataJson IS NOT NULL
ORDER BY lastUpdate DESC LIMIT 1

Query Output:

enter image description here

See more BigQuery logging query examples and you can refer to the metadata json structure to have an idea on what details you can pull on your tables.

EDIT 20210923:

Another option is to query __TABLES__ it will return:

  • project_id
  • dataset_id
  • table_id
  • creation_time
  • last_modified_time
  • row_count
  • size_bytes

See query:

SELECT * FROM `projec_id.dataset_id.__TABLES__` LIMIT 100

Output: enter image description here

Upvotes: 1

Related Questions