Manage materialized views

This document describes how to manage materialized views inBigQuery.

BigQuery management of materialized views includes the followingoperations:

For more information about materialized views, 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.

Alter materialized views

You can alter a materialized view through the Google Cloud console or thebq command-line tool, by using data definition language (DDL) withALTER MATERIALIZEDVIEW andSET OPTIONS. Fora list of materialized view options, seematerialized_view_set_options_list.

The following shows an example that setsenable_refresh totrue. Adjust asneeded for your use case.

Required permissions

To alter materialized views, you need thebigquery.tables.get andbigquery.tables.update IAM permissions.

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

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

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

SQL

To alter a materialized view, use theALTER MATERIALIZED VIEW SET OPTIONS DDL statement:

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

    Go to BigQuery

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

    ALTERMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEWSETOPTIONS(enable_refresh=true);

    Replace the following:

    • PROJECT: the name of the project that contains the materialized view
    • DATASET: the name of the dataset that contains the materialized view
    • MATERIALIZED_VIEW: the name of the materialized view you want to alter

  3. ClickRun.

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

bq

Run thebq update command:

bqupdate\--enable_refresh=true\--refresh_interval_ms=\PROJECT.DATASET.MATERIALIZED_VIEW

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.MaterializedViewDefinition;importcom.google.cloud.bigquery.Table;importcom.google.cloud.bigquery.TableId;// Sample to update materialized viewpublicclassUpdateMaterializedView{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringmaterializedViewName="MY_MATERIALIZED_VIEW_NAME";updateMaterializedView(datasetName,materializedViewName);}publicstaticvoidupdateMaterializedView(StringdatasetName,StringmaterializedViewName){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,materializedViewName);// Get existing materialized viewTabletable=bigquery.getTable(tableId);MaterializedViewDefinitionmaterializedViewDefinition=table.getDefinition();// Update materialized viewmaterializedViewDefinition.toBuilder().setEnableRefresh(true).setRefreshIntervalMs(1000L).build();table.toBuilder().setDefinition(materializedViewDefinition).build().update();System.out.println("Materialized view updated successfully");}catch(BigQueryExceptione){System.out.println("Materialized view was not updated. \n"+e.toString());}}}

List materialized views

You can list materialized views through the Google Cloud console, thebq command-line tool, or the BigQuery API.

Required permissions

To list materialized views in a dataset, you need thebigquery.tables.listIAM permission.

Each of the following predefined IAM roles includes thepermissions that you need in order to list materialized views in a 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.

The process to list materialized views is identical to the process for listingtables. To list the materialized views in a dataset:

Console

  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 inthe dataset. Tables, views, and materialized views are identified bydifferent values in theType column. Materialized view replicas havethe same value as materialized views.

bq

Issue thebq ls command. The--format flag can be used to control theoutput. If you are listing materialized views in a project other than yourdefault project, add the project ID to the dataset in the following format:project_id:dataset.

bqls--format=prettyproject_id:dataset

Where:

  • project_id is your project ID.
  • dataset is the name of the dataset.

When you run the command, theType field displays the table type.For example:

+-------------------------+--------------------+----------------------+-------------------+|         tableId         | Type               |        Labels        | Time Partitioning |+-------------------------+--------------------+----------------------+-------------------+| mytable                 | TABLE              | department:shipping  |                   || mymatview               | MATERIALIZED_VIEW  |                      |                   |+-------------------------+--------------------+----------------------+-------------------+

Examples:

Enter the following command to list materialized views in datasetmydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to list materialized views in datasetmydataset inmyotherproject.

bq ls --format=pretty myotherproject:mydataset

API

To list materialized views using the API, call thetables.list method.

Go

Before trying this sample, follow theGo setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryGo API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

import("context""fmt""io""cloud.google.com/go/bigquery""google.golang.org/api/iterator")// listTables demonstrates iterating through the collection of tables in a given dataset.funclistTables(wio.Writer,projectID,datasetIDstring)error{// projectID := "my-project-id"// datasetID := "mydataset"// tableID := "mytable"ctx:=context.Background()client,err:=bigquery.NewClient(ctx,projectID)iferr!=nil{returnfmt.Errorf("bigquery.NewClient: %v",err)}deferclient.Close()ts:=client.Dataset(datasetID).Tables(ctx)for{t,err:=ts.Next()iferr==iterator.Done{break}iferr!=nil{returnerr}fmt.Fprintf(w,"Table: %q\n",t.TableID)}returnnil}

Python

Before trying this sample, follow thePython setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryPython API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

fromgoogle.cloudimportbigquery# Construct a BigQuery client object.client=bigquery.Client()# TODO(developer): Set dataset_id to the ID of the dataset that contains#                  the tables you are listing.# dataset_id = 'your-project.your_dataset'tables=client.list_tables(dataset_id)# Make an API request.print("Tables contained in '{}':".format(dataset_id))fortableintables:print("{}.{}.{}".format(table.project,table.dataset_id,table.table_id))

Get information about materialized views

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

Required permissions

To query information about a materialized view, 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, including any dependantmaterialized view replicas:

SQL

To get information about materialized views, 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 views
    • DATASET_ID: the name of the dataset thatcontains the materialized views

  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

Replace the following:

  • project_id: the project ID. You only need to include thisflag to get information about a materialized view in a different projectthan the default project.
  • dataset: the name of the dataset that contains thematerialized view.
  • materialized_view: the name of the materialized view thatyou want information about.

Example:

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

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

API

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

Delete materialized views

You can delete a materialized view through the Google Cloud console, thebq command-line tool, or the API.

Caution: Deleting a materialized view cannot be undone.

Deleting a materialized view also deletes any permissions associated with thismaterialized view. When you recreate a deleted materialized view, you must alsomanuallyreconfigure any access permissionspreviously associated with it.

Required permissions

To delete materialized views, 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:

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

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

SQL

To delete a materialized view, use theDROP MATERIALIZED VIEW DDL statement:

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

    Go to BigQuery

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

    DROPMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEW;

    Replace the following:

    • PROJECT: the name of the project that contains the materialized view
    • DATASET: the name of the dataset that contains the materialized view
    • MATERIALIZED_VIEW: the name of the materialized view you want to delete

  3. ClickRun.

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

bq

Use thebq rm commandto delete the materialized view.

API

Call thetables.deletemethod and specify values for theprojectId,datasetId, andtableIdparameters:

  • Assign theprojectId parameter to your project ID.
  • Assign thedatasetId parameter to your dataset ID.
  • Assign thetableId parameter to the table ID of the materialized viewthat you're deleting.

Java

Before trying this sample, follow theJava setup instructions in theBigQuery quickstart using client libraries. For more information, see theBigQueryJava API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, seeSet up authentication for client libraries.

importcom.google.cloud.bigquery.BigQuery;importcom.google.cloud.bigquery.BigQueryException;importcom.google.cloud.bigquery.BigQueryOptions;importcom.google.cloud.bigquery.TableId;// Sample to delete materialized viewpublicclassDeleteMaterializedView{publicstaticvoidmain(String[]args){// TODO(developer): Replace these variables before running the sample.StringdatasetName="MY_DATASET_NAME";StringmaterializedViewName="MY_MATERIALIZED_VIEW_NAME";deleteMaterializedView(datasetName,materializedViewName);}publicstaticvoiddeleteMaterializedView(StringdatasetName,StringmaterializedViewName){try{// Initialize client that will be used to send requests. This client only needs to be created// once, and can be reused for multiple requests.BigQuerybigquery=BigQueryOptions.getDefaultInstance().getService();TableIdtableId=TableId.of(datasetName,materializedViewName);booleansuccess=bigquery.delete(tableId);if(success){System.out.println("Materialized view deleted successfully");}else{System.out.println("Materialized view was not found");}}catch(BigQueryExceptione){System.out.println("Materialized view was not found. \n"+e.toString());}}}
Caution: If you delete a materialized view's base table without first deletingthe materialized view, then any refresh or query of the materialized view willfail. If you decide to recreate the base table, then you must also recreate thematerialized view.

Refresh materialized views

Refreshing a materialized view updates the view's cached data to reflect thecurrent state of its base tables.

When you query a materialized view, BigQuery returns results fromboth cached materialized view data and data retrieved from thebase table. Where possible, BigQuery readsonly the changes since the last time the view was refreshed. While recentlystreamed data might not be included during a refresh of the materialized view,queries always read streamed data regardless of whether a materialized view isused.

Returning query results directly from the base table incurs highercompute cost than returning results from cached materialized view data.Regularly refreshing materialized view cached data reduces the amountof data returned directly from the base table, which reduces the compute cost.

This section describes how to do the following:

Note: If you delete a base table without first deleting the materializedview, refreshes of the materialized view will fail. To recreate a basetable, you must also recreate the materialized view.

Automatic refresh

You can enable or disable automatic refresh at any time. The automatic refreshjob is performed by thebigquery-adminbot@system.gserviceaccount.com serviceaccount and appears in the materialized view project's job history.

By default, cached data in a materialized view is automatically refreshedfrom the base table within 5 to 30 minutes of a change to the base table,for example, row insertions or row deletions.

You can set therefresh frequency cap to manage the frequencyof automatic refreshes of cached data, and thus manage the costs andquery performance of materialized views.

Enable and disable automatic refresh

To turn automatic refresh off when you create a materialized view,setenable_refresh tofalse.

CREATEMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEWPARTITIONBYRANGE_BUCKET(column_name,buckets)OPTIONS(enable_refresh=false)ASSELECT...

For an existing materialized view, you can modify theenable_refresh valueusingALTER MATERIALIZED VIEW.

ALTERMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEWSETOPTIONS(enable_refresh=true);
Note: Enabling automatic refresh immediately triggers an automatic refresh ofthe materialized view.

Set the frequency cap

You can configure a frequency cap on how often automatic refresh is run. Bydefault, materialized views are refreshed no more often than every 30 minutes.

The refresh frequency cap can be changed at any time.

To set a refresh frequency cap when you create a materialized view, setrefresh_interval_minutes in DDL (orrefresh_interval_ms in the API andbq command-line tool), to the value you want.

CREATEMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEWOPTIONS(enable_refresh=true,refresh_interval_minutes=60)ASSELECT...

Similarly, you can set the frequency cap when you modify a materialized view.This example assumes you have already enabled automatic refresh, and just wantto change the frequency cap:

ALTERMATERIALIZEDVIEWPROJECT.DATASET.MATERIALIZED_VIEWSETOPTIONS(refresh_interval_minutes=60);

The minimum refresh frequency cap is 1 minute. The maximum refresh frequency capis 7 days.

You can perform a manual refresh of a materialized view at any time, and itstiming is not subject to the frequency cap.

Best-effort

Automatic refresh is performed on a best-effort basis.BigQuery attempts to start a refresh within 5 minutes of a change inthe basetable (if the previous refresh was done earlier than 30 minutes ago), butit doesn't guarantee that the refresh will be started at that time, nor does itguarantee when it will complete.

Note: Querying materialized views reflectsthe latest state of the base tables, but if the view wasn't refreshed recently,the query cost or latency can be higher than expected.

Automatic refresh is treated similarly to a query withbatchpriority. If the materialized view's project does not have the capacity at themoment, the refresh is delayed. If the project contains many views whose refreshturns out to be expensive, each individual view might lag significantly relativeto its base tables.

Manual refresh

You can manually refresh a materialized view at any time.

Required permissions

To manually refresh materialized views, you need thebigquery.tables.getData,bigquery.tables.update, andbigquery.tables.updateData IAMpermissions.

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

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

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

To update the data in the materialized view, call theBQ.REFRESH_MATERIALIZED_VIEWsystem procedure. When this procedure is called,BigQuery identifies the changes that have taken place in the basetables and applies those changes to the materialized view. The query to runBQ.REFRESH_MATERIALIZED_VIEW finishes when the refresh is complete.

CALLBQ.REFRESH_MATERIALIZED_VIEW('PROJECT.DATASET.MATERIALIZED_VIEW');
Caution: Don't perform more than one refresh at a time. If you run multiplerefreshes concurrently for the same materialized view, thenonly the first refresh to complete is successful.

Monitor materialized views

You can get information about materialized views and materialized viewsrefresh jobs by using the BigQuery API. For more information, seeMonitor materialized views.

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 2025-12-15 UTC.