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 permissionssection:

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