Bigquery: get tables' sizes from all datasets

user_10522758 0
Viewed 266

I have a simple query that returns the tabels' sizes for each table in the dataset orders:

SELECT
  table_id,
  TRUNC(size_bytes/1024/1024/1024/1024,2) size_tb,
FROM orders.__TABLES__

If I wish to run this query once for the whole project and all its tables, how can I do it? I tried to change the last row to From __TABLES__ but that is an error.

google-bigquery
2 Answers

I use this Python script for something similar (probably originate in Stackoverflow) with my adjustments

from google.cloud import bigquery

client = bigquery.Client()

datasets = list(client.list_datasets())
project = client.project

sizes = []

if datasets:
    print('Datasets in project {}:'.format(project))
    for dataset in datasets:  # API request(s)
        print('Dataset: {}'.format(dataset.dataset_id))

        query_job = client.query("select table_id, sum(size_bytes)/pow(10,9) as size from `"+dataset.dataset_id+"`.__TABLES__ group by 1")

        results = query_job.result()
        for row in results:
            print("\tTable: {} : {}".format(row.table_id, row.size))
            
            item = {
                'project': project,
                'dataset': dataset.dataset_id,
                'table': row.table_id,
                'size': row.size
            }
            sizes.append(item)

else:
    print('{} project does not contain any datasets.'.format(project))
user_16836260

You could use INFORMATION_SCHEMA data to query

select 
  project_id,
  TABLE_SCHEMA,
  TABLE_NAME,
  sum(TOTAL_PHYSICAL_BYTES) / pow(10,9) as size
from 
  project.region.INFORMATION_SCHEMA.TABLE_STORAGE 
group by 1,2, 3
order by size DESC

Where project is your project name and region is region where data is located (e.g. region-us). Refer to https://cloud.google.com/bigquery/docs/information-schema-table-storage for more info

user_27334180
Related