如何列出项目中所有表格的大小 [英] how to list ALL table sizes in a project

问题描述

有没有办法列出BigQuery中所有表的大小?

Is there a way to list all the table size in BigQuery?

我知道这样的命令:

select 
  table_id,
  sum(size_bytes)/pow(10,9) as size
from
  certain_dataset.__TABLES__
group by 
  1

但是我想知道所有数据集中的所有表.

But I want to know all the tables in ALL datasets.

谢谢

推荐答案

目前没有可能在单个查询中执行此操作，但是您可以使用脚本来执行此操作，这是我的python脚本，可打印出列表:

At the moment there's no possible way to do that in a single query, but you can do it with a script, here is my python script that prints out the list:

from google.cloud import bigquery

client = bigquery.Client()

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

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

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

这篇关于如何列出项目中所有表格的大小的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！