Analyze access patterns

You can analyze how your applications read and write data in Bigtable visually by using the Access patterns page in the Google Cloud console, or you can programmatically call the Cloud Monitoring API to query the underlying access pattern metrics.

Before you read this document, review Reads, Writes, and Monitoring.

Overview

Bigtable automatically classifies incoming data requests into query types in Cloud Monitoring. Your workload access pattern is the blend of different query types. For example, your workload access pattern can consist of point read, short scan, and point write query types. Bigtable defines 10 distinct query types.

While the System insights page shows overall resource utilization, and Key Visualizer shows how Bigtable distributes traffic across row-key ranges, the Access patterns page provides a query-centric breakdown of your workload at the cluster, table, and app profile levels. You can use the Access patterns page for the following tasks:

Understand your workload: view the proportion and rate of read, write, and read-write query types for each cluster, and group the metrics by table or app profile
Detect inefficient query patterns and troubleshoot latency:
- Identify sparse reads where Bigtable scans significantly more rows or cells than it returns because of selective filters or uncollected cells awaiting garbage collection
- Monitor reverse scans, which are less efficient than forward scans
- Compare server-side latency percentiles alongside scanned rows, returned rows, and cells seen to pinpoint which access patterns contribute to latency spikes
Identify workloads for feature adoption:
- Identify tables or app profiles with high volumes of point reads that can benefit from the in-memory tier in the Enterprise Plus edition
- Identify large scan or batch read workloads that you can isolate from serving traffic by using Data Boost serverless compute

Limitations

Bigtable doesn't classify GoogleSQL for Bigtable queries as access patterns and doesn't include those queries in the row, cell, or byte access pattern metrics. In the QPS distribution, QPS, and Latency charts, SQL queries and other unclassified requests appear as QUERY_TYPE_UNKNOWN.

Required roles

To get the permissions that you need to view the Access patterns page in the Google Cloud console , ask your administrator to grant you the following IAM roles on the project:

View Bigtable resources:
- Bigtable Viewer (roles/bigtable.viewer)
- Bigtable Reader (roles/bigtable.reader)
View Monitoring time-series data: Monitoring Viewer (roles/monitoring.viewer)

For more information about granting roles, see Manage access to projects, folders, and organizations.

These predefined roles contain the permissions required to view the Access patterns page in the Google Cloud console . To see the exact permissions that are required, expand the Required permissions section:

Required permissions

The following permissions are required to view the Access patterns page in the Google Cloud console :

View instance details: bigtable.instances.get
List clusters, app profiles, and tables: bigtable.clusters.list, bigtable.appProfiles.list, bigtable.tables.list
Query Monitoring metrics: monitoring.timeSeries.list

You might also be able to get these permissions with custom roles or other predefined roles.

Access patterns

When your application uses a Bigtable client library, the client library translates your method calls into Bigtable Data API requests:

Reads: all client library read methods—including single-row reads such as readRow, multi-row reads such as readRows or bulkReadRows with multiple row keys, and row range or prefix scans such as readRows with a range or prefix—send a ReadRows Data API request.
Writes: client library write methods send a MutateRow request for simple writes and incremental updates, a MutateRows request for batch writes, a CheckAndMutateRow request for conditional writes, or a ReadModifyWriteRow request for appends and increments.

Bigtable classifies each incoming Data API request on the server after it completes, based on the following factors rather than the client library method name alone:

Request parameters: whether the request targets a single row key, multiple row keys, or a row range, whether the request sets reversed, and which filters it applies
Execution statistics: the number of rows and cells scanned and returned, and the byte payload

For example, when you call a single-row read method such as readRow, or call readRows with a single row key, Bigtable typically classifies the request as a point read, which is eligible for the in-memory tier. However, if that single-row read uses a complex filter or scans a large number of cells, Bigtable might classify it as a sparse read, bulk read, or other read. Conversely, if you call readRows to read multiple row keys or scan a row range, Bigtable classifies the request as a bulk read or scan, which isn't eligible for the in-memory tier.

Read evaluation order

Because a single ReadRows Data API request can match the characteristics of more than one category, Bigtable evaluates read requests in the following order and assigns the first matching access pattern:

Point read
Short scan (for forward scans) or reverse short scan (when reversed is set to true)
Reverse scan (for any remaining read request where reversed is set to true)
Sparse read (evaluated before bulk read so that Bigtable flags multi-key reads or larger forward scans with heavy filter elimination as sparse)
Bulk read
Other read (for any remaining ReadRows request)

Query type definitions

The following table describes each access pattern category, its corresponding query_type label in Monitoring, the typical client library operations and underlying Data API method (method metric label) it applies to, and how Bigtable classifies requests into that category. Because method names vary by programming language, the table lists representative client library method names; for language-specific code samples, see Read examples and Write examples. For information about simple and complex filters, see the Simple filters versus complex filters section of this document.

Query type	`query_type` metric label	Client operation and Data API method	Classification criteria
Point read	`QUERY_TYPE_POINT_READ`	Single-row read, such as `readRow` or single-key `readRows` Data API: `ReadRows`	A forward read of a single row key that uses no filter or a simple filter, where the number of cells scanned and returned and the total response size remain within small single-row limits.
Short scan	`QUERY_TYPE_SHORT_SCAN`	Row range or prefix scan, such as `readRows` with a single range or prefix Data API: `ReadRows`	A forward scan of a single contiguous row range that uses no filter or a simple filter, where the number of rows and cells scanned and returned and the total response size remain within small bounded limits.
Reverse short scan	`QUERY_TYPE_REVERSE_SHORT_SCAN`	Reverse scan, such as `readRows` with a single range or prefix in reverse Data API: `ReadRows`	A reverse scan of a single contiguous row range that uses no filter or a simple filter and stays within the same bounded row, cell, and byte limits as a Short scan.
Reverse scan	`QUERY_TYPE_REVERSE_SCAN`	Reverse scan, such as `readRows` in reverse Data API: `ReadRows`	Any reverse read that doesn't qualify as a reverse short scan, for example, a reverse scan that exceeds short-scan row, cell, or byte limits, specifies individual row keys, scans multiple row ranges, or uses a complex filter.
Sparse read	`QUERY_TYPE_SPARSE_READ`	Filtered read or scan across uncollected cells, using `readRow` or `readRows` Data API: `ReadRows`	Any forward read request (evaluated after point read, short scan, and reverse scan) where Bigtable scans significantly more rows or cells than it returns because of filter elimination or uncollected cells awaiting garbage collection.
Bulk read	`QUERY_TYPE_BULK_READ`	Multi-row read, such as multi-key `readRows` or `bulkReadRows`, or a large single-row read using `readRow` Data API: `ReadRows`	A forward read that specifies one or more row keys with no filter or a simple filter, exceeds point read limits (for example, requests multiple row keys or reads a large single row), and doesn't qualify as a sparse read.
Other read	`QUERY_TYPE_OTHER_READ`	Large range scan, multi-range scan, or filtered read using `readRow` or `readRows` Data API: `ReadRows`	Any `ReadRows` request that doesn't match the preceding read categories, for example, large forward range scans, multi-range scans, requests that combine row keys and row ranges, or reads with complex filters that don't qualify as sparse.
Point write	`QUERY_TYPE_POINT_WRITE`	Simple write, incremental update, or small batch write, such as `mutateRow` or `bulkMutateRows` Data API: `MutateRow`, `MutateRows`	A single-row `MutateRow` request or a small `MutateRows` batch request that modifies a small number of rows and cells with a small total byte payload.
Other write	`QUERY_TYPE_OTHER_WRITE`	Large batch write or large simple write, such as `bulkMutateRows` or `mutateRow` Data API: `MutateRow`, `MutateRows`	Any `MutateRow` or `MutateRows` request that exceeds the point write limits, such as larger batch writes or writes with a large number of mutations or byte payload.
Read-write	`QUERY_TYPE_READ_WRITE`	Conditional write or append, such as `checkAndMutateRow` or `readModifyWriteRow` Data API: `CheckAndMutateRow`, `ReadModifyWriteRow`	Any conditional write (`CheckAndMutateRow`) or atomic increment or append (`ReadModifyWriteRow`) request, regardless of payload size.

In request count and latency metrics, Bigtable labels unclassified requests, such as GoogleSQL for Bigtable queries, as QUERY_TYPE_UNKNOWN.

Simple filters versus complex filters

To qualify as a point read, short scan, reverse short scan, or bulk read, a ReadRows request must use either no filter or a simple filter.

Bigtable classifies a filter as a simple filter if it uses only the following filter types, either alone or combined in a bounded chain filter. For more information, see Chain.

No filter (FILTER_NOT_SET)
pass_all_filter or block_all_filter
column_range_filter, column_qualifier_regex_filter, or family_name_regex_filter
cells_per_column_limit_filter, cells_per_row_limit_filter, or cells_per_row_offset_filter
timestamp_range_filter

Bigtable treats any ReadRows request as using a complex filter if the request uses a filter type that's not in the preceding list, such as the following, or if it chains a large number of filters:

value_regex_filter
value_range_filter
row_key_regex_filter
row_sample_filter
interleave
condition
sink
strip_value_transformer
apply_label_transformer

Bigtable classifies such requests as a reverse scan if reversed is true, and otherwise as a sparse read (if it scans significantly more rows or cells than it returns) or an other read.

View access patterns

You can view the Access patterns page in the Google Cloud console at either the instance level or the cluster level.

View access patterns for an instance

In the Google Cloud console, open the list of Bigtable instances.

Open the instance list
Click the instance whose metrics you want to view.
In the navigation pane, under Observability, click Access patterns.
In the Cluster list, select the cluster whose access patterns you want to inspect. By default, the Google Cloud console selects the first cluster in the instance.
In the Group by list, choose how to break down the charts:
- None: default option that breaks down all charts by access pattern, such as QUERY_TYPE_POINT_READ
- App profile: breaks down all charts by both application profile ID and access pattern
- Table: breaks down all charts by both table ID and access pattern
Optional: To change the time span or toggle automatic refresh, use the time range selector.

View access patterns for a cluster

In the Google Cloud console, open the list of Bigtable instances.

Open the instance list
Click the instance whose metrics you want to view.

The instance overview page opens.
In the clusters table, click the cluster ID.
In the navigation pane, click Access patterns. The page displays metrics for the selected cluster.
In the Group by list, choose how to break down the charts:
- None: default option that breaks down all charts by access pattern, such as QUERY_TYPE_POINT_READ
- App profile: breaks down all charts by both application profile ID and access pattern
- Table: breaks down all charts by both table ID and access pattern
Optional: To change the time span or toggle automatic refresh, use the time range selector.

Charts and metrics on the access patterns page

The Access patterns page provides charts for the following metrics:

Metric	Available for	Description
QPS distribution	Clusters Tables App profiles	The proportion and average rate of requests per second (QPS) aggregated across the entire selected time range for the cluster, broken down by `query_type` and the selected Group by option.
Latency	Clusters Tables App profiles	The server-side request latency over time, broken down by `query_type` and the selected Group by option. Use the percentile menu on the chart to switch between 99th percentile (default), 95th percentile, and 50th percentile.
QPS	Clusters Tables App profiles	The rate of requests per second over time, broken down by `query_type` and the selected Group by option.
Returned rows	Clusters Tables App profiles	The number of rows that Bigtable returns to the client per second for read and read-write requests after applying filters.
Scanned rows	Clusters Tables App profiles	The number of rows that Bigtable reads per second while processing read and read-write requests, before applying filters. Comparing Scanned rows with Returned rows helps you identify inefficient read filters.
Modified rows	Clusters Tables App profiles	The number of rows modified per second by write and read-write requests.
Cells seen	Clusters Tables App profiles	The number of cells that Bigtable inspects per second during read and read-write requests before applying filters, including cells that it filters out or that are awaiting garbage collection.
Sent cells	Clusters Tables App profiles	The number of cells that Bigtable returns to the client per second after applying filters. Comparing Cells seen with Sent cells helps you identify read requests that inspect many cells for each returned cell.
Sent bytes	Clusters Tables App profiles	The number of bytes of encoded row data returned per second.

You can also query these metrics programmatically by using the Cloud Monitoring API, view them in Metrics Explorer, or add them to custom dashboards. Each bigtable.googleapis.com/query_type/* metric on the bigtable_table monitored resource includes the following labels:

Resource labels: project_id, instance, cluster, and table
Metric labels: query_type, app_profile, and method

For more information about querying Bigtable metrics in Monitoring, see Monitor with Monitoring and Metrics.

Optimize workloads based on access patterns

You can use the metrics on the Access patterns page to make the following decisions:

Enable the in-memory tier for low-latency point reads: if the QPS distribution, QPS, and Latency charts show a high volume of QUERY_TYPE_POINT_READ requests on tables or app profiles with strict read latency targets, then consider enabling the in-memory tier that is available in the Enterprise Plus edition. Group the charts by Table or App profile to identify which tables and workloads will benefit most.
Isolate large scans and batch reads with Data Boost: if you observe high volumes or periodic spikes of QUERY_TYPE_BULK_READ or QUERY_TYPE_OTHER_READ requests sharing a cluster with latency-sensitive serving traffic, then group by App profile to identify the source of the scans. Route batch, reporting, or analytics reads through a dedicated app profile configured to use Data Boost serverless compute so that large scans don't compete with serving nodes for CPU resources.
Reduce filter overhead and tune garbage collection: if you see a high rate of QUERY_TYPE_SPARSE_READ requests, if Scanned rows is much higher than Returned rows, or if Cells seen is much higher than Sent cells, then your read requests are scanning data that Bigtable doesn't return to the client. To improve read efficiency, do the following:
- Narrow your row key ranges or prefixes in the read request rather than relying on value or regular expression filters to discard rows.
- Include a cell limit filter, such as cells_per_column_limit_filter, when your application needs only the most recent cell versions. For more information, see Cells per column limit.
- Review your garbage collection policies so that expired or obsolete cell versions don't accumulate and slow down scans. For more information, see Garbage collection overview.
Replace reverse scans with forward scans: if you observe frequent QUERY_TYPE_REVERSE_SCAN or QUERY_TYPE_REVERSE_SHORT_SCAN requests with elevated latency, then consider designing your row keys so that your application can issue forward scans. For more information, see Schema design best practices.