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