> [!WARNING]
>
> **Preview
> --- [Gemini](https://docs.cloud.google.com/gemini/docs/overview.md.txt) in Spanner**
>
>
> This feature is
>
> subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the
> [Service Specific
> Terms](https://docs.cloud.google.com/terms/service-terms#1).
>
> Pre-GA features are available "as is" and might have limited support.
>
> For more information, see the
> [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).

This document describes how you can get AI-powered
assistance with the following in Spanner:

- [Generate SQL queries using natural language prompts.](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#generate-sql-queries-using-natural-language-prompts)
- [Explain SQL queries in the query editor.](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#explain-sql-in-query-editor)

Learn [how and when Gemini
for Google Cloud uses your data](https://docs.cloud.google.com/gemini/docs/discover/data-governance).

This document is intended for database administrators and data engineers who
are familiar with Spanner, SQL, and data analysis. If you're new
to Spanner, see
[Create and query a database by using the Google Cloud console](https://docs.cloud.google.com/spanner/docs/create-query-database-console.md.txt).

## Before you begin

1. Optional: [Set up Gemini Code Assist](https://docs.cloud.google.com/gemini/docs/discover/set-up-gemini).

2. To complete the tasks in this document, ensure that you have the
   [necessary Identity and Access Management (IAM) permissions](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#required-roles).

3. In the Google Cloud console, go to the **Spanner** page.

   [Go to Spanner](https://console.cloud.google.com/spanner)
4. Select an instance from the list.

5. Select a database.

6. In the navigation menu, click **Spanner Studio**.

7. In Spanner Studio, click the

   settings_suggestion **Gemini Code Assist** button to view Gemini
   features in Spanner.

8. Select the Gemini features that you want to enable---for
   example,
   [**Comment-to-query generation**](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#generate-sql-queries-using-natural-language-prompts).
   You can select and try features for yourself without affecting others
   working in your project.

9. Optional: If you want to follow along with the examples in this document,
   first create the `Singers` table as described in
   [Create a schema for your database](https://docs.cloud.google.com/spanner/docs/create-query-database-console.md.txt#create-schema).

To disable Gemini features in Spanner, repeat
these steps, and then deselect the Gemini features that you want
to disable.

### 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](https://docs.cloud.google.com/iam/docs/roles-permissions/cloudaicompanion.md.txt#cloudaicompanion.user) (`roles/cloudaicompanion.user`) IAM role on the project.


For more information about granting roles, see [Manage access to projects, folders, and organizations](https://docs.cloud.google.com/iam/docs/granting-changing-revoking-access.md.txt).


You might also be able to get
the required permissions through [custom
roles](https://docs.cloud.google.com/iam/docs/creating-custom-roles) or other [predefined
roles](https://docs.cloud.google.com/iam/docs/roles-overview#predefined).

## Generate SQL queries using natural language prompts

> [!CAUTION]
> 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](https://docs.cloud.google.com/gemini/docs/discover/responsible-ai.md.txt).

You can give Gemini natural language comments (or *prompts*) to
generate queries that are based on your schema. For example, you can prompt
Gemini to generate SQL in response to the following prompts:

- "Create a table that tracks customer satisfaction survey results."
- "Add a date column called birthday to the Singers table."
- "How many singers were born in the 90s?"

> [!NOTE]
> **Note:** When you enter a prompt for Gemini in Spanner, 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 Spanner and aren't sent to Gemini.

To generate SQL in Spanner with Gemini
assistance, follow these steps:

1. In the Google Cloud console, go to the **Spanner** page.

   [Go to Spanner](https://console.cloud.google.com/spanner)
2. Select an instance from the list.

3. Select a database.

4. In the navigation pane, click **Spanner Studio** . The **Explorer**
   pane displays a list of objects in your database.

5. To query your database, click the
   **New tab** . Make sure that
   [SQL generation is enabled](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#before-you-begin).

6. To generate SQL, type a comment in the query editor starting
   with `--` followed by a
   [single-line comment](https://docs.cloud.google.com/bigquery/docs/reference/standard-sql/lexical.md.txt#comments),
   and then press `Return`.

   For example, if you enter the prompt `-- add a row to table singers` and
   press `Return`, then Gemini generates SQL that's
   similar to the following:

   ```sql
   INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate)
   VALUES (1, Alex, 'M.', '1977-10-16');
   ```

   To continue the example using the `Singers` table, if you enter the
   prompt `-- show all singers born in the 70s`, then Gemini
   generates SQL that's similar to the following:

   ```sql
   SELECT *
   FROM Singers
   WHERE Singers.BirthDate
   BETWEEN '1970-01-01' AND '1979-12-31'

   ```

   > [!NOTE]
   > **Note:** Gemini might suggest different syntax each time that you enter the same prompt.

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

   - To accept SQL generated by Gemini, press `Tab`, and then click **Run** to execute the suggested SQL.
   - To edit the SQL generated by Gemini, press `Tab`, edit the SQL, and then click **Run**.
   - To dismiss the suggestion, press `Esc` or continue typing.

## Explain SQL statements in the query editor

You can use Gemini in Spanner to explain SQL queries
in natural language. This explanation can help you understand the syntax,
underlying schema, and business context for complex or long queries.

1. In the Google Cloud console, go to the **Spanner** page.

   [Go to Spanner](https://console.cloud.google.com/spanner)
2. Select an instance from the list.

3. Select a database.

4. In the navigation pane, click **Spanner Studio**.

5. To query your database, click the
   **New tab**.

6. In the query editor, paste the query.

7. Highlight the query that you want Gemini to explain, and
   then click astrophotography_mode
   **Explain this selected query**.

   The SQL explanation appears in the Gemini Cloud Assist pane.

## Use Gemini to fix errors in queries

> [!WARNING]
>
> **Preview
> --- [Gemini](https://docs.cloud.google.com/gemini/docs/overview.md.txt) in Spanner**
>
>
> This feature is
>
> subject to the "Pre-GA Offerings Terms" in the General Service Terms section of the
> [Service Specific
> Terms](https://docs.cloud.google.com/terms/service-terms#1).
>
> You can process personal data for
>
> this feature
>
> as outlined in the
> [Cloud Data
> Processing Addendum](https://docs.cloud.google.com/terms/data-processing-addendum), subject to the obligations and restrictions described in the
> agreement under which you access Google Cloud.
>
> Pre-GA features are available "as is" and might have limited support.
>
> For more information, see the
> [launch stage descriptions](https://cloud.google.com/products/#product-launch-stages).

Gemini offers an extended capability that helps you fix
errors in your Spanner queries without having to leave the query
editor.

Gemini's AI-powered capabilities are integrated within Spanner Studio
to provide the following benefits:

- Provides a line-for-line comparison of the original query next to a new query with recommended changes using the query editor.
- Highlights segments of the original query that might be causing the issue and provides a natural-language summary of changes.
- References the exact error message and schema information associated with the query to provide accurate, relevant corrections.
- Eliminates the need to rely on third-party, general-purpose LLMs that might provide less-nuanced help and might also expose your data to potential security risks.
- Provides faster, more efficient troubleshooting for common issues such as syntax, schema, and runtime errors.

### Required permissions

Before you begin, confirm you have the following permissions:

- The roles outlined in [Required roles](https://docs.cloud.google.com/spanner/docs/write-sql-gemini.md.txt#required-roles).
- The Gemini for Google Cloud API permission, [`cloudaicompanion.googleapis.com/instances.generateCode`](https://docs.cloud.google.com/iam/docs/roles-permissions/cloudaicompanion.md.txt#cloudaicompanion.instances.generateCode).

### Fix your query using Gemini

To use Gemini's capabilities to fix certain errors in a query,
complete the following:

1. In the query editor, run your query.

   If the query contains an error, an error message is returned in an error banner at the bottom of the editor.
2. Click **Fix**.

   In the query window, a differences editor ("diff editor") opens in the tab,
   showing a line-for-line comparison of the existing query with recommended
   changes. The query editor also provides a summary of the suggested edits.
3. Review the suggested changes to the query and update as needed.

4. To accept the changes and run your new query, click **Accept and run**.

### Limitations

- This capability is only accessible in Spanner Studio query editor in Google Cloud console.
- Current [Gemini context window limits apply](https://ai.google.dev/gemini-api/docs/long-context#getting-started-with-long-context).

## What's next

- Read [Gemini for Google Cloud overview](https://docs.cloud.google.com/gemini/docs/overview.md.txt).
- Learn [how Gemini uses your
  data](https://docs.cloud.google.com/gemini/docs/discover/data-governance).