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.

Practice

1. What is the main purpose of designing a row key carefully in Bigtable schema design?

easy

A. To balance load and enable fast queries

B. To store large binary files

C. To create multiple databases

D. To encrypt data automatically

Solution

1. Step 1: Understand the role of row keys in Bigtable

   Row keys uniquely identify rows and determine data distribution across nodes.

2. Step 2: Analyze the impact of row key design

   Good row key design balances load and speeds up queries by avoiding hotspots.

3. Final Answer:

   To balance load and enable fast queries -> Option A

4. Quick Check:

   Row key design = balance load + fast queries [OK]

Hint: Row keys control data distribution and query speed [OK]

Common Mistakes:

• Thinking row keys store data themselves
• Confusing row keys with column families
• Assuming row keys encrypt data

2. Which of the following is the correct way to group related columns in Bigtable?

easy

A. Using multiple row keys

B. Using separate tables

C. Using column families

D. Using indexes

Solution

1. Step 1: Recall Bigtable's data organization

   Bigtable organizes columns into column families to group related data.

2. Step 2: Identify the correct grouping method

   Column families group related columns for performance and management.

3. Final Answer:

   Using column families -> Option C

4. Quick Check:

   Related columns = column families [OK]

Hint: Group related columns with column families [OK]

Common Mistakes:

• Confusing row keys with column grouping
• Thinking indexes group columns
• Using separate tables unnecessarily

3. Given a Bigtable with row keys designed as userID#timestamp, what is the main benefit of this design?

medium

A. It enables efficient queries for a single user's recent activity

B. It groups all data by timestamp only

C. It allows fast retrieval of all users' data at once

D. It prevents any data duplication

Solution

1. Step 1: Analyze the row key format

   The row key combines userID and timestamp, so rows are sorted by user and time.

2. Step 2: Understand query benefits

   This design allows quick access to recent activity for a specific user by scanning a key range.

3. Final Answer:

   It enables efficient queries for a single user's recent activity -> Option A

4. Quick Check:

   Row key userID#timestamp = fast user recent queries [OK]

Hint: Row keys with userID first speed user-specific queries [OK]

Common Mistakes:

• Assuming it groups all users' data together
• Thinking it only sorts by timestamp
• Believing it prevents duplication

4. You notice that your Bigtable cluster has hotspots causing slow queries. Which schema design change can help fix this?

medium

A. Use a sequential numeric row key

B. Add a random prefix to the row key

C. Group all columns into one column family

D. Remove timestamps from row keys

Solution

1. Step 1: Identify cause of hotspots

   Sequential row keys cause data to cluster on few nodes, creating hotspots.

2. Step 2: Apply solution to distribute load

   Adding a random prefix spreads data across nodes, balancing load and reducing hotspots.

3. Final Answer:

   Add a random prefix to the row key -> Option B

4. Quick Check:

   Random prefix = balanced load, fewer hotspots [OK]

Hint: Randomize row keys to avoid hotspots [OK]

Common Mistakes:

• Using sequential keys which cause hotspots
• Grouping columns doesn't fix hotspots
• Removing timestamps reduces query flexibility

5. You want to design a Bigtable schema for IoT sensor data that arrives frequently from many devices. Which design choice best supports fast queries for recent data per device and balanced load?

hard

A. Row key as randomUUID with sensor data in multiple tables

B. Row key as timestamp#deviceID with all columns in one family

C. Row key as deviceID only, with timestamps as columns

D. Row key as deviceID#timestamp with column families for sensor types

Solution

1. Step 1: Consider query pattern

   Queries focus on recent data per device, so deviceID first in row key helps range scans.

2. Step 2: Consider load balancing and data grouping

   Using column families for sensor types groups related data efficiently; timestamp after deviceID orders data by time.

3. Final Answer:

   Row key as deviceID#timestamp with column families for sensor types -> Option D

4. Quick Check:

   DeviceID#timestamp + column families = fast, balanced queries [OK]

Hint: Use deviceID first in row key, group columns by type [OK]

Common Mistakes:

• Putting timestamp first slows device queries
• Using random UUID breaks data locality
• Storing timestamps as columns complicates queries