GSP1096

Overview

This lab gives you hands-on experience deploying SingleStoreDB and combining it with Google Cloud's cloud native products like Pub/Sub, Dataflow and Cloud Storage. To demonstrate these product features, work with the NYC public taxi dataset.

The flow of the lab is to first deploy the SingleStoreDB through your local browser and create the appropriate schema. Then, use Pub/Sub to push the data to Cloud Storage in real time with the help of Dataflow. The data generated and stored in object storage is consumed using SingleStoreDB's native pipeline. Once SingleStoreDB has ingested the data, you run queries and interact with SingleStore.

What you'll learn

In this lab, you learn how to perform the following tasks:

• Log in and navigate the Google Cloud.
• Create a SingleStore DB instance/cluster.
• Use Cloud Shell to connect and create schema on SingleStoreDB.
• Create Pub/Sub topics and subscriptions, as well as pull and push data to other services.
• Create a Dataflow streaming job.
• Use SingleStore native pipeline to load data from object storage.
• Run queries and interact with your data in SingleStore.

Prerequisites

• Familiarity with Google Cloud and SingleStore is helpful but is not required.

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 are made available to you.

This hands-on lab lets you do the lab activities in a real cloud environment, not in a simulation or demo environment. It does so by giving you new, temporary credentials 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).

Note: Use an Incognito (recommended) or private browser window to run this lab. This prevents conflicts between your personal account and the student account, which may cause extra charges incurred to your personal account.

• Time to complete the lab—remember, once you start, you cannot pause a lab.

Note: Use only the student account for this lab. If you use a different Google Cloud account, you may incur charges to that account.

How to start your lab and sign in to the Google Cloud console

1. Click the Start Lab button. If you need to pay for the lab, a dialog opens for you to select your payment method. On the left is the Lab Details pane 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

2. 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.

3. 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 pane.

4. Click Next.

5. 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 pane.

6. 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.

7. 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.

Note: To access Google Cloud products and services, click the Navigation menu or type the service or product name in the Search field.

Task 1. Launch your own SingleStore cluster

At this point you should be logged into the Google Cloud console in an Incognito window.

1. In another tab in the Incognito window that you have open, go to the SingleStore Portal. You should see the below page:

2. Click Sign in with Google and choose the Qwiklabs Username and Password from the lab.
3. Click Continue to sign in to SingleStore.
4. Select the checkbox for Accept Terms of Service & Privacy Policy and click Continue.
5. In the Add your details screen, select a Job Title and Country, and click Continue.
6. In the What's your data stack screen, select one of the options or just click Continue.
7. Click View my Deployment to go to the SingleStore Customer Portal.
8. Click Start Using SingleStore when you see Your free starter workspace is ready!

Create the Workspace

1. On the upper left corner click + Create New and Select Deployment.

2. On the Create Workspace page, configure the following settings:

   • Workspace Group Name: Workshop
   • Cloud Provider: GCP
   • Region: US East 4 (N.Virginia), this should be the default, if not, change accordingly.

Your configuration should look like this:

3. Click Next.

4. On the Workspace Details page, leave the default settings, and click Create Workspace.

Wait a few minutes as your workspace spins up. It generates a sample database for you, which you are not going to use for this lab.

Task 2. Connect to SingleStore and Google Cloud

1. Before connecting to the SingleStore Workspace, navigate back to the Google Cloud console.

2. Open a new Cloud Shell window by clicking the Activate Cloud Shell () icon in the top right corner of the console. If prompted, click Continue, and Authorize.

3. In Cloud Shell, run the following commands to clone the GitHub repository to download the code for the workshop.

git clone https://github.com/mlochbihler/singlestore-gcp-looker-devday-01 cd singlestore-gcp-looker-devday-01/section_b

4. Return to the SingleStore Portal. Once your workspace is initialized, click the dropdown arrow next to Connect, and select CLI Client.

5. On the Connect to Workspace page, first copy the password that was generated for you. There is a copy icon next to the password. It is a good idea to store this in a local file because you need it later in this lab.

6. Click the Copy icon to the right of the MySQL Command. This copies the command to your clipboard.

The MySQL command should resemble: mysql -u admin -h svc-b675ae2f-b129-4baf-86ca-0a03c2c31d19-dml.gcp-virginia-1.svc.singlestore.com -P 3306 --default-auth=mysql_native_password --password

Note: You might see the password you just created in the MySQL command above. This only happens the first time you open that window. Thereafter, the password is not shown anymore, that's why it is important to save it. If for some reason you are not able to connect to the SingleStore DB, you can reset your password by going to the Access tab and clicking Reset.

7. Navigate back to the Google Cloud console and your Cloud Shell instance.

8. Paste the MySQL command into the terminal.

9. Enter the password that you copied earlier if prompted. You're now at a mysql> prompt.

Note: If your deployment stops responding to the MySQL commands, check the workspace state in SingleStore. It is configured to pause if left idle for more than 20 minutes. If that's the case, the button shows Resume. Click it to resume its operations. It should take a couple of minutes.

Task 3. Create schema and interact with SingleStore

In this section, you build the table schemas.

Use the following DDL to create a new database named nyc_taxi.

1. At the mysql> prompt, run the following code:

source create_nyctaxi_tables.ddl; use nyc_taxi; show tables;

You should get results similar to the following.

Output:

Database changed +--------------------+ | Tables_in_nyc_taxi | +--------------------+ | avgcost | | avgdist | | avgriders | | avgridetime | | avgwait_driver | | avgwait_passenger | | drivers | | nab_nyctaxi_scored | | neighborhoods | | trips | | triptotals | | triptotalsc | +--------------------+ 12 rows in set (0.05 sec)

You have created the Schema and the Database tables are listed.

2. Run the following command:

select * from avgcost limit 5;

The output of this query is "Empty set" or "0". This is because you have only created the Schema and the Table is empty.

Pause here for a second. You've successfully created the SingleStore Database and connected to it. The Schema is set up but there is no data yet.

If you have extra time, feel free to poke around and explore SingleStore!

Task 4. Explore the data in the lab (Optional)

In today's lab, you use the NYC Taxi data, this data can be found in BigQuery's public datasets or on the NYC Open Data website.

This data has been put in a Cloud Storage bucket for you ahead of time, so there is no need for you to download/import this into Google Cloud. You can explore the data if you so wish.

The NYC Open Data website provides free datasets related to New York City, including taxi, education, police data and more.

In this lab, you work with the NYC Department of City Planning's Neighborhood Tabulation Areas (NTAs) which provides information on approximate zones and neighborhoods in NYC. You use this in combination with NYC Taxi data to see where passengers were picked up and dropped off.

On the website you can visualize the data by neighborhood:

Task 5. Set up a Cloud Storage bucket

1. Before starting this section, open a new Cloud Shell terminal tab by clicking the "+" button:

In Google Cloud, the two main ways to set up resources are through the GUI and CLI. In this lab, you use the CLI to create the bucket and get the latest neighborhood data from NYC taxi cab website.

2. Copy and run the commands that follow to create your own bucket and import the data from a public bucket into your own bucket:

gcloud storage buckets create gs://$GOOGLE_CLOUD_PROJECT --location={{{ project_0.default_region | "REGION" }}} gcloud storage cp -r gs://configuring-singlestore-on-gcp/drivers gs://$GOOGLE_CLOUD_PROJECT gcloud storage cp -r gs://configuring-singlestore-on-gcp/trips gs://$GOOGLE_CLOUD_PROJECT gcloud storage cp gs://configuring-singlestore-on-gcp/neighborhoods.csv gs://$GOOGLE_CLOUD_PROJECT

If prompted, click Authorize.

Notice there is already another bucket created in your project. Don't worry about this bucket, it's a staging/temp storage location for Dataflow.

3. Go to Cloud Storage > Buckets, find your bucket (), and make sure it contains 2 folders and a CSV file, which should look like this:

Click Check my progress to verify the objective. Set up the Cloud Storage bucket

Task 6. Configure Pub/Sub and a Dataflow streaming job

Pub/Sub and Dataflow resources have been pre-populated in your project. To view them, use the search box at the top of your Cloud console to find Pub/Sub and Dataflow, respectively.

1. For Pub/Sub, do the following:

• Check that there is a Topic called "Taxi"
• Verify there is a subscription called "Taxi-sub"

2. For Dataflow, do the following:

• Check that there is a job called "GCStoPS" with status failed.

3. Click on the job and click Clone to run this exact job. Rename it and check the job info (which you've pre populated since you cloned it) so you know what you're running. Scroll down and click Run Job.

4. Return to Dataflow jobs and make sure your job has a status of "running".

Note: It can take 5-7 min for your Dataflow job to start streaming.

5. Return to Pub/Sub, locate the Taxi-sub subscription, click Messages, and click Pull. Messages should start rolling in. This should resemble the following:

You've now simulated live data streaming! As an example, if you were to upload more data to GCS and Pull in Pub/Sub, Dataflow would be moving the data live.

Click Check my progress to verify the objective. Clone the Dataflow Job

Task 7. Set up a streaming architecture on Google Cloud

A common streaming architecture on Google Cloud consists of Pub/Sub > Dataflow > GCS. In this task, you set this up and then plug SingleStore into GCS.

1. Navigate back to Dataflow.

2. Click Create a job from template and name it pstogcs (PubSub to GCS).

3. Select the Regional endpoint as .

4. Select the following Dataflow template: Pub/Sub Subscription or Topic to Text Files on Cloud Storage. A number of options appear, so you may need to Filter for it.

5. For Target, click Browse, and select the bucket you created earlier, called . You want the output of this job to land in the bucket you created earlier in the lab. Click Select.

6. Leave the output filename prefix as output. This is to mark the output files clearly.

7. Expand Optional Source Parameters. For Pub/Sub input subscription, select the Taxi-sub that you just saw messages coming into. No need to include input topic.

8. Leave the defaults for everything else, then go to the bottom of the page, and click RUN JOB.

Note: If you get an error when attempting to run the job, check if the issues are in the Custom Pattern fields for date and time. If that's the case, delete the values on all of those fields, and try again.

This Dataflow job should read the messages you saw earlier in Pub/Sub and Stream them into your Cloud Storage bucket.

Note: It can take 5-7 min for your Dataflow job to start streaming.

9. Go to Cloud Storage > Buckets and open your bucket, . You should see a timestamped output file in the bucket.

10. Click on the output file and select Download. You should see Taxi data that includes names, coordinates, and times

Click Check my progress to verify the objective. Set up a streaming architecture on Google Cloud

Enable Gemini Code Assist in the Cloud Shell IDE

You can use Gemini Code Assist in an integrated development environment (IDE) such as Cloud Shell to receive guidance on code or solve problems with your code. Before you can start using Gemini Code Assist, however, you need to enable it.

1. In Cloud Shell, enable the Gemini for Google Cloud API with the following command:

gcloud services enable cloudaicompanion.googleapis.com

2. Click Open Editor on the Cloud Shell toolbar.

Note: To open the Cloud Shell Editor, click Open Editor on the Cloud Shell toolbar. You can switch between Cloud Shell and the code Editor by clicking Open Editor or Open Terminal, as required.

3. In the Cloud Shell Editor, navigate to Cloud Code > Help and Feedback > Change Settings.

4. In the Settings, search for Gemini Code Assist.

5. Locate and ensure that the checkbox is selected for Geminicodeassist: Enable, and close the Settings.

6. Click Cloud Code - No Project in the status bar at the bottom of the screen.

7. Authorize the plugin as instructed. If a project is not automatically selected, click Select a Google Cloud Project, and choose .

8. Verify that your Google Cloud project () displays in the Cloud Code status message in the status bar.

Connect Cloud Storage to SingleStore

You now need to create a Key to connect this private Cloud Storage bucket to SingleStore.

1. In Cloud Storage, go to Settings (in the Navigation menu).

2. Click the Interoperability tab. At the bottom, click Create a Key.

Next, you ingest data into SingleStore with pipelines and start a stored procedure.

3. Navigate back to the Cloud Shell tab with the SingleStore Terminal open.

4. To create initial SingleStore pipelines:

   • From the first CloudShell tab, click Open Editor. If prompted click Open in a new window.
   • Open the singlestore-gcp-looker-devday-01 > section b > create_nyctaxi_pipelines.dml file.

   This action enables Gemini Code Assist, as indicated by the presence of the icon in the upper-right corner of the editor.

Use Gemini Code Assist to learn about a SingleStore pipeline file

1. Click the Gemini Code Assist: Smart Actions icon and select Explain this.

2. Gemini Code Assist opens a chat pane with the prefilled prompt of Explain this. In the inline text box of the Code Assist chat, replace the prefilled prompt with the following, and click Send:

You are an expert Data Engineer at Cymbal AI. A new team member is unfamiliar with this SingleStore Pipelines code. Explain this "create_nyctaxi_pipelines.dml" file in detail, breaking down its key components used in the code. For the suggested improvements, don't update this file.

The explanation for the code in the create_nyctaxi_pipelines.dml file appears in the Gemini Code Assist chat.

3. Make the following updates to the file:

• On line 4, change the bucket name to your bucket name.
• On line 5, add the Storage Bucket Access ID and Secret Key you just created.
• On line 12, change the bucket name to your bucket name.
• On line 13, add the Storage Bucket Access ID and Secret Key you just created.
• On line 22, change the bucket name to your bucket name. and .tsv to .csv.
• On line 23, add the Storage Bucket Access ID and Secret Key you just created.
• On line 26, change /t to ,.

Your file should resemble the following:

4. Click Save.

5. Return to the Cloud Shell Terminal. Connect to SingleStore using the MySQL command you used earlier. You can use the same command you used earlier to connect to SingleStore.

6. At the MySQL prompt, run the following command to create the pipelines:

source create_nyctaxi_pipelines.dml; show pipelines;

The output should resemble the following.

Output:

| Pipelines_in_nyc_taxi | State | Scheduled | | --------------------- | ------- | --------- | | neighborhoods | Stopped | False | | drivers | Stopped | False | | trips | Stopped | False |

Task 8. Start the pipelines

1. Run the following commands to start the pipelines:

source start_nyctaxi_pipelines.dml; show pipelines;

Output:

| Pipelines_in_nyc_taxi | State | Scheduled | | --------------------- | ------- | --------- | | neighborhoods | Running | False | | drivers | Running | False | | trips | Running | False |

2. Make sure the Pipeline status for all three is Running.

Task 9. Run operational analytic queries

1. Run the following commands to run the operational analytic queries:

select * from trips limit 5; select * from drivers limit 5; select * from neighborhoods limit 5;

Report 1: Total number of trips for each neighborhood.

2. Then, run:

source total_trips.sql;

Report 2: The average amount of time between someone requesting a ride and that person being picked up.

3. Next, run:

source average_wait.sql;

Report 3: The average distance of a trip.

4. Run the following:

source average_distance.sql;

Report 4: The average amount of time between someone being picked up and that person being dropped off.

5. Run the following:

source average_ride_time.sql;

Report 5: The average cost of a trip.

6. Run the following:

source average_cost.sql;

Report 6: The average amount of time it takes from the time a driver accepts a ride to the time they pick up the passenger.

7. Run the following:

source average_wait2.sql;

Report 7: The average number of riders per trip.

8. Finally, run the following:

source average_num_riders.sql;

Congratulations!

Congratulations! In this lab, you have deployed SingleStoreDB and combined it with Google Cloud's cloud native products like Pub/Sub, Dataflow, and Cloud Storage. You have also used SingleStoreDB's native pipeline to ingest data from Cloud Storage and run operational analytic queries.

You can learn more about SingleStore at SingleStore.com.

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 August 22, 2025

Lab Last Tested August 12, 2025

Copyright 2025 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.