This document describes how you can use Gemini, an AI-powered collaborator in Google Cloud, to help you do the following in AlloyDB for PostgreSQL:
- Generate SQL queries using natural language prompts.
- Complete SQL queries in the query editor.
- Explain SQL queries in the query editor.
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 AlloyDB, and somewhat familiar with SQL and data analysis. If you're new to AlloyDB, see AlloyDB overview.
Before you begin
- Ensure that you have set up Gemini in Databases.
- To complete the tasks in this document, ensure that you have the necessary Identity and Access Management (IAM) permissions.
- Confirm access to AlloyDB Studio. If you don't have access to AlloyDB Studio, then see AlloyDB Studio required roles and permissions
Optional: If you want to follow along with the examples in this document, create the
Singers
table using the following schema:CREATE TABLE Singers ( BirthDate TIMESTAMPTZ, SingerId BIGINT PRIMARY KEY, FirstName VARCHAR(1024), LastName VARCHAR(1024), SingerInfo BYTEA );
After you create the
Singers
table, click refreshRefresh to update your database schema.
To disable Gemini features in AlloyDB, repeat these steps, and then deselect the Gemini features that you want to disable.
Required roles and permissions
To use Gemini with AlloyDB, you need the following permissions:
alloydb.clusters.get
alloydb.databases.list
alloydb.instances.executeSql
alloydb.instances.list
alloydb.users.list
You can get this permission through the
roles/alloydb.admin
role. If you don't have this role, contact your Organization Administrator to
request access. You might also be able to get the required permissions through
custom roles or
other predefined roles.
Additionally, ensure that you have database-level permissions for the database that you're using for authentication.
Generate SQL queries using natural language prompts
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?"
If your schema changes, such as addition of a new table or column, then you must click refreshRefresh to update your schema before using Gemini.
To generate SQL in AlloyDB with Gemini assistance, follow these steps:
In the Google Cloud console, open the AlloyDB page.
Select a cluster from the list.
In the navigation menu, click AlloyDB Studio.
Select a database and user, and enter the user's password.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
To query your database, click the SQL generation is enabled.
New SQL editor tab. Make sure thatTo generate SQL, type a comment in the query editor starting with
--
followed by a single-line comment, 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: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:SELECT * FROM Singers WHERE Singers.BirthDate BETWEEN '1970-01-01' AND '1979-12-31'
Review the SQL suggestion and take any of the following actions:
- To view the options for accepting the SQL generated by Gemini,
hold the pointer over the query. The following options are displayed:
- Accept: To accept the suggested query, press
Tab
, then click Run. - Accept word: To accept the suggested query partially,
press
Control
+Right arrow
. (orCommand
+Right arrow
on macOS), then click Run.
- Accept: To accept the suggested query, press
- To edit the original SQL, press
Tab
, edit the SQL, and then click Run. - To dismiss the suggestion, press
Esc
or continue typing.
- To view the options for accepting the SQL generated by Gemini,
hold the pointer over the query. The following options are displayed:
Help me code tool
To use the Help me code tool, follow these steps:
In the Google Cloud console, open the AlloyDB page.
Select a cluster from the list.
In the navigation menu, click AlloyDB Studio.
Select a database and user, and enter the user's password.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
To query your database, click the
New tab.Click pen_spark Help me code next to the query editor.
In the Help me code window, enter a prompt. For example,
add a row to table singers
and click Generate.Gemini generates SQL that's similar to the following:
INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate) VALUES (1, Alex, 'M.', '1977-10-16');
Review the generated SQL and take any of the following actions:
- To accept SQL generated by Gemini, click Insert to insert the statement into the query editor. Then click Run to execute the suggested SQL.
- To ask Gemini to generate a new query, click Edit. After you've edited your prompt, click Update. You can then decide to accept the new generated statement or dismiss the suggestion.
- To dismiss the suggestion, close the Help me code window.
Complete SQL statements in the query editor
To help you write SQL code, Gemini in AlloyDB provides AI-assisted code completion suggestions. With Gemini enabled, when you enter text in the AlloyDB Studio query editor, Gemini predicts and autofills SQL code.
To use inline code suggestions, follow these steps:
In the Google Cloud console, open the AlloyDB page.
Select a cluster from the list.
In the navigation menu, click AlloyDB Studio.
Select a database and user, and enter the user's password.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
To query your database, click SQL completion is enabled.
New SQL editor tab. Make sure thatEnter a query and a space or newline at the end of the line. As you enter text, Gemini shows suggested SQL that's based on the supported objects in the selected database's schema.
For example, to add a row to the
Singers
table, begin to write the following SQL:INSERT INTO Singers
.Gemini suggests SQL that's similar to the following:
INSERT INTO Singers (SingerId, FirstName, LastName, BirthDate) VALUES (1, 'Marc', 'Singer', '1970-03-24')
Review the SQL suggestion 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 AlloyDB 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.
In the Google Cloud console, open the AlloyDB page.
Select a cluster from the list.
In the navigation menu, click AlloyDB Studio.
Select a database and user, and enter the user's password.
Click Authenticate. The Explorer pane displays a list of the objects in your database.
To query your database, click the
New tab.In the query editor, paste the query.
Highlight the query that you want Gemini to explain, and then click pen_spark Explain this query.
The SQL explanation appears in the Gemini Chat window.
What's next
- Read Gemini for Google Cloud overview.
- Learn how Gemini uses your data.
- Learn how to analyze your data in Google Cloud using Gemini.