Checkpoints
Create a Bigtable table
/ 40
Delete a Bigtable table
/ 40
Designing and Querying Bigtable Schemas
GSP1053
Overview
Bigtable is Google's fully managed, scalable NoSQL database service. Bigtable is ideal for storing large amounts of data in a key-value store and for use cases such as personalization, ad tech, financial tech, digital media, and Internet of Things (IoT). Bigtable supports high read and write throughput at low latency for fast access to large amounts of data for processing and analytics.
In Bigtable, each row represents a single entity (such as an individual user or sensor) and is labeled with a unique row key. Each column stores attribute values for each row, and column families can be used to organize related columns. At the intersection of a row and column, there can be multiple cells, with each cell representing a different version of the data at a given timestamp.
In this lab, you use the Bigtable page of the Google Cloud Console to explore a Bigtable instance and the Bigtable CLI (cbt CLI) to query data in a Bigtable table. You also design a table schema and row key using best practices for Bigtable.
Objectives
In this lab, you learn how to access a Bigtable instance and query Bigtable schemas.
- Explore a Bigtable instance using the Google Cloud Console.
- Configure the
cbtCLI for your instance. - Design an appropriate schema and row key using Bigtable best practices.
- Query data within Bigtable.
Prerequisites
- You should have a basic understanding of database concepts and terms such as instances, schemas, and keys.
Setup and requirements
Qwiklabs setup
Before you click the Start Lab button
Read these instructions. Labs are timed and you cannot pause them. The timer, which starts when you click Start Lab, shows how long Google Cloud resources will be made available to you.
This hands-on lab lets you do the lab activities yourself in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials that you use to sign in and access Google Cloud for the duration of the lab.
What you need
To complete this lab, you need:
- Access to a standard internet browser (Chrome browser recommended).
- Time to complete the lab.
Cloud Console
Log in to Google Cloud Console
- Using the browser tab or window you are using for this lab session, copy the Username from the Connection Details panel and click the Open Google Console button.
- Paste in the Username, and then the Password as prompted.
- Click Next.
- Accept the terms and conditions.
Since this is a temporary account, which will last only as long as this lab:
- Do not add recovery options
- Do not sign up for free trials
- Once the console opens, view the list of services by clicking the Navigation menu (
) at the top-left.
Activate Cloud Shell
Cloud Shell is a virtual machine that contains development tools. It offers a persistent 5-GB home directory and runs on Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources. gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab completion.
-
Click the Activate Cloud Shell button (
) at the top right of the console.
-
Click Continue.
It takes a few moments to provision and connect to the environment. When you are connected, you are also authenticated, and the project is set to your PROJECT_ID.
Sample commands
- List the active account name:
(Output)
(Example output)
- List the project ID:
(Output)
(Example output)
Check project permissions
Before you begin working on Google Cloud, you must ensure that your project has the correct permissions within Identity and Access Management (IAM).
-
In the Google Cloud console, on the Navigation menu (
-
Confirm that the default compute Service Account
{project-number}-compute@developer.gserviceaccount.comis present and has theeditorrole assigned. The account prefix is the project number, which you can find on Navigation menu > Cloud overview.
If the account is not present in IAM or does not have the editor role, follow the steps below to assign the required role.
-
In the Google Cloud console, on the Navigation menu, click Cloud overview.
-
From the Project info card, copy the Project number.
-
On the Navigation menu, click IAM & Admin > IAM.
-
At the top of the IAM page, click Add.
-
For New principals, type:
Replace {project-number} with your project number.
-
For Select a role, select Basic (or Project) > Editor.
-
Click Save.
Task 1. Explore a Bigtable instance in the Console
For this lab exercise, a Bigtable instance and table have been pre-created for you to explore. In this task, you access the Bigtable instance named personalized-sales in the Google Cloud Console and review relevant details about the instance.
-
In the Google Cloud Console, in the Navigation menu (
-
From the list of Bigtable instances, identify the instance ID named personalized-sales.
Review the details provided for Nodes, and answer the following question.
-
To navigate to the instance details, click on the instance ID named personalized-sales.
-
Click Edit instance.
Review the details for the instance, and answer the following questions.
-
To close the instance edit page, click Cancel.
-
From the table of cluster IDs, on the line for personalized-sales-cluster1, click Edit pencil icon.
Review the details of the cluster, and answer the following questions.
-
To close the cluster details, click Cancel.
-
In the navigation menu, under Instance, click Tables.
Review the details for the table named UserSessions, and answer the following question.
Now that you have reviewed the details of the Bigtable instance, you can proceed to the next task to connect to the instance using the cbt CLI.
Task 2. Configure the Bigtable CLI
To connect to Bigtable using cbt CLI commands, you first need to update the .cbtrc configuration file with your project ID and your Bigtable instance ID using Cloud Shell.
For a review of how to access Cloud Shell, see the Setup and requirements section earlier in this lab guide.
While cbt CLI is primarily intended for debugging and exploration, it is a useful tool for learning the basics of Bigtable. To complete CRUD (Create, Read, Update, Delete) operations in production, we recommend using one of the client libraries for Bigtable.
- To modify the
.cbtrcfile with the project ID, in Cloud Shell, run the following commands:
- To see a list of available Bigtable instances in the project, run the following command:
The output confirms that there is one instance named personalized-sales.
- To modify the
.cbtrcfile with the Bigtable instance ID, run the following commands:
- To verify that you have successfully modified the
.cbtrcfile with the project ID and instance ID, run the following command:
The output should resemble the following:
- To see a list of available tables in the Bigtable instance named personalized-sales, run the following command:
The output confirms that the instance already contains one table named UserSessions. You will work with this table in a later task.
Task 3. Design a schema and row key in Bigtable
In this task, you create a test table to explore schema and row key design principles in Bigtable.
Review raw data to help design schema
To design a schema and row key in Bigtable, it is helpful to first answer key questions about the data that will be stored.
| Question | Purpose |
|---|---|
| What does an individual row represent? (for example, an individual user or sensor) | To identify the row structure |
| What will be the most common queries to this data? | To create a row key |
| What values are collected for each row? | To identify the columns (referred to as column qualifiers) |
| Are there related columns that can be grouped or organized together? | To identify the column families |
For example, consider a dataset that captures online shopping sessions for all users of an ecommerce company website. Each row represents an individual online shopping session with a timestamp. The most common queries to the dataset will retrieve details about individual sessions and the associated user ID. The values stored for each shopping session are all items that the user interacted with and purchased during the session as well as a color preference for the user.
The raw data could be organized as follows, with more columns for additional products (such as blue_jacket or purple_bag):
| timestamp | user_id | preferred_color | red_skirt | red_hat | orange_shoes | sale |
|---|---|---|---|---|---|---|
| 1638940844260 | 1939 | green | seen | seen | ||
| 1638940844260 | 2466 | blue | seen | seen | ||
| 1638940844260 | 1679 | blue | seen | blue_blouse#blue_jacket | ||
| 1638940844260 | 2737 | blue | seen | blue_dress#blue_jacket | ||
| 1638940844260 | 582 | yellow | yellow_skirt |
Create test table
A best practice for Bigtable is to store data with similar schemas in the same table, rather than in separate tables. For example, all data for the online shopping sessions can be stored in one table for easy retrieval.
- To create an empty table named test-sessions, run the following command:
Create column families
In Bigtable, the best practices for columns and column families include:
-
Using column qualifiers as data, so that you do not repeat the value for each row.
-
Organizing related columns in the same column family.
-
Choosing short but meaningful names for your column families.
For this dataset, the column qualifiers that store product interactions can be grouped into one column family named Interactions, while the column qualifier that stores purchases can be organized by itself into another column family named Sales. The resulting schema would be organized as follows:
| ... | ... | ... | Interactions | --- | --- | Sales |
|---|---|---|---|---|---|---|
| timestamp | user_id | preferred_color | red_skirt | red_hat | orange_shoes | sale |
| 1638940844260 | 1939 | green | seen | seen | ||
| 1638940844260 | 2466 | blue | seen | seen | ||
| 1638940844260 | 1679 | blue | seen | blue_blouse#blue_jacket | ||
| 1638940844260 | 2737 | blue | seen | blue_dress#blue_jacket | ||
| 1638940844260 | 582 | yellow | yellow_skirt |
- To add a column family named Interactions to the test_sessions table, run the following command:
- To add another column family named Sales, repeat the previous command and specify the new column family name:
- To see a list of column families in the test_sessions table, run the following command:
The command returns the following output:
Click Check my progress to verify the objective.
Create row key
In Bigtable, a best practice is to store all information for a single entity (such as an individual online shopping session) in a single row. A related best practice is to create a row key that makes it possible to easily query and retrieve a defined range of rows.
To apply best practices for row keys in Bigtable, it is recommended that you:
-
Design your row key based on the queries you will use to retrieve the data.
-
Avoid row keys that start with a timestamp or sequential numeric IDs or that cause related data to not be grouped.
-
Design row keys that start with a more common value (such as country) and end with a more granular value (such as city).
-
Store multiple delimited values in each row key using human-readable string values (such as user ID followed by timestamp).
In the previous section, timestamp, user_id, and preferred_color were not organized under a column family. Recall from the questions about the raw data that most of the queries to this dataset will retrieve details about individual sessions and the associated user ID.
To support these queries, a good row key for this table would be a combination of the user ID plus the timestamp of the session. In addition, the row key can include a prefix to label the color preference for each user, such as green1939#1638940844260 for user ID 1939, to make it easy to retrieve all users with a specific color preference.
| ... | Interactions | --- | --- | Sales |
|---|---|---|---|---|
| row_key | red_skirt | red_hat | orange_shoes | sale |
| green1939#1638940844260 | seen | seen | ||
| blue2466#1638940844260 | seen | seen | ||
| blue1679#1638940844260 | seen | blue_blouse#blue_jacket | ||
| blue2737#1638940844260 | seen | blue_dress#blue_jacket | ||
| yellow582#1638940844260 | yellow_skirt |
- To use a row key to add data to the Interactions column family, run the following command:
- To use a row key to add data to the Sales column family, run the following command:
- To see the data stored in the table, run the following command:
Notice that although the data for blue2737#1638940844260 was added second, it is sorted higher in the results than green1939#1638940844260. The records are returned in this order because in Bigtable, rows are sorted and stored lexicographically by row key. This order is similar to alphabetical order; however, rows that begin with numbers will not be sorted as smallest to largest (such as 1, 13, 2, 25, 6, and 70).
Clean up test data
- To delete the test table, run the following command:
Click Check my progress to verify the objective.
Task 4. Query data in Bigtable
In this task, you use the cbt CLI retrieve data from a pre-created, fully populated version of your test table (the existing table named UserSessions) and examine how the table applies best practices for designing schemas and row keys in Bigtable.
Query rows with limit
In this step, you review how the UserSessions table follows Bigtable best practices by storing all user interactions with products and purchases of products in one table that contains one row for each online shopping session.
- To see the data for the first five rows of the table, run the following command:
The output is structured as follows:
The output values should resemble the following:
Each row contains multiple product interactions for one user (such as blue_hat and green_jacket) including whether the user has seen, viewed details, or purchased the product. In addition, purchases are captured in the table in the sale column qualifier in the Sales column family.
Instead of creating one table for each interaction type, product, or sale, UserSessions follows best practices by containing all of the related user interactions and products in one table. In addition, all product interactions and purchases for an individual online shopping session are stored as one row in the table.
Query by row key
The most efficient queries in Bigtable retrieve data using one of the following:
- row key
- row key prefix
- range of rows defined by starting and ending row keys
In the next steps, you use each option in the cbt CLI to query the UserSessions table and retrieve desired records.
Information on using Bigtable client libraries to read single rows of data using row keys is available in the Bigtable documentation.
Query by row key prefix
- To see the first ten rows with a color preference of yellow, run the following command:
The output values should resemble the following:
Query by specific range of row keys
- To see all rows within a specific range of row keys, run the following command:
The output values should resemble the following:
The read command begins the range with the row key provided as the start value and ends the range before the row key provided as the end value. Therefore, the row key yellow991#1638940844645 is not returned in the output.
Query by specific row key
- To see all data for a specific row key, run the following command:
The output values should resemble the following:
Query by column qualifiers and column families
In these next steps, you retrieve data filtered by column qualifiers and column families to see how column best practices are implemented in the UserSessions table.
- To query the first five rows that have data in the Interactions column family, run the following command:
The output values should resemble the following:
- To query the first five rows that have data in the green_jacket column qualifier in the Interactions column family, run the following command:
The output values should resemble the following:
- To query the first five rows that have data in the sale column qualifier in the Sales column family, run the following command:
The output values should resemble the following:
Because the column family named Sales only has one column qualifier (sale), the values "Sales:sale" and "Sales:.*" for columns return the same columns.
Congratulations!
You have now used the Google Cloud Console to explore a Bigtable instance and the Cloud Bigtable CLI (cbt CLI) to query data in a Bigtable table. You also designed a table schema and row key using best practices for Bigtable.
Finish your quest
This self-paced lab is part of the Manage Bigtable on Google Cloud quest. A quest is a series of related labs that form a learning path. Completing this quest earns you a badge to recognize your achievement. You can make your badge (or badges) public and link to them in your online resume or social media account. Enroll in this quest and get immediate completion credit if you have successfully completed this lab. Search for other available quests in the Cloud Skills Boost catalog.
Take your next lab
Continue your quest with GSP1054 Creating and Populating a Bigtable Instance.
Manual Last Updated: February 16, 2023
Lab Last Tested: February 16, 2023
Copyright 2022 Google LLC All rights reserved. Google and the Google logo are trademarks of Google LLC. All other company and product names may be trademarks of the respective companies with which they are associated.