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.

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.

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';

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