Points de contrôle
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.
What you'll do
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
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.
To complete this lab, you need:
- Access to a standard internet browser (Chrome browser recommended).
- Time to complete the lab---remember, once you start, you cannot pause a lab.
How to start your lab and sign in to the Google Cloud console
-
Click the Start Lab button. If you need to pay for the lab, a pop-up opens for you to select your payment method. On the left is the Lab Details panel with the following:
- The Open Google Cloud console button
- Time remaining
- The temporary credentials that you must use for this lab
- Other information, if needed, to step through this lab
-
Click Open Google Cloud console (or right-click and select Open Link in Incognito Window if you are running the Chrome browser).
The lab spins up resources, and then opens another tab that shows the Sign in page.
Tip: Arrange the tabs in separate windows, side-by-side.
Note: If you see the Choose an account dialog, click Use Another Account. -
If necessary, copy the Username below and paste it into the Sign in dialog.
{{{user_0.username | "Username"}}} You can also find the Username in the Lab Details panel.
-
Click Next.
-
Copy the Password below and paste it into the Welcome dialog.
{{{user_0.password | "Password"}}} You can also find the Password in the Lab Details panel.
-
Click Next.
Important: You must use the credentials the lab provides you. Do not use your Google Cloud account credentials. Note: Using your own Google Cloud account for this lab may incur extra charges. -
Click through the subsequent pages:
- Accept the terms and conditions.
- Do not add recovery options or two-factor authentication (because this is a temporary account).
- Do not sign up for free trials.
After a few moments, the Google Cloud console opens in this tab.
Activate Cloud Shell
Cloud Shell is a virtual machine that is loaded with development tools. It offers a persistent 5GB home directory and runs on the Google Cloud. Cloud Shell provides command-line access to your Google Cloud resources.
- Click Activate Cloud Shell
at the top of the Google Cloud console.
When you are connected, you are already authenticated, and the project is set to your Project_ID,
gcloud is the command-line tool for Google Cloud. It comes pre-installed on Cloud Shell and supports tab-completion.
- (Optional) You can list the active account name with this command:
- Click Authorize.
Output:
- (Optional) You can list the project ID with this command:
Output:
gcloud, in Google Cloud, refer to the gcloud CLI overview guide.
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 (
), under Databases, click Bigtable.
-
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!
In this lab, you 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.
Next steps / Learn more
- Check out the lab titled Creating and Populating a Bigtable Instance.
Google Cloud training and certification
...helps you make the most of Google Cloud technologies. Our classes include technical skills and best practices to help you get up to speed quickly and continue your learning journey. We offer fundamental to advanced level training, with on-demand, live, and virtual options to suit your busy schedule. Certifications help you validate and prove your skill and expertise in Google Cloud technologies.
Manual Last Updated May 30, 2024
Lab Last Tested February 16, 2023
Copyright 2024 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.