Overview

In this lab, you are a data analyst who will use Gemini and BigQuery to analyze data and predict product sales as part of a proof of concept project at Cymbal Superstore. As part of the project, you will also determine if Gemini can be used to help analysts generate new SQL queries, complete queries, and explain complex queries.

The data used in the lab is based on the BigQuery public datasets, specifically the bigquery-public-data.thelook_ecommerce dataset that contains synthetic ecommerce and digital marketing data.

This lab assumes that you are familiar with SQL (Structured Query Language) and basic data analytics tasks. Knowledge of Google Cloud products is not assumed. If you're new to BigQuery, see the BigQuery quickstarts.

Objectives

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

• Use Gemini to answer your questions about Google Cloud data analytics products and use cases.
• Prompt Gemini to explain and generate SQL queries in BigQuery.
• Build a machine learning (ML) model to forecast future periods.

Setup

For each lab, you get a new Google Cloud project and set of resources for a fixed time at no cost.

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

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

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

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 view a menu with a list of Google Cloud products and services, click the Navigation menu at the top-left, or type the service or product name in the Search field.

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.

1. Click the Activate Cloud Shell button () at the top right of the console.

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

Task 1. Configure your environment and account

In this task, you will configure your environment, account, and user, so that you can use the Cloud AI Companion API for Gemini.

1. Sign in to the Google Cloud console with your lab credentials, and open the Cloud Shell terminal window.

2. To set your project ID and region environment variables, in Cloud Shell, run the following commands:

   PROJECT_ID=$(gcloud config get-value project) REGION={{{project_0.default_region|set at lab start}}} echo "PROJECT_ID=${PROJECT_ID}" echo "REGION=${REGION}"

3. To store the signed-in Google user account in an environment variable, run the following command:

   USER=$(gcloud config get-value account 2> /dev/null) echo "USER=${USER}"

4. Enable the Cloud AI Companion API for Gemini:

   gcloud services enable cloudaicompanion.googleapis.com --project ${PROJECT_ID}

5. To use Gemini, grant the necessary IAM roles to your Google Cloud Qwiklabs user account:

   gcloud projects add-iam-policy-binding ${PROJECT_ID} --member user:${USER} --role=roles/cloudaicompanion.user gcloud projects add-iam-policy-binding ${PROJECT_ID} --member user:${USER} --role=roles/serviceusage.serviceUsageViewer

   Adding these roles lets the user use Gemini assistance.

To verify the objective, click Check my progress. Enable relevant APIs and set IAM roles.

Task 2. Create a dataset and enable Gemini features in BigQuery

In this task, you will create a dataset and enable the Gemini features in BigQuery.

Create a dataset

1. In the Google Cloud console, select the Navigation menu (), and then select BigQuery. If prompted click Done.

2. In the Explorer panel, for , select View actions (), then select Create dataset.

   You create a dataset to store database objects, including tables and models.

3. In the Create dataset pane, enter the following information:

   Field	Value
   Dataset ID	bqml_tutorial
   Location type	select Multi-region

   Leave the other fields at their defaults.

4. Click Create Dataset.

Enable the Gemini features in BigQuery

1. To view Gemini features in BigQuery, in the toolbar, click Gemini (). If it's not visible, refresh the page.

2. In the Gemini in BigQuery SQL editor list, select all of the following options:

   • Auto completion

   • Auto generation

   • Explanation

To verify the objective, click Check my progress. Create a dataset.

Task 3. Use Gemini to analyze your data

Gemini can help you discover and analyze your available data.

Before you can query data, you need to know what data you can access. Every data product organizes and stores data differently. To get help, you can send Gemini a natural language statement (or prompt) like, "How do I view which datasets and tables are available to me in BigQuery?"

If you want to understand the characteristics of different data query systems, you might prompt Gemini for specific product information like the following:

• "How do I get started with BigQuery?"

• "What are the benefits of using BigQuery for data analysis?"

• "How does BigQuery handle auto-scaling for queries?"

In this task, you will prompt Gemini to answer questions about your data.

Prompt Gemini to answer questions about your data

1. In the Google Cloud console, select the Navigation menu (), and then select BigQuery.

2. In the Google Cloud console toolbar, click Open Gemini ().

3. Click ENABLE to enable the Gemini for Google Cloud API.

4. The Welcome to Gemini in Cloud Console message is displayed in the Gemini pane. Click Start Chatting.

   Note: If the Start Chatting button is not enabled, refresh the page and open Gemini again.

5. In the Gemini pane, enter the prompt:

   How do I learn which datasets and tables are available to me in BigQuery?

6. Click send Send prompt ().

   Gemini doesn't use your prompts or its responses as data to train its model. For more information, see How Gemini for Google Cloud uses your data.

   Gemini returns a response similar to the following:

   There are a few ways to learn which datasets and tables are available to you in BigQuery. You can use the Google Cloud console to browse the public datasets that are available. You can use the bq command-line tool to list the datasets and tables in your project. You can make calls to the BigQuery REST API to get a list of the datasets and tables in your project.

7. To optionally reset your chat history, in the Gemini pane, click Reset Chat ().

Task 4. Prompt Gemini to explain SQL queries in a sales dataset

Gemini can help you work with SQL. For instance, if you work with SQL queries that other people wrote, Gemini in BigQuery can explain a complex query in plain language. Such explanations can help you understand the query syntax, underlying schema, and business context.

To prompt Gemini to explain an example SQL query, follow these steps:

1. In the Google Cloud console, select the Navigation menu (), and then select BigQuery.

2. Under Welcome to BigQuery Studio. Click SQL QUERY, to create a new SQL query.

3. In the query editor, paste the query that you want explained.

   For example, you might want to understand how data tables and queries are related in a sales dataset, and you might want help writing queries that use the dataset. In the following example query, you might understand which tables are being used, but other sections of the query might take you time to parse and understand.

   SELECT u.id as user_id, u.first_name, u.last_name, avg(oi.sale_price) as avg_sale_price FROM `bigquery-public-data.thelook_ecommerce.users` as u JOIN `bigquery-public-data.thelook_ecommerce.order_items` as oi ON u.id = oi.user_id GROUP BY 1,2,3 ORDER BY avg_sale_price DESC LIMIT 10

4. Select the query that you want Gemini to explain, and right-click on this selected query. In the menu, click Explain current selection.

   The SQL explanation appears in the Gemini pane.

   Using the example query from the previous step, Gemini returns an explanation similar to the following:

   The intent of this query is to find the top 10 users by average sale price. The query first joins the users and order_items tables on the user_id column. It then groups the results by user_id , first_name , and last_name, and calculates the average sale price for each group. The results are then ordered by average sale price in descending order, and the top 10 results are returned.

To verify the objective, click Check my progress. Prompt Gemini to explain SQL queries in a sales dataset.

Task 5. Generate a SQL query that groups sales by day and product

You can provide Gemini with a prompt to generate a SQL query based on your data's schema. Even if you're starting with no code, a limited knowledge of the data schema, or only a basic knowledge of SQL syntax, Gemini can suggest one or more SQL statements.

In this task, you generate a query that lists your top products for each day. This type of query is often complex, but you can automatically create a statement using Gemini. You then use tables in the thelook_ecommerce dataset and prompt Gemini to generate a query to calculate sales by order item and by product name.

Review the order_items and products tables the public dataset

1. In the Google Cloud console, select the Navigation menu (), and then select BigQuery.

2. In the navigation menu, click BigQuery Studio.

3. In the Explorer pane, click +ADD.

4. In the Add dialog, within the Search for data sources field, enter public datasets.

5. Press Enter. You see public datasets in the list.

6. Click public datasets. You see the list of public datasets in the Marketplace.

7. Search for thelook. You see TheLook Ecommerce public dataset appears in the list.

8. Click thelook Ecommerce, two times.

9. Click VIEW DATASET.

10. Expand the bigquery-public-data that is added to the explorer panel.

11. Scroll down and find thelook_ecommerce, then expand the dataset. You see the order_items and products tables listed.

12. Click on the order_items table. You see the data schema displayed.

13. Return to the explorer panel.

14. Click on the products table. You see the data schema displayed.

    Note: Reviewing the schema for tables before you run Queries based upon prompts in Gemini will help to avoid errors, and potential hallucinations.

Use a prompt to generate the query

1. Click to open a new untitled query tab.

2. In the query editor, enter the following prompt, and then press ENTER. The pound character (#) prompts Gemini to generate SQL.

   # select the sum of sales by date, and product_id casted to day from order_items, joined with products. Include the product name in the results. Round the total_sales field to two decimal places and order results by total_sales descending.

   Gemini suggests a SQL query similar to the one below. If you encounter any errors, please re-run the prompt, or you can execute the following command.

   # select the sum of sales by date, and product_id casted to day from order_items, joined with products. Include the product name in the results. Round the total_sales field to two decimal places and order results by total_sales descending. SELECT DATE(order_items.created_at) AS order_date, order_items.product_id, products.name AS product_name, ROUND(SUM(order_items.sale_price), 2) AS total_sales FROM `bigquery-public-data.thelook_ecommerce.order_items` AS order_items LEFT JOIN `bigquery-public-data.thelook_ecommerce.products` AS products ON order_items.product_id = products.id GROUP BY order_date, order_items.product_id, product_name ORDER BY total_sales DESC; Note: Gemini might suggest multiple SQL statements for your prompt.

3. To accept the suggested code, click Tab, and then click Run to execute the SQL statement. You can also scroll through the suggested SQL and accept specific words suggested in the statement.

4. In the Query results pane, view the query results.

To verify the objective, click Check my progress. Generate a SQL query that groups sales by day and product.

Task 6. Build a forecasting model and view results

In this task, you use BigQuery ML to build a forecasting model and query it using a Gemini prompt.

Build the model

You use the following example query with actual sales, which are used as an input to the model. The query is used as a part of creating the ML model.

1. To create a forecasting ML model, in the BigQuery SQL editor, run the following SQL:

   CREATE MODEL bqml_tutorial.sales_forecasting_model OPTIONS(MODEL_TYPE='ARIMA_PLUS', time_series_timestamp_col='date_col', time_series_data_col='total_sales', time_series_id_col='product_id') AS SELECT sum(sale_price) as total_sales, DATE(created_at) as date_col, product_id FROM `bigquery-public-data.thelook_ecommerce.order_items` AS t1 INNER JOIN `bigquery-public-data.thelook_ecommerce.products` AS t2 ON t1.product_id = t2.id GROUP BY 2, 3;

You can use Gemini to help you understand this query.

When the model is created, the Results pane displays a message similar to the following:

Query the model with a prompt

1. Click to open a new untitled query tab.

2. In the query editor, enter the following prompt, and then press ENTER. The pound character (#) prompts Gemini to generate SQL.

   # Use sales_forecasting_model from the bqml_tutorial dataset in my project to generate a forecast and return all the resulting data.

   Gemini suggests a SQL query similar to the one below. If you encounter any errors, please re-run the prompt, or you can execute the following command.

   # Use sales_forecasting_model from the bqml_tutorial dataset in my project to generate a forecast and return all the resulting data. SELECT * FROM ML.FORECAST(MODEL `bqml_tutorial.sales_forecasting_model`) Note: Gemini might suggest multiple SQL statements for your prompt.

3. To accept the suggested code, click Tab, and then click Run to execute the SQL statement. You can also scroll through the suggested SQL and accept specific words suggested in the statement.

4. In the Query results pane, view the query results.

To verify the objective, click Check my progress. Build a forecasting model and view results.

Congratulations!

In this lab you learned how to:

• Use Gemini to answer your questions about Google Cloud data analytics products and use cases.
• Prompt Gemini to explain and generate SQL queries in BigQuery.
• Build a machine learning (ML) model to forecast future periods.

Optional reading

Now that you have learned how to use Gemini to analyze your data with BigQuery, if you want to learn more about Gemini please refer to Write better prompts for Gemini in Google Cloud.

End your lab

When you have completed your lab, click End Lab. Qwiklabs removes the resources you’ve used and cleans the account for you.

You will be given an opportunity to rate the lab experience. Select the applicable number of stars, type a comment, and then click Submit.

The number of stars indicates the following:

• 1 star = Very dissatisfied
• 2 stars = Dissatisfied
• 3 stars = Neutral
• 4 stars = Satisfied
• 5 stars = Very satisfied

You can close the dialog box if you don't want to provide feedback.

For feedback, suggestions, or corrections, please use the Support tab.

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.