How to find Bigquery storage size for a single table / all tables in a dataset - Google Bigquery

Ever wanted to calculate table wise cost for Google BigQuery Storage, Here's a handy query to view size of storage in GB for each table individually

• There are two good things,
• First you don't have to be an admin to run this query
• There is no charge to run this query as it uses internal metadata tables

To find the size of all the tables within a data set:

select table_id, sum(size_bytes)/(1024*1024*1024) as size_GB
from datasetname.__TABLES__ 
group by table_id order by size_GB desc;

To find the size of one table within a dataset:

select sum(size_bytes)/(1024*1024*1024) as size_GB
from datasetname.__TABLES__
where table_id = '<your_table>';

To find the size of the entire dataset:

select sum(size_bytes)/(1024*1024*1024) as size_GB
from datasetname.__TABLES__;

These queries appear to retrieve 0 billable bytes, so they're free. You can also get: project_id, dataset_id, table_id, creation_time, last_modified_time, row_count, size_bytes, type from __TABLES__. Timestamps are in Unix milliseconds

nVector

posted on 22 Oct 19