Bigtable Basics
This skill provides core workflows and guidance for administering and developing
with Google Bigtable.
Core Principles
- Control Plane vs. Data Plane:
- Use
gcloud for Control Plane & DDL operations: Manage Instances,
Clusters, App Profiles, Backups, and IAM. Manage schemas: Create,
update, and delete Tables, Column Families, Logical Views, Materialized
Views, and Authorized Views.
- Use
cbt for Data Plane operations: Reading, writing, and
inspecting data.
- Performance First: Bigtable is a NoSQL database. Efficiency is tied to
Row Key design. Always warn about Full Table Scans.
- Client Selection: For production use cases, prefer Java or Go
for their superior performance and feature coverage compared to other
languages.
- Observability: When diagnosing performance or hotspotting, always
mention Key Visualizer (via Cloud Console) as the primary diagnostic
tool because it provides the most granular view of access patterns across
row keys. This should be followed by the hot-tablets tool and table stats in
gcloud CLI and
include-stats=full option under cbt read to diagnose slow
queries.
[!IMPORTANT] Safety Rule: You MUST obtain explicit user confirmation
before making non-emulator database changes. You MUST mention this safety
requirement when providing commands or instructions that modify the database
structure or data.
Quick Recipes
1. Querying Data
Use SQL for complex transforms or aggregations and key-value APIs for simpler
query patterns. Note: Use exact match, prefix (_key LIKE 'myprefix%'), or
range predicates on _key to avoid expensive unbounded scans. Recommend
explicit row ranges (_key BETWEEN 'start' AND 'end') as a more performant
alternative to prefix matches where possible.
If expensive scans (either unbounded or prefix or range queries scanning a large
range) are unavoidable due to multiple access patterns that can’t all be
accommodated in a single schema, consider one of these two options:
- If the query will be used in user facing and/or latency sensitive
applications, use continuous materialized views with keys optimized for the
additional access patterns.
- If secondary access patterns are infrequent, batch patterns like ETL, ML
model training or analytical read-only tasks, use Bigtable Data Boost
instead.
2. Manipulating Data
Use key-value APIs for insert, update, increment and delete operations. SQL API
is read-only.
3. Data Model Definition (DDL)
SQL API doesn't support DDL operations. Table creation, deletion, updates should
be made using gcloud CLI. Logical Views and Continuous Materialized Views are
defined as SQL queries but they must be created using gcloud CLI.
Reference Guides
- CLI Operations:
- infrastructure_management.md:
Provisioning instances, clusters, and table schemas.
- cli_data_access.md: Reading and writing
data via the
cbt CLI.
- Design & Discovery:
- schema_design.md: Best practices for row
keys and performance with tables and continuous materialized views.
- dataplex.md: Data catalog search for Bigtable
assets.
- Querying & Code:
- sql_guide.md: Querying structured row keys
via SQL and CLI.
- client_libraries.md: Patterns for
high-performance Go/Java/Python code.
Common Workflows
Schema Evolution (DevOps)
Prefer Terraform for production schema changes to prevent accidental
data loss.
For manual cbt changes, first check the existing state by listing the
table's column families and GC policies before proposing any modifications:
cbt ls {table}
If modifications are needed, create the family or update the GC policy:
cbt createfamily {table} {family}
cbt setgcpolicy {table} {family} "maxversions=5 AND maxage=30d"
Reference
infrastructure_management.md for
full syntax.
External Resources
1---2name: bigtable-basics3description: >-4 Provisions instances and tables, designs performant schemas, and queries data in Bigtable. Use when designing Bigtable row keys, configuring column families, writing SQL queries or client library code (Java, Go, Python) for Bigtable, or diagnosing performance/hotspotting issues. Also use when provisioning Bigtable clusters using gcloud or cbt CLIs. Don't use for generic Cloud SQL administration.5metadata:6 source: https://skillmd.com/skills/googlecloudplatform/bigtable-basics7 version: v38 publisher: google9 category: Databases10license: Apache-2.011---1213# Bigtable Basics1415This skill provides core workflows and guidance for administering and developing16with Google Bigtable.1718## Core Principles1920- **Control Plane vs. Data Plane:**21 - Use **`gcloud`** for Control Plane & DDL operations: Manage Instances,22 Clusters, App Profiles, Backups, and IAM. Manage schemas: Create,23 update, and delete Tables, Column Families, Logical Views, Materialized24 Views, and Authorized Views.25 - Use **`cbt`** for Data Plane operations: Reading, writing, and26 inspecting data.27- **Performance First:** Bigtable is a NoSQL database. Efficiency is tied to28 Row Key design. Always warn about Full Table Scans.29- **Client Selection:** For production use cases, prefer **Java** or **Go**30 for their superior performance and feature coverage compared to other31 languages.32- **Observability:** When diagnosing performance or hotspotting, **always**33 mention **Key Visualizer** (via Cloud Console) as the primary diagnostic34 tool because it provides the most granular view of access patterns across35 row keys. This should be followed by the hot-tablets tool and table stats in36 gcloud CLI and `include-stats=full` option under `cbt read` to diagnose slow37 queries.3839> [!IMPORTANT] **Safety Rule:** You MUST obtain explicit user confirmation40> before making non-emulator database changes. You MUST mention this safety41> requirement when providing commands or instructions that modify the database42> structure or data.4344## Quick Recipes4546### 1. Querying Data4748Use SQL for complex transforms or aggregations and key-value APIs for simpler49query patterns. *Note: Use exact match, prefix (`_key LIKE 'myprefix%'`), or50range predicates on `_key` to avoid expensive unbounded scans. Recommend51explicit row ranges (`_key BETWEEN 'start' AND 'end'`) as a more performant52alternative to prefix matches where possible.*5354If expensive scans (either unbounded or prefix or range queries scanning a large55range) are unavoidable due to multiple access patterns that can’t all be56accommodated in a single schema, consider one of these two options:5758- If the query will be used in user facing and/or latency sensitive59 applications, use continuous materialized views with keys optimized for the60 additional access patterns.61- If secondary access patterns are infrequent, batch patterns like ETL, ML62 model training or analytical read-only tasks, use Bigtable Data Boost63 instead.6465### 2. Manipulating Data6667Use key-value APIs for insert, update, increment and delete operations. SQL API68is read-only.6970### 3. Data Model Definition (DDL)7172SQL API doesn't support DDL operations. Table creation, deletion, updates should73be made using gcloud CLI. Logical Views and Continuous Materialized Views are74defined as SQL queries but they must be created using gcloud CLI.7576## Reference Guides7778- **CLI Operations**:79 - [infrastructure_management.md](references/infrastructure_management.md):80 Provisioning instances, clusters, and table schemas.81 - [cli_data_access.md](references/cli_data_access.md): Reading and writing82 data via the `cbt` CLI.83- **Design & Discovery**:84 - [schema_design.md](references/schema_design.md): Best practices for row85 keys and performance with tables and continuous materialized views.86 - [dataplex.md](references/dataplex.md): Data catalog search for Bigtable87 assets.88- **Querying & Code**:89 - [sql_guide.md](references/sql_guide.md): Querying structured row keys90 via SQL and CLI.91 - [client_libraries.md](references/client_libraries.md): Patterns for92 high-performance Go/Java/Python code.9394## Common Workflows9596### Schema Evolution (DevOps)97981. **Prefer Terraform** for production schema changes to prevent accidental99 data loss.1002. For manual `cbt` changes, first check the existing state by listing the101 table's column families and GC policies before proposing any modifications:102103 ```bash104 cbt ls {table}105 ```106107 If modifications are needed, create the family or update the GC policy:108109 ```bash110 cbt createfamily {table} {family}111 cbt setgcpolicy {table} {family} "maxversions=5 AND maxage=30d"112 ```1131143. Reference115 [infrastructure_management.md](references/infrastructure_management.md) for116 full syntax.117118## External Resources119120* [Cloud Bigtable Documentation](https://cloud.google.com/bigtable/docs)121* [Bigtable SQL Reference](https://cloud.google.com/bigtable/docs/googlesql-overview)122* [cbt CLI Reference](https://cloud.google.com/bigtable/docs/cbt-reference)123* [gcloud bigtable Reference](https://cloud.google.com/sdk/gcloud/reference/bigtable)