Stay organized with collections Save and categorize content based on your preferences.

TABLE_STORAGE view

The INFORMATION_SCHEMA.TABLE_STORAGE view provides a current snapshot of storage usage for tables and materialized views. When you query the INFORMATION_SCHEMA.TABLE_STORAGE view, the query results contain one row for each table or materialized view for the current project. The data in this table is not kept in real time, and might be delayed by a few seconds to a few minutes.

The table storage views give you a convenient way to observe your current and historical storage consumption, and in addition provide details on whether your storage uses logical uncompressed bytes, physical compressed bytes, or time travel bytes. This information can help you with tasks like planning for future 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 your use of storage bytes. This information is determined by looking at your storage usage for materialized views and the following types of tables:

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

Storage usage by clones and snapshots is billed, but information about clones and snapshots isn't included in the table storage views.

Forecast storage billing

In order to forecast the monthly storage billing for a dataset, you can use either the logical or physical *_BYTES columns in this view, depending on the dataset storage billing model used by the dataset. Please note that this is only a rough forecast, and the precise billing amounts are calculated based on the usage by BigQuery storage billing infrastructure and visible in Cloud Billing.

For datasets that use a logical billing model, you can forecast your monthly storage 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)

The ACTIVE_LOGICAL_BYTES value for a table reflects the active bytes currently used by that table.

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

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

The ACTIVE_PHYSICAL_BYTES value for a table reflects the active bytes currently used by that table plus the bytes used for time travel for that table.

To see the active bytes of the table alone, subtract the TIME_TRAVEL_PHYSICAL_BYTES value from the ACTIVE_PHYSICAL_BYTES value.

For more information, see Storage pricing.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_STORAGE view, you need the following Identity and Access Management (IAM) permissions:

  • bigquery.tables.get
  • bigquery.tables.list

Each of the following predefined IAM roles includes the preceding permissions:

  • roles/bigquery.admin
  • roles/bigquery.dataViewer
  • roles/bigquery.dataEditor
  • roles/bigquery.metadataViewer

For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project.

For more information about BigQuery permissions, see Access control with IAM.

Schema

The INFORMATION_SCHEMA.TABLE_STORAGE view has the following schema:

Column name Data type Value
PROJECT_ID STRING The project ID of the project that contains the dataset
PROJECT_NUMBER INT64 The project number of the project that contains the dataset
TABLE_SCHEMA STRING The name of the dataset that contains the table or materialized view, also referred to as the datasetId
TABLE_NAME STRING The name of the table or materialized view, also referred to as the tableId
CREATION_TIME TIMESTAMP The table's creation time
TOTAL_ROWS INT64 The total number of rows in the table or materialized view
TOTAL_PARTITIONS INT64 The number of partitions present in the table or materialized view. Unpartitioned tables return 0.
TOTAL_LOGICAL_BYTES INT64 Total number of logical (uncompressed) bytes in the table or materialized view
ACTIVE_LOGICAL_BYTES INT64 Number of logical (uncompressed) bytes that are less than 90 days old
LONG_TERM_LOGICAL_BYTES INT64 Number of logical (uncompressed) bytes that are more than 90 days old
TOTAL_PHYSICAL_BYTES INT64 Total number of physical (compressed) bytes used for storage, including active, long term, and time travel (deleted or changed data) bytes
ACTIVE_PHYSICAL_BYTES INT64 Number of physical (compressed) bytes less than 90 days old, including time travel (deleted or changed data) bytes
LONG_TERM_PHYSICAL_BYTES INT64 Number of physical (compressed) bytes more than 90 days old
TIME_TRAVEL_PHYSICAL_BYTES INT64 Number of physical (compressed) bytes used by time travel storage (deleted or changed data)

Scope and syntax

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

View name Resource scope Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE[_BY_PROJECT] Project level REGION
Replace the following:

  • Optional: PROJECT_ID: the ID of your Cloud project. If not specified, the default project is used.
  • REGION: any dataset region name. For example, region-us.
SELECT
  CONCAT(v1.table_catalog, ":", v1.table_schema, ".", v1.table_name) AS unmodified_table_name,
FROM
  `region-REGION`.INFORMATION_SCHEMA.TABLES v1
LEFT JOIN `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE v2 ON v1.table_name = v2.table_name
WHERE v2.table_name IS NULL;

The following example show how to return storage information for tables in a specified project:

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

The following example show how to return storage information for tables 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 the current project.

SELECT
  SUM(total_logical_bytes) AS total_logical_bytes
FROM
  `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE;

The result is similar to the following:

+---------------------+
| total_logical_bytes |
+---------------------+
| 971329178274633     |
+---------------------+
Example 2:

The following example shows you how to calculate the price difference per dataset. This example assumes that usage was constant over the last month from the moment the query was run. Your actual bill might vary somewhat from the calculations returned by this query, because storage usage by clones and snapshots is billed, but is currently not included in the *_BYTES columns of the table storage views.

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

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

    Go to the BigQuery page

  2. Enter the following Google Standard SQL query in the Query editor box. INFORMATION_SCHEMA requires Google Standard SQL syntax. Google Standard SQL is the default syntax in the Google Cloud console.

      DECLARE active_logical_gb_price FLOAT64 DEFAULT 0.02;
      DECLARE long_term_logical_gb_price FLOAT64 DEFAULT 0.01;
      DECLARE active_physical_gb_price FLOAT64 DEFAULT 0.04;
      DECLARE long_term_physical_gb_price FLOAT64 DEFAULT 0.02; 
    WITH storage_sizes AS ( SELECT table_schema AS dataset_name, SUM(active_logical_bytes) / power(1024, 3) AS active_logical_gb, SUM(long_term_logical_bytes) / power(1024, 3) AS long_term_logical_gb, SUM(active_physical_bytes) / power(1024, 3) AS active_physical_gb, SUM(long_term_physical_bytes) / power(1024, 3) AS long_term_physical_gb, SUM(total_physical_bytes) / power(1024, 3) AS total_physical_gb, SUM(total_logical_bytes) / power(1024, 3) AS total_logical_gb FROM region-REGION.INFORMATION_SCHEMA.TABLE_STORAGE_BY_PROJECT WHERE total_logical_bytes > 0 AND total_physical_bytes > 0 GROUP BY 1 ) SELECT dataset_name, active_logical_gb, long_term_logical_gb, active_physical_gb, long_term_physical_gb, total_logical_gb / total_physical_gb AS compression_ratio, active_logical_gb * active_logical_gb_price AS active_logical_cost, long_term_logical_gb * long_term_logical_gb_price AS long_term_logical_cost, active_physical_gb * active_physical_gb_price AS active_physical_cost, long_term_physical_gb * long_term_physical_gb_price AS long_term_physical_cost, ((active_logical_gb * active_logical_gb_price) + (long_term_logical_gb * long_term_logical_gb_price)) - ((active_physical_gb * active_physical_gb_price) + (long_term_physical_gb * long_term_physical_gb_price)) AS total_cost_difference FROM storage_sizes ORDER BY compression_ratio DESC;

  3. Click Run.

The result is similar to following:

+--------------+-------------------+----------------------+-----------------------+-----------------------+--------------------+---------------------+------------------------+-----------------------+-------------------------+-----------------------+
| dataset_name | active_logical_gb | long_term_logical_gb | active_physical_gb    | long_term_physical_gb | compression_ratio  | active_logical_cost | long_term_logical_cost | active_physical_cost  | long_term_physical_cost | total_cost_difference |
+--------------+-------------------+----------------------+-----------------------+-----------------------+--------------------+---------------------+-------------------- ---+-----------------------+------------- -----------+-----------------------+
| dataset1     |  1.25             |                  0.0 | 4.79724258184433E-6   |                   0.0 | 260566.35216462822 |               0.025 |                    0.0 | 1.9188970327377319E-7 |                     0.0 |   0.02499980811029673 |
| dataset2     |  1.25             |                  0.0 | 6.5052881836891174E-6 |                   0.0 | 192151.36435218324 |               0.025 |                    0.0 | 2.6021152734756473E-7 |                     0.0 |  0.024999739788472655 |
| dataset3     |  1.25             |                  0.0 | 6.5052881836891174E-6 |                   0.0 | 192151.36435218324 |               0.025 |                    0.0 | 2.6021152734756473E-7 |                     0.0 |  0.024999739788472655 |