MATERIALIZED_VIEWS view

TheINFORMATION_SCHEMA.MATERIALIZED_VIEWS view contains status about materialized views.

Required permissions

To get the permissions that you need to query theINFORMATION_SCHEMA.MATERIALIZED_VIEWS view, ask your administrator to grant you theBigQuery Metadata Viewer (roles/bigquery.metadataViewer) IAM role on your project or dataset. For more information about granting roles, seeManage access to projects, folders, and organizations.

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

You might also be able to get these permissions withcustom roles or otherpredefined roles.

Schema

When you query theINFORMATION_SCHEMA.MATERIALIZED_VIEWS view, the query results containone row for each materialized view in a dataset.

TheINFORMATION_SCHEMA.MATERIALIZED_VIEWS view has the following 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 a dataset or a region qualifier. Forqueries 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 moreinformation, seeSyntax.The following table explains the region and resource scopes 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`.
• DATASET_ID: the ID of your dataset. For more information, seeDataset qualifier.

  Note: You must usea region qualifier to queryINFORMATION_SCHEMA views. The location of the query execution must match the region of theINFORMATION_SCHEMA view.

For example:

-- Returns metadata for views in a single dataset.SELECT*FROMmyDataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;-- Returns metadata for all views in a region.SELECT*FROMregion-us.INFORMATION_SCHEMA.MATERIALIZED_VIEWS;

Examples

Example 1:

The following example retrieves all the unhealthy materialized views from theINFORMATION_SCHEMA.MATERIALIZED_VIEWS view. It returns the materialized viewswith nonNULLlast_refresh_status values inmydataset in your defaultproject —myproject.

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

SELECTtable_name,last_refresh_statusFROMmydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWSWHERElast_refresh_statusISNOTNULL;

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

SELECTtable_name,last_refresh_time,refresh_watermarkFROMmydataset.INFORMATION_SCHEMA.MATERIALIZED_VIEWSWHEREtable_name='myview';

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