Write queries with Duet AI assistance

Preview — Duet AI in BigQuery

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, Duet AI can generate output that seems plausible but is factually incorrect. We recommend that you validate all output from Duet AI before you use it. For more information, see Duet AI in Google Cloud and responsible AI.

This document describes how 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

In addition to setting up Duet AI for a project, you enable or disable specific Duet AI features in BigQuery.

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 Duet AI is set up for your Google Cloud user account and project.

3. Enable specific Duet AI features in BigQuery:

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

      Go to BigQuery

   2. In the taskbar, click pen_spark Duet AI to view Duet AI features in BigQuery:

      

   3. In the Duet AI in BigQuery SQL editor list, select the Duet AI features that you want to enable—for example, SQL completion and SQL generation.

   To disable Duet AI features in BigQuery, repeat these steps, and then clear the Duet AI features that you want to disable. If you disable SQL generation or completion, you can still prompt SQL generation or completion for your query by pressing Ctrl + Shift + Space.

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.

To get your data schema, Duet AI needs the dataset and table name, which must start and end with a backtick (`). In the following example, you can 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 page.

   Go to BigQuery

2. In the navigation menu, click BigQuery Studio.

3. In the SQL query editor, click Compose a new query.

4. In the query editor, enter one of the following natural language prompts, and then press Enter.

   Example 1

   # Using `bigquery-public-data.austin_bikeshare.bikeshare_trips`, calculate the
   # average trip length by subscriber type.
   

   Example 2

   # Write a query that creates a table in the dataset `1234` with a string column called "name"
   

   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
   

   Duet AI SQL generation suggests one or more SQL statements that answer your prompt. To view suggestions, hold the pointer over the suggested SQL. You can also click through suggested SQL or accept words suggested in the statement by pressing Control+Right Arrow.

5. To accept Duet AI code completion suggestions, press Tab.

6. To run the query, click Run.

Tips for SQL generation

The following tips can improve suggestions that Duet AI in BigQuery provides:

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

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

   Go to BigQuery

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. To trigger SQL completion, press Enter 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;
   

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

   Go to BigQuery

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

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

   

The SQL explanation appears in the Duet AI pane.

Duet AI and BigQuery data

When you enter a Duet AI prompt in BigQuery, the information schema for the dataset that's referenced in the prompt is sent to Duet AI. The information sent can include column names, data types, and column descriptions. The data in tables, views, and models remains in BigQuery and isn't sent to Duet AI.

Provide feedback

You can provide feedback about Duet AI suggestions.

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

What's next

• For information about Duet AI in Google Cloud, see Duet AI in Google Cloud overview.
• For information about the Duet AI data policy, see How Duet AI in Google Cloud uses your data.