Finding Bigquery table size across all projects

Question

We are maintaining a table in Bigquery that captures all the activity logs from the Stack driver logs. This table helps me list all the tables present, User, who created the table, what was the last command run on the table etc across projects and data sets in our organization. Along with this information, I also want the table size for the tables I am trying to check.

I can Join with the TABLES and TABLE_SUMMARY however I need to explicitly specify the project and dataset I want to query, but my driving table has details of multiple projects and Datasets.

Is there any other metadata table I can get the table size from, or any logs that I can load into a Bigquery table to join and get the desired results

Answer 1

You can use the bq command line tool. With the command:

bq show --format=prettyjson

This provides the numBytes, datasetId, projectId and more. With a script you can use:

bq ls

and loop through the datasets and tables in each projects to get the information needed. Keep in mind that you can also use API or a client library.