Write queries with Gemini assistance

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.

As an early-stage technology, Gemini can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Gemini 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 do the following in BigQuery:

• Generate a SQL query.
• Complete a SQL query.
• Explain a SQL query.
• Generate Python code.
• Complete Python code.

Gemini 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

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

2. Ensure that Gemini is set up for your Google Cloud project. Gemini buttons aren't visible until setup is complete.
3. 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.

Use the Help me code tool

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:

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

   Go to BigQuery

2. In the BigQuery Studio query editor, click Create SQL query.

3. In the toolbar, click pen_sparkGemini and select Code generation tool if it isn't already selected.

   

4. Next to the query editor, click pen_spark Help me code.

   

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

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

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

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

Disable the Help me code tool

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.

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

   Go to BigQuery Studio

   The remaining steps will appear automatically in the Google Cloud console.

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

3. In the toolbar, click pen_spark Gemini and select Auto-generation if it isn't already selected.

   

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

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

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

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:

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

   Go to BigQuery Studio

   The remaining steps will appear automatically in the Google Cloud console.

2. In the toolbar, click pen_spark Gemini and select Auto-completion if it isn't already selected.

   

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

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

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

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

   Go to BigQuery Studio

   The remaining steps will appear automatically in the Google Cloud console.

2. In the toolbar, click pen_spark Gemini and select Explanation if it isn't already selected.

   

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

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

1. Go to the BigQuery Studio page.

   Go to BigQuery

2. In the toolbar, click pen_sparkGemini, and in the Gemini in Python notebooks section, select Code generation if it isn't already selected.

   

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

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

5. In the new code cell, click generate.

6. In the code editor, enter the following natural language prompt:

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

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

1. Go to the BigQuery Studio page.

   Go to BigQuery

2. In the toolbar, click pen_sparkGemini, and in Gemini in Python notebook section, select Code completion if it isn't already selected.

   

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

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

1. In the toolbar of the SQL query editor, click pen_spark Gemini.

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

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.

What's next

• For information about Gemini for Google Cloud, see Use Gemini for AI assistance and development.
• For information about the Gemini data policy, see How Gemini for Google Cloud uses your data.