This tutorial describes how you can use Gemini for Google Cloud, an AI-powered collaborator for Google Cloud, to analyze data. In the tutorial, you use Gemini in BigQuery to analyze and predict product sales.
This tutorial assumes that you're familiar with SQL and basic data analytics tasks. Knowledge of Google Cloud products is not assumed. If you're new to BigQuery, see the BigQuery quickstarts.
Objectives
- 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.
Costs
This tutorial uses the following billable components of Google Cloud:
Use the Pricing Calculator to estimate your costs based on your projected usage.
Before you begin
-
In the Google Cloud console, on the project selector page, select or create a Google Cloud project.
- Ensure that you have set up Gemini in BigQuery in your Google Cloud project.
In the Google Cloud console, go to the BigQuery page.
Create a dataset that's named
bqml_tutorial
. You use the dataset to store database objects, including tables and models.Enable the Gemini features in BigQuery that you need to complete this tutorial:
To view Gemini features in BigQuery, in the toolbar, click pen_spark Gemini.
In the Gemini in BigQuery SQL editor list, select all of the following options:
- Auto completion
- Auto generation
- Explanation
To disable Gemini features in BigQuery, deselect the Gemini features that you want to disable.
Use Gemini to analyze your data
Gemini can help you know what data you can access for analysis, and how to analyze that 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?"
Gemini can also provide information about how to analyze your data. For that type of help, you might send Gemini prompts such as the following:
- "How do I create a time series forecasting model in BigQuery?"
- "How do I load different types of data into BigQuery?"
To prompt Gemini to answer questions about your data, follow these steps:
In the Google Cloud console, go to the BigQuery page.
In the Google Cloud console toolbar, click spark Open Gemini.
In the Gemini pane, enter a prompt like
How do I learn which datasets and tables are available to me in BigQuery?
.Click send Send prompt.
Learn how and when Gemini for Google Cloud uses your data.
Gemini returns a response similar to the following:
To learn which datasets and tables are available to you in BigQuery, you can use the Google Cloud console, the Google Cloud CLI, or the BigQuery API. ...
Optional: To reset your chat history, in the Gemini pane, click the delete icon, and then click Reset chat.
Use Gemini to understand and write SQL in BigQuery
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.
Prompt Gemini to explain SQL queries in a sales dataset
To prompt Gemini to explain an example SQL query, follow these steps:
In the Google Cloud console, go to the BigQuery page.
In the query editor, open or 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
Highlight the query that you want Gemini to explain, and then click pen_spark Explain this query.
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.
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 the following example, you generate a query that lists your top products for each day.
This type of query is often complex, but using Gemini, you can
automatically create a statement. 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.
To prompt Gemini to generate a query that lists your top products, follow these steps:
In the Google Cloud console, go to the BigQuery page.
In the navigation menu, click BigQuery Studio.
Click
Compose a new query. The Explorer pane automatically loads the selected database.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 casted to day from bigquery-public-data.thelook_ecommerce.order_items joined with bigquery-public-data.thelook_ecommerce.products
Gemini suggests a SQL query similar to the following:
SELECT sum(sale_price), DATE(created_at), 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
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.
In the Query results pane, view the query results.
Build a forecasting model and view results
In this section, you use BigQuery ML to do the following:
- Use a trend query to build a forecasting model.
- Use Gemini to explain and help you write a query to view results of the forecasting 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.
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:
This statement will replace the model named sales_forecasting_model. Depending on the type of model, this may take several hours to complete.
In the Gemini pane, enter a prompt for Gemini to help you write a query to get a forecast from the model when it's completed. For example, enter
How can I get a forecast in SQL from the model?
Based on the context of the prompt, Gemini returns an example of an ML model that forecasts sales:
To get a forecast in SQL from the model, you can use the following query: SELECT * FROM ML.FORECAST(MODEL `PROJECT_ID.DATASET_ID.MODEL_NAME`, STRUCT( 7 AS horizon, 0.95 AS confidence_level ) )
In the Gemini pane, copy the SQL query.
In the BigQuery SQL editor, paste and then run the SQL query.
Clean up
To avoid incurring charges to your Google Cloud account for the resources used in this tutorial, you can delete the Google Cloud project that you created for this tutorial. Alternatively, you can delete the individual resources.
- In the Google Cloud console, go to the Manage resources page.
- In the project list, select the project that you want to delete, and then click Delete.
- In the dialog, type the project ID, and then click Shut down to delete the project.
Delete your dataset
Deleting your project removes all datasets and all tables in the project. If you prefer to reuse the project, then you can delete the dataset that you created in this tutorial.
In the Google Cloud console, open the BigQuery page.
In the navigation, select the
bqml_tutorial
dataset that you created.To delete the dataset, the table, and all of the data, click Delete dataset.
To confirm deletion, in the Delete dataset dialog, type the name of your dataset (
bqml_tutorial
), and then click Delete.
What's next
Learn about Gemini quotas and limits.
Learn about locations for Gemini.