Bigtable schema design in GCP - Commands & Configuration

Introduction

Bigtable stores data in tables with rows and columns. Designing the schema well helps you find and update data fast and keeps costs low.

When you want to store large amounts of time-series data like sensor readings or logs.

When you need to quickly look up data by a key, such as user profiles or device info.

When you want to store data that changes often and needs fast updates.

When you want to organize data so related information is stored close together for fast access.

When you want to avoid slow queries by designing your keys and columns carefully.

Config File - bigtable-schema.yaml

bigtable-schema.yaml

instance_id: example-instance
cluster_id: example-cluster
cluster_location: us-east1-b
table_id: user-activity
column_families:
  activity_data:
    gc_rule:
      max_age: 86400s
  user_info:
    gc_rule:
      max_versions: 1
row_key_design:
  pattern: "userID#timestamp"
  description: "Combines user ID and timestamp to keep recent activity sorted and grouped by user."

instance_id: The Bigtable instance to use.

cluster_id and cluster_location: Where the data is stored physically.

table_id: The name of the table.

column_families: Groups of columns with rules for data retention.

row_key_design: How the row keys are structured to organize data efficiently.

Commands

Create a Bigtable instance with a cluster in the specified zone to hold your tables and data.

Terminal

gcloud bigtable instances create example-instance --cluster=example-cluster --cluster-zone=us-east1-b --display-name="Example Instance"

Expected Output

Created [https://bigtableadmin.googleapis.com/v2/projects/PROJECT_ID/instances/example-instance].

→

--cluster - Name of the cluster to create with the instance

→

--cluster-zone - Physical location of the cluster

→

--display-name - Friendly name for the instance

Create a table named 'user-activity' in the Bigtable instance to store your data.

Terminal

gcloud bigtable tables create user-activity --instance=example-instance

Expected Output

Created table [user-activity].

→

--instance - Specifies which Bigtable instance to use

Add a column family 'activity_data' to the table with a rule to keep data for 1 day (86400 seconds).

Terminal

gcloud bigtable column-families create activity_data --table=user-activity --instance=example-instance --max-age=86400s

Expected Output

Created column family [activity_data].

→

--max-age - Sets how long data is kept before deletion

Add a column family 'user_info' that keeps only the latest version of each cell to save space.

Terminal

gcloud bigtable column-families create user_info --table=user-activity --instance=example-instance --max-versions=1

Expected Output

Created column family [user_info].

→

--max-versions - Limits the number of versions stored per cell

Read the first 5 rows from the 'user-activity' table to verify data is stored as expected.

Terminal

gcloud bigtable rows read user-activity --instance=example-instance --limit=5

Expected Output

ROW KEY                  COLUMN FAMILY:QUALIFIER  VALUE

user123#20240601120000  activity_data:clicks      5

user123#20240601120000  user_info:name            "Alice"

→

--limit - Limits the number of rows returned

Key Concept

If you remember nothing else from this pattern, remember: design your row keys to group related data together and keep access patterns fast.

Common Mistakes

Using a simple numeric ID as the row key without adding a timestamp or grouping.

This causes uneven data distribution and slow queries because related data is scattered.

Combine user ID with a timestamp or category to keep related data close and sorted.

Not setting garbage collection rules on column families.

Data accumulates indefinitely, increasing storage costs and slowing queries.

Set max age or max versions rules to automatically clean old data.

Creating too many column families for small data.

Each column family adds overhead and can reduce performance.

Group related columns into fewer column families with clear retention policies.

Summary

Create a Bigtable instance and cluster to hold your data.

Create tables and column families with rules to organize and clean data.

Design row keys to group related data and optimize access speed.

Use commands to verify your schema and data layout.