TABLE_STORAGE_TIMELINE_BY_ORGANIZATION view

Preview

This product or feature is covered by the Pre-GA Offerings Terms of the Google Cloud Terms of Service. Pre-GA products and features might have limited support, and changes to pre-GA products and features might not be compatible with other pre-GA versions. For more information, see the launch stage descriptions.

The INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION view contains information for all tables in the parent folder of the current or specified project, including the projects in subfolders under it.

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 maintenance bytes. This information can help you with tasks like planning for future growth and understanding the update patterns for tables.

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:

• Permanent tables created through any of the methods described in Create and use tables.
• Temporary tables generated in sessions and multi-statement queries ("scripts").

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.

Required permissions

To query the INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION view, you need the following Identity and Access Management (IAM) permissions at the organization level:

• 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 more information about BigQuery permissions, see Access control with IAM.

Schema

The table storage timeline views return one row for every event that triggers a storage change for the table, like writing, updating, or deleting a row. This means there can be multiple rows for a table for a single day. When querying a view for a time range, use the most recent timestamp on the day of interest.

This view has the following schema:

Column name	Data type	Value
TIMESTAMP	TIMESTAMP	Timestamp of when storage was last recalculated. Recalculation is triggered by changes to the data in the table.
DELETED	BOOLEAN	Indicates whether or not the table is deleted
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 for the table or materialized view. Unpartitioned tables will 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 (for deleted tables) bytes
ACTIVE_PHYSICAL_BYTES	INT64	Number of physical (compressed) bytes less than 90 days old
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. If you do not 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_TIMELINE_BY_ORGANIZATION	Organization that contains the specified project	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.

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

Example

The following example shows you the sum of physical storage that's used by each project in your organization for a given point in time:

WITH most_recent_records as (
  SELECT
    project_id,
    table_schema,
    table_name,
    MAX(timestamp) as max_timestamp
  FROM
    `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
  WHERE
    timestamp <= 'TIMESTAMP'
  GROUP BY
    project_id, table_schema, table_name
  )
  SELECT
    i_s.project_id,
    SUM(i_s.total_physical_bytes) AS TotalPhysicalBytes
  FROM
    `region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION as i_s
  JOIN
    most_recent_records
  ON
    i_s.project_id = most_recent_records.project_id
    AND i_s.table_schema = most_recent_records.table_schema
    AND i_s.table_name = most_recent_records.table_name
    AND i_s.timestamp = most_recent_records.max_timestamp
  GROUP BY
    project_id;

The result is similar to the following:

-----------------+------------------------+
|  project_id    |  TotalPhysicalBytes    |
+----------------+------------------------+
| projecta       | 3844                   |
| projectb       | 16022778               |
| projectc       | 8934009                |
+----------------+------------------------+