Considerations when interacting with generative SQL Using generative SQL Updating generative SQL settings as an administrator Example using Amazon Q generative SQL capability with the TICKIT data

Interacting with query editor v2 generative SQL (preview)

This is prerelease documentation for query editor v2 generative SQL, which is in preview release. The documentation and the feature are both subject to change. We recommend that you use this feature only in test environments, and not in production environments. For preview terms and conditions, see Beta Service Participation in Amazon Service Terms.

Note

Currently, generative SQL support is only available in the following Amazon Web Services Regions:

US East (N. Virginia) Region (us-east-1)
US West (Oregon) Region (us-west-2)
Europe (Frankfurt) Region (eu-central-1)

You can interact with Amazon Q generative SQL capability in Amazon Redshift query editor v2. It's a coding assistant that generates SQL statements based on your prompts and database schema. This coding assistant is available while you're authoring a notebook in query editor v2.

When interacting with generative SQL, ask specific questions, iterate when you have complex requests, and verify the answers for accuracy.

When providing analysis requests in natural language, be as specific as possible to help the coding assistant understand exactly what you need. Instead of asking "find top venues that sold the most tickets," provide more details like "find names/ids of top three venues that sold the most tickets in 2008." Use consistent names of objects in your database, such as the schema, table, and column names as defined in your database instead of referring to the same object in different ways, which can confuse the assistant.

Break down complex requests into multiple simple statements that are easier for the assistant to interpret. Iteratively ask follow-up questions to get more detailed analysis from the assistant. For example, first ask "which state has the most venues?" Then based on the response, ask "which is the most popular venue from this state?".

Review the generated SQL before running it to ensure accuracy. If the generated SQL query has errors or does not match your intent, provide instructions to the assistant on how to correct it instead of rephrasing the entire request. For example, if the query is missing a predicate clause on year, ask "Provide venues from year 2008."

Considerations when interacting with generative SQL

Consider the following when working in the chat panel.

The query editor v2 administrator for your account must have turned on the chat capability in the Generative SQL settings page.
To use query editor v2 generative SQL, you need permission sqlworkbench:GetQSqlRecommendations in your IAM policy, in addition to other permissions specified in the Amazon managed policy for query editor v2. For more information about Amazon managed policies, see Accessing the query editor v2.
Your questions must be written in English.
Your questions must be in reference to the connected database in your cluster or workgroup. To avoid empty state errors, there should be at least one table and some data in the database.
Your questions must be in reference to data that is stored in the connected database. It cannot reference an external schema. For more information on the supported schemas, see Create schema in the Amazon Redshift Database Developer Guide.
Any questions that result in SQL that changes the connected database might result in a warning.
Generative AI technology is new and there can be mistakes, sometimes called hallucinations, in the responses. Test and review all code for errors and vulnerabilities before using it in your environment or workload.
You can improve recommendations by sharing the SQL queries run by other users in your account. Your account administrator can run the following SQL commands to allow access to the account's query history.
```
GRANT ROLE SYS:MONITOR to "IAMR:role-name";
GRANT ROLE SYS:MONITOR to "IAM:user-name";
GRANT ROLE SYS:MONITOR to "database-username";
```
For more information about SYS:MONITOR, see Amazon Redshift system-defined roles in the Amazon Redshift Database Developer Guide.
Your data is secure and private. Your data is not shared across accounts. Your queries, data, and database schemas are not used to train a generative AI foundation model (FM). Your input is used as contextual prompts to the FM to answer only your queries.

Using generative SQL

After the correct permissions are configured, when working with a notebook in query editor v2, you can choose an icon to start a conversation.

To interact with the query editor v2 generative SQL chat to generate SQL

In the Editor tab of the query editor v2, open a notebook.
Choose the Generative SQL icon, then follow the directions to ask your questions of the Amazon Redshift query editor v2 generative SQL in the chat panel.

You provide questions in a prompt field and query editor v2 responds with suggested SQL. Any errors encountered are returned to you in the chat panel.
Choose Add to notebook to add a Markdown cell with your prompt and a SQL cell with the suggested SQL to your notebook.
(Optional) Choose Regenerate SQL to generate another response for the same prompt. You can choose to Regenerate SQL one time for the current prompt.
(Optional) In the generative SQL chat panel, choose the More icon, then choose Refresh database to refresh the metadata describing your connected database. This metadata includes the definitions of schemas, tables, and columns in your database.

Updating generative SQL settings as an administrator

A user with the right IAM permissions can view and change Generative SQL settings for other users in the same Amazon Web Services account. This administrator must have permission sqlworkbench:UpdateAccountQSqlSettings in their IAM policy, in addition to other permissions specified in the Amazon managed policy for query editor v2. For more information about managed policies, see Permissions required to use the query editor v2 .

For an administrator to turn on generative SQL chat for all users in the account

Choose the Settings icon to show a menu of the different settings screens.
Then choose the Generative SQL settings icon to show the Generative SQL settings page.
Select Generative SQL to turn on the generative SQL capability for users in the account.

Example using Amazon Q generative SQL capability with the TICKIT data

To author efficient prompts to generate SQL, you must learn about your database schema and your data. The TICKIT data consists of seven tables: two fact tables and five dimensions. The sample data contains records about sales to attendants of entertainment events that occurred in 2008. For more information about the TICKIT data schema, see Sample database in the Amazon Redshift Database Developer Guide. You can load the TICKIT data into a database by various methods in both the Amazon Redshift console and the query editor v2. The query editor v2 provides a method to load TICKIT data into database sample_data_dev. For information, see Loading sample data. The query editor v2 also provides example prompts for the TICKIT data. The following scenario describes a conversation with generative SQL to generate SQL about the TICKIT sample data. In this scenario, the TICKIT sample data is already created in a dev database in an Amazon Redshift cluster.

Note

This example is to illustrate a conversation. The responses from generative SQL might not match your results using the same prompts.

Example conversation with query editor v2 generative SQL

In the Editor, connect to a cluster or workgroup that contains the TICKIT sample data.
Create an empty notebook and choose the Generative SQL icon to open the chat panel.
Enter the following prompt to generate SQL to verify the number of records in the VENUE table:
```
How many venues are there?
```
```
SELECT
  COUNT(*) AS num_venues
FROM
  tickit.venue
```
Choose Add to notebook to add two cells to the open notebook. One Markdown cell "How many venues are there?" and one containing the generated SQL.

In the SQL cell, choose Run to receive the result:
```
count
------
202	
```
To ask for another version of SQL, choose Regenerate SQL and receive the following answer:
```
SELECT
  venuestate,
  COUNT(*) AS num_venues
FROM
  tickit.venue
GROUP BY
  venuestate
```
Choose Add to notebook to add two more cells to the open notebook. One Markdown cell "How many venues are there?" and one containing the generated SQL.

In the SQL cell, choose Run to receive the result listing venue state's by number:
```
venuestate num_venues
----------------------
MA         4	
OH         8	
MI         5	
...
```
The generative SQL assistant can make assumptions about what you're asking. Although there are no widgets in the database, you can still ask. In this case, it gives you a message that it was not able to generate SQL.
```
How many widgets are there?
```
```
I was not able to generate any SQL
```
As you learn more about your database, you can ask more specific questions to generate SQL. For example, you're aware that the database contains sales data for several months in 2008. Enter the following prompt to generate SQL to find the number of sales in November 2008:
```
How many sales were there in November 2008?
```
```
SELECT
  COUNT(*) AS num_sales
FROM
  tickit.sales s
  JOIN tickit.date d ON s.dateid = d.dateid
WHERE
  d.month = 'November'
  AND d.year = 2008
```
Choose Add to notebook to add two cells to the open notebook. One Markdown cell "How many sales were there in November 2008?" and one containing the generated SQL.

In the SQL cell, choose Run to receive the result:
```
num_sales
-----
0	
```
You realize this is not the result you were expecting.
You notice that the predicate date.month='November' expects the month in the DATE table to be represented by an abbreviation of month name. You change the predicate to d.month='NOV' and rerun the SQL.
```
SELECT
  COUNT(*)
FROM
  sales
  JOIN date ON sales.dateid = date.dateid
WHERE
  date.month = 'NOV'
  AND date.year = 2008
```
In the SQL cell, choose Run to get new results.
```
count
-----
14261	
```

If you ask a question that tries to change the connected database, a warning message is returned along with any recommended SQL. Enter the following prompt to generate SQL to inset data into a table:


Insert 1 into the venue table.

INSERT
,
UPDATE
  OR delete data
FROM
  the database AS that could potentially change the data.Please provide a query that ONLY selects data


I was not able to generate the correct SQL code. I generated SQL, but you'll have to edit it to work with your database.

If you choose Add to notebook to add two cells to the open notebook and run the SQL, then the SQL fails.



ERROR: syntax error at or near "," Position: 132 [ErrorId: 1-6546764a-011df2691778846219ce6ec2]

This scenario only illustrated some basic ways to interact with the query editor v2 generative SQL. You can experiment even more with this generative AI technology to help you start authoring SQL to query your database.

Javascript is disabled or is unavailable in your browser.

To use the Amazon Web Services Documentation, Javascript must be enabled. Please refer to your browser's Help pages for instructions.

Working with query editor v2

Loading data into a database