google-bigquery - 大查询 : get tables' sizes from all datasets

Question

我有一个简单的查询，它返回数据集中每个表的表大小 orders :

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

如果我希望对整个项目及其所有表运行一次此查询，我该怎么做？
我试图将最后一行更改为 From __TABLES__但这是一个错误。

Answer 1

我将这个 Python 脚本用于类似的事情(可能源自 Stackoverflow)并进行调整

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