Create and manage continuous materialized views

This document describes how to create and perform operations on a continuous materialized view in Bigtable. Before you read this document, read the overview at Continuous materialized views.

You can create a continuous materialized view using the Google Cloud CLI, the Bigtable Studio query editor in the Google Cloud console, or the Bigtable client libraries for Java and Go.

Before you begin

Take the following steps if you plan to use the gcloud CLI.

  1. Install the Google Cloud CLI.

  2. If you're using an external identity provider (IdP), you must first sign in to the gcloud CLI with your federated identity.

  3. To initialize the gcloud CLI, run the following command: 

    gcloud init

Required roles

To get the permissions that you need to create and manage continuous materialized views, ask your administrator to grant you the Bigtable Admin (roles/bigtable.admin) role on the instance. Otherwise, ask for the following permissions at the instance level, depending on the operation that you want to perform:

  • Create: bigtable.materializedViews.create
  • Update: bigtable.materializedViews.update
  • Delete: bigtable.materializedViews.delete
  • List: bigtable.materializedViews.list

To create a continuous materialized view, you must also have at least bigtable.tables.readRows permission on the source table.

To see all available roles and permissions, see Bigtable access control with IAM.

Create a continuous materialized view

Before you create a continuous materialized view, do the following:

  • Optional: As a best practice, enable autoscaling for clusters in your instance to make sure they can scale up if necessary while Bigtable processes and synchronizes the continuous materialized view and to handle any additional storage usage.
  • Identify a Bigtable table that you have permission to read to use as the source for the continuous materialized view.
  • Prepare the SQL query that defines your continuous materialized view. For query requirements, see Continuous materialized view queries.

After you create a continuous materialized view, it might take a few minutes to become active and queryable. You can look at metrics for the view to monitor its progress.

Console

  1. Open the list of Bigtable instances in the Google Cloud console.

    Open the instance list

  2. Select an instance.

  3. In the navigation pane, click Bigtable Studio.

  4. Open a new tab by clicking and then choosing Editor.

    1. When the query editor appears, write your query. If the query is valid SQL, a Valid message is displayed.

    2. Optional: To format your statement in SQL style, click Format.

    3. Click Run. The results of your query appear in the Results table.

    4. When you're satisfied with the query, click Save and then choose Materialized view.

    For more information about using the query editor, see Manage your data using Bigtable Studio.

gcloud

To create a continuous materialized view, use the gcloud beta bigtable materialized-views create command:

    gcloud beta bigtable materialized-views create VIEW \
            --instance=INSTANCE --query=QUERY

Replace the following:

  • VIEW: a unique identifier for the continuous materialized view. You use this ID as the table ID when querying the continuous materialized view.
  • INSTANCE: the identifier of the instance to create the continuous materialized view in. Must be the same instance as the source table.
  • QUERY: a valid SQL statement that defines the continuous materialized view.

Optional: to enable deletion protection for the continuous materialized view, append the command with the following:

--deletion-protection=DELETION_PROTECTION

Replace DELETION_PROTECTION with either true or false.

Update a continuous materialized view

You can update deletion protection for a continuous materialized view. You can't update the SQL query that defines a continuous materialized view; if you need to modify the query, you must delete the continuous materialized view and then create a new one that includes your changes.

Console

You can't update a continuous materialized view in the console. Use the gcloud CLI instead.

gcloud

To enable or disable deletion protection for a continuous materialized view, use the gcloud beta bigtable materialized-views update command.

gcloud beta bigtable materialized-views update VIEW \
        -–instance=INSTANCE --deletion-protection=DELETION_PROTECTION

Replace the following:

  • VIEW: the unique identifier for the continuous materialized view that you want to update
  • INSTANCE: the identifier of the instance containing the continuous materialized view
  • DELETION_PROTECTION: either true or false

Delete a continuous materialized view

You can delete a continuous materialized view that doesn't have deletion protection enabled. You can't undo this operation.

To delete a continuous materialized view, use the gcloud beta bigtable materialized-views delete command:

gcloud beta bigtable materialized-views delete VIEW \
        --instance=INSTANCE

Replace the following:

  • VIEW: the unique identifier for the continuous materialized view that you want to delete
  • INSTANCE: the identifier of the instance containing the continuous materialized view

Get a list of continuous materialized views

You can see a list of continuous materialized views for an instance.

Console

  1. Open the list of Bigtable instances in the Google Cloud console.

    Open the instance list

  2. Select an instance from the list.

  3. In the navigation pane, click Bigtable Studio. A list of continuous materialized views is displayed.

  4. Optional: The first 10 views in the instance are listed. To see 10 more, click Show more.

gcloud

To list all continuous materialized views for an instance, use the gcloud bigtable materialized-views list command.

gcloud bigtable materialized-views list --instance=INSTANCE

Replace INSTANCE with the instance ID.

What's next