Write queries with Gemini assistance

This document describes how to use AI-powered assistance in Gemini in BigQuery to help you query your data with SQL queries and Python code. Gemini in BigQuery can generate queries and code, complete queries and code while you type, and explain queries.

---

To follow step-by-step guidance for this task directly in the Google Cloud console, click Guide me:

Guide me

---

Gemini for Google Cloud doesn't use your prompts or its responses as data to train its models without your express permission. For more information about how Google uses your data, see How Gemini for Google Cloud uses your data.

Note: To opt in to data sharing for Gemini in BigQuery features in Preview, see Help improve suggestions.

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 that you know how to query data in the BigQuery Studio environment or how to work with Python notebooks to analyze BigQuery data.

Before you begin

1. Ensure that Gemini in BigQuery is set up for your Google Cloud project. This step is normally done by an administrator. Gemini in BigQuery features might be turned off or unavailable until you complete the remaining steps in this section.

2. In the Google Cloud console, on the project selector page, select or create a Google Cloud project.

   Note: If you don't plan to keep the resources that you create in this procedure, create a project instead of selecting an existing project. After you finish these steps, you can delete the project, removing all resources associated with the project.

   Go to project selector

3. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

4. In the BigQuery toolbar, click pen_sparkGemini.

   

5. In the list of features, ensure the following features are selected:

   • Gemini in SQL query list:

     • Auto-completion (Preview). As you type in the query editor, Gemini can suggest logical next steps that are relevant to your current query's context, or it can help you iterate on a query.
     • Auto-generation. You can prompt Gemini in BigQuery using a natural language comment in the BigQuery query editor to generate a SQL query.
     • SQL generation tool. You can enter natural language text in a tool to generate a SQL query, with options to refine query results, choose table sources, and compare results.
     • Explanation. You can prompt Gemini in BigQuery to explain a SQL query using natural language.

   • Gemini in Python notebook list:

     • Code completion (Preview). Gemini provides contextually appropriate recommendations that are based on content in the notebook.
     • Code generation. You can prompt Gemini using a natural language statement or question to generate Python code.

6. To complete the tasks in this document, ensure that you have the required Identity and Access Management (IAM) permissions.

Required roles

To get the permissions that you need to write queries with Gemini assistance, ask your administrator to grant you the Gemini for Google Cloud User (roles/cloudaicompanion.user) IAM role on the project. For more information about granting roles, see Manage access to projects, folders, and organizations.

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.entitlements.get
• cloudaicompanion.instances.completeTask
• Explain SQL queries: cloudaicompanion.companions.generateChat
• Complete SQL or Python code: cloudaicompanion.instances.completeCode
• Generate SQL or Python code: cloudaicompanion.instances.generateCode

You might also be able to get these permissions with custom roles or other predefined roles.

For more information about IAM roles and permissions in BigQuery, see Introduction to IAM.

Generate a SQL query

As an early-stage technology, Gemini for Google Cloud products can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Gemini for Google Cloud products before you use it. For more information, see Gemini for Google Cloud and responsible AI.

To generate a SQL query based on your data's schema, you can provide Gemini in BigQuery 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 in BigQuery can generate SQL that can help you explore your data.

Use the SQL generation tool

The SQL generation tool lets you use natural language to generate a SQL query about your recently viewed or queried tables. You can also use the tool to modify an existing query, and to manually specify the tables for which you want to generate SQL.

To use the SQL generation tool, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. Next to the query editor, click pen_spark SQL generation tool.

   

3. In the Generate SQL with Gemini dialog, enter a natural language prompt about a table that you recently viewed or queried. For example, if you recently viewed bigquery-public-data.austin_bikeshare.bikeshare_trips table, you might enter the following:

    Show me the duration and subscriber type for the ten longest trips.
   

4. Click Generate.

   The generated SQL query is similar to the following:

   SELECT
       subscriber_type,
       duration_sec
     FROM
         `bigquery-public-data.san_francisco_bikeshare.bikeshare_trips`
   ORDER BY
       duration_sec DESC
   LIMIT 10;
   

   Note: Gemini in BigQuery might suggest different syntax each time that you enter the same prompt.

5. 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. You can then click Run to execute the suggested SQL query.
   • To edit your prompt, click Edit and then modify or replace your initial prompt. After you've edited your prompt, click Update to generate a new query.
   • To update the table sources that were used as context to generate the suggested SQL query, click Edit Table Sources, select the appropriate checkboxes, and then click Apply.
   • To view a natural language summary of the generated query, click Query Summary.
   • To refine the suggested SQL query, enter any refinements in the Refine field, and then click Refine. For example, enter limit to 1000 to limit the number of query results. To compare the changes to your query, select the Show diff checkbox.
   • To dismiss a suggested query, close the SQL generation tool.

Turn off the SQL generation tool

To learn how to turn off the SQL generation tool, see Turn off Gemini query assistant features.

Generate SQL from a comment

You can generate SQL in the query editor by describing the query that you want in a comment.

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, click SQL query.

3. In the query editor, write a SQL comment about a table you have recently viewed or queried. For example, if you recently viewed the bigquery-public-data.austin_bikeshare.bikeshare_trips table, then you might write the following comment:

   # Show me the duration and subscriber type for the ten longest trips.
   

4. Press Enter (Return on macOS).

   The suggested SQL query is similar to the following:

   # Show me the duration and subscriber type for the ten longest trips
   
   SELECT
     duration_sec,
     subscriber_type
     AVG(duration_minutes) AS average_trip_length
   FROM
     `bigquery-public-data.austin_bikeshare.bikeshare_trips`
   ORDER BY
     duration_sec
   LIMIT 10;
   

5. To accept the suggestion, press Tab.

Tips for SQL generation

The following tips can improve suggestions that Gemini in BigQuery provides:

• To manually specify which tables to use, you can include the fully qualified table name 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. 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.
• When you generate SQL from a comment, you can format your prompt over multiple lines by prefixing each line with the # character.
• Column descriptions are considered when you generate SQL queries. To improve accuracy, add column descriptions to your schema. For more information about column descriptions, see Column descriptions in "Specify a schema."

Gemini and BigQuery data

Gemini in BigQuery can access the metadata of the tables that you have permission to access. This metadata 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.

Complete a SQL query

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

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 that are relevant to your current query's context, or it can help you iterate on a query.

To try SQL completion with Gemini in BigQuery, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. 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 isn't grouped or aggregated. It's not uncommon to need some help getting a query just right.

3. At the end of the line for subscriber_type, press Space.

   The suggested refinements to the query might end in text that's similar to the following:

   GROUP BY
     subscriber_type, hour_of_day;
   

   You can also press Enter (Return on macOS) to generate suggestions.

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

To get an explanation for a SQL query, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the query editor, open or paste a query that you want explained.

3. Highlight the query that you want Gemini in BigQuery to explain.

4. Click astrophotography_mode Gemini, and then click Explain this query.

   

   The SQL explanation appears in the Gemini pane.

Generate Python code

You can ask Gemini in BigQuery to generate Python code by using a prompt (a natural language statement or question). Gemini in BigQuery responds with one or more Python code suggestions.

In the following example, you generate code for a BigQuery public dataset, bigquery-public-data.ml_datasets.penguins.

1. Go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the drop-down arrow next to SQL query, and then click Python notebook.

   The new notebook opens, containing cells that show example queries against the bigquery-public-data.ml_datasets.penguins public dataset.

3. To insert a new code cell, in the toolbar, click Code. The new code cell contains the message Start coding or generate with AI.

4. In the new code cell, click generate.

5. In the Generate editor, enter the following natural language prompt:

   Using bigquery magics, query the `bigquery-public-data.ml_datasets.penguins` table
   

6. Press Enter (Return on macOS).

   The suggested Python code is similar to the following:

   %%bigquery
   SELECT *
   FROM `bigquery-public-data.ml_datasets.penguins`
   LIMIT 10
   

7. To run the code, press Run cell.

Complete Python code

Preview

This feature is subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the Service Specific Terms. Pre-GA features are available "as is" and might have limited support. For more information, see the launch stage descriptions.

Python code completion attempts to provide contextually appropriate recommendations that are based on content in the query editor. As you type, Gemini in BigQuery can suggest logical next steps that are relevant to your current code's context, or it can help you iterate on your code.

To try Python code completion with Gemini in BigQuery, follow these steps:

1. Go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the tab bar of the query editor, click the drop-down arrow next to SQL query, and then click Python notebook.

   A new notebook opens, containing cells that show example queries against the bigquery-public-data.ml_datasets.penguins public dataset.

3. In the editor, begin typing Python code. For example %%bigquery. Gemini in BigQuery suggests code inline while you type.

4. To accept the suggestion, press Tab.

Turn off Gemini query assistant features

To turn off specific features in Gemini in BigQuery, do the following:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_sparkGemini.

   

3. In the list, clear the query assistant features that you want to turn off.

To learn how to turn off Gemini in BigQuery, see Turn off Gemini in BigQuery.

Provide feedback

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_sparkGemini.

   

3. Click Send feedback.

Help improve suggestions

You can help improve Gemini suggestions by sharing with Google the prompt data that you submit to features in Preview.

To share your prompt data, follow these steps:

1. In the Google Cloud console, go to the BigQuery Studio page.

   Go to BigQuery Studio

2. In the BigQuery toolbar, click pen_sparkGemini.

   

3. Select Share data to improve Gemini in BigQuery.

4. In the Data Use Settings dialog, update your data use settings.

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.

Pricing

For details about pricing for this feature, see Gemini in BigQuery pricing overview.

Quotas and limits

For information about quotas and limits for this feature, see Quotas for Gemini in BigQuery.

What's next

• Read Gemini for Google Cloud overview.
• Learn how Gemini for Google Cloud uses your data.