Write queries with Gemini assistance
You can use
Gemini for Google Cloud
, which
offers AI-powered assistance, to help you do the following in BigQuery:
- Generate a SQL query.
- Complete a SQL query.
- Explain a SQL query.
- Generate Python code.
- Complete Python code.
Gemini for Google Cloud doesn't use your prompts or its responses as data to
train its models. For more information, see
How Gemini for Google Cloud uses your data
.
Only English
language prompts are supported for Gemini in
BigQuery.
This document is intended for data analysts, data scientists, and data
developers who work with SQL queries and
Colab Enterprise notebooks in
BigQuery
. It assumes you have
knowledge of how to query data in the BigQuery SQL workspace or
how to work with notebooks to analyze BigQuery data using
Python.
Before you begin
-
In the Google Cloud console, on the project selector page,
select or
create a Google Cloud project
.
Go to project selector
- Ensure that
Gemini is set up for your Google Cloud project
.
Gemini buttons aren't visible until setup is complete.
- To use Gemini with Python code,
enable
BigQuery Studio for asset management
.
Required roles
To get the permissions that you need to write queries with Gemini assistance,
ask your administrator to grant you the
Cloud AI Companion User
(
roles/cloudaicompanion.user
) IAM role on project.
For more information about granting roles, see
Manage access
.
This predefined role contains
the permissions required to write queries with Gemini assistance. To see the exact permissions that are
required, expand the
Required permissions
section:
Required permissions
The following permissions are required to write queries with Gemini assistance:
-
cloudaicompanion.companions.generateCode
-
cloudaicompanion.entitlements.get
You might also be able to get
these permissions
with
custom roles
or
other
predefined roles
.
Generate a SQL query
To generate a SQL query based on your data's schema, you can provide
Gemini with a natural language statement or question (also known
as a
prompt
). Even if you're starting with no code, a limited knowledge of the
data schema, or only a basic knowledge of GoogleSQL syntax,
Gemini can generate one or more SQL statements that can help you
explore your data.
The
Help me code
tool lets you use natural language to generate a SQL query
that you can then run in BigQuery Studio.
To use the
Help me code
tool, follow these steps:
In the Google Cloud console, go to the
BigQuery
page.
Go to BigQuery
In the BigQuery Studio query editor, click
add_box
Create SQL query
.
In the toolbar, click
pen_spark
Gemini
and select
Code generation tool
if it isn't already selected.
Next to the query editor, click
pen_spark
Help me code
.
In the
Help me code
tool, enter a prompt. For example:
Using `bigquery-public-data.austin_bikeshare.bikeshare_trips`, show me the
ten longest trip lengths by subscriber type.
Click
Generate
.
Gemini generates a SQL query that's similar to the following:
SELECT subscriber_type,
MAX(duration_minutes) AS longest_trip_duration
FROM `bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY subscriber_type
ORDER BY longest_trip_duration DESC
LIMIT 10;
Review the generated SQL query and take any of the following actions:
- To accept the generated SQL query, click
Insert
to
insert the statement into the query editor. Then click
Run
to
execute the suggested SQL query.
- To ask Gemini to generate a new query, click
Edit
.
After you've edited your prompt, click
Update
. You can then decide
to accept the new generated statement or dismiss the suggestion.
- To dismiss the suggestion, close the
Help me code
dialog.
To ask Gemini to generate a new query using a specific table
source, click
Edit table sources
, select the new table source, and then
click
Apply
. You can accept the new statement or dismiss the suggestion.
To learn how to disable the
Help me code
tool, see
Disable Gemini features
.
Prompt to generate SQL queries
To generate SQL, type the
#
character in the BigQuery
query editor followed by a natural language statement or question about the
information that you want. Gemini reviews your recent queries
to find table schema that might be relevant to your prompt. If you know
the table that you want to use, then you can specify the table name in
backticks (
`
) in your prompt.
In the following example, you generate a query for a BigQuery
public table,
bigquery-public-data.austin_bikeshare.bikeshare_trips
.
In the Google Cloud console, go to the
BigQuery Studio
page.
Go to BigQuery Studio
school
The remaining steps will appear
automatically in the Google Cloud console.
In the SQL query editor, click
add_box
Create a new query
.
In the toolbar, click
pen_spark
Gemini
and select
Auto-generation
if it isn't already selected.
In the query editor, enter the following natural language prompt:
# Using `bigquery-public-data.austin_bikeshare.bikeshare_trips`, calculate the
# average trip length by subscriber type.
Press
Enter
(
Return
on macOS).
Gemini suggests a SQL query similar to the following:
SELECT
subscriber_type,
AVG(duration_minutes) AS average_trip_length
FROM
`bigquery-public-data.austin_bikeshare.bikeshare_trips`
GROUP BY
subscriber_type
To accept the suggestion, press
Tab
.
View additional suggestions
Gemini might suggest more than one SQL statement that answers
your prompt. For example:
In the query editor, enter the following natural language prompt,
and then press
Enter
(
Return
on macOS):
# Write a query that creates a table in the dataset `1234` with a string column called "name"
Gemini suggests a SQL query.
To see if there are additional suggestions, hold the pointer over the
suggested SQL query.
Click through any additional suggestions, and then do one of the following:
- To accept a suggestion, press
Tab
.
- To accept specific words, press
Control+Right Arrow
(
Command+Right Arrow
on macOS).
- To dismiss suggestions, press
Esc
.
Tips for SQL generation
The following tips can improve suggestions that Gemini in
BigQuery provides:
- To specify a data schema, provide the fully qualified table
name enclosed in backticks (
`
), such as
`
PROJECT
.
DATASET
.
TABLE
`
.
- If the column names or their semantic relationships are unclear or complex,
then you can provide context in the prompt to guide Gemini towards
the answer that you want. This technique is known as
prompt engineering
. For
example, to encourage a generated query to reference a column name, describe
the column name and its relevance to the answer that you want. To encourage
an answer that references complex terms like
lifetime value
or
gross
margin
, describe the concept and its relevance to your data to improve SQL
generation results.
- Prompts can extend over multiple lines in the query editor, but each line
must begin with a
#
character.
Gemini and BigQuery data
Gemini in BigQuery can access the metadata of the
tables that you have permission to access. This can include the table names,
column names, data types, and column descriptions. Gemini in
BigQuery cannot access the data in your tables, views, or
models. For more information on how Gemini uses your data, see
How Gemini for Google Cloud uses your
data
.
Disable SQL code generation
To learn how to disable the SQL code generation in BigQuery, see
Disable Gemini features
.
Complete a SQL query
SQL completion attempts to provide contextually appropriate recommendations that
are based on content in the query editor. As you type, Gemini
can suggest logical next steps relevant to your current query's context or help
you iterate on a query.
To try SQL completion with Gemini, follow these steps:
In the Google Cloud console, go to the
BigQuery Studio
page.
Go to BigQuery Studio
school
The remaining steps will appear
automatically in the Google Cloud console.
In the toolbar, click
pen_spark
Gemini
and select
Auto-completion
if it isn't already selected.
In the query editor, copy the following:
SELECT
subscriber_type
, EXTRACT(HOUR FROM start_time) AS hour_of_day
, AVG(duration_minutes) AS avg_trip_length
FROM
`bigquery-public-data.austin_bikeshare.bikeshare_trips`
An error message states that
subscriber_type
is neither grouped nor
aggregated. It's not uncommon to need some help getting a query just right.
Press
Enter
(
Return
on macOS) or
Space
.
Gemini suggests refinements to the
query that might end in text similar to the following:
GROUP BY
subscriber_type, hour_of_day;
To accept the suggestion, press
Tab
, or hold the
pointer over the suggested text and click through alternate suggestions. To
dismiss a suggestion, press
ESC
or continue typing.
Explain a SQL query
You can prompt Gemini in BigQuery to explain a
SQL query in natural language. This explanation can help you understand a
query whose syntax, underlying schema, and business context might be difficult
to assess due to the length or complexity of the query.
Additional required permissions
In addition to the
permissions required to write queries with Gemini
,
to explain SQL you must have the
cloudaicompanion.companions.generateChat
permission. This permission is included in the
Cloud AI Companion User
(
roles/cloudaicompanion.user
) IAM role.
Explain SQL queries
To explain a SQL query, follow these steps:
In the Google Cloud console, go to the
BigQuery Studio
page.
Go to BigQuery Studio
school
The remaining steps will appear
automatically in the Google Cloud console.
In the toolbar, click
pen_spark
Gemini
and select
Explanation
if it isn't already selected.
In the query editor, open or paste the query that you want explained.
Highlight the query that you want Gemini to explain, and then
click
astrophotography_mode
Explain this query
.
The SQL explanation appears in the
Gemini
pane.
Generate Python code
You can prompt Gemini with a natural language statement or
question to generate Python code.
Gemini responds with one or more Python code suggestions.
Additional required permissions
In addition to
permissions required to write queries with
Gemini
, you must
have the
cloudaicompanion.instances.generateCode
permission to generate Python code. This permission is included in the
Cloud AI Companion User
(
roles/cloudaicompanion.user
) IAM role.
Use Gemini to generate Python code
In the following example, you generate code for a BigQuery
public dataset,
bigquery-public-data.ml_datasets.penguins
.
Go to the
BigQuery Studio
page.
Go to BigQuery
In the toolbar, click
pen_spark
Gemini
, and in the
Gemini in Python notebooks
section, select
Code generation
if it isn't already selected.
In the tab bar of the editor pane, click the
arrow_drop_down
drop-down arrow next to the
+
sign, and then click
Create Python notebook
:
The new notebook opens, containing cells that show example queries against
the
bigquery-public-data.ml_datasets.penguins
public dataset.
In the toolbar, click
+ Code
to insert a new code cell. A new code cell
appears that reads:
Start coding or generate with AI.
In the new code cell, click
generate
.
In the code editor, enter the following natural language prompt:
Using bigquery magics query the `bigquery-public-data.ml_datasets.penguins` table
Press
Enter
(
Return
on macOS).
Gemini suggests Python code similar to the following:
%%bigquery
SELECT *
FROM `bigquery-public-data.ml_datasets.penguins`
LIMIT 10
Python code completion
Python code completion attempts to provide contextually appropriate
recommendations that are based on content in the query editor. As you type,
Gemini can suggest logical next steps relevant to your current
code's context or help you iterate on your code.
Additional required permissions
In addition to the
permissions required to write queries with
Gemini
, you must
have the
cloudaicompanion.instances.completeCode
permission to generate Python code. This permission is included in the
Cloud AI Companion User
(
roles/cloudaicompanion.user
) IAM role.
Use Gemini to complete Python code
To try Python code completion with Gemini, follow these steps:
Go to the
BigQuery Studio
page.
Go to BigQuery
In the toolbar, click
pen_spark
Gemini
, and in
Gemini in Python notebook
section, select
Code completion
if it isn't already selected.
In the tab bar of the editor pane, click the
arrow_drop_down
drop-down arrow next to the
+
sign, and then click
Create Python notebook
:
The new notebook opens, containing cells that show example queries against
the
bigquery-public-data.ml_datasets.penguins
public dataset.
In the code editor, begin typing Python code. Gemini
suggests code inline while you type. To accept the suggestion, press
Tab
.
Disable Gemini features
To disable Gemini features in BigQuery, do the
following:
In the toolbar of the SQL query editor, click
pen_spark
Gemini
.
Clear the Gemini features that you want to disable.
Provide feedback
You can provide feedback about Gemini suggestions.
- To provide feedback, in the toolbar, click
pen_spark
Gemini
,
and then select
Send feedback
.
Help improve suggestions
You can help improve Gemini suggestions by sharing your prompt
data with Google. To share your prompt data, follow these steps:
In the Google Cloud console on the
BigQuery Studio
page, in the
toolbar, click
pen_spark
Gemini
.
Select
Share data to improve Gemini
.
Update your data use settings in the data use settings dialog.
Data sharing settings apply to the entire project and can only be set by a
project administrator with the
serviceusage.services.enable
and
serviceusage.services.list
IAM permissions. For more
information about data use in the Trusted Tester Program, see
Gemini
for Google Cloud Trusted Tester Program
.
What's next