Write SQL with Gemini assistance

Preview — Gemini in Bigtable

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.

This document describes how you can use Gemini Code Assist to get AI-powered assistance with generating SQL queries using natural language prompts in Bigtable.

Learn how and when Gemini for Google Cloud uses your data.

This document is intended for database administrators and data engineers who are familiar with Bigtable, SQL, and data analysis. If you're new to Bigtable, see Quickstart: Create a Bigtable instance by using the Google Cloud console.

Note: Coding assistance is part of Gemini Code Assist and is available at no charge until it's included in Gemini Code Assist Standard edition. This change will be communicated at a later date. At that time, you will need to acquire a Gemini Code Assist Standard edition license to continue to use coding assistance in Bigtable Studio.

Limitations

• You can't use Gemini in Bigtable to explain SQL queries in natural language.

• Gemini in Bigtable might generate queries that are not valid in GoogleSQL for Bigtable or queries that use the following unsupported statements or syntax:

  • Data Manipulation Language (DML) statements other than SELECT—for example, INSERT, UPDATE, or DELETE.
  • Data Definition Language (DDL) statements—for example, CREATE, ALTER, or DROP.
  • Data access control statements.
  • Query syntax for subqueries, JOIN, UNION, and CTEs.

  For more information, see GoogleSQL for Bigtable overview.

Before you begin

1. Enable the Gemini for Google Cloud API in a Google Cloud project.

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

3. Optional: If you want to follow along with the examples in this document, create and populate test-table as described in Create a test table.

Required roles

To get the permissions that you need to complete the tasks in this document, 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.

You might also be able to get the required permissions through custom roles or other predefined roles.

Generate SQL queries using natural language prompts

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 give natural language comments (or prompts) to Gemini to generate queries that are based on your schema. For example, you can prompt Gemini to generate SQL in response to the following prompts:

• "Count the number of devices that have a 1GB data plan."
• "List all phones that were connected to Wifi on May 1st, 2019."
• "Which tablets have the 10GB data plan enabled?"

Note: When you enter a prompt for Gemini in Bigtable, information about your database's schema is included with the prompt. This can include table and column names, data types, and column descriptions. Your database schema and data remain in Bigtable and aren't sent to Gemini.

To generate SQL in Bigtable with Gemini, follow these steps:

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

   Go to Bigtable

2. Select an instance from the list.

3. In the navigation pane, click Bigtable Studio.

4. Open a new tab by clicking , and then choose Editor.

5. Click Generate SQL.

6. In the Help me code dialog, enter your prompt, and then click Generate.

   For example, if you enter the prompt Count the number of devices that have a 1GB data plan., then Gemini generates SQL that's similar to the following:

   SELECT
       count(*)
   FROM
       `test_table`
   WHERE
       cell_plan['data_plan_01gb'] = 'true'
   

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

7. Review the generated SQL and take one of the following actions:

   • To execute the suggested SQL, click Insert, and then click Run.
   • To edit the prompt, click Edit, and then click Update.
   • To edit the suggested SQL, click Insert. In the query builder, manually edit the query, and then click Run.

8. Optional: To continue the test_table example, if you enter the prompt Show all data for phones from May 2019., then Gemini generates SQL that is similar to the following:

   SELECT
       *
   FROM
       `test_table`
   WHERE
       _key LIKE 'phone#%#201905%'
   

What's next

• Read Gemini for Google Cloud overview.