Write queries with Gemini assistance

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.

You can use Gemini for Google Cloud, which offers AI-powered assistance, to help you query your data with SQL queries and Python code. Gemini in BigQuery can generate queries, complete code while you type, and explain queries.

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

1. Ensure that Gemini is set up for your Google Cloud project. Gemini in BigQuery features might be disabled or unavailable until setup is complete. These steps are normally done by an administrator.

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

   Go to BigQuery

4. In the toolbar, click the pen_sparkGemini drop-down icon.

   

5. In the dropdown list of features, select the features that you want to enable. Available features include the following:

   • Gemini in SQL query:
     • 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 help you iterate on a query.
     • Auto-generation: You can prompt Gemini in BigQuery with 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 in natural language.
   • Gemini in Python notebook:
     • Code completion (Preview): Gemini provides contextually appropriate recommendations that are based on content in the notebook.
     • Code generation: You can prompt Gemini with a natural language statement or question to generate Python code.

Required permissions

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 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.companions.generateCode
• cloudaicompanion.entitlements.get
• cloudaicompanion.instances.completeTask
• To explain SQL queries: cloudaicompanion.companions.generateChat
• To complete Python code: cloudaicompanion.instances.completeCode
• To generate Python code: cloudaicompanion.instances.generateCode

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.

Use the SQL generation tool

The SQL generation tool lets you use natural language to generate a SQL query that you can then run in BigQuery Studio. You can also use the tool to refine query results, specify a table, and compare results within the tool.

To use the SQL generation tool, follow these steps:

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

   Go to BigQuery

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

   

3. To the left of the BigQuery Studio query editor, click the pen_spark SQL generation tool icon to open the tool.

4. In the SQL generation tool, enter a prompt. For example:

    Using `bigquery-public-data.austin_bikeshare.bikeshare_trips`, show me the
    ten longest trip lengths by subscriber type.
   

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

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

6. 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 view the new query.
   • To update the table sources that were used as context to generate the suggested SQL query, click Edit Table Sources, select the table sources by marking checkboxes, and then click Apply.
   • To view a natural language summary of the generated query, click Query Summary.
   • To refine the suggested SQL query, type any refinements in the Refine input box and then click the apply icon to refine your query. For example, type limit to 1000 to limit the number of query results. To compare the changes to your query, select Show diff.
   • To dismiss the suggestion without inserting the generated query, close the SQL generation tool.

Disable the SQL generation tool

To learn how to disable the SQL generation 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. To generate SQL, type the # or -- in the BigQuery query editor followed by a natural language statement or question about the information that you want.

In the following example, you generate a query for a BigQuery public table, bigquery-public-data.austin_bikeshare.bikeshare_trips.

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

   Go to BigQuery Studio

   The remaining steps appear in the Google Cloud console.

2. In the SQL query editor, click Create a new query.

   

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

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

5. To accept the suggestion, press Tab.

View additional suggestions

Gemini might suggest more than one SQL statement that answers your prompt. For example:

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

2. To see if there are additional suggestions, hold the pointer over the suggested SQL query.

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

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

To try SQL completion with Gemini, follow these steps:

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

   Go to BigQuery Studio

   The remaining steps appear in the Google Cloud console.

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 is neither grouped nor aggregated. It's not uncommon to need some help getting a query just right.

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

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.

Explain SQL queries

To explain a SQL query, follow these steps:

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

   Go to BigQuery Studio

   The remaining steps appear in the Google Cloud console.

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

3. Highlight the query that you want Gemini to explain, and then click astrophotography_mode Gemini.

   

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.

Use Gemini to generate Python code

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

2. In the tab bar of the editor pane, click the 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.

3. In the toolbar, click + Code to insert a new code cell. A new code cell appears that reads: Start coding or generate with AI.

4. In the new code cell, click generate.

5. In the code 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).

   Gemini suggests Python code similar to the following:

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

7. Run the code, press Enter.

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 can suggest logical next steps relevant to your current code's context or help you iterate on your code.

Use Gemini to complete Python code

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

1. Go to the BigQuery Studio page.

   Go to BigQuery

2. In the tab bar of the editor pane, click the drop-down arrow next to the + sign and then click Create 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 code editor, begin typing Python code. For example %%bigquery. Gemini suggests code inline while you type. To accept the suggestion, press Tab.

Disable Gemini query assistant features

To disable Gemini features in BigQuery, do the following:

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

   Go to BigQuery

2. In the toolbar, click the pen_spark Gemini drop-down icon.

![Gemini button in the BigQuery toolbar.](/bigquery/images/duet-ai-assistant-link.png){: class="screenshot" } 

1. In the dropdown list of features, clear the Gemini in BigQuery query assistant features that you want to disable.

   • Gemini in SQL query:
     • Auto-completion (Preview): As you type, Gemini can suggest logical next steps that are relevant to your current query's context or help you iterate on a query.
     • Auto-generation: You can provide Gemini with a natural language prompt to generate SQL syntax that answers business questions.
     • SQL generation tool: Use natural language to generate and iterate on SQL query results based on your organization's data.
     • Explanation: You can prompt Gemini in BigQuery to explain a SQL query in natural language.
   • Gemini in Python notebook:
     • Code completion (Preview): Gemini provides contextually appropriate recommendations that are based on content in the query editor.
     • Code generation: You can prompt Gemini with a natural language statement or question to generate Python code.

To learn how to disable the Gemini in BigQuery feature, see Disable Gemini products.

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 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 on the BigQuery Studio page, in the toolbar, click pen_spark Gemini.

2. Select Share data to improve Gemini.

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

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

• For information about Gemini for Google Cloud, see Gemini for Google Cloud overview.
• For information about the Gemini data policy, see How Gemini for Google Cloud uses your data.