TABLE_STORAGE view

TheINFORMATION_SCHEMA.TABLE_STORAGE view provides a current snapshot of storageusage for tables and materialized views. When you query theINFORMATION_SCHEMA.TABLE_STORAGE view, the query results contain one row foreach table or materialized view for the current project.

The data in theINFORMATION_SCHEMA.TABLE_STORAGE view isnot kept in real time, and updates are typically delayed by a few seconds to a fewminutes. Storage changes that are caused by partition or table expiration alone,or that are caused by modifications to the dataset time travel window, might takeup to a day to be reflected in theINFORMATION_SCHEMA.TABLE_STORAGE view.In cases of dataset deletion where the dataset contains more than 1,000 tables,this view won't reflect the change until thetime travel window for the deleteddataset has passed.

The table storage views give you a convenient way to observe your currentstorage consumption, and in addition provide details on whether yourstorage uses logical uncompressed bytes, physical compressed bytes, ortime travel bytes. This information can help you with tasks like planning forfuture growth and understanding the update patterns for tables.

Data included in the*_BYTES columns

The*_BYTES columns in the table storage views include information about youruse of storage bytes. This information is determined by looking at your storageusage for materialized views and the following types of tables:

• Permanent tables created through any of the methods described inCreate and use tables.
• Temporary tables created insessions. These tables are placed into datasets with generated names like "_c018003e063d09570001ef33ae401fad6ab92a6a".
• Temporary tables created inmulti-statement queries("scripts"). These tables are placed into datasets with generated names like "_script72280c173c88442c3a7200183a50eeeaa4073719".

Data stored in thequery results cacheis not billed to you and so is not included in the*_BYTES column values.

Clones and snapshots show*_BYTES column values as if they were completetables, rather than showing the delta from the storage used by the base table,so they are an over-estimation. Your bill does account correctly for this deltain storage usage. For more information on the delta bytes stored and billed by clones andsnapshots, see theTABLE_STORAGE_USAGE_TIMELINEview.

Forecast storage billing

In order to forecast the monthly storage billing for a dataset, you can useeither thelogical orphysical *_BYTES columns in this view, dependingon thedataset storage billing modelused by the dataset. Please note that this is only a rough forecast, andthe precise billing amounts are calculated based on the usage byBigQuery storage billing infrastructure and visible inCloud Billing.

For datasets that use a logical billing model, you can forecast your monthlystorage costs as follows:

((ACTIVE_LOGICAL_BYTES value /POW(1024, 3)) * active logical bytes pricing) +((LONG_TERM_LOGICAL_BYTES value /POW(1024, 3)) * long-term logical bytes pricing)

TheACTIVE_LOGICAL_BYTES value for a table reflects the active bytescurrently used by that table.

For datasets that use a physical billing model, you can forecast your storagecosts as follows:

((ACTIVE_PHYSICAL_BYTES + FAIL_SAFE_PHYSICAL_BYTES value /POW(1024, 3)) * active physical bytes pricing) +((LONG_TERM_PHYSICAL_BYTES value /POW(1024, 3)) * long-term physical bytes pricing)

TheACTIVE_PHYSICAL_BYTES value for a table reflects the active bytescurrently used by that table plus the bytes used for time travel for that table.

To see the active bytes of the table alone, subtract theTIME_TRAVEL_PHYSICAL_BYTES value from theACTIVE_PHYSICAL_BYTES value.

For more information, seeStorage pricing.

For comprehensive details about how storage costs are calculated, seeStorage pricing page.

Required roles

To get the permissions that you need to query theINFORMATION_SCHEMA.TABLE_STORAGE view, ask your administrator to grant you theBigQuery Metadata Viewer (roles/bigquery.metadataViewer) IAM role on the project. For more information about granting roles, seeManage access to projects, folders, and organizations.

This predefined role contains the permissions required to query theINFORMATION_SCHEMA.TABLE_STORAGE view. To see the exact permissions that are required, expand theRequired permissions section:

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Schema

TheINFORMATION_SCHEMA.TABLE_STORAGE view has the following schema:

For stability, we recommend that you explicitly list columns in your information schema queries instead ofusing a wildcard (SELECT *). Explicitly listing columns prevents queries frombreaking if the underlying schema changes.

Scope and syntax

Queries against this view must include aregion qualifier.The following table explains the region scope for this view:

• Optional:PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: anydataset region name. For example,`region-us`.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

The following example shows how to return storage information for tables ina specified project and region:

SELECT*FROM`myProject`.`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

The following example shows how to return storage information for tables in thecurrent project in a specified region:

SELECT*FROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT;

Examples

Example 1:

The following example shows you the total logical bytes billed for thecurrent project.

SELECTSUM(total_logical_bytes)AStotal_logical_bytesFROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

The result is similar to the following:

+---------------------+| total_logical_bytes |+---------------------+| 971329178274633     |+---------------------+

Example 2:

The following example shows different storage bytes in GiB at the dataset(s) level for current project.

SELECTtable_schemaASdataset_name,-- LogicalSUM(total_logical_bytes)/power(1024,3)AStotal_logical_gib,SUM(active_logical_bytes)/power(1024,3)ASactive_logical_gib,SUM(long_term_logical_bytes)/power(1024,3)ASlong_term_logical_gib,-- PhysicalSUM(total_physical_bytes)/power(1024,3)AStotal_physical_gib,SUM(active_physical_bytes)/power(1024,3)ASactive_physical_gib,SUM(active_physical_bytes-time_travel_physical_bytes)/power(1024,3)ASactive_no_tt_physical_gib,SUM(long_term_physical_bytes)/power(1024,3)ASlong_term_physical_gib,SUM(time_travel_physical_bytes)/power(1024,3)AStime_travel_physical_gib,SUM(fail_safe_physical_bytes)/power(1024,3)ASfail_safe_physical_gibFROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGEWHEREtable_type='BASE TABLE'GROUPBYtable_schemaORDERBYdataset_name

Example 3:

The following example shows you how to forecast the price difference perdataset between logical and physical billing models for the next 30 days.This example assumes that future storage usage is constant over the next30 days from the moment the query was run. Note that the forecast is limited tobase tables, it excludes all other types of tables within a dataset.

The prices used in the pricing variables for this query are fortheus-central1 region. If you want to run this query for a different region,update the pricing variables appropriately. SeeStorage pricing for pricing information.

1. Open the BigQuery page in the Google Cloud console.

   Go to the BigQuery page

2. Enter the following GoogleSQL query in theQuery editor box.INFORMATION_SCHEMA requires GoogleSQL syntax. GoogleSQLis the default syntax in the Google Cloud console.

   DECLAREactive_logical_gib_priceFLOAT64DEFAULT0.02;DECLARElong_term_logical_gib_priceFLOAT64DEFAULT0.01;DECLAREactive_physical_gib_priceFLOAT64DEFAULT0.04;DECLARElong_term_physical_gib_priceFLOAT64DEFAULT0.02;WITHstorage_sizesAS(SELECTtable_schemaASdataset_name,-- LogicalSUM(IF(deleted=false,active_logical_bytes,0))/power(1024,3)ASactive_logical_gib,SUM(IF(deleted=false,long_term_logical_bytes,0))/power(1024,3)ASlong_term_logical_gib,-- PhysicalSUM(active_physical_bytes)/power(1024,3)ASactive_physical_gib,SUM(active_physical_bytes-time_travel_physical_bytes)/power(1024,3)ASactive_no_tt_physical_gib,SUM(long_term_physical_bytes)/power(1024,3)ASlong_term_physical_gib,-- Restorable previously deleted physicalSUM(time_travel_physical_bytes)/power(1024,3)AStime_travel_physical_gib,SUM(fail_safe_physical_bytes)/power(1024,3)ASfail_safe_physical_gib,FROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECTWHEREtotal_physical_bytes+fail_safe_physical_bytes>0-- Base the forecast on base tables only for highest precision resultsANDtable_type='BASE TABLE'GROUPBY1)SELECTdataset_name,-- LogicalROUND(active_logical_gib,2)ASactive_logical_gib,ROUND(long_term_logical_gib,2)ASlong_term_logical_gib,-- PhysicalROUND(active_physical_gib,2)ASactive_physical_gib,ROUND(long_term_physical_gib,2)ASlong_term_physical_gib,ROUND(time_travel_physical_gib,2)AStime_travel_physical_gib,ROUND(fail_safe_physical_gib,2)ASfail_safe_physical_gib,-- Compression ratioROUND(SAFE_DIVIDE(active_logical_gib,active_no_tt_physical_gib),2)ASactive_compression_ratio,ROUND(SAFE_DIVIDE(long_term_logical_gib,long_term_physical_gib),2)ASlong_term_compression_ratio,-- Forecast costs logicalROUND(active_logical_gib*active_logical_gib_price,2)ASforecast_active_logical_cost,ROUND(long_term_logical_gib*long_term_logical_gib_price,2)ASforecast_long_term_logical_cost,-- Forecast costs physicalROUND((active_no_tt_physical_gib+time_travel_physical_gib+fail_safe_physical_gib)*active_physical_gib_price,2)ASforecast_active_physical_cost,ROUND(long_term_physical_gib*long_term_physical_gib_price,2)ASforecast_long_term_physical_cost,-- Forecast costs totalROUND(((active_logical_gib*active_logical_gib_price)+(long_term_logical_gib*long_term_logical_gib_price))-(((active_no_tt_physical_gib+time_travel_physical_gib+fail_safe_physical_gib)*active_physical_gib_price)+(long_term_physical_gib*long_term_physical_gib_price)),2)ASforecast_total_cost_differenceFROMstorage_sizesORDERBY(forecast_active_logical_cost+forecast_active_physical_cost)DESC;

   Note:INFORMATION_SCHEMA view names are case-sensitive.

3. ClickRun.

The result is similar to following:

+--------------+--------------------+-----------------------+---------------------+------------------------+--------------------------+-----------------------------+------------------------------+----------------------------------+-------------------------------+----------------------------------+--------------------------------+| dataset_name | active_logical_gib | long_term_logical_gib | active_physical_gib | long_term_physical_gib | active_compression_ratio | long_term_compression_ratio | forecast_active_logical_cost | forecaset_long_term_logical_cost | forecast_active_physical_cost | forecast_long_term_physical_cost | forecast_total_cost_difference |+--------------+--------------------+-----------------------+---------------------+------------------------+--------------------------+-----------------------------+------------------------------+----------------------------------+-------------------------------+----------------------------------+--------------------------------+| dataset1     |               10.0 |                  10.0 |                 1.0 |                    1.0 |                     10.0 |                        10.0 |                          0.2 |                              0.1 |                          0.04 |                             0.02 |                           0.24 |