Manage materialized views

This document describes how to manage materialized views in BigQuery.

BigQuery management of materialized views includes the following operations:

For more information about materialized views, see the following:

Before you begin

Grant Identity and Access Management (IAM) roles that give users the necessary permissions to perform each task in this document. The permissions required to perform a task (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 the bq command-line tool, by using data definition language (DDL) with ALTER MATERIALIZED VIEW and SET OPTIONS. For a list of materialized view options, see materialized_view_set_options_list.

The following shows an example that sets enable_refresh to true. Adjust as needed for your use case.

Required permissions

To alter materialized views, you need the bigquery.tables.get and bigquery.tables.update IAM permissions.

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

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

For more information about BigQuery Identity and Access Management (IAM), see Predefined roles and permissions.

SQL

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

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

    Go to BigQuery

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

    ALTER MATERIALIZED VIEW PROJECT.DATASET.MATERIALIZED_VIEW
SET OPTIONS (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. Click Run.

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

bq

Run the bq update command:

bq update \
--enable_refresh=true \
--refresh_interval_ms= \
PROJECT.DATASET.MATERIALIZED_VIEW

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

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

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.MaterializedViewDefinition;
import com.google.cloud.bigquery.Table;
import com.google.cloud.bigquery.TableId;

// Sample to update materialized view
public class UpdateMaterializedView {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String materializedViewName = "MY_MATERIALIZED_VIEW_NAME";
    updateMaterializedView(datasetName, materializedViewName);
  }

  public static void updateMaterializedView(String datasetName, String materializedViewName) {
    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.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      TableId tableId = TableId.of(datasetName, materializedViewName);

      // Get existing materialized view
      Table table = bigquery.getTable(tableId);
      MaterializedViewDefinition materializedViewDefinition = table.getDefinition();
      // Update materialized view
      materializedViewDefinition
          .toBuilder()
          .setEnableRefresh(true)
          .setRefreshIntervalMs(1000L)
          .build();
      table.toBuilder().setDefinition(materializedViewDefinition).build().update();
      System.out.println("Materialized view updated successfully");
    } catch (BigQueryException e) {
      System.out.println("Materialized view was not updated. \n" + e.toString());
    }
  }
}

List materialized views

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

Required permissions

To list materialized views in a dataset, you need the bigquery.tables.list IAM permission.

Each of the following predefined IAM roles includes the permissions 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 in IAM, see Predefined roles and permissions.

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

Console

  1. In the left pane, click Explorer:

    Highlighted button for the Explorer pane.

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

  2. In the Explorer pane, expand your project, click Datasets, and then click the dataset.

  3. Click Overview > Tables. Scroll through the list to see the tables in the dataset. Tables, views, and materialized views are identified by different values in the Type column. Materialized view replicas have the same value as materialized views.

bq

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

bq ls --format=pretty project_id:dataset

Where:

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

When you run the command, the Type 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 dataset mydataset in your default project.

bq ls --format=pretty mydataset

Enter the following command to list materialized views in dataset mydataset in myotherproject.

bq ls --format=pretty myotherproject:mydataset

API

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

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set 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.
func listTables(w io.Writer, projectID, datasetID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	ts := client.Dataset(datasetID).Tables(ctx)
	for {
		t, err := ts.Next()
		if err == iterator.Done {
			break
		}
		if err != nil {
			return err
		}
		fmt.Fprintf(w, "Table: %q\n", t.TableID)
	}
	return nil
}

Python

Before trying this sample, follow the Python setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Python API reference documentation.

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


from google.cloud import bigquery

# 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))