TABLE_STORAGE_TIMELINE_BY_ORGANIZATION view
The INFORMATION_SCHEMA.TABLE_STORAGE_TIMELINE_BY_ORGANIZATION
view contains
information for all tables in all projects in the current organization.
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:
- 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 |
- 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 | +----------------+------------------------+