Write SQL with Gemini assistance

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 .

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

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?"

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

1. In the Google Cloud console, go to the Bigtablepage.

   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 add , and then choose Editor.

5. Click Generate SQL.

6. In the Help me codedialog, 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' 
    
   

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 .