Write queries with Duet AI assistance

To request access to this preview feature, complete the Duet AI in BigQuery Preview form. To provide feedback or request support with this feature, send email to duet-ai-bq-feedback-external@google.com.

You can use Duet AI, an AI-powered collaborator in Google Cloud, to help you do the following in BigQuery:

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

Duet AI doesn't use your prompts or its responses as data to train its model. For more information, see How Duet AI in Google Cloud uses your data.

This document is intended for data analysts, data scientists, and data developers who work with SQL queries. It assumes you have knowledge of how to query data in the BigQuery SQL workspace.

Before you begin

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

    Go to project selector

  2. Ensure that Duet AI is set up for your Google Cloud user account and project.

Generate a SQL query

You can provide Duet AI a natural language statement (or 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 Google SQL syntax, Duet AI can suggest one or more SQL statements.

You begin SQL generation by using the # character in the BigQuery query editor. You then follow the prompt with a natural language statement or question about the data that you want. Duet AI then suggests one or more SQL statements to help you analyze your data.

In the following example, you generate a query for a BigQuery public dataset, 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 Compose a new query.

  3. Ensure that Duet AI is set up for your Google Cloud user account and project. Duet AI buttons aren't visible until setup is complete.

  4. In the taskbar, click pen_spark Duet AI and select Auto-generation if it isn't already selected.

    Duet AI button in the BigQuery query editor.

  5. 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.
    
  6. Press Enter (Return on macOS).

    Duet AI 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
    
  7. To accept the suggestion, press Tab.

Duet AI 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"
    

    Duet AI 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 Duet AI in BigQuery provides:

  • To get a data schema, Duet AI needs the dataset and table name, which must start and end with a backtick (`).
  • You can include column descriptions in your table schema. Duet AI in BigQuery uses column description information with the prompt preamble that accompanies the natural language statement.
  • If the column names or their semantic relationships are unclear or complex, then you can provide context in the prompt to guide Duet AI 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.

Duet AI and BigQuery data

Duet AI 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. Duet AI in BigQuery cannot access the data in your tables, views, or models. For more information on how Duet AI uses your data, see How Duet AI in Google Cloud uses your data.

Complete a SQL query

SQL completion attempts to provide contextually appropriate recommendations that are based on content in the query editor. As you type, Duet AI can suggest logical next steps relevant to your current query's context or help you iterate on a query.

To try SQL completion with Duet AI, 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. Ensure that Duet AI is set up for your Google Cloud user account and project. Duet AI buttons aren't visible until setup is complete.

  3. In the taskbar, click pen_spark Duet AI and select Auto-completion if it isn't already selected.

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

  5. Press Enter (Return on macOS) or Space.

    Duet AI suggests refinements to the query that might end in text similar to the following:

    GROUP BY
    subscriber_type, hour_of_day;
    
  6. 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 Duet AI 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.

  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. Ensure that Duet AI is set up for your Google Cloud user account and project. Duet AI buttons aren't visible until setup is complete.

  3. In the taskbar, click pen_spark Duet AI and select Explanation if it isn't already selected.

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

  5. Highlight the query that you want Duet AI to explain, and then click pen_spark Explain this query.

    The Explain this query icon and text highlighted in the left column of the BigQuery query editor.

The SQL explanation appears in the Duet AI pane.

Disable Duet AI features

To disable Duet AI features in BigQuery, do the following:

  1. In the taskbar of the SQL query editor, click pen_spark Duet AI.

  2. Clear the Duet AI features that you want to disable.

Provide feedback

You can provide feedback about Duet AI suggestions.

  • To provide feedback, in the taskbar, click pen_spark Duet AI, and then select Send feedback.

Help improve suggestions

You can help improve Duet AI 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 taskbar, click pen_spark Duet AI.

  2. Select Share data to improve Duet AI.

  3. Update your data use settings in the data use setting 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 Duet AI in Google Cloud Trusted Tester Program .

What's next