Amazon Q Generative SQL(Redshift) querying view(referencing external table)

Yes, views can be used with Amazon Q's Generative SQL in Amazon Redshift, but there are some important considerations to keep in mind when working with external tables and views.

Amazon Q Generative SQL is designed to simplify query authoring and increase productivity by allowing users to express queries in natural language and receive SQL code recommendations. It analyzes user intent, query patterns, and schema metadata to identify common SQL query patterns within Amazon Redshift.

However, when working with external tables (such as those defined in AWS Glue Data Catalog), there are some limitations. External tables are not stored directly in Amazon Redshift; instead, they contain metadata about data stored externally, typically in Amazon S3.

If you're experiencing issues with querying views that reference external tables, it could be due to a few reasons:

1. Metadata visibility: Amazon Q Generative SQL might have limited visibility into the metadata of external tables, which could cause the error you're seeing.

2. Schema and permissions: Ensure that you have the necessary permissions to access both the external schema and the data it references.

3. View definition: Make sure your view is correctly defined and references the external table properly.

4. Data catalog integration: Verify that your Amazon Redshift cluster is correctly set up to access the AWS Glue Data Catalog.

The fact that creating a table reference to the external table works suggests that the issue might be related to how Amazon Q Generative SQL interacts with views referencing external data.

As a workaround, you could try the following:

1. Use regular tables or materialized views instead of standard views when working with external data.

2. Ensure that your external schema and table definitions are up to date and accessible.

3. Double-check that you're in the correct database context when using Amazon Q Generative SQL.

4. If possible, try using Amazon Q Generative SQL on simpler queries first, gradually increasing complexity to identify where the issue occurs.

Remember that Amazon Q Generative SQL is a relatively new feature, and its capabilities are continually evolving. If you continue to experience issues, it may be worth reaching out to AWS support for further assistance or checking for any recent updates or known limitations of the feature when working with external tables and views.

Sources
Amazon Q generative SQL is now available in Amazon Redshift Query Editor (preview)
Announcing general availability (GA) of Amazon Q generative SQL for Amazon Redshift - AWS
External tables for Redshift Spectrum - Amazon Redshift