TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view

The INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view provides daily totals of storage usage for the past 90 days for the following types of tables:

  • Standard tables
  • Materialized views
  • Table clones that have a delta in bytes from the base table
  • Table snapshots that have a delta in bytes from the base table

Tables that don't have billable bytes aren't included in the INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view. This includes the following types of tables:

  • External tables
  • Anonymous tables
  • Empty tables
  • Table clones that have no delta in bytes from the base table
  • Table snapshots that have no delta in bytes from the base table

When you query the INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view, the query results contain one row per day for each table or materialized view for the whole organization associated with the current project.

The data in this table is not available in real time. It takes approximately 72 hours for table data to be reflected in this view.

Storage usage is returned in MB second. For example, if a project uses 1,000,000 physical bytes for 86,400 seconds (24 hours), the total physical usage is 86,400,000,000 byte seconds, which is converted to 82,397 MB seconds, as shown in the following example:

86,400,000,000 / 1,024 / 1,024 = 82,397

This is the value that would be returned by the BILLABLE_TOTAL_PHYSICAL_USAGE column.

For more information, see Storage pricing details.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view, you need the following Identity and Access Management (IAM) permissions for your organization:

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

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

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

This schema view is only available to users with defined Google Cloud organizations.

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

Schema

The INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view has the following schema:

USAGE_DATE DATE The billing date for the bytes shown, using the America/Los_Angeles time zone
PROJECT_ID STRING The project ID of the project that contains the dataset
TABLE_CATALOG 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
BILLABLE_TOTAL_LOGICAL_USAGE INT64

The total logical usage, in MB second.

Returns 0 if the dataset uses the physical storage billing model.

BILLABLE_ACTIVE_LOGICAL_USAGE INT64

The logical usage that is less than 90 days old, in MB second.

Returns 0 if the dataset uses the physical storage billing model.

BILLABLE_LONG_TERM_LOGICAL_USAGE INT64

The logical usage that is more than 90 days old, in MB second.

Returns 0 if the dataset uses the physical storage billing model.

BILLABLE_TOTAL_PHYSICAL_USAGE INT64

The total usage in MB second. This includes physical bytes used for fail-safe and time travel storage.

Returns 0 if the dataset uses the logical storage billing model.

BILLABLE_ACTIVE_PHYSICAL_USAGE INT64

The physical usage that is less than 90 days old, in MB second. This includes physical bytes used for fail-safe and time travel storage.

Returns 0 if the dataset uses the logical storage billing model.

BILLABLE_LONG_TERM_PHYSICAL_USAGE INT64

The physical usage that is more than 90 days old, in MB second.

Returns 0 if the dataset uses the logical storage billing model.

Scope and syntax

Queries against this view must include a region qualifier. If you don't specify a regional qualifier, metadata is retrieved from all regions. The following table explains the region scope for this view:

View name Resource scope Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION Organization that contains the specified project REGION
Replace the following:

  • Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.

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

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

The following example shows how to return storage information by project for tables in an organization:

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

Example

The following example shows the usage for all tables in the organization for the most recent usage date.

SELECT
  usage_date,
  project_id,
  table_schema,
  table_name,
  billable_total_logical_usage,
  billable_total_physical_usage
FROM
  (
    SELECT
      *,
      ROW_NUMBER()
        OVER (PARTITION BY project_id, table_schema, table_name ORDER BY usage_date DESC) AS rank
    FROM
      `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION
  )
WHERE rank = 1;

The result is similar to the following:

+--------------+------------+--------------+------------+------------------------------+-------------------------------+
| usage_date   | project_id | table_schema | table_name | billable_total_logical_usage | billable_total_physical_usage |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project1   | dataset_A    | table_x    | 734893409201                 |           0                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project1   | dataset_A    | table_z    | 110070445455                 |           0                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project1   | dataset_B    | table_y    |            0                 | 52500873256                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project1   | dataset_B    | table_t    |            0                 | 32513713981                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project2   | dataset_C    | table_m    |   8894535352                 |           0                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+
|  2023-04-03  | project2   | dataset_C    | table_n    |   4183337201                 |           0                   |
+--------------+------------+--------------+------------+------------------------------+-------------------------------+