Create and manage logical views

In Bigtable, a logical view is the result of a SQL query that functions as a virtual table that can be queried by other SQL queries. Data remains in the source table.

This document describes logical views and how to create and perform operations on them. For a comparison with materialized views and authorized views, see Tables and views.

Logical views let you do the following with your Bigtable data:

  • Avoid query repetition: You can store and evolve column mapping and cast logic in a logical view, which additional logical views can use instead of copying and pasting that logic or needing to understand it.
  • Integrate with systems designed to read from relational databases: Logical views let you make your Bigtable data read like a relational database table or Cassandra table.
  • Run the same logical view against many tables: By modifying the FROM clause, you can use the same view to query any table in your Bigtable instance.
  • Provide a consistent interface: Your application can use a logical view even if the source table changes.

A logical view is an instance-level resource. You can create a logical view using the Google Cloud CLI or the Bigtable Studio query editor in the Google Cloud console. To query a logical view, you can use the Bigtable Studio query editor or any of the Bigtable client libraries that support SQL.

A logical view has the following characteristics:

  • Must use SQL features supported by GoogleSQL for Bigtable
  • Read-only
  • Must be read with SQL rather than with a call to ReadRows
  • Uses definer rights. You can query a logical view if you have permissions to read from it even if you don't have permission to read from the source table.

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 logical 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.logicalViews.create
  • Update: bigtable.logicalViews.update
  • Delete: roles/bigtable.logicalViews.delete
  • List: bigtable.logicalViews.list
  • Describe: bigtable.logicalViews.get

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

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

Create a logical view

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. In the query editor, 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 Save view.

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

gcloud

To create a logical view, use the gcloud bigtable logical-views create command.

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

Replace the following:

  • VIEW: an ID up to 128 characters long for the new view. The ID must be unique among table and view IDs in the instance.
  • QUERY: a valid GoogleSQL for Bigtable query
  • INSTANCE: the ID of instance to create the view in

Update a logical view

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 views is displayed in the explorer.

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

  5. Next to the view that you want to update, click the more_vert action menu, and then click View definition.

  6. Modify the query.

  7. Click Run.

  8. After you've verified that the results pane shows data that should be included in the view, click Save as.

  9. In the dialog, enter the ID of the view that you modified.

    The dialog displays a message warning you that you are overwriting the existing view.

  10. Click Save.

gcloud

To update a logical view to run a different query, use the gcloud bigtable logical-views update command.

gcloud bigtable logical-views update VIEW  \
    --instance=INSTANCE --query=QUERY

Replace the following:

  • VIEW: the ID of the logical view to update
  • QUERY: a valid GoogleSQL query
  • INSTANCE: the ID of instance that contains the logical view

Delete a logical view

This action is permanent. To delete a logical view, use the gcloud bigtable logical-views delete command.

    gcloud bigtable logical-views delete VIEW \
      --instance=INSTANCE

Replace the following:

  • VIEW: the ID of the logical view to update
  • INSTANCE: the ID of instance that contains the logical view

Get a list of logical views for an instance

You can see a list of logical 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 views is displayed in the explorer.

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

gcloud

To see a list of logical views for an instance, use the gcloud bigtable logical-views list command.

gcloud bigtable logical-views list --instance=INSTANCE

Replace INSTANCE with the instance ID.

Describe a logical view

To get details about a logical view, use the gcloud bigtable logical-views describe command.

  gcloud bigtable logical-views describe VIEW \
      --instance=INSTANCE

The terminal displays details similar to the following:

  createTime: '2025-03-07T19:49:56.316578Z'
  etag: W/"/v1/17919275593532352351"
  name: projects/my-project/instances/my-instance/logicalViews/my-view
  query: SELECT street FROM addresses
  updateTime: '2025-03-07T19:49:56.316578Z'

What's next