TABLE_STORAGE_USAGE_TIMELINE_BY_FOLDER 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_FOLDER view providesdaily storage usage totals 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

TheINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_FOLDER view excludestables without billable bytes. The following table types are excluded:

• 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_FOLDERview, the query results contain one row per day for each table ormaterialized view in the current project's parent folder, including itssubfolders.

This table's data isn't available in real time. Table data takes approximately72 hours to appear in this view.

The view returns storage usage in MiB seconds. 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 converts to 82,397 MiB seconds,as shown in the following example:

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

The storage usage value can be found in theBILLABLE_TOTAL_PHYSICAL_USAGEcolumn. For more information, seeStorage pricing details.

For more information about BigQuery permissions, seeBigQuery IAM roles and permissions.

Schema

TheINFORMATION_SCHEMA.TABLE_STORAGE_USAGE_TIMELINE_BY_FOLDER 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.

To retrieve storage information for tables in the specified project's parentfolder, run the following query:

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

Example

The following query shows usage for all tables in the folder on the mostrecent 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_FOLDER)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                |+--------------+------------+--------------+------------+------------------------------+-------------------------------+