MATERIALIZED_VIEWS view

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 to projects, folders, and organizations.

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 use a region qualifier to query INFORMATION_SCHEMA views. 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;

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.

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

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   |
  +---------------+------------------------------------------------+