Manage materialized view replicas

This document describes how to manage materialized view replicas inBigQuery.

BigQuery management of materialized view replicas includesthe following operations:

For more information about materialized view replicas, see the following:

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissionsto perform each task in this document. The permissions required to perform atask (if any) are listed in the "Required permissions" section of the task.

List materialized view replicas

You can list materialized view replicas through the Google Cloud console.

Required permissions

To list materialized view replicas in a dataset, you need thebigquery.tables.list IAM permission.

Each of the following predefined IAM roles includes thepermissions that you need in order to list materialized view replicas ina dataset:

  • roles/bigquery.user
  • roles/bigquery.metadataViewer
  • roles/bigquery.dataViewer
  • roles/bigquery.dataOwner
  • roles/bigquery.dataEditor
  • roles/bigquery.admin

For more information on IAM roles and permissions inIAM, seePredefined roles and permissions.

To list the materialized view replicas in a dataset:

  1. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

    If you don't see the left pane, clickExpand left pane to open the pane.

  2. In theExplorer pane, expand your project, clickDatasets, andthen click the dataset.

  3. ClickOverview> Tables. Scroll through the list to see the tables in thedataset. Tables, views, and materialized views are identified by differentvalues in theType column. Materialized view replicas have the same valueas materialized views.

Get information about materialized view replicas

You can get information about a materialized view replica by using SQL, thebq command-line tool, or the BigQuery API.

Required permissions

To query information about a materialized view replica, you need the followingIdentity and Access Management (IAM) permissions:

  • bigquery.tables.get
  • bigquery.tables.list
  • bigquery.routines.get
  • bigquery.routines.list

Each of the following predefined IAM roles includes the precedingpermissions:

  • roles/bigquery.metadataViewer
  • roles/bigquery.dataViewer
  • roles/bigquery.admin

For more information about BigQuery permissions, seeAccess control with IAM.

To get information about a materialized view replica, including the sourcematerialized view:

SQL

To get information about materialized view replicas, query theINFORMATION_SCHEMA.TABLES view:

  1. In the Google Cloud console, go to theBigQuery page.

    Go to BigQuery

  2. In the query editor, enter the following statement:

    SELECT*FROMPROJECT_ID.DATASET_ID.INFORMATION_SCHEMA.TABLESWHEREtable_type='MATERIALIZED VIEW';

    Replace the following:

    • PROJECT_ID: the name of the project thatcontains the materialized view replicas
    • DATASET_ID: the name of the dataset thatcontains the materialized view replicas

  3. ClickRun.

For more information about how to run queries, seeRun an interactive query.

bq

Use thebq show command:

bqshow--project=project_id--format=prettyjsondataset.materialized_view_replica

Replace the following:

  • project_id: the project ID. You only need to include thisflag to get information about a materialized view replica in a differentproject than the default project.
  • dataset: the name of the dataset that contains thematerialized view replica.
  • materialized_view_replica: the name of the materializedview replica that you want information about.

Example:

Enter the following command to show information about the materializedview replicamy_mv_replica in thereport_views dataset in themyproject project.

bq show --project=myproject --format=prettyjson report_views.my_mv_replica

API

To get materialized view replica information by using the API, call thetables.get method.

Delete materialized view replicas

You can delete a materialized view replica through the Google Cloud console.

Caution: Deleting a materialized view replica can't be undone.

Required permissions

To delete materialized view replicas, you need thebigquery.tables.deleteIAM permission.

Each of the following predefined IAM roles includes thepermissions that you need in order to delete a materialized view replica:

  • bigquery.dataEditor
  • bigquery.dataOwner
  • bigquery.admin

For more information aboutBigQuery Identity and Access Management (IAM), seePredefined roles and permissions.

  1. In the left pane, clickExplorer:

    Highlighted button for the Explorer pane.

  2. In theExplorer pane, expand your project, clickDatasets, andthen click the dataset.

  3. ClickOverview> Tables, and then click the materialized view replica.

  4. ClickDelete.

  5. In theDelete materialized view? dialog, typedelete into the field,and then clickDelete.

Except as otherwise noted, the content of this page is licensed under theCreative Commons Attribution 4.0 License, and code samples are licensed under theApache 2.0 License. For details, see theGoogle Developers Site Policies. Java is a registered trademark of Oracle and/or its affiliates.

Last updated 2026-02-18 UTC.