MATERIALIZED_VIEWS view

Preview

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

The INFORMATION_SCHEMA.MATERIALIZED_VIEWS view contains status about materialized views.

Required permissions

To get the permissions that you need to query the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view, ask your administrator to grant you the BigQuery Metadata Viewer (roles/bigquery.metadataViewer) IAM role on your project or dataset. For more information about granting roles, see Manage access.

This predefined role contains the permissions required to query the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view. To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to query the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view:

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

You might also be able to get these permissions with custom roles or other predefined roles.

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

Schema

When you query the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view, the query results contain one row for each materialized view in a dataset.

The INFORMATION_SCHEMA.MATERIALIZED_VIEWS view has the following schema:

Column name	Data type	Value
TABLE_CATALOG	STRING	The name of the project that contains the dataset. Also referred to as the projectId.
TABLE_SCHEMA	STRING	The name of the dataset that contains the materialized view. Also referred to as the datasetId.
TABLE_NAME	STRING	The name of the materialized view. Also referred to as the tableId.
LAST_REFRESH_TIME	TIMESTAMP	The time when this materialized view was last refreshed.
REFRESH_WATERMARK	TIMESTAMP	The refresh watermark of the materialized view. The data contained in materialized view base tables up to this time are included in the materialized view cache.
LAST_REFRESH_STATUS	RECORD	Error result of the last automatic refresh job as an ErrorProto object. If present, indicates that the last automatic refresh was unsuccessful.

Scope and syntax

Queries against this view must include a dataset or a region qualifier. For queries with a dataset qualifier, you must have permissions for the dataset. For queries with a region qualifier, you must have permissions for the project. For more information, see Syntax. The following table explains the region and resource scopes for this view:

View name	Resource scope	Region scope
[PROJECT_ID.]`region-REGION`.INFORMATION_SCHEMA.MATERIALIZED_VIEWS	Project level	REGION
[PROJECT_ID.]DATASET_ID.INFORMATION_SCHEMA.MATERIALIZED_VIEWS	Dataset level	Dataset location

Replace the following:

• Optional: PROJECT_ID: the ID of your Google Cloud project. If not specified, the default project is used.
• REGION: any dataset region name. For example, region-us.
• DATASET_ID: the ID of your dataset. For more information, see Dataset qualifier.

Note: You must specify the location to query the INFORMATION_SCHEMA views and the location of the query execution must match the region of the INFORMATION_SCHEMA view.

For example:

-- Returns metadata for views in a single dataset.
SELECT * FROM myDataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

-- Returns metadata for all views in a region.
SELECT * FROM region-us.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

Examples

Example 1:

The following example retrieves all the unhealthy materialized views from the INFORMATION_SCHEMA.MATERIALIZED_VIEWS view. It returns the materialized views with non NULL last_refresh_status values in mydataset in your default project — myproject.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `project_id`.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS; for example, `myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS.

SELECT
  table_name, last_refresh_status
FROM
  mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE
  last_refresh_status IS NOT NULL;

Note: INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

  +---------------+---------------------------------------------------------------------+
  |  table_name   |                        last_refresh_status                          |
  +---------------------------------------------------------------------+---------------+
  |  myview       |   {"reason":"invalidQuery","location":"query","message":"..."}      |
  +---------------------------------------------------------------------+---------------+
  

Example 2:

The following example retrieves the last_refresh_time and refresh_watermark of materialized view myview in mydataset in your default project — myproject. The result shows when the materialized was last refreshed and up to when data of base tables are collected into the materialized view cache.

To run the query against a project other than your default project, add the project ID to the dataset in the following format: `project_id`.dataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS; for example, `myproject`.mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS.

SELECT
  table_name, last_refresh_time, refresh_watermark
FROM
  mydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS
WHERE
  table_name = 'myview';

Note: INFORMATION_SCHEMA view names are case-sensitive.

The result is similar to the following:

  +---------------+------------------------------------------------+
  |  table_name   |  last_refresh_time     | refresh_watermark     |
  +---------------+------------------------------------------------+
  |  myview       | 2023-02-22 19:37:17    | 2023-03-08 16:52:57   |
  +---------------+------------------------------------------------+
  

Note: If there have been no recent changes to the base tables, BigQuery periodically increases the refresh_watermark to indicate that the materialized view is up-to-date without actually refreshing it. As a result, the last_refresh_time can be earlier than the refresh_watermark.