Create and manage parameterized views

You can create a parameterized view from a logical view in Bigtable and then perform operations on parameterized views.

Before you read this page, familiarize yourself with Parameterized views overview.

Before you begin

If you plan to use the Google Cloud CLI, follow these steps:

1. Install the Google Cloud CLI.

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

2. Configure the gcloud CLI to use your federated identity.

   For more information, see 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 parameterized views, ask your administrator to grant you the Bigtable Admin (roles/bigtable.admin) role on the instance.

Alternatively, you can ask for the following permissions at the instance level:

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

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

Create a parameterized view

A parameterized view is a virtual table defined by a SQL SELECT statement that can include the VIEW_PARAMETERS() function.

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

gcloud bigtable logical-views create VIEW \
  --instance=INSTANCE \
  --query="SELECT * FROM TABLE_ID WHERE STARTS_WITH(_key, CAST(VIEW_PARAMETERS('VIEW_PARAMETERS') AS BYTES))"

Replace the following:

• VIEW: an ID up to 128 characters long for the new parameterized view. The ID must be unique among table IDs and view IDs in the instance.
• INSTANCE: the ID of the instance to create the parameterized view in.
• TABLE_ID: the ID of the source table.
• VIEW_PARAMETERS: the name of the view's parameter in single quotes to pass as an argument to the VIEW_PARAMETERS() function.

Optional:

• To protect the parameterized view from deletion, append the command with the --deletion-protection flag. If you don't apply this setting, the view can be deleted. You can also explicitly allow view deletion by appending --no-deletion-protection. For more information, see the Update a parameterized view section of this document.

Create a parameterized view with a structured row key

If your table uses a structured row key, you can filter on a specific segment of the row key.

For example, if a row key in a purchase history table stores the user, timestamp of the purchase date, and order ID, delimited by a # symbol, you can specify the row schema as follows:

field {
    field_name: "user_id"
    type: { bytesType { encoding { raw {} } } }
  }
  field {
    field_name: "reversed_timestamp"
    type: { timestampType { encoding { unixMicrosInt64 { encoding: {           orderedCodeBytes: {} } } } } }
  }
  field {
    field_name: "order_id"
    type: { stringType { encoding { utf8Bytes {} } } }
  }
  encoding {
    delimitedBytes { delimiter "#" }
  }

You can then create a view that filters on the user ID field:

gcloud bigtable logical-views create VIEW \
    --instance=INSTANCE \
    --query="SELECT * FROM TABLE_ID WHERE user_id = CAST(VIEW_PARAMETERS('user_id') AS BYTES)"

Replace the following:

• VIEW: an ID up to 128 characters long for the new parameterized view. The ID must be unique among table IDs and view IDs in the instance.
• INSTANCE: the ID of the instance to create the parameterized view in.
• TABLE_ID: the ID of the source table.

Update a parameterized view

You update a parameterized view in the same way that you update a logical view.

Delete a parameterized view

You delete a parameterized view in the same way that you delete a logical view.

View information about parameterized views

You view a list of parameterized views in the same way that you view a list of logical views for an instance.

Query parameterized views

You query parameterized views similarly to regular tables, but you provide the view_parameters map in the request.

Note: For each view parameter in your request, you must specify the data type. Only values of the STRING type are supported. If a parameter represents a different data type in your view's SQL definition, you must pass the parameter value as a string and cast it to the correct type in the SQL definition—for example, CAST(VIEW_PARAMETERS('parameter_name') AS INT64).

The following example shows how to query a parameterized view named purchase_history_pv, which filters data based on a user ID.

// Assumes 'purchase_history_pv' was created with the definition:
// SELECT * FROM purchases WHERE user_id = CAST(VIEW_PARAMETERS('user_id') AS BYTES)

String query = "SELECT customer_info[email], order_details[status], order_info[items] from purchase_history_pv";
PreparedStatement preparedStatement = dataClient.prepareStatement(query);
BoundStatement boundStatement = preparedStatement.bind().build();

// The user ID is now passed out-of-band in a view parameters map.
Map<String, Value> viewParameters = new HashMap<>();
viewParameters.put("user_id", Value.newBuilder().setType(stringType()).setStringValue(userId).build());

// Execute the query, passing the view parameters using a proto field in the request.
ResultSet rs = dataClient.executeQuery(
    boundStatement,
    viewParameters
);

This prevents the user from being able to see or manipulate the user_id parameter within the query itself, providing a clean logical separation.

What's next

• Use parameterized views in agentic applications