Create and manage logical views

In Bigtable, alogical view is the result of a SQL query thatfunctions as a virtual table that can be queried by other SQL queries. Dataremains in the source table.

This document describes logical views and how to create and perform operationson them. For a comparison with continuous materialized views and authorized views, seeTables and views.

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

• Avoid query repetition: You can store and evolve column mapping and castlogic in a logical view, which additional logical views can use instead ofcopying 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 arelational database table or Cassandra table.
• Run the same logical view against many tables: By modifying theFROMclause, you can use the same view to query any table in yourBigtable instance.
• Provide a consistent interface: Your application can use a logical vieweven if the source table changes.
• Limit read access to specific columns or rows: You can expose only thedata that you want to allow users to read, without giving them access to theentire table.

A logical view is an instance-level resource. You can create a logical viewusing the Google Cloud CLI or the Bigtable Studio query editor inthe Google Cloud console. To query a logical view, you can use theBigtable Studio query editor or any of the Bigtableclient libraries that supportSQL.

A logical view has the following characteristics:

• Must use SQL features supported byGoogleSQL forBigtable
• Read-only
• Must be read with SQL rather than with a call toReadRows
• Can be queried using any of the Bigtableclient libraries that supportSQL
• Usesdefiner rights. You can query a logical view if you have permissionsto read from it even if you don't have permission to read from the sourcetable.

The row key, column qualifier, and column values that you use to define alogical view are treated as service data. For this reason, don'tcreate a logical view using row key, column qualifier, orcolumn values that containsensitiveinformation. For information about howservice data is handled, see theGoogle Cloud PrivacyNotice.

Before you begin

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

1. Install the Google Cloud CLI.

   Note: If you installed the gcloud CLI previously, make sure you have the latest version by runninggcloud components update.

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

3. Toinitialize the gcloud CLI, run the following command:

   gcloudinit

   Note: You can run the gcloud CLI in the Google Cloud console without installing the Google Cloud CLI. To run the gcloud CLI in the Google Cloud console,use Cloud Shell.

Required roles

To work with logical views, you need the following permissions:

To manage logical views:

• To create, update, or delete logical views, you need theBigtable Admin (roles/bigtable.admin) role on the instance.
• Alternatively, you can ask your administrator to grant you the followingpermissions at the instance level:
  • Create:bigtable.logicalViews.create
  • Update:bigtable.logicalViews.update
  • Delete:bigtable.logicalViews.delete

To view and query logical views:

• To list or describe logical views, you need the BigtableReader (roles/bigtable.reader) role on the instance.

• Alternatively, you can ask your administrator to grant you the followingpermissions at the instance level:

  • List:bigtable.logicalViews.list
  • Describe:bigtable.logicalViews.get

• To query a logical view, you need thebigtable.logicalViews.readRowspermission on the logical view.

  Note: You don't need permission to read the source table.

To create a logical view:

• You must have at leastbigtable.tables.readRows permission on the sourcetable.

To grant read access to a specific view:

You can grant a user permission to read a specific logical view, even if theydon't have any other Bigtable permissions. To do this, use anIAM condition to grant thebigtable.logicalViews.readRowspermission only on that view:

1. Grant the user thebigtable.reader role on the project.

2. Create an IAM condition that limits thebigtable.logicalViews.readRows permission to the specific logical view.For example:

   resource.name == projects/PROJECT_ID/instances/INSTANCE_ID/logicalViews/VIEW

   Replace the following:

   • PROJECT_ID: the Google Cloud project thatyour Bigtable instance is in.
   • VIEW: an ID up to 128 characters long for the newlogical view. The ID must be unique among table IDs and view IDs in the instance.
   • INSTANCE_ID: the ID of instance to create the logical view in.

   For more information, seeManage access to resources using IAM conditions.

For more information about all available roles and permissions, seeBigtable access control with IAM.

Create a logical view

gcloudbigtablelogical-viewscreateVIEW\--instance=INSTANCE--query=QUERY

Update a logical view

gcloudbigtablelogical-viewsupdateVIEW\--instance=INSTANCE--query=QUERY

gcloudbigtablelogical-viewsupdateVIEW\--instance=INSTANCE\--deletion-protection=true

gcloudbigtablelogical-viewsupdateVIEW\--instance=INSTANCE\--deletion-protection=false

Delete a logical view

This action is permanent.

If a logical view has deletion protection enabled, you can't delete it. Todelete a logical view that has deletion protection enabled, you must update thelogical view to disable deletion protection. For more information, see theUpdate a logical view section of this document.

gcloudbigtablelogical-viewsdeleteVIEW\--instance=INSTANCE

Get a list of logical views for an instance

You can see a list of logical views for an instance.

gcloudbigtablelogical-viewslist--instance=INSTANCE

Describe a logical view

To get details about a logical view, use thegcloud bigtable logical-viewsdescribe command.

gcloudbigtablelogical-viewsdescribeVIEW\--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-viewquery:SELECTstreetFROMaddressesupdateTime:'2025-03-07T19:49:56.316578Z'deletion_protection:true

Query a logical view

After you create a logical view, you can query it using SQL, much like you querya regular table. You can use logical views in theFROM clause ofSELECTstatements.

In the query editor, enter a GoogleSQL query that references the viewname. For example, if your view is namedMyLogicalView:

SELECT*FROMMyLogicalViewLIMIT100;

For more information about executing SQL queries in Bigtable, seeQuery your data with SQL in the query editor.

What's next

• GoogleSQL for Bigtable overview
• Continuous materialized views
• Overview of authorized views