TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view

This product or feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of theService Specific Terms. Pre-GA products and features are available "as is" and might have limited support. For more information, see thelaunch stage descriptions.

TheINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION viewprovides daily totals of storage usage for the past 90 daysfor 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 theINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view. Thisincludes 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 theINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATIONview, the query results contain one row per day for each table ormaterialized view for the whole organization associated with thecurrent project.

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

Storage usage is returned in MiB second. For example, if a project uses1,000,000 physical bytes for 86,400 seconds (24 hours), the total physicalusage is 86,400,000,000 byte seconds, which is converted to 82,397 MiB 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 theBILLABLE_TOTAL_PHYSICAL_USAGEcolumn.

For more information, seeStorage pricing details.

This schema view is only available to users with definedGoogle Cloudorganizations.

For more information about BigQuery permissions, seeAccess control with IAM.

Schema

TheINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION view has thefollowing 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.If you don't specify a regional qualifier, metadata is retrieved from allregions. 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 in aspecified project in an organization:

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

The following example shows how to return storage information by project fortables 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 forthe most recent usage date.

SELECTusage_date,project_id,table_schema,table_name,billable_total_logical_usage,billable_total_physical_usageFROM(SELECT*,ROW_NUMBER()OVER(PARTITIONBYproject_id,table_schema,table_nameORDERBYusage_dateDESC)ASrankFROM`region-REGION`.INFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_ORGANIZATION)WHERErank=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                   |+--------------+------------+--------------+------------+------------------------------+-------------------------------+