Amazon Redshift will no longer support the creation of new Python UDFs starting Patch 198. Existing Python UDFs will continue to function until June 30, 2026. For more information, see the [ blog post ](https://aws.amazon.com/blogs/big-data/amazon-redshift-python-user-defined-functions-will-reach-end-of-support-after-june-30-2026/). # Querying a database using the query editor v2 The query editor v2 is a separate web-based SQL client application that you use to author and run queries on your Amazon Redshift data warehouse. The query editor v2 is primarily used to edit and run queries, visualize results, and share your work with your team. With query editor v2, you can create databases, schemas, tables, and user-defined functions (UDFs). In a tree-view panel, for each of your databases, you can view its schemas. For each schema, you can view its tables, views, UDFs, and stored procedures. The query editor v2 is a replacement for the previous query editor. **Note** The query editor v2 is available in commercial AWS Regions. For a list of AWS Regions where the query editor v2 is available, see the endpoints listed for [Redshift query editor v2](https://docs.aws.amazon.com/general/latest/gr/redshift-service.html) in the *Amazon Web Services General Reference*. For a demo of query editor v2, watch the following videos. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/IwZNIroJUnc/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/IwZNIroJUnc) [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/4EIV4XTE9iI/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/4EIV4XTE9iI) [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/7y-f1wlyVhI/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/7y-f1wlyVhI) The query editor v2 has a rich set of features to manage and run your SQL statements. The topics in the following sections get you started with many of these features. Explore the query editor v2 on your own to familiarize yourself with its capabilities. # Configuring your AWS account You can perform this set of tasks to configure the query editor v2 to query an Amazon Redshift database.With the proper permissions, you can access data in an Amazon Redshift cluster or workgroup owned by your AWS account that is in the current AWS Region. The first time an administrator configures query editor v2 for your AWS account, they choose the AWS KMS key that is used to encrypt query editor v2 resources. By default, an AWS owned key is used to encrypt resources. Alternatively, an administrator can use a customer managed key by choosing the Amazon Resource Name (ARN) for the key in the configuration page. After configuring an account, AWS KMS encryption settings can't be changed. For more information about creating and using a customer managed key with query editor v2, see [Creating an AWS KMS customer managed key to use with query editor v2](#query-editor-v2-kms-key). The administrator can also optionally choose an S3 bucket and path that is used for some features, such as loading data from a file. For more information, see [Loading data from a local file setup and workflow](query-editor-v2-loading-data-local.md). Amazon Redshift query editor v2 supports authentication, encryption, isolation, and compliance to keep your data at rest and data in transit secure. For more information about data security and query editor v2, see the following: + [Encryption at rest](security-server-side-encryption.md) + [Encryption in transit](security-encryption-in-transit.md) + [Configuration and vulnerability analysis in Amazon Redshift](security-vulnerability-analysis-and-management.md) AWS CloudTrail captures API calls and related events made by or on behalf of your AWS account and delivers the log files to an Amazon S3 bucket that you specify. You can identify which users and accounts called AWS, the source IP address from which the calls were made, and when the calls occurred. To learn more about how query editor v2 runs on AWS CloudTrail, see [Logging with CloudTrail](logging-with-cloudtrail.md). For more information about CloudTrail, see the [AWS CloudTrail User Guide](https://docs.aws.amazon.com/awscloudtrail/latest/userguide/cloudtrail-user-guide.html). The query editor v2 has adjustable quotas for some of its resources. For more information, see [Quotas for Amazon Redshift objects](amazon-redshift-limits.md#amazon-redshift-limits-quota). ## Resources created with query editor v2 Within query editor v2, you can create resources such as saved queries and charts. All resources in query editor v2 are associated with an IAM role or with a user. We recommend attaching policies to an IAM role and assigning the role to a user. In the query editor v2, you can add and remove tags for saved queries and charts. You can use these tags when setting up custom IAM policies or to search for resources. You can also manage tags by using the AWS Resource Groups Tag Editor. You can set up IAM roles with IAM policies to share queries with others in your same AWS account in the AWS Region. ## Creating an AWS KMS customer managed key to use with query editor v2 **To create a symmetric encryption customer managed key**: You can create a symmetric encryption customer managed key to encrypt query editor v2 resources using the AWS KMS console or AWS KMS API operations. For instructions about creating a key, see [Creating symmetric encryption AWS KMS key](https://docs.aws.amazon.com/kms/latest/developerguide/create-keys.html#create-symmetric-cmk) in the *AWS Key Management Service Developer Guide*. **Key policy** Key policies control access to your customer managed key. Every customer managed key must have exactly one key policy, which contains statements that determine who can use the key and how they can use it. When you create your customer managed key, you can specify a key policy. For more information, see [Managing access to AWS KMS keys](https://docs.aws.amazon.com/kms/latest/developerguide/control-access-overview.html#managing-access) in the *AWS Key Management Service Developer Guide*. To use your customer managed key with Amazon Redshift query editor v2, the following API operations must be allowed in the key policy: + `kms:GenerateDataKey` – Generates a unique symmetric data key to encrypt your data. + `kms:Decrypt` – Decrypts data that was encrypted with the customer managed key. + `kms:DescribeKey` – Provides the customer managed key details to allow the service to validate the key. The following is a sample AWS KMS policy for AWS account `111122223333`. In the first section, the `kms:ViaService` limits use of the key to the query editor v2 service (which is named `sqlworkbench.region.amazonaws.com` in the policy). The AWS account using the key must be `111122223333`. In the second section, the root user and key administrators of AWS account `111122223333` can access to the key. When you create an AWS account, you begin with one sign-in identity called the AWS account *root user* that has complete access to all AWS services and resources. We strongly recommend that you don't use the root user for everyday tasks. For tasks that require root user credentials, see [Tasks that require root user credentials](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_root-user.html#root-user-tasks) in the *IAM User Guide*. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Id": "key-consolepolicy", "Statement": [ { "Sid": "Allow access to principals authorized to use Amazon Redshift Query Editor V2", "Effect": "Allow", "Principal": { "AWS": "*" }, "Action": [ "kms:GenerateDataKey", "kms:Decrypt", "kms:DescribeKey" ], "Resource": "*", "Condition": { "StringEquals": { "kms:ViaService": "sqlworkbench.us-east-1.amazonaws.com", "kms:CallerAccount": "111122223333" } } }, { "Sid": "Allow access for key administrators", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::111122223333:root" }, "Action": [ "kms:*" ], "Resource": "arn:aws:kms:us-east-1:111122223333:key/key_ID" } ] } ``` ------ The following resources provide more information about AWS KMS keys: + For more information about AWS KMS policies, see [Specifying permissions in a policy](https://docs.aws.amazon.com/kms/latest/developerguide/control-access-overview.html#overview-policy-elements) in the *AWS Key Management Service Developer Guide*. + For information about troubleshooting AWS KMS policies, see [Troubleshooting key access](https://docs.aws.amazon.com/kms/latest/developerguide/policy-evaluation.html#example-no-iam) in the *AWS Key Management Service Developer Guide*. + For more information about keys, see [AWS KMS keys](https://docs.aws.amazon.com/kms/latest/developerguide/concepts.html#kms_keys) in the *AWS Key Management Service Developer Guide*. ## Accessing the query editor v2 To access the query editor v2, you need permission. An administrator can attach one of the following AWS managed policies to the role to grant permission. (We recommend attaching policies to an IAM role and assigning the role to a user.) These AWS managed policies are written with different options that control how tagging resources allows sharing of queries. You can use the IAM console ([https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/)) to attach IAM policies. + **AmazonRedshiftQueryEditorV2FullAccess** – Grants full access to the Amazon Redshift query editor v2 operations and resources. This policy also grants access to other required services. + **AmazonRedshiftQueryEditorV2NoSharing** – Grants the ability to work with Amazon Redshift query editor v2 without sharing resources. This policy also grants access to other required services. + **AmazonRedshiftQueryEditorV2ReadSharing** – Grants the ability to work with Amazon Redshift query editor v2 with limited sharing of resources. The granted principal can read the resources shared with its team but can’t update them. This policy also grants access to other required services. + **AmazonRedshiftQueryEditorV2ReadWriteSharing** – Grants the ability to work with Amazon Redshift query editor v2 with sharing of resources. The granted principal can read and update the resources shared with its team. This policy also grants access to other required services. You can also create your own policy based on the permissions allowed and denied in the provided managed policies. If you use the IAM console policy editor to create your own policy, choose **SQL Workbench** as the service for which you create the policy in the visual editor. The query editor v2 uses the service name AWS SQL Workbench in the visual editor and IAM Policy Simulator. For a principal (a user with an IAM role assigned) to connect to an Amazon Redshift cluster, they need the permissions in one of the query editor v2 managed policies. They also need one of the `redshift:GetClusterCredentialsWithIAM` or `redshift:GetClusterCredentials` permission to the cluster. To get this permission, someone with administrative permission can attach a policy to the IAM roles used to connect to the cluster by using temporary credentials. You can scope the policy to specific clusters or be more general. For more information about permission to use temporary credentials, see [Create an IAM role or user with permissions to call GetClusterCredentialsWithIAM or GetClusterCredentials](generating-iam-credentials-steps.md#generating-iam-credentials-role-permissions). For a principal (typically a user with an IAM role assigned) to turn on the ability in the **Account settings** page for others in the account to **Export result set**, they need the `sqlworkbench:UpdateAccountExportSettings` permission attached the role. This permission is included in the `AmazonRedshiftQueryEditorV2FullAccess` AWS managed policy. As new features are added to query editor v2, the AWS managed policies are updated as needed. If you create your own policy based on the permissions allowed and denied in the provided managed policies, edit your policies to keep them up to date with changes to the managed policies. For more information about managed policies in Amazon Redshift, see [AWS managed policies for Amazon Redshift](redshift-iam-access-control-identity-based.md#redshift-policy-resources.managed-policies). To provide access, add permissions to your users, groups, or roles: + Users and groups in AWS IAM Identity Center: Create a permission set. Follow the instructions in [Create a permission set](https://docs.aws.amazon.com//singlesignon/latest/userguide/howtocreatepermissionset.html) in the *AWS IAM Identity Center User Guide*. + Users managed in IAM through an identity provider: Create a role for identity federation. Follow the instructions in [Create a role for a third-party identity provider (federation)](https://docs.aws.amazon.com//IAM/latest/UserGuide/id_roles_create_for-idp.html) in the *IAM User Guide*. + IAM users: + Create a role that your user can assume. Follow the instructions in [Create a role for an IAM user](https://docs.aws.amazon.com//IAM/latest/UserGuide/id_roles_create_for-user.html) in the *IAM User Guide*. + (Not recommended) Attach a policy directly to a user or add a user to a user group. Follow the instructions in [Adding permissions to a user (console)](https://docs.aws.amazon.com//IAM/latest/UserGuide/id_users_change-permissions.html#users_change_permissions-add-console) in the *IAM User Guide*. **Note** If an AWS IAM Identity Center administrator removes all permission set associations for a particular permission set in the entire account, access to any query editor resources originally associated with the removed permission set are no longer accessible. If later the same permissions are recreated, a new internal identifier is created. Because the internal identifier has changed, access to query editor resources previously owned by a user cannot be accessed. We recommend that before administrators delete a permission set, that users of that permission set export query editor resources such as notebooks and queries as a backup. ## Setting up principal tags to connect a cluster or workgroup from query editor v2 To connect to your cluster or workgroup using the federated user option, either set up your IAM role or user with principal tags. Or, set up your identity provider (IdP) to pass in `RedshiftDbUser` and (optionally) `RedshiftDbGroups`. For more information about using IAM to manage tags, see [Passing session tags in AWS Security Token Service](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_session-tags.html) in the *IAM User Guide*. To set up access using AWS Identity and Access Management, an administrator can add tags using the IAM console ([https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/)). **To add principal tags to an IAM role** 1. Sign in to the AWS Management Console and open the IAM console at [https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/). 1. Choose **Roles** in the navigation pane. 1. Choose the role that needs access to the query editor v2 using a federated user. 1. Choose the **Tags** tab. 1. Choose the **Manage tags**. 1. Choose **Add tag** and enter the **Key** as `RedshiftDbUser` and enter a **Value** of the federated user name. 1. Optionally choose **Add tag** and enter the **Key** as `RedshiftDbGroups` and enter a **Value** of the group name to associate to the user. 1. Choose **Save changes** to view the list of tags associated with your chosen IAM role. Propagating changes might take several seconds. 1. To use the federated user, refresh your query editor v2 page after the changes have propagated. **Setup your identity provider (IdP) to pass principal tags** The procedure to set up tags using an identity provider (IdP) varies by IdP. See your IdP documentation for instructions on how to pass user and group information to SAML attributes. When configured correctly, the following attributes appear in your SAML response that is used by the AWS Security Token Service to populate in the principal tags for `RedshiftDbUser` and `RedshiftDbGroups`. ``` db-user-name db-groups ``` The optional *db\$1groups* must be a colon-separated list such as `group1:group2:group3`. Additionally, you can set the `TransitiveTagKeys` attribute to persist the tags during role chaining. ``` RedshiftDbUser RedshiftDbGroups ``` For more information about setting up query editor v2, see [Permissions required to use the query editor v2](redshift-iam-access-control-identity-based.md#redshift-policy-resources.required-permissions.query-editor-v2). For information about how to set up Active Directory Federation Services (AD FS), see the blog post: [ Federate access to Amazon Redshift query editor v2 with Active Directory Federation Services (AD FS)](https://aws.amazon.com/blogs//big-data/federate-access-to-amazon-redshift-query-editor-v2-with-active-directory-federation-services-ad-fs-part-3/). For information about how to set up Okta, see the blog post: [ Federate single sign-on access to Amazon Redshift query editor v2 with Okta](https://aws.amazon.com/blogs//big-data/federate-single-sign-on-access-to-amazon-redshift-query-editor-v2-with-okta/). **Note** When you connect to your cluster or workgroup using the **Federated user** connection option of the query editor v2, the Identity Provider (IdP) can supply custom principal tags for `RedshiftDbUser` and `RedshiftDbGroups`. Currently, AWS IAM Identity Center dosesn't support the passing custom principal tags directly to the query editor v2. # Opening query editor v2 With Amazon Redshift, you can execute SQL queries against your data warehouse cluster using the query editor v2 in the Amazon Redshift console. The query editor v2 is a web-based tool that provides a user-friendly interface for running ad-hoc queries, exploring data, and performing data analysis tasks. The following sections guide you through the process of opening the query editor v2 in the console and utilizing its functionalities effectively. **To open the query editor v2** 1. Sign in to the AWS Management Console and open the Amazon Redshift console at [https://console.aws.amazon.com/redshiftv2/](https://console.aws.amazon.com/redshiftv2/). 1. From the navigator menu, choose **Editor**, then **Query editor V2**. The query editor v2 opens in a new browser tab. The query editor page has a navigator menu where you choose a view as follows: **Editor ![\[Horizontal lines representing redacted or censored text.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-align-left.png)** You manage and query your data organized as tables and contained in a database. The database can contain stored data or contain a reference to data stored elsewhere, such as Amazon S3. You connect to a database contained in either a cluster or a serverless workgroup. When working in the **Editor** view, you have the following controls: + The **Cluster** or **Workgroup** field displays the name you are currently connected to. The **Database** field displays the databases within the cluster or workgroup. The actions that you perform in the **Database** view default to act on the database you have selected. + A tree-view hierarchical view of your clusters or workgroups, databases, and schemas. Under schemas, you can work with your tables, views, functions, and stored procedures. Each object in the tree view supports a context menu to perform associated actions, such as **Refresh** or **Drop**, for the object. + A ![\[The create icon used in the AWS Console.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png) **Create** action to create databases, schemas, tables, and functions. + A ![\[The upload icon used in the AWS Console.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-upload.png)**Load data** action to load data from Amazon S3 or from a local file into your database. + A ![\[The floppy disk icon used in the AWS Console.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-floppy-disk.png) **Save** icon to save your query. + A ![\[The shortcut icon used in the AWS Console.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-key-command.png) **Shortcuts** icon to display keyboard shortcuts for the editor. + A ![\[The more actions icon used in the AWS Console.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-more.png) **More** icon to display more actions in the editor. Such as: + **Share with my team** to share the query or notebook with your team. For more information, see [Collaborating and sharing as a team](query-editor-v2-team.md). + **Shortcuts** to display keyboard shortcuts for the editor. + **Tab history** to display tab history of a tab in the editor. + **Refresh autocomplete** to refresh the displayed suggestions when authoring SQL. + An ![\[The editor icon in the AWS Console where can enter and run queries.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) **Editor** area where you can enter and run your query. After you run a query, a **Result** tab appears with the results. Here is where you can turn on **Chart** to visualize your results. You can also **Export** your results. + A ![\[The icon in the AWS Console where you can add sections to enter and run SQL or add Markdown.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) **Notebook** area where you can add sections to enter and run SQL or add Markdown. After you run a query, a **Result** tab appears with the results. Here is where you can **Export** your results. **Queries ![\[A folder icon used in the AWS Console used to query databases.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-folder-close.png)** A query contains the SQL commands to manage and query your data in a database. When you use query editor v2 to load sample data, it also creates and saves sample queries for you. When you choose a saved query, you can open, rename, and delete it using the context (right-click) menu. You can view attributes such as the **Query ARN** of a saved query by choosing **Query details**. You can also view its version history, edit tags attached to the query, and share it with your team. **Notebooks ![\[A book icon used in the AWS Console used as SQL notebook.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-manual.png)** A SQL notebook contains SQL and Markdown cells. Use notebooks to organize, annotate, and share multiple SQL commands in a single document. When you choose a saved notebook, you can open, rename, duplicate, and delete it using the context (right-click) menu. You can view attributes such as the **Notebook ARN** of a saved notebook by choosing **Notebook details**. You can also view its version history, edit tags attached to the notebook, export it, and share it with your team. For more information, see [Notebooks in Amazon RedshiftNotebooks](query-editor-v2-notebooks.md). **Charts ![\[Icon of a chart used in the AWS Console as visual representation of data.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-chart.png)** A chart is a visual representation of your data. The query editor v2 provides the tools to create many types of charts and save them. When you choose a saved chart, you can open, rename, and delete it using the context (right-click) menu. You can view attributes such as the **Chart ARN** of a saved chart by choosing **Chart details**. You can also edit tags attached to the chart and export it. For more information, see [Visualizing query results](query-editor-v2-charts.md). **History ![\[Icon of a clock used in the AWS Console for query history.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-clock.png)** The query history is a list of queries you ran using Amazon Redshift query editor v2. These queries either ran as individual queries or as part of a SQL notebook. For more information, see [Viewing query and tab history](query-editor-v2-history.md). **Scheduled queries ![\[Icon of a calendar used in the AWS Console for scheduled queries.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-calendar.png)** A scheduled query is a query that is set up to start at specific times. All query editor v2 views have the following icons: + A ![\[Icon of a quarter moon used in the AWS Console to switch between light and dark modes.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-moon.png) **Visual mode** icon to toggle between light mode and dark mode. + A ![\[Icon of a gear used in the AWS Console to show settings.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-cog.png) **Settings** icon to show a menu of the different settings screens. + An ![\[Icon used in the AWS Console to show editor preferences.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-properties.png) **Editor preferences** icon to edit your preferences when you use query editor v2. Here you can **Edit workspace settings** to change font size, tab size, and other display settings. You can also turn on (or off) **Autocomplete** to show suggestions as you enter your SQL. + A ![\[Icon used in the AWS Console to view connections used in the editor tab.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-connection.png) **Connections** icon to view the connections used by your editor tabs. A connection is used to retrieve data from a database. A connection is created for a specific database. With an isolated connection, the results of a SQL command that changes the database, such as creating a temporary table, in one editor tab, are not visible in another editor tab. When you open an editor tab in query editor v2, the default is an isolated connection. When you create a shared connection, that is, turn off the **Isolated session** switch, then the results in other shared connections to the same database are visible to each other. However, editor tabs using a shared connection to a database don't run in parallel. Queries using the same connection must wait until the connection is available. A connection to one database can't be shared with another database, and thus SQL results are not visible across different database connections. The number of connections any user in the account can have active is controlled by a query editor v2 administrator. + An ![\[Icon used in the AWS Console used by administrators to change settings of user accounts.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-settings.png) **Account settings** icon used by an administrator to change certain settings of all users in the account. For more information, see [Account settings](#query-editor-v2-settings). ## Considerations when working with query editor v2 Consider the following when working with query editor v2. + The maximum duration of a query is 24 hours. + The maximum query result size is 100 MB. If a call returns more than 100 MB of response data, the first 100 MB is returned with a warning. + You can run a query up to 300,000 characters long. + You can save a query up to 30,000 characters long. + By default, query editor v2 auto-commits each individual SQL command that runs. When a BEGIN statement is provided, statements within BEGIN-COMMIT or BEGIN-ROLLBACK block are run as a single transaction. For more information about transactions, see [BEGIN](https://docs.aws.amazon.com/redshift/latest/dg/r_BEGIN.html) in the *Amazon Redshift Database Developer Guide.* + The maximum number of warnings that query editor v2 displays while running a SQL statement is `10`. For example, when a stored procedure is run, no more than 10 RAISE statements are displayed. + The query editor v2 doesn't support an IAM `RoleSessionName` that contains commas (,). You might see an error similar to the following: Error Message : "'AROA123456789EXAMPLE:mytext,yourtext' is not a valid value for TagValue - it contains illegal characters" This issue arises when you define an IAM `RoleSessionName` that includes a comma and then use query editor v2 with that IAM role. For more information about an IAM `RoleSessionName`, see [RoleSessionName SAML attribute](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_providers_create_saml_assertions.html#saml_role-session-attribute) in the *IAM User Guide*. ## Account settings A user with the right IAM permissions can view and change **Account settings** for other users in the same AWS account. This administrator can view or set the following: + The maximum concurrent database connections per user in the account. This includes connections for **Isolated sessions**. When you change this value, it can take 10 minutes for the change to take effect. + Allow users in the account to export an entire result set from a SQL command to a file. + Load and display sample databases with some associated saved queries. + Specify an Amazon S3 path used by account users to load data from a local file. + View the KMS key ARN used to encrypt query editor v2 resources. # Connecting to an Amazon Redshift database To connect to a database, choose the cluster or workgroup name in the tree-view panel. If prompted, enter the connection parameters. When you connect to a cluster or workgroup and its databases, you usually provide a **Database** name. You also provide parameters required for one of the following authentication methods: **IAM Identity Center** With this method, connect to your Amazon Redshift data warehouse with your single sign-on credentials from your identity provider (IdP). Your cluster or workgroup must be enabled for IAM Identity Center in the Amazon Redshift console. For help setting up connections to IAM Identity Center, see [Connect Redshift with AWS IAM Identity Center for a single sign-on experience](redshift-iam-access-control-idp-connect.md). **Federated user** With this method, the principal tags of your IAM role or user must provide the connection details. You configure these tags in AWS Identity and Access Management or your identity provider (IdP). The query editor v2 relies on the following tags. + `RedshiftDbUser` – This tag defines the database user that is used by query editor v2. This tag is required. + `RedshiftDbGroups` – This tag defines the database groups that are joined when connecting to query editor v2. This tag is optional and its value must be a colon-separated list such as `group1:group2:group3`. Empty values are ignored, that is, `group1::::group2` is interpreted as `group1:group2`. These tags are forwarded to the `redshift:GetClusterCredentials` API to get credentials for your cluster. For more information, see [Setting up principal tags to connect a cluster or workgroup from query editor v2](query-editor-v2-getting-started.md#query-editor-v2-principal-tags-iam). **Temporary credentials using a database user name** This option is only available when connecting to a cluster. With this method, query editor v2, provide a **User name** for the database. The query editor v2 generates a temporary password to connect to the database as your database user name. A user using this method to connect must be allowed IAM permission to `redshift:GetClusterCredentials`. To prevent users from using this method, modify their IAM user or role to deny this permission. **Temporary credentials using your IAM identity** This option is only available when connecting to a cluster. With this method, query editor v2 maps a user name to your IAM identity and generates a temporary password to connect to the database as your IAM identity. A user using this method to connect must be allowed IAM permission to `redshift:GetClusterCredentialsWithIAM`. To prevent users from using this method, modify their IAM user or role to deny this permission. **Database user name and password** With this method, also provide a **User name** and **Password** for the database that you are connecting to. The query editor v2 creates a secret on your behalf stored in AWS Secrets Manager. This secret contains credentials to connect to your database. **AWS Secrets Manager** With this method, instead of a database name, you provide a **Secret** stored in Secrets Manager that contains your database and sign-in credentials. For information about creating a secret, see [Creating a secret for database connection credentials](redshift-secrets-manager-integration-create.md). When you select a cluster or workgroup with query editor v2, depending on the context, you can create, edit, and delete connections using the context (right-click) menu. You can view attributes such as the **Connection ARN** of the connection by choosing **Connection details**. You can also edit tags attached to the connection. # Browsing an Amazon Redshift database Within a database, you can manage schemas, tables, views, functions, and stored procedures in the tree-view panel. Each object in the view has actions associated with it in a context (right-click) menu. The hierarchical tree-view panel displays database objects. To refresh the tree-view panel to display database objects that might have been created after the tree-view was last displayed, choose the ![\[Circular arrow icon representing a refresh or reload action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-refresh.png) icon. Open the context (right-click) menu for an object to see what actions you can perform. ![\[Tree-view icons\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/sqlworkbench-tree-view.png) After you choose a table, you can do the following: + To start a query in the editor with a SELECT statement that queries all columns in the table, use **Select table**. + To see the attributes or a table, use **Show table definition**. Use this to see column names, column types, encoding, distribution keys, sort keys, and whether a column can contain null values. For more information about table attributes, see [CREATE TABLE](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) in the *Amazon Redshift Database Developer Guide*. + To delete a table, use **Delete**. You can either use **Truncate table** to delete all rows from the table or **Drop table** to remove the table from the database. For more information, see [TRUNCATE](https://docs.aws.amazon.com/redshift/latest/dg/r_TRUNCATE.html) and [DROP TABLE](https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE.html) in the *Amazon Redshift Database Developer Guide*. Choose a schema to **Refresh** or **Drop schema**. Choose a view to **Show view definition** or **Drop view**. Choose a function to **Show function definition** or **Drop function**. Choose a stored procedure to **Show procedure definition** or **Drop procedure**. # Creating database objects You can create database objects, including databases, schemas, tables, and user-defined functions (UDFs). You must be connected to a cluster or workgroup and a database to create database objects. ## Creating databases You can use query editor v2 to create databases in your cluster or workgroup. **To create a database** For information about databases, see [CREATE DATABASE](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_DATABASE.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Database**. 1. Enter a **Database name**. 1. (Optional) Select **Users and groups**, and choose a **Database user**. 1. (Optional) You can create the database from a datashare or the AWS Glue Data Catalog. For more information about AWS Glue, see [What is AWS Glue?](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html) in the *AWS Glue Developer Guide*. + (Optional) Select **Create using a datashare**, and choose a **Select a datashare**. The list includes producer datashares that can be used to create a consumer datashare in the current cluster or workgroup. + (Optional) Select **Create using AWS Glue Data Catalog**, and choose a **Choose an AWS Glue database**. In **Data catalog schema**, enter the name that will be used for the schema when referencing the data in a three-part name (database.schema.table). 1. Choose **Create database**. The new database displays in the tree-view panel. When you choose the optional step to query a database created from a datashare, connect to a Amazon Redshift database in the cluster or workgroup (for example, the default database `dev`), and use three-part notation (database.schema.table) that references the database name you created when you selected **Create using a datashare**. The datasharing database is listed in the query editor v2 editor tab, but it is not enabled for direct connection. When you choose the optional step to query a database created from a AWS Glue Data Catalog, connect to your Amazon Redshift database in the cluster or workgroup (for example, the default database `dev`), and use three-part notation (database.schema.table) that references the database name you created when you selected **Create using AWS Glue Data Catalog**, the schema you named in **Data catalog schema**, and the table in the AWS Glue Data Catalog. Similar to: ``` SELECT * FROM glue-database.glue-schema.glue-table ``` **Note** Confirm that you are connected to the default database using the connection method **Temporary credentials using your IAM identity**, and that your IAM credentials have been granted usage privilege to the AWS Glue database. ``` GRANT USAGE ON DATABASE glue-database to "IAM:MyIAMUser" ``` The AWS Glue database is listed in the query editor v2 editor tab, but it is not enabled for direct connection. For more information about querying an AWS Glue Data Catalog, see [Working with Lake Formation-managed datashares as a consumer](https://docs.aws.amazon.com/redshift/latest/dg/lake-formation-getting-started-consumer.html) and [Working with Lake Formation-managed datashares as a producer](https://docs.aws.amazon.com/redshift/latest/dg/lake-formation-getting-started-producer.html) in the *Amazon Redshift Database Developer Guide*. **Example creating a database as a datashare consumer** The following example describes a specific scenario that was used to create a database from a datashare using query editor v2. Review this scenario to learn how you can create a database from a datashare in your environment. This scenario uses two clusters, `cluster-base` (the producer cluster) and `cluster-view` (the consumer cluster). 1. Use the Amazon Redshift console to create a datashare for the table `category2` in cluster `cluster-base`. The producer datashare is named `datashare_base`. For information about creating datashares, see [Sharing data across clusters in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/datashare-overview.html) in the *Amazon Redshift Database Developer Guide*. 1. Use the Amazon Redshift console to accept the datashare `datashare_base` as a consumer for the table `category2` in cluster `cluster-view`. 1. View the tree-view panel in query editor v2 which shows the hierarchy of `cluster-base` as: + Cluster: `cluster-base` + Database: `dev` + Schema: `public` + Tables: `category2` 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Database**. 1. Enter `see_datashare_base` for **Database name**. 1. Select **Create using a datashare**, and choose a **Select a datashare**. Choose `datashare_base` to use as the source of the database you are creating. The tree-view panel in query editor v2 shows the hierarchy of `cluster-view` as: + Cluster: `cluster-view` + Database: `see_datashare_base` + Schema: `public` + Tables: `category2` 1. When you query the data, connect to the default database of the cluster `cluster-view` (typically named `dev`), but reference the datashare database `see_datashare_base` in your SQL. **Note** In the query editor v2 editor view, the selected cluster is `cluster-view`. The selected database is `dev`. The database `see_datashare_base` is listed but is not enabled for direct connection. You choose the `dev` database and reference `see_datashare_base` in the SQL you run. ``` SELECT * FROM "see_datashare_base"."public"."category2"; ``` The query retrieves data from the datashare `datashare_base` in the cluster `cluster_base`. **Example creating a database from an AWS Glue Data Catalog** The following example describes a specific scenario that was used to create a database from an AWS Glue Data Catalog using query editor v2. Review this scenario to learn how you can create a database from an AWS Glue Data Catalog in your environment. This scenario uses one cluster, `cluster-view` to contain the database you create. 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Database**. 1. Enter `data_catalog_database` for **Database name**. 1. Select **Create using a AWS Glue Data Catalog**, and choose **Choose an AWS Glue database**. Choose `glue_db` to use as the source of the database you are creating. Choose **Data catalog schema** and enter `myschema` as the schema name to use in three-part notation. The tree-view panel in query editor v2 shows the hierarchy of `cluster-view` as: + Cluster: `cluster-view` + Database: `data_catalog_database` + Schema: `myschema` + Tables: `category3` 1. When you query the data, connect to the default database of the cluster `cluster-view` (typically named `dev`), but reference the database `data_catalog_database` in your SQL. **Note** In the query editor v2 editor view, the selected cluster is `cluster-view`. The selected database is `dev`. The database `data_catalog_database` is listed but is not enabled for direct connection. You choose the `dev` database and reference `data_catalog_database` in the SQL you run. ``` SELECT * FROM "data_catalog_database"."myschema"."category3"; ``` The query retrieves data that is cataloged by AWS Glue Data Catalog. ## Creating schemas You can use query editor v2 to create schemas in your cluster or workgroup. **To create a schema** For information about schemas, see [Schemas](https://docs.aws.amazon.com/redshift/latest/dg/r_Schemas_and_tables.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Schema**. 1. Enter a **Schema name**. 1. Choose either **Local** or **External** as the **Schema type**. For more information about local schemas, see [CREATE SCHEMA](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_SCHEMA.html) in the *Amazon Redshift Database Developer Guide*. For more information about external schemas, see [CREATE EXTERNAL SCHEMA](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_SCHEMA.html) in the *Amazon Redshift Database Developer Guide*. 1. If you choose **External**, then you have the following choices of an external schema. + **Glue Data Catalog** – to create an external schema in Amazon Redshift that references tables in AWS Glue. Besides choosing the AWS Glue database, choose the IAM role associated with the cluster and the IAM role associated with the Data Catalog. + **PostgreSQL** – to create an external schema in Amazon Redshift that references an Amazon RDS for PostgreSQL or Amazon Aurora PostgreSQL-Compatible Edition database. Also provide the connection information to the database. For more information about federated queries, see [Querying data with federated queries](https://docs.aws.amazon.com/redshift/latest/dg/federated-overview.html) in the *Amazon Redshift Database Developer Guide*. + **MySQL** – to create an external schema in Amazon Redshift that references an Amazon RDS for MySQL or and Amazon Aurora MySQL-Compatible Edition database. Also provide the connection information to the database. For more information about federated queries, see [Querying data with federated queries](https://docs.aws.amazon.com/redshift/latest/dg/federated-overview.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Create schema**. The new schema appears in the tree-view panel. ## Creating tables You can use query editor v2 to create tables in your cluster or workgroup. **To create a table** You can create a table based on a comma-separated value (CSV) file that you specify or define each column of the table. For information about tables, see [Designing tables](https://docs.aws.amazon.com/redshift/latest/dg/c_designing-tables-best-practices.html) and [CREATE TABLE](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_NEW.html) in the *Amazon Redshift Database Developer Guide*. Choose **Open query in editor** to view and edit the CREATE TABLE statement before you run the query to create the table. 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and choose **Table**. 1. Choose a schema. 1. Enter a table name. 1. Choose ![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) **Add field** to add a column. 1. Use a CSV file as a template for the table definition: 1. Choose **Load from CSV**. 1. Browse to the file location. If you use a CSV file, be sure that the first row of the file contains the column headings. 1. Choose the file and choose **Open**. Confirm that the column names and data types are what you intend. 1. For each column, choose the column and choose the options that you want: + Choose a value for **Encoding**. + Choose a **Default value**. + Turn on **Automatically increment** if you want the column values to increment. Then specify a value for **Auto increment seed** and **Auto increment step**. + Turn on **Not NULL** if the column should always contain a value. + Enter a **Size** value for the column. + Turn on **Primary key** if you want the column to be a primary key. + Turn on **Unique key** if you want the column to be a unique key. 1. (Optional) Choose **Table details** and choose any of the following options: + Distribution key column and style. + Sort key column and sort type. + Turn on **Backup** to include the table in snapshots. + Turn on **Temporary table** to create the table as a temporary table. 1. Choose **Open query in editor** to continue specifying options to define the table or choose **Create table** to create the table. ## Creating functions You can use query editor v2 to create functions in your cluster or workgroup. **To create a function** 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and choose **Function**. 1. For **Type**, choose **SQL** or **Python**. 1. Choose a value for **Schema**. 1. Enter a value for **Name** for the function. 1. Enter a value for **Volatility** for the function. 1. Choose **Parameters** by their data types in the order of the input parameters. 1. For **Returns**, choose a data type. 1. Enter the **SQL program** or **Python program** code for the function. 1. Choose **Create**. For more information about user-defined functions (UDFs), see [Creating user-defined functions](https://docs.aws.amazon.com/redshift/latest/dg/user-defined-functions.html) in the *Amazon Redshift Database Developer Guide*. # Viewing query and tab history You can view your query history with query editor v2. Only queries that you ran using query editor v2 appear in the query history. Both queries that ran from using an **Editor** tab or **Notebook** tab are shown. You can filter the list displayed by a time period, such as `This week`, where a week is defined as Monday–Sunday. The list of queries fetches 25 rows of queries that match your filter at a time. Choose **Load more** to see the next set. Choose a query and from the **Actions** menu. The actions available depend on whether the chosen query has been saved. You can choose the following operations: + **View query details** – Displays a query details page with more information about the query that ran. + **Open query in a new tab** – Opens a new editor tab and primes it with the chosen query. If still connected, the cluster or workgroup and database are automatically selected. To run the query, first confirm that the correct cluster or workgroup and database are chosen. + **Open source tab** – If it is still open, navigates to the editor or notebook tab that contained the query when it ran. The contents of the editor or notebook might have changed after the query ran. + **Open saved query** – Navigates to the editor or notebook tab and opens the query. You can also view the history of queries run in an **Editor** tab or the history of queries run in a **Notebook** tab. To see a history of queries in a tab, choose **Tab history**. Within the tab history, you can do the following operations: + **Copy query** – Copies the query version SQL content to the clipboard. + **Open query in a new tab** – Opens a new editor tab and primes it with the chosen query. To run the query, you must choose the cluster or workgroup and database. + **View query details** – Displays a query details page with more information about the query that ran. # Interacting with Amazon Q generative SQL **Note** Amazon Q generative SQL support is only available in the following AWS Regions: US East (N. Virginia) Region (us-east-1) US East (Ohio) Region (us-east-2) US West (Oregon) Region (us-west-2) Asia Pacific (Mumbai) Region (ap-south-1) Asia Pacific (Seoul) Region (ap-northeast-2) Asia Pacific (Singapore) Region (ap-southeast-1) Asia Pacific (Sydney) Region (ap-southeast-2) Asia Pacific (Tokyo) Region (ap-northeast-1) Canada (Central) Region (ca-central-1) Europe (Frankfurt) Region (eu-central-1) Europe (Ireland) Region (eu-west-1) Europe (London) Region (eu-west-2) Europe (Paris) Region (eu-west-3) South America (São Paulo) Region (sa-east-1) For information about where your data is processed, see [Cross region inference in Amazon Q Developer](https://docs.aws.amazon.com/amazonq/latest/qdeveloper-ug/cross-region-inference.html) in the *Amazon Q Developer User Guide*. 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. The SQL generated is for the database your notebook is connected to. When interacting with Amazon Q 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 and specific names of objects in your database when you know them. 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." Submit text of errors you receive from running generated SQL as prompts back to the Amazon Q generative SQL. It learns from these errors to produce better SQL. Add your schema to the SQL search path to signal that schema should be used. For example, add the tickit schema when the data is in the tickit schema rather than the public schema. ``` set search_path to '$user', tickit; ``` ## Considerations when interacting with Amazon Q 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 Amazon Q generative SQL, you need permission `sqlworkbench:GetQSqlRecommendations` in your IAM policy, in addition to other permissions specified in the AWS managed policy for query editor v2. For more information about AWS managed policies, see [Accessing the query editor v2](query-editor-v2-getting-started.md#query-editor-v2-configure). + 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](https://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_EXTERNAL_SCHEMA.html) 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](https://docs.aws.amazon.com/redshift/latest/dg/r_roles-default.html) 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 Amazon Q generative SQL chat to generate SQL** 1. In the **Editor** tab of the query editor v2, open a notebook. 1. Choose the ![\[Generative SQL panel\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-amazon-q.png) 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 Amazon Q generative SQL responds with suggested SQL. Any errors encountered are returned to you in the chat panel. 1. Choose **Add to notebook** to add a Markdown cell with your prompt and a SQL cell with the suggested SQL to your notebook. 1. (Optional) Provide feedback regarding the SQL generated by choosing the ![\[Helpful feedback\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-thumbs-up.png) helpful feedback icon or the ![\[Not helpful feedback\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-thumbs-down.png) not helpful feedback icon. You can categorize not helpful feedback as `Incorrect tables/columns`, `Incorrect predicates/literals/group bys`, `Incorrect SQL structure`, or `Other`. In addition, you can provide some free-form text with your feedback about the accuracy of the SQL. 1. (Optional) Choose **Regenerate SQL** to generate another response for the same prompt. You can choose to **Regenerate SQL** one time for the current prompt. 1. (Optional) In the generative SQL chat panel, choose the ![\[More\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-vmore.png) **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 AWS account. This administrator must have permission `sqlworkbench:UpdateAccountQSqlSettings` in their IAM policy, in addition to other permissions specified in the AWS managed policy for query editor v2. For more information about managed policies, see [Permissions required to use the query editor v2](redshift-iam-access-control-identity-based.md#redshift-policy-resources.required-permissions.query-editor-v2). **For an administrator to turn on generative SQL chat for all users in the account** 1. Choose the ![\[Settings\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-cog.png) **Settings** icon to show a menu of the different settings screens. 1. Then choose the ![\[Generative SQL settings\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-amazon-q.png) Generative SQL settings icon to show the **Q generative SQL settings** page. 1. Select **Q generative SQL settings** to turn on the generative SQL capability for users in the account. After you turn on Amazon Q generative SQL, you can view the number of prompts left in your allocation. The query editor v2 administrator can enable users in the account to use Amazon Q Developer Pro tier. To use the Pro tier, set up your users with IAM Identity Center and subscribe each user to Amazon Q Developer Pro tier. For information about setting up IAM Identity Center with Amazon Redshift, see [Connect Redshift with AWS IAM Identity Center for a single sign-on experience](redshift-iam-access-control-idp-connect.md). For information about Amazon Q Developer pricing, see [Amazon Q Developer pricing](https://aws.amazon.com/q/developer/pricing/). When using Amazon Q Developer Free tier, the total number of prompts of all users of an AWS account is limited to 1,000 in a month. When using Amazon Q Developer Pro tier, the total number of prompts that any individual user can submit is limited to 1,000 in a month. You can view the number of prompts available on the **Settings** page. For information about Amazon Q Developer pricing, see [Amazon Q Developer pricing](https://aws.amazon.com/q/developer/pricing/). ## Custom context The query editor v2 administrator can specify a *custom context* to tailor the generated SQL to your environment. A custom context provides domain knowledge and preferences to provide fine-grained control over SQL generation. A custom context is defined in a JSON file which can be uploaded by the query editor v2 administrator to Amazon Q generative SQL. The JSON keys used to personalize generated SQL for a data warehouse are follows. All table references need to follow the three-part notation `database.schema.table`. **Resources** A resource specifies the scope or portion of a data asset to which the custom context is applied. **ResourceId** Specifies a unique identifier of the resource. For an Amazon Redshift cluster, specify the `cluster id`. For an Redshift Serverless workgroup specify the `workgroup name`. **ResourceType** Valid value: `REDSHIFT_WAREHOUSE`. **TablesToInclude** Specifies a set of tables that are considered for SQL generation. This field is crucial when you want to limit the scope of SQL queries to a defined subset of available tables. It can help optimize the generation process by reducing unnecessary table references. You can pair this field with `TablesToExclude` for finer control over query generation. **TablesToExclude** Specifies the set of tables that are excluded from SQL generation. Use this when certain tables are irrelevant or should not be considered in the query generation process. **TableAnnotations** Provides metadata or supplementary information about the tables in use. These annotations can include table descriptions, usage notes, or any additional attributes that help Amazon Q generative SQL better understand the context or structure of the table. This is valuable for enhancing the accuracy of SQL generation by adding clarity to the table definitions. **ColumnsToInclude** Defines which columns from the specified tables are included when generating SQL queries. This field helps Amazon Q generative SQL focus on the relevant columns and improves performance by narrowing down the scope of data retrieval. It ensures the Amazon Q generative SQL only pulls data that’s needed for the given query context. **ColumnsToExclude** Specifies the columns that are omitted from consideration in SQL generation. This can be used when certain columns contain irrelevant or redundant data that should not be considered by Amazon Q generative SQL. By managing the inclusion and exclusion of columns, you can refine the results and maintain control over the data retrieved. **ColumnAnnotations** Similar to `TableAnnotations`, this field provides metadata or annotations specific to individual columns. These annotations can offer insight into column definitions or special handling instructions. This information is beneficial in guiding the SQL generation process and ensuring that columns are used appropriately in queries. **CuratedQueries** A set of predefined question and answer examples, where the question is written in natural language (NLQ) and the answer is the corresponding SQL query. These examples help Amazon Q generative SQL understand the kinds of queries it is expected to generate. They serve as reference points to improve the accuracy and relevance of Amazon Q generative SQL outputs. **CustomDocuments** Additional pieces of information or hints provided to Amazon Q generative SQL, such as definitions, domain-specific knowledge, or explanations. For example, if your business unit uses a unique way to calculate a value, for example "in manufacturing division total sales is price \$1 revenue" this can be documented here. These documents enhance the Amazon Q generative SQL ability to interpret the natural language inputs by providing additional context. **AdditionalTables** Specifies any additional tables that should be considered for SQL generation but are not part of the data stored in the data warehouse. This allows the Amazon Q generative SQL to integrate external data sources into its SQL generation logic, broadening its capacity to handle complex data environments. **AppendToPrompt** Additional instructions or guidelines provided to Amazon Q generative SQL to guide the SQL generation process. This can include specific directives on how to structure the query, preferences for certain SQL constructs, or any other high-level instruction that enhances the quality of the Amazon Q generative SQL output. The following example custom context shows you the format of the JSON file and defines the following: + Defines a custom context for the Amazon Redshift data warehouse for cluster `mycluster`. + Defines specific tables and columns to include and to exclude to help optimize the SQL generation process. + Defines annotations for the tables and columns called out to include. + Defines sample curated queries for Amazon Q generative SQL to use. + Defines custom documents and guardrails to use when generating SQL. + Defines the DDL for additional tables to use when generating SQL. ``` { "resources": [ { "ResourceId": "mycluster", "ResourceType": "REDSHIFT_WAREHOUSE", "TablesToInclude": [ "database.schema.table1", "database.schema.table2" ], "TablesToExclude": [ "database.schema.table3", "database.schema.table4" ], "ColumnsToInclude": { "database.schema.table1": [ "col1", "col2" ], "database.schema.table2": [ "col1", "col2" ] }, "ColumnsToExclude": { "database.schema.table5": [ "col1", "col2" ], "database.schema.table6": [ "col1", "col2" ] }, "TableAnnotations": { "database.schema.table1": "table1 refers to Q3 sales", "database.schema.table2": "table2 refers to Q4 sales" }, "ColumnAnnotations": { "database.schema.table1": { "col1": "col1 refers to Q3 sale total", "col2": "col2 refers to sale location" }, "database.schema.table2": { "col1": "col2 refers to Q4 sale total", "col2": "col2 refers to sale location" } }, "CuratedQueries": [ { "Question": "what is the sales data for Q3", "Answer": "SELECT * FROM table1" }, { "Question": "what is the sales data for Q4", "Answer": "SELECT * FROM table2" } ], "CustomDocuments": [ "in manufacturing division total sales is price * revenue", "in research division total sales is price * revenue" ], "AdditionalTables": { "database.schema.table8": "create table database.schema.table8(col1 int)", "database.schema.table9": "create table database.schema.table9(col1 int)" }, "AppendToPrompt": "Apply these guardrails: Queries should never return the secretId field of a user." } ] } ``` # Tutorial: 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](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) 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 data into a database](query-editor-v2-loading.md). 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 Amazon Q generative SQL** 1. In the **Editor**, connect to a cluster or workgroup that contains the TICKIT sample data. 1. Create an empty notebook and choose the ![\[Generative SQL\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-amazon-q.png) Generative SQL icon to open the chat panel. 1. 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 ``` 1. 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 ... ``` 1. 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 ``` 1. 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. 1. 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 ``` 1. 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 Amazon Q generative SQL. You can experiment even more with this generative AI technology to help you start authoring SQL to query your database. # Loading data into a database You can use query editor v2 to load data into a database in an Amazon Redshift cluster or workgroup. This section covers how to load sample data, data from S3, and data from a local file setup and workflow. ## Sample data The query editor v2 comes with sample data and notebooks available to be loaded into a sample database and corresponding schema. To load sample data, choose the ![\[External\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/external.png) icon associated with the sample data you want to load. The query editor v2 then loads the data into a schema in database `sample_data_dev` and creates a folder of saved notebooks. The following sample datasets are available. **tickit** Most of the examples in the Amazon Redshift documentation use sample data called `tickit`. This data consists of seven tables: two fact tables and five dimensions. When you load this data, the schema `tickit` is updated with sample data. For more information about the `tickit` data, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) in the *Amazon Redshift Database Developer Guide*. **tpch** This data is used for a decision support benchmark. When you load this data, the schema `tpch` is updated with sample data. For more information about the `tpch` data, see [TPC-H](http://www.tpc.org/tpch/). **tpcds** This data is used for a decision support benchmark. When you load this data, the schema `tpcds` is updated with sample data. For more information about the `tpcds` data, see [TPC-DS](http://www.tpc.org/tpcds/). # Loading data from Amazon S3 You can load Amazon S3 data into an existing or new table. **To load data into an existing table** The COPY command is used by query editor v2 to load data from Amazon S3. The COPY command generated and used in the query editor v2 load data wizard supports many of the parameters available to the COPY command syntax to copy from Amazon S3. For information about the COPY command and its options used to load data from Amazon S3, see [COPY from Amazon Simple Storage Service](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html) in the *Amazon Redshift Database Developer Guide*. 1. Confirm that the table is already created in the database where you want to load data. 1. Confirm that you are connected to the target database in the tree-view panel of query editor v2 before continuing. You can create a connection using the context menu (right-click) to the cluster or workgroup where the data will be loaded. Choose ![\[Load\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-upload.png)**Load data**. 1. For **Data source**, choose **Load from S3 bucket**. 1. In **S3 URIs**, choose **Browse S3** to look for the Amazon S3 bucket that contains the data to load. 1. If the specified Amazon S3 bucket isn't in the same AWS Region as the target table, then choose the **S3 file location** for the AWS Region where the data is located. 1. Choose **This file is a manifest file** if the Amazon S3 file is actually a manifest containing multiple Amazon S3 bucket URIs. 1. Choose the **File format** for the file to be uploaded. The supported data formats are CSV, JSON, DELIMITER, FIXEDWIDTH, SHAPEFILE, AVRO, PARQUET, and ORC. Depending on the specified file format, you can choose the respective **File options**. You can also select **Data is encrypted** if the data is encrypted and enter the Amazon Resource Name (ARN) of the KMS key used to encrypt the data. If you choose CSV or DELIMITER, you can also choose the **Delimiter character** and whether to **Ignore header rows** if the specified number of rows are actually column names and not data to load. 1. Choose a compression method to compress your file. The default is no compression. 1. (Optional) The **Advanced settings** support various **Data conversion parameters** and **Load operations**. Enter this information as needed for your file. For more information about data conversion and data load parameters, see [Data conversion parameters](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html) and [Data load operations](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Next**. 1. Choose **Load existing table**. 1. Confirm or choose the location of the **Target table** including **Cluster or workgroup**, **Database**, **Schema**, and **Table** name where the data is loaded. 1. Choose an **IAM role** that has the required permissions to load data from Amazon S3. 1. (Optional) Choose column names to enter them in **Column mapping** to map columns in the order of the input data file. 1. Choose **Load data** to start the data load. When the load completes, the query editor displays with the generated COPY command that was used to load your data. The **Result** of the COPY is shown. If successful, you can now use SQL to select data from the loaded table. When there is an error, query the system view STL\$1LOAD\$1ERRORS to get more details. For information about COPY command errors, see [STL\$1LOAD\$1ERRORS](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html) in the *Amazon Redshift Database Developer Guide*. When you load data into a new table, query editor v2 first creates the table in the database, then loads the data as separate actions in the same workflow. **To load data into a new table** The COPY command is used by query editor v2 to load data from Amazon S3. The COPY command generated and used in the query editor v2 load data wizard supports many of the parameters available to the COPY command syntax to copy from Amazon S3. For information about the COPY command and its options used to load data from Amazon S3, see [COPY from Amazon Simple Storage Service](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html) in the *Amazon Redshift Database Developer Guide*. 1. Confirm that you are connected to the target database in the tree-view panel of query editor v2 before continuing. You can create a connection using the context menu (right-click) to the cluster or workgroup where the data will be loaded. Choose ![\[Load\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-upload.png)**Load data**. 1. For **Data source**, choose **Load from S3 bucket**. 1. In **S3 URIs**, choose **Browse S3** to look for the Amazon S3 bucket that contains the data to load. 1. If the specified Amazon S3 bucket isn't in the same AWS Region as the target table, then choose the **S3 file location** for the AWS Region where the data is located. 1. Choose **This file is a manifest file** if the Amazon S3 file is actually a manifest containing multiple Amazon S3 bucket URIs. 1. Choose the **File format** for the file to be uploaded. The supported data formats are CSV, JSON, DELIMITER, FIXEDWIDTH, SHAPEFILE, AVRO, PARQUET, and ORC. Depending on the specified file format, you can choose the respective **File options**. You can also select **Data is encrypted** if the data is encrypted and enter the Amazon Resource Name (ARN) of the KMS key used to encrypt the data. If you choose CSV or DELIMITER, you can also choose the **Delimiter character** and whether to **Ignore header rows** if the specified number of rows are actually column names and not data to load. 1. Choose a compression method to compress your file. The default is no compression. 1. (Optional) The **Advanced settings** support various **Data conversion parameters** and **Load operations**. Enter this information as needed for your file. For more information about data conversion and data load parameters, see [Data conversion parameters](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html) and [Data load operations](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Next**. 1. Choose **Load new table**. The table columns are inferred from the input data. You can modify the definition of the table schema by adding columns and table details. To revert to the query editor v2 inferred table schema, choose **Restore to defaults**. 1. Confirm or choose the location of the **Target table** including **Cluster or workgroup**, **Database**, and **Schema** where the data is loaded. Enter a **Table** name to be created. 1. Choose an **IAM role** that has the required permissions to load data from Amazon S3. 1. Choose **Create table** to create the table using the definition shown. A review summary of the table definition is displayed. The table is created in the database. To later delete the table, run a DROP TABLE SQL command. For more information, see [DROP TABLE](https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Load data** to start the data load. When the load completes, the query editor displays with the generated COPY command that was used to load your data. The **Result** of the COPY is shown. If successful, you can now use SQL to select data from the loaded table. When there is an error, query the system view STL\$1LOAD\$1ERRORS to get more details. For information about COPY command errors, see [STL\$1LOAD\$1ERRORS](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html) in the *Amazon Redshift Database Developer Guide*. # Loading data from a local file setup and workflow You can load data from a local file into an existing or new table. ## Administrator setup to load data from a local file Your query editor v2 administrator must specify the common Amazon S3 bucket in the **Account settings** window. The account users must be configured with the proper permissions. + Required IAM permissions – the users of load from local file must have `s3:ListBucket`, `s3:GetBucketLocation`, `s3:putObject`, `s3:getObject`, and `s3:deleteObject` permissions. The *optional-prefix* can be specified to limit query editor v2 related use of this bucket to objects with this prefix. You might use this option when using this same Amazon S3 bucket for uses other than query editor v2. For more information about buckets and prefixes, see [Managing user access to specific folders](https://docs.aws.amazon.com/AmazonS3/latest/userguide/example-bucket-policies.html#example-bucket-policies-folders) in the *Amazon Simple Storage Service User Guide*. To make sure that cross user data access is not allowed, we recommend that the query editor v2 administrator use an Amazon S3 bucket policy to restrict object access based on `aws:userid`. The following example allows Amazon S3 permissions to a ** with read/write access only to Amazon S3 objects with the `aws:userid` as a prefix. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Action": [ "s3:ListBucket", "s3:GetBucketLocation" ], "Resource": [ "arn:aws:s3:::" ] }, { "Effect": "Allow", "Action": [ "s3:PutObject", "s3:GetObject", "s3:DeleteObject" ], "Resource": [ "arn:aws:s3:::[/]/${aws:userid}/*" ] } ] } ``` ------ + Data separation – we recommend that users not have access to each other's data (even briefly). Load from a local file uses the staging Amazon S3 bucket set up by the query editor v2 administrator. Configure the bucket policy for the staging bucket to provide data separation between users. The following example shows a bucket policy that separates data between users of the **. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ {"Sid": "userIdPolicy", "Effect": "Deny", "Principal": "*", "Action": ["s3:PutObject", "s3:GetObject", "s3:DeleteObject"], "NotResource": [ "arn:aws:s3:::[/]/${aws:userid}/*" ] } ] } ``` ------ ## Loading data from a local file **To load local file data into an existing table** Your query editor v2 administrator must specify the common Amazon S3 bucket in the **Account settings** window. query editor v2 automatically uploads the local file to a common Amazon S3 bucket used by your account, and then uses the COPY command to load data. The COPY command generated and run by the query editor v2 load local file window supports many of the parameters available to the COPY command syntax to copy from Amazon S3. For information about the COPY command and its options used to load data from Amazon S3, see [COPY from Amazon S3](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html) in the *Amazon Redshift Database Developer Guide*. 1. Confirm that the table is already created in the database where you want to load data. 1. Confirm that you are connected to the target database in the tree-view panel of query editor v2. You can create a connection using the context menu (right-click) to the cluster or workgroup where the data will be loaded. 1. Choose ![\[Load\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-upload.png)**Load data**. 1. For **Data source**, choose **Load from local file**. 1. Choose **Browse** to find the file that contains the data to **Load file**. By default, files with extension `.csv`, `.avro` `.parquet`, and `.orc` are shown, but you can choose other file types. The maximum file size is 100 MB. 1. Choose the **File format** for the file to be uploaded. The supported data formats are CSV, JSON, DELIMITER, FIXEDWIDTH, SHAPEFILE, AVRO, PARQUET, and ORC. Depending on the specified file format, you can choose the respective **File options**. You can also select **Data is encrypted** if the data is encrypted and enter the Amazon Resource Name (ARN) of the KMS key used to encrypt the data. If you choose CSV or DELIMITER, you can also choose the **Delimiter character** and whether to **Ignore header rows** if the specified number of rows are actually column names and not data to load. 1. (Optional) The **Advanced settings** support various **Data conversion parameters** and **Load operations**. Enter this information as needed for your file. For more information about data conversion and data load parameters, see [Data conversion parameters](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html) and [Data load operations](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Next**. 1. Choose **Load existing table**. 1. Confirm or choose the location of the **Target table** including **Cluster or workgroup**, **Database**, **Schema**, and **Table** name where the data is loaded. 1. (Optional) You can choose column names to enter in **Column mapping** to map columns in the order of the input data file. 1. Choose **Load data** to start the data load. When the load completes, a message is displayed whether the load was successful or not. If successful, you can now use SQL to select data from the loaded table. When there is an error, query the system view STL\$1LOAD\$1ERRORS to get more details. For information about COPY command errors, see [STL\$1LOAD\$1ERRORS](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html) in the *Amazon Redshift Database Developer Guide*. The COPY command template that was used to load data appears in your **Query history**. This COPY command template shows some of the parameters used, but it can't be run directly in an editor tab. For more information about query history, see [Viewing query and tab history](query-editor-v2-history.md). When you load data into a new table, query editor v2 first creates the table in the database, then loads the data as separate actions in the same workflow. **To load local file data into a new table** Your query editor v2 administrator must specify the common Amazon S3 bucket in the **Account settings** window. The local file is automatically uploaded to a common Amazon S3 bucket used by your account, and then the COPY command is used by query editor v2 to load data. The COPY command generated and run by the query editor v2 load local file window supports many of the parameters available to the COPY command syntax to copy from Amazon S3. For information about the COPY command and its options used to load data from Amazon S3, see [COPY from Amazon S3](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-source-s3.html) in the *Amazon Redshift Database Developer Guide*. 1. Confirm that you are connected to the target database in the tree-view panel of query editor v2. You can create a connection using the context menu (right-click) to the cluster or workgroup where the data will be loaded. 1. Choose ![\[Load\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-upload.png)**Load data**. 1. For **Data source**, choose **Load from local file**. 1. Choose **Browse** to find the file that contains the data to **Load file**. By default, files with extension `.csv`, `.avro` `.parquet`, and `.orc` are shown, but you can choose other file types. The maximum file size is 100 MB. 1. Choose the **File format** for the file to be uploaded. The supported data formats are CSV, JSON, DELIMITER, FIXEDWIDTH, SHAPEFILE, AVRO, PARQUET, and ORC. Depending on the specified file format, you can choose the respective **File options**. You can also select **Data is encrypted** if the data is encrypted and enter the Amazon Resource Name (ARN) of the KMS key used to encrypt the data. If you choose CSV or DELIMITER, you can also choose the **Delimiter character** and whether to **Ignore header rows** if the specified number of rows are actually column names and not data to load. 1. (Optional) The **Advanced settings** support various **Data conversion parameters** and **Load operations**. Enter this information as needed for your file. For more information about data conversion and data load parameters, see [Data conversion parameters](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-conversion.html) and [Data load operations](https://docs.aws.amazon.com/redshift/latest/dg/copy-parameters-data-load.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Next**. 1. Choose **Load new table**. 1. Confirm or choose the location of the **Target table** including **Cluster or workgroup**, **Database**, and **Schema** where the data is loaded. Enter a **Table** name to be created. 1. Choose **Create table** to create the table using the definition shown. A review summary of the table definition is displayed. The table is created in the database. To later delete the table, run a DROP TABLE SQL command. For more information, see [DROP TABLE](https://docs.aws.amazon.com/redshift/latest/dg/r_DROP_TABLE) in the *Amazon Redshift Database Developer Guide*. 1. Choose **Load data** to start the data load. When the load completes, a message displays indicating whether the load was successful or not. If successful, you can now use SQL to select data from the loaded table. When there is an error, query the system view STL\$1LOAD\$1ERRORS to get more details. For information about COPY command errors, see [STL\$1LOAD\$1ERRORS](https://docs.aws.amazon.com/redshift/latest/dg/r_STL_LOAD_ERRORS.html) in the *Amazon Redshift Database Developer Guide*. The COPY command template that was used to load data appears in your **Query history**. This COPY command template shows some of the parameters used, but it can't be run directly in an editor tab. For more information about query history, see [Viewing query and tab history](query-editor-v2-history.md). # Authoring queries with Amazon Redshift You can enter a query in the editor or select a saved query from the **Queries** list and choose **Run**. By default, **Limit 100** is set to limit the results to 100 rows. You can turn off this option to return a larger result set. If you turn off this option, you can include the LIMIT option in your SQL statement if you want to avoid very large result sets. For more information, see [ORDER BY clause](https://docs.aws.amazon.com/redshift/latest/dg/r_ORDER_BY_clause.html) in the *Amazon Redshift Database Developer Guide*. To display a query plan in the results area, turn on **Explain**. Turn on **Explain graph** for the results to also display a graphical representation of the explain plan. To save a query to the **Queries** folder, choose **Save**. For a successful query, a success message appears. If the query returns information, the results display in the **Results** section. If the number of results exceeds the display area, numbers appear at the top of the results area. You can choose the numbers to display successive pages of results. You can filter and sort **Result** for each column. To enter filter criteria in the result column header, hover over the column to see a menu (![\[Filter menu\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/menu.png)) where you can enter criteria to filter the column. If the query contains an error, the query editor v2 displays an error message in the results area. The message provides information on how to correct the query. You can export or copy the results of your query by using the context (right-click) menu in the results area as follows: + Choose **Copy to** and either **JSON** or **CSV** to download the rows that are selected to a file. + Choose **Copy rows** to copy the selected rows to the clipboard. + Choose **Copy rows with headers** to copy the selected rows with column headers to the clipboard. You can also choose **Export** in the results area, then choose either **JSON** or **CSV** to download the entire set of row results to a file. The number of rows in the result set might be limited by the **Limit** option or the SQL `limit` clause in the query. The maximum size of the downloaded result set is 5 MB. You can also use the shortcut Ctrl\$1C on Windows or Cmd\$1C on macOS to copy data from the current results page to the clipboard. If no rows are selected, then the cell with focus is copied to the clipboard. If rows are selected, then the selected rows are copied to the clipboard. To add a new query tab, choose the ![\[New query tab\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) icon, then **Editor**, which appears in the row with the query tabs. The query tab is either using an `Isolated session` or not. With an isolated session, the results of a SQL command, such as creating a temporary table in one editor tab, are not visible in another editor tab. When you open an editor tab in query editor v2, the default is an isolated session. **To run a query** 1. In the query area, do one of the following: + Enter a query. + Paste a query that you copied. + Choose the **Queries** folder, open the context menu (right-click) a saved query, and choose **Open query**. 1. Confirm that you chose the correct **Cluster** or **Workgroup**, and **Database** value for the SQL you plan to run. Initially, you can choose your **Cluster** or **Workgroup** in the tree view. Choose your **Database** in the tree view also. You can change the **Cluster** or **Workgroup**, and **Database** within each editor tab with the drop-down control located near the **Isolated session** header of each editor tab. For each editor tab, you choose whether to run the SQL in an **Isolated session**. An isolated session has its own connection to a database. Use it to run SQL that is isolated from other query editor sessions. For more information about connections, see [Opening query editor v2](query-editor-v2-open.md). 1. Choose **Run**. The **Result** area opens and displays the query results. **To display the explain plan for a query** 1. Select the query. 1. Turn on **Explain**. By default, the **Explain graph** is also on. 1. Choose **Run**. The query runs and the explain plan displays in the query **Result** area. The query editor v2 supports the following features: + You can author queries with multiple SQL statements in one query tab. The queries are run serially and multiple results tabs open for each query. + You can author queries with session variables and temporary tables. + You can author queries with replaceable parameters designated by `${parameter}`. You can author your SQL query with multiple replaceable parameters and use the same parameter in multiple places in your SQL statement. When the query runs, a window is presented to enter the value of the parameter. Each time you run the query, the window is presented to enter your parameter values. For an example, see [Example: Sales greater than a specific parameter](#query-editor-v2-example-sales-qtysold-greater-than-parameter). + Queries are versioned automatically. You can choose an earlier version of a query to run. + You don't need to wait for a query to complete before continuing on with your workflow. Your queries continue to run even if you close the query editor. + When authoring queries, automatic completion of schema, table, and column names is supported. The SQL editor supports the following features: + The beginning and ending brackets used in SQL have matching colors. Vertical lines are shown in the editor to help you match brackets. + You can collapse and expand sections of your SQL. + You can search and replace text in your SQL. + You can use shortcut keys for several common editing tasks. + SQL errors are highlighted in the editor for convenient location of problem areas. For a demo of editor features, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/9JAq0yDs0YE/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/9JAq0yDs0YE) ## Query examples Following, you can find descriptions of the various types of queries that you can run. The data used in many of these queries is from the `tickit` sample schema. For more information about loading the sample `tickit` data, see [Loading data into a database](query-editor-v2-loading.md). For more information about the `tickit` sample data, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) in the *Amazon Redshift Database Developer Guide*. When you run these example queries, confirm that you choose the correct database in the editor, such as `sample_data_dev`. **Topics** + [Example: Setting session variables](#query-editor-v2-example-set-session-variable) + [Example: Top event by total sales](#query-editor-v2-example-top-event-sales) + [Example: Sales greater than a specific parameter](#query-editor-v2-example-sales-qtysold-greater-than-parameter) + [Example: Create a temporary table](#query-editor-v2-example-create-temporary-table) + [Example: Selecting from a temporary table](#query-editor-v2-example-select-from-temporary-table) ### Example: Setting session variables The following command sets the `search_path` server configuration parameter to *public* for the session. For more information, see [SET](https://docs.aws.amazon.com/redshift/latest/dg/r_SET.html) and [search\$1path](https://docs.aws.amazon.com/redshift/latest/dg/r_search_path.html) in the *Amazon Redshift Database Developer Guide*. ``` set search_path to public; ``` ### Example: Top event by total sales The following query finds the event with the most sales. ``` select eventname, count(salesid) totalorders, sum(pricepaid) totalsales from sales, event where sales.eventid=event.eventid group by eventname order by 3; ``` Following is a partial list of the results. ``` eventname totalorders totalsales White Christmas 20 9352 Joshua Radin 38 23469 Beach Boys 58 30383 Linda Ronstadt 56 35043 Rascal Flatts 76 38214 Billy Idol 67 40101 Stephenie Meyer 72 41509 Indigo Girls 57 45399 ... ``` ### Example: Sales greater than a specific parameter The following query finds sales where the quantity sold is greater than the parameter specified by `${numberoforders}`. When the parameter value is `7`, the result is 60 rows. When you run the query, the query editor v2 displays a **Run query form** window to gather the value of parameters in the SQL statement. ``` select salesid, qtysold from sales where qtysold > ${numberoforders} order by 2; ``` Following is a partial list of the results. ``` salesid qtysold 20005 8 21279 8 130232 8 42737 8 74681 8 67103 8 105533 8 91620 8 121552 8 ... ``` ### Example: Create a temporary table The following statement creates the temporary table *eventsalestemp* by selecting information from the *sales* and *event* tables. ``` create temporary table eventsalestemp as select eventname, count(salesid) totalorders, sum(pricepaid) totalsales from sales, event where sales.eventid=event.eventid group by eventname; ``` ### Example: Selecting from a temporary table The following statement selects events, total orders, and total sales from the temporary table *eventsalestemp*, ordered by total orders. ``` select eventname, totalorders, totalsales from eventsalestemp order by 2; ``` Following is a partial list of results. ``` eventname totalorders totalsales White Christmas 20 9352 Joshua Radin 38 23469 Martina McBride 50 52932 Linda Ronstadt 56 35043 Indigo Girls 57 45399 Beach Boys 58 30383 ... ``` # Notebooks in Amazon Redshift You can use notebooks to organize, annotate, and share multiple SQL queries in a single document. You can add multiple SQL query and Markdown cells to a notebook. Notebooks provide a way to group queries and explanations associated with a data analysis in a single document by using multiple query and Markdown cells. You can add text and format the appearance using Markdown syntax to provide context and additional information for your data analysis tasks. You can share your notebooks with team members. To use notebooks, you must add permission for notebooks to your IAM principal (an IAM user or IAM role). As a best practice, we recommend attaching permissions policies to an IAM role and then assigning it to users and groups as needed. For more information, see [Identity and access management in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/mgmt/redshift-iam-authentication-access-control.html). You can add the permission to one of the query editor v2 managed policies. For more information, see [Accessing the query editor v2](query-editor-v2-getting-started.md#query-editor-v2-configure). You can run all the cells of a notebook sequentially. The SQL query cell of a notebook has most of the same capabilities as a query editor tab. For more information, see [Authoring queries with Amazon Redshift](query-editor-v2-query-run.md). The following are differences between a query editor tab and a SQL cell in a notebook. + There isn't a control to run `Explain` on a SQL statement in a notebook. + You can create only one chart per SQL cell in a notebook. You can export and import notebooks to files created with query editor v2. The file extension is `.ipynb` and the file size can be a maximum of 5 MB. The SQL and Markdown cells are stored in the file. A cluster or workgroup and database is not stored in the exported notebook. When you open an imported notebook you choose the cluster or workgroup and the database where to run it. After running SQL cells, you can then choose in the results tab whether to display the current page of results as a chart. The result set of a query is not stored in the notebook. # Creating a notebook You can create a notebook to organize, annotate, and share multiple SQL queries in a single document. **To create a notebook** 1. From the navigator menu, choose the Editor icon (![\[Horizontal lines representing redacted or censored text.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-align-left.png)). 1. Choose the plus icon (![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png)), and then choose **Notebook**. By default, a SQL query cell appears in the notebook. 1. In the SQL query cell, do one of the following: + Enter a query. + Paste a query that you copied. 1. (Optionally) Choose the plus icon (![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png)), then choose **Markdown** to add a Markdown cell where you can provide descriptive or explanatory text using standard Markdown syntax. 1. (Optionally) Choose the plus icon (![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png)), then choose **SQL** to insert a SQL cell. You can rename notebooks using the pencil icon (![\[Pencil icon representing an editing or writing function.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-edit.png)). From the menu icon (![\[Three dots arranged horizontally, representing an ellipsis or "more" menu icon.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-more.png)), you can also perform the following operations on a notebook: + ![\[Share\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-share.png) **Share with my team** – To share the notebook with your team as defined by tags. To share a notebook with your team, make sure that you have the principal tag `sqlworkbench-team` set to the same value as the rest of your team members in your account. For example, an administrator might set the value to `accounting-team` for everyone in the accounting department. For an example, see [Permissions required to use the query editor v2](redshift-iam-access-control-identity-based.md#redshift-policy-resources.required-permissions.query-editor-v2). + ![\[Export\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-export.png) **Export** – To export the notebook to a local file with the `.ipynb` extension. + ![\[Import query\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-import.png) **Import query** – To import a query from a local file into a cell in the notebook. You can import files with `.sql` and `.txt` extensions. + ![\[Save\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-floppy-disk.png) **Save version** – To create a version of the notebook. To see versions of a notebook, navigate to your saved notebooks and open **Version history**. + ![\[Duplicate\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-duplicate.png) **Duplicate** – To create a copy of the notebook and open it in a new notebook tab. + ![\[Shortcuts\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-key-command.png) **Shortcuts** – To display the shortcuts available when authoring a notebook. # Importing into notebooks You can import an entire notebook or individual SQL cells into a query editor v2 notebook. To import an entire notebook from a local file to **My notebooks**, choose ![\[Import\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-import.png) **Import**, then choose **Import notebook**. Navigate to the `.ipynb` file that contains your notebook. The notebook is imported into the currently open notebook folder. You can then open the notebook in the notebook editor. To import a query from a local file into a SQL cell in a notebook, choose ![\[Import\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-import.png) **Import**, then choose **Import query**. On the **Import query** window, follow the directions on the screen to choose file and folders that can be imported as a query into a new notebook or an existing notebook. The files must have an extension of `.sql` or `.txt`. Each query can be up to 10,000 characters. When you add to an existing notebook, you choose which notebook from all notebooks in your **Saved notebooks** list. The imported queries are added as SQL cells at the end of the notebook. When you choose a new notebook, you choose the name of the notebook and it is created in the currently open saved notebooks folder. **Note** When creating `.sql` files on macOS using the TextEdit application, you might encounter an issue where an additional hidden extension is added to the file. For instance, a file named `Test.sql` created in TextEdit might end up being saved as `Test.sql.rtf`. The query editor v2 does not support files with the `.rtf` extension. However, if you create a `.sql` file using TextEdit, and save it as a plain text file, the file has an additional hidden `.txt` extension. For example, a file named `Text.sql` might be saved as `Text.sql.txt`. Unlike the `.rtf` extension, query editor v2 does support files with the `.txt` extension, so `Text.sql.txt` is supported when importing queries to notebooks. For a demo of notebooks, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/GNahyu7j98M/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/GNahyu7j98M) # Querying the AWS Glue Data Catalog You can use query editor v2 to query data cataloged in your AWS Glue Data Catalog by using specific SQL commands and granting the permissions outlined in this section. By default, the AWS Glue Data Catalog is listed as a query editor v2 database named `awsdatacatalog`. Querying the AWS Glue Data Catalog is not available in all Amazon Redshift AWS Regions. Use the SHOW command to determine if this capability is available. For more information about AWS Glue, see [What is AWS Glue?](https://docs.aws.amazon.com/glue/latest/dg/what-is-glue.html) in the *AWS Glue Developer Guide*. **Note** Querying the AWS Glue Data Catalog is only supported in Amazon Redshift RA3 node type clusters and Amazon Redshift Serverless. You can configure your data warehouse and view the AWS Glue database objects cataloged using the following SQL commands: + SHOW – to display whether `awsdatacatalog` is mounted for the currently connected data warehouse. For example, to show the `data_catalog_auto_mount` parameter value, run: ``` SHOW data_catalog_auto_mount; ``` For more information, see [SHOW](https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW.html) in the *Amazon Redshift Database Developer Guide*. + ALTER SYSTEM – to change the system-level configuration of `data_catalog_auto_mount`. For example, to change the `data_catalog_auto_mount` parameter value to `on`, run: ``` ALTER SYSTEM SET data_catalog_auto_mount = on; ``` The change takes effect when a provisioned cluster is rebooted or a serverless workgroup is automatically paused and resumed. For more information, see [ALTER SYSTEM](https://docs.aws.amazon.com/redshift/latest/dg/r_ALTER_SYSTEM.html) in the *Amazon Redshift Database Developer Guide*. + SHOW SCHEMAS – shows a list of schemas. The schemas in the database named `awsdatacatalog` represent the AWS Glue databases cataloged in the AWS Glue Data Catalog. For example, to show these schemas, run: ``` SHOW SCHEMAS FROM DATABASE awsdatacatalog; ``` For more information, see [SHOW SCHEMAS](https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_SCHEMAS.html) in the *Amazon Redshift Database Developer Guide*. + SHOW TABLES – shows a list of tables in a schema. For example, to show the tables in the AWS Glue Data Catalog database named `awsdatacatalog` that are in schema `myglue` run: ``` SHOW TABLES FROM SCHEMA awsdatacatalog.myschema; ``` For more information, see [SHOW TABLES](https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_TABLES.html) in the *Amazon Redshift Database Developer Guide*. + SHOW COLUMNS – shows a list of columns in a table. For example, to show the columns in the AWS Glue Data Catalog database named `awsdatacatalog` that are in schema `myglue` and table `mytable` run: ``` SHOW COLUMNS FROM TABLE awsdatacatalog.myglue.mytable; ``` For more information, see [SHOW COLUMNS](https://docs.aws.amazon.com/redshift/latest/dg/r_SHOW_COLUMNS.html) in the *Amazon Redshift Database Developer Guide*. **To grant your IAM user or role permission to query the AWS Glue Data Catalog,** 1. In the tree-view pane, connect to your initial database in your provisioned cluster or serverless workgroup using the **Database user name and password** authentication method. For example, connect to the `dev` database using the admin user and password you used when you created the cluster or workgroup. 1. In an editor tab, run the following SQL statement to grant an IAM user access to the AWS Glue Data Catalog. ``` GRANT USAGE ON DATABASE awsdatacatalog to "IAM:myIAMUser" ``` Where *IAM:myIAMUser* is an IAM user that you want to grant usage privilege to the AWS Glue Data Catalog. Alternatively, you can grant usage privilege to *IAMR:myIAMRole* for an IAM role. 1. In the tree-view pane, edit or delete the connection to the cluster or workgroup you previously created. Connect to either your cluster or workgroup in one of the following ways: + To access the `awsdatacatalog` database from a cluster, you must use the authentication method **Temporary credentials using your IAM identity**. For more information about this authentication method, see [Connecting to an Amazon Redshift database](query-editor-v2-connecting.md). Your query editor v2 administrator might need to configure the **Account settings** for the account to display this authentication method on the connection window. + To access the `awsdatacatalog` database from a workgroup, you must use the authentication method **Federated user**. For more information about this authentication method, see [Connecting to an Amazon Redshift database](query-editor-v2-connecting.md). 1. With the granted privilege, you can use your IAM identity to run SQL against your AWS Glue Data Catalog. After connecting, you can use query editor v2 to query data cataloged in AWS Glue Data Catalog. On the query editor v2 tree-view pane, choose the cluster or workgroup and `awsdatacatalog` database. In the editor or notebook pane, confirm the correct cluster or workgroup is chosen. The database chosen should be the initial Amazon Redshift database such as `dev`. For information about authoring queries, see [Authoring queries with Amazon Redshift](query-editor-v2-query-run.md) and [Notebooks in Amazon RedshiftNotebooks](query-editor-v2-notebooks.md). The database named `awsdatacatalog` is reserved to reference the external Data Catalog database in your account. Queries against the `awsdatacatalog` database can only be read-only. Use three-part notation to reference the table in your SELECT statement. Where the first part is the database name, the second part is the AWS Glue database name, and the third part is the AWS Glue table name. ``` SELECT * FROM awsdatacatalog..; ``` You can perform various scenarios that read the AWS Glue Data Catalog data and populate Amazon Redshift tables. The following example SQL joins two tables that are defined in AWS Glue. ``` SELECT pn.emp_id, alias, role, project_name FROM "awsdatacatalog"."empl_db"."project_name_table" pn, "awsdatacatalog"."empl_db"."project_alias_table" pa WHERE pn.emp_id = pa.emp_id; ``` The following example SQL creates an Amazon Redshift table and populates it with data from a join of two AWS Glue tables. ``` CREATE TABLE dev.public.glue AS SELECT pn.emp_id, alias, role, project_name FROM "awsdatacatalog"."empl_db"."project_name_table" pn, "awsdatacatalog"."empl_db"."project_alias_table" pa WHERE pn.emp_id = pa.emp_id; ``` ## Querying Amazon S3 tables (preview) You can use query editor v2 to query data held in Amazon S3table catalogs mounted to the AWS Glue Data Catalog. Amazon S3 table catalogs are mounted to the AWS Glue Data Catalog on creation, and automatically appear as external databases on all provisioned clusters and serverless workgroups in the same AWS Region under the same account. For more information on accessing Amazon S3 tables using Amazon Redshift, see [ Accessing Amazon S3 tables with Amazon Redshift ](https://docs.aws.amazon.com/AmazonS3/latest/userguide/s3-tables-integrating-redshift.html) in the Amazon Simple Storage Service User Guide. # Querying a data lake You can query data in an Amazon S3 data lake by following the set of tasks in this tutorial. First, you create an external schema to reference the external database in the [AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/components-overview.html#data-catalog-intro). Then, you can query data in the Amazon S3 data lake. ## Demo: Query a data lake To learn how to query a data lake, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/-pyy0qNmEKo/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/-pyy0qNmEKo) ## Prerequisites Before you work with your data lake in query editor v2, confirm the following was set up in your Amazon Redshift environment: + Crawl your Amazon S3 data using AWS Glue and enable your Data Catalog for AWS Lake Formation. + Create an IAM role for Amazon Redshift using the AWS Glue enabled Data Catalog for AWS Lake Formation. For details on this procedure, see [ To create an IAM role for Amazon Redshift using an AWS Glue Data Catalog enabled for AWS Lake Formation](https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum-create-role.html#spectrum-get-stared-create-role-lake-formation). For more information about using Redshift Spectrum and Lake Formation, see [ Using Redshift Spectrum with AWS Lake Formation](https://docs.aws.amazon.com/redshift/latest/dg/spectrum-lake-formation.html). + Grant SELECT permissions on the table to query in the Lake Formation database. For details on this procedure, see [ To grant SELECT permissions on the table to query in the Lake Formation database](https://docs.aws.amazon.com/redshift/latest/dg/c-getting-started-using-spectrum-create-role.html#spectrum-get-started-grant-lake-formation-table). You can verify in the Lake Formation console (https://console.aws.amazon.com/lakeformation/), **Permissions** section, **Data lake permissions** page, that the IAM role, AWS Glue database, and tables have the proper permissions. + Confirm your connected user has permission to create schemas in the Amazon Redshift database and access data in your data lake. When you connect to a database in query editor v2, you choose an authentication method that includes credentials, which can be a database user or IAM user. The connected user must have the proper permissions and database privileges, such as a `superuser`. The Amazon Redshift `admin` user who created the cluster or workgroup has `superuser` privileges and can create schemas and manage the Redshift database. For more information about connecting to a database with query editor v2, see [Connecting to an Amazon Redshift database](query-editor-v2-connecting.md). ## Creating an external schema To query data in an Amazon S3 data lake, first create an external schema. The external schema references the external database in the [AWS Glue Data Catalog](https://docs.aws.amazon.com/glue/latest/dg/components-overview.html#data-catalog-intro). 1. In the **Editor** view of query editor v2, choose ![\[Create\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Schema**. 1. Enter a **Schema** name. 1. For **Schema type**, choose **External**. 1. Within **Data Catalog** details, the **Region** defaults to the AWS Region where your Redshift database is located. 1. Choose the **AWS Glue database** that the external schema will map to and that contains references to the AWS Glue tables. 1. Choose an **IAM role** for Amazon Redshift that has the required permissions to query data on Amazon S3. 1. Optionally, choose an **IAM role** that has permission to the Data Catalog. 1. Choose **Create schema**. The schema appears under your database in the tree-view panel. When creating the schema, if you receive a permission denied error for your database, check if the connected user has the database privilege to create a schema. ## Querying data in your Amazon S3 data lake You use the schema that you created in the previous procedure. 1. In the tree-view panel, choose the schema. 1. To view a table definition, choose a table. The table columns and data types display. 1. To query a table, choose the table and in the context menu (right-click), choose **Select table** to generate a query. 1. Run the query in the **Editor**. The following example SQL was generated by query editor v2 to query all the rows in AWS Glue table named `flightscsv`. The columns and rows shown in the output are truncated for simplicity. ``` SELECT * FROM "dev"."mydatalake_schema"."flightscsv"; year quarter month dom day_of_week fl_date unique_carrier airline_id carrier tail_num fl_num 2016 4 10 19 3 10/19/16 OO 20304 OO N753SK 3086 2016 4 10 19 3 10/19/16 OO 20304 OO N753SK 3086 2016 4 10 19 3 10/19/16 OO 20304 OO N778SK 3087 2016 4 10 19 3 10/19/16 OO 20304 OO N778SK 3087 ... ``` # Datashares You can create a datashare so that users on another cluster can query the data. The cluster containing the data that you want to share is called the *producer* cluster. You create a datashare on the producer cluster for the database objects that you want to share. You can share schemas, tables, views, and SQL user-defined functions (UDFs). The cluster that you want to share the data to is called the *consumer* cluster. On the consumer cluster, you create a database from the datashare. Then, users on the consumer cluster can query the data. For more information, see [Getting started with data sharing](https://docs.aws.amazon.com/redshift/latest/dg/getting-started-datasharing.html) in the *Amazon Redshift Database Developer Guide*. # Creating datashares You create a datashare on the cluster that you want to use as the producer cluster. To learn more about datashare considerations, see [Data sharing considerations in Amazon Redshift](https://docs.aws.amazon.com/redshift/latest/dg/datashare-considerations.html) in the *Amazon Redshift Database Developer Guide*. 1. Choose the database on the producer cluster that you want to use. 1. Create the datashare. For example: ``` create datashare mysource; ``` 1. Set permissions on the datashare. For example: ``` grant alter, share on datashare mysource to admin; ``` 1. Set permissions on the database objects that you want to share. For example: ``` alter datashare mysource add schema public; ``` ``` alter datashare mysource add table public.event; ``` 1. Set permissions on the consumer cluster namespace to access the datashare. For example: ``` grant usage on datashare mysource to namespace '2b12345-1234-5678-9012-bb1234567890'; ``` # Showing datashares You can show the datashares that you've created on the producer cluster. 1. Choose the producer cluster. 1. Show the datashares. For example: ``` show datashares; ``` ``` share_name share_owner source_database consumer_database share_type createdate is_publicaccessible share_acl producer_account producer_namespace test_datashare 100 db_producer NULL OUTBOUND 2/15/2022 FALSE admin 123456789012 p1234567-8765-4321-p10987654321 ``` # Creating the consumer database On the consumer cluster, you create a database from the datashare. These steps describe how to share data between two clusters in the same account. For information on sharing data across AWS accounts, see [Sharing data across AWS accounts](https://docs.aws.amazon.com/redshift/latest/dg/across-account.html) in the *Amazon Redshift Database Developer Guide*. You can use SQL commands or the query editor v2 tree-view panel to create the database. **To use SQL** 1. Create a database from the datashare for your account and the namespace of the producer cluster. For example: ``` create database share_db from datashare mysource of account '123456789012' namespace 'p1234567-8765-4321-p10987654321'; ``` 1. Set permissions so that users can access the database and the schema. For example: ``` grant usage on database share_db to usernames; ``` ``` grant usage on schema public to usernames; ``` **To use the query editor v2 tree-view panel** 1. Choose ![\[Plus sign icon inside a circle, indicating an add or create action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-add.png)**Create**, and then choose **Database**. 1. Enter a **Database name**. 1. (Optional) Select **Users and groups**, and choose a **Database user**. 1. Choose **Create using a datashare**. 1. Choose the datashare. 1. Choose **Create database**. The new ![\[datashare\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-datashare.png)**datashare** database displays in the query editor v2 tree-view panel. 1. Set permissions so that users can access the database and the schema. For example: ``` grant usage on database share_db to usernames; ``` ``` grant usage on schema public to usernames; ``` # Querying datashare objects On the consumer cluster, you can query datashare objects using fully qualified object names expressed with the three-part notation: database, schema, and name of the object. 1. In the query editor v2 tree-view panel, choose the schema. 1. To view a table definition, choose a table. The table columns and data types display. 1. To query a table, choose the table and use the context menu (right-click) to choose **Select table**. 1. Query tables using SELECT commands. For example: ``` select top 10 * from test_db.public.event; ``` # Scheduled queries with query editor v2 With Amazon Redshift query editor v2, you can automate SQL queries to run on a schedule. Scheduled queries are SQL statements that run automatically at specified times or intervals, letting you efficiently manage recurring data operations and analytics tasks. You might want to schedule queries if you're seeking to streamline batch processing, generate regular reports, or maintain data pipelines within their Amazon Redshift environment. Scheduled queries facilitate automating extract, transform, and load (ETL) workflows, refreshing dashboards with up-to-date insights, and operationalizing various data management routines. The following pages detail the process of creating, configuring, and managing scheduled queries to optimize your Amazon Redshift workloads. # Creating a query schedule with query editor v2 You can create a schedule to run a SQL statement with Amazon Redshift query editor v2. You create a schedule to run your SQL statement at the time intervals that match your business needs. When it's time for the scheduled query to run, the query is started by Amazon EventBridge and uses the Amazon Redshift Data API. **To create a schedule to run a SQL statement** 1. On the **Editor** ![\[Editor\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-align-left.png) view, choose ![\[Schedule\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-calendar.png) **Schedule** to create a schedule to run a SQL statement. 1. When you define the schedule, you provide the following information. + The IAM role that assumes the required permissions to run the query. This IAM role is also attached to your cluster or workgroup. + The authentication values for either AWS Secrets Manager or temporary credentials to authorize access your cluster or workgroup. These authentication methods are supported by the Data API. For more information, see [Authenticating a scheduled query](query-editor-v2-schedule-query-authentication.md). + The cluster or workgroup where your database resides. + The name of the database that contains the data to be queried. + The name of the scheduled query and its description. The query editor v2 prefixes the scheduled query name you provide with "QS2-". The query editor v1 prefixes its scheduled query names with "QS-". + The SQL statement to be run on the schedule. + The schedule frequency and repeat options or a cron formatted value that defines the schedule. For more information, see [Cron Expressions](https://docs.aws.amazon.com/AmazonCloudWatch/latest/events/ScheduledEvents.html#CronExpressions) in the *Amazon CloudWatch Events User Guide*. + Optionally, you can enable standard Amazon SNS notifications to monitor the scheduled query. You might need to confirm the email address you provide to the Amazon SNS notification. Check your email for a link to confirm the email address for the Amazon SNS notification. For more information, see [Email notifications](https://docs.aws.amazon.com/sns/latest/dg/sns-email-notifications.html) in the *Amazon Simple Notification Service Developer Guide*. If your query is being run but you don't see messages published in your SNS topic, see [ My rule runs, but I don't see any messages published into my Amazon SNS topic](https://docs.aws.amazon.com/eventbridge/latest/userguide/eventbridge-troubleshooting.html#eb-no-messages-published-sns) in the *Amazon EventBridge User Guide*. 1. Choose **Schedule query** to save and activate the schedule and add the schedule to the list of queries in the **Scheduled queries** view. The **Scheduled queries** ![\[Scheduled queries\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/qev2-calendar.png) view lists all the scheduled queries for your clusters and workgroups. With this view, you can display schedule query details, activate or deactivate the schedule, edit the schedule, and delete the scheduled query. When you view query details, you can also view the history of running the query with the schedule. **Note** A schedule query run is only available in the **Schedule history** list for 24 hours. Queries that run on a schedule don't appear in the **Query history** view of query editor v2. ## Demo of scheduling a query For a demo of scheduling a query, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/gTw0XUpO8sw/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/gTw0XUpO8sw) # Setting up permissions to schedule a query To schedule queries, the AWS Identity and Access Management (IAM) user defining the schedule and the IAM role associated with the schedule must be configured with the IAM permissions to use Amazon EventBridge and Amazon Redshift Data API. To receive emails from scheduled queries, the Amazon SNS notification you optionally specify must be configured also. The following describes the tasks to use AWS managed policies to provide permission, but depending on your environment, you might want to scope down the permissions allowed. For the IAM user logged into query editor v2, edit the IAM user using the IAM console ([https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/)). + In addition to permissions to run Amazon Redshift and query editor v2 operations, attach the `AmazonEventBridgeFullAccess` and `AmazonRedshiftDataFullAccess` AWS managed policies to an IAM user. + Alternatively, assign the permissions to a role and assign the role to the user. Attach a policy that allows the `sts:AssumeRole` permission to the resource ARN of the IAM role you specify when you define the scheduled query. For more information about assuming roles, see [Granting a user permissions to switch roles](https://docs.aws.amazon.com/IAM/latest/UserGuide/id_roles_use_permissions-to-switch.html) in the *IAM User Guide*. The following example shows a permission policy that assumes the IAM role `myRedshiftRole` in account `123456789012`. The IAM role `myRedshiftRole`is also the IAM role that is attached to the cluster or workgroup where the scheduled query runs. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "AssumeIAMRole", "Effect": "Allow", "Action": "sts:AssumeRole", "Resource": [ "arn:aws:iam::123456789012:role/myRedshiftRole" ] } ] } ``` ------ Update the trust policy of the IAM role used to schedule the query to allow the IAM user to assume it. ``` { "Sid": "AssumeRole", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789012:user/myIAMusername" }, "Action": "sts:AssumeRole" } ] } ``` For the IAM role that you specify to allow the scheduled query to run, edit the IAM role using the IAM console ([https://console.aws.amazon.com/iam/](https://console.aws.amazon.com/iam/)). + Attach the `AmazonRedshiftDataFullAccess` and `AmazonEventBridgeFullAccess` AWS managed policies to the IAM role. The `AmazonRedshiftDataFullAccess` managed policy only allows `redshift-serverless:GetCredentials` permission for Redshift Serverless workgroups that are tagged with the key `RedshiftDataFullAccess`. # Authenticating a scheduled query When you schedule a query, you use one of the following authentication methods when the SQL runs. Each method requires a different combination of input on the query editor v2. These authentication methods are supported by the Data API which is used to run your SQL statements. The database user or role that is used to run the query must have the necessary database privileges. For example, to grant `IAMR:MyRedshiftQEv2Scheduler` privileges to table `mytable`, run the following SQL command. ``` GRANT all ON TABLE mytable TO "IAMR:MyRedshiftQEv2Scheduler"; ``` To view the list of database users in your cluster or workgroup, query the system view `PG_USER_INFO`. **Note** Any Redshift Serverless workgroup for which you schedule queries much be tagged with the key `RedshiftDataFullAccess`. For more information, see [Authorizing access to the Amazon Redshift Data API](data-api-access.md). As an alternative to tagging the workgroup, you can add an inline policy to the IAM role (that is specified with the schedule) that allows `redshift-serverless:GetCredentials`. For example: **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "UseTemporaryCredentialsForAllServerlessWorkgroups", "Effect": "Allow", "Action": "redshift-serverless:GetCredentials", "Resource": [ "arn:aws:redshift-serverless:*:*:workgroup/*" ] } ] } ``` **AWS Secrets Manager** With this method, provide a secret value for **secret-arn** that is stored in AWS Secrets Manager. This secret contains credentials to connect to your database. You might have created a secret with the proper credentials when you created your cluster or workgroup. The secret must be tagged with the key `RedshiftDataFullAccess`. If the tag key is not already present, use the AWS Secrets Manager console to add it. For information about creating a secret, see [Creating a secret for database connection credentials](redshift-secrets-manager-integration-create.md). For more information about the minimum permissions, see [Creating and Managing Secrets with AWS Secrets Manager](https://docs.aws.amazon.com/secretsmanager/latest/userguide/managing-secrets.html) in the *AWS Secrets Manager User Guide*. **Temporary credentials** With this method, provide your **Database name** and **Database user** values when connecting to a database in a cluster. You only need to provide your **Database name** when connecting to a database in a workgroup. When connecting to a cluster, the `AmazonRedshiftDataFullAccess` policy allows the database user named `redshift_data_api_user` permission for `redshift:GetClusterCredentials`. If you want to use a different database user to run the SQL statement, then add a policy to the IAM role attached to your cluster to allow `redshift:GetClusterCredentials`. The following example policy allows database users `awsuser` and `myuser`. **** ``` { "Version":"2012-10-17", "Statement": [ { "Sid": "UseTemporaryCredentialsForAllDbUsers", "Effect": "Allow", "Action": "redshift:GetClusterCredentials", "Resource": [ "arn:aws:redshift:*:*:dbuser:*/awsuser", "arn:aws:redshift:*:*:dbuser:*/myuser" ] } ] } ``` # Setting up permissions to view schedule query history To allow users to view schedule query history, edit the IAM role (that is specified with the schedule) **Trust relationships** to add permissions. The following is an example of a trust policy in an IAM role that allows the IAM user *myIAMusername* to view schedule query history. Instead of allowing an IAM user `sts:AssumeRole` permission you can choose to allow an IAM role this permission. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Statement": [ { "Effect": "Allow", "Principal": { "Service": [ "redshift.amazonaws.com", "redshift-serverless.amazonaws.com" ] }, "Action": "sts:AssumeRole" }, { "Effect": "Allow", "Principal": { "Service": "events.amazonaws.com" }, "Action": "sts:AssumeRole" }, { "Sid": "AssumeRole", "Effect": "Allow", "Principal": { "AWS": "arn:aws:iam::123456789012:user/myIAMusername" }, "Action": "sts:AssumeRole" } ] } ``` ------ # Monitoring the scheduled query For the Amazon SNS topic that you specify to send email notifications, create the Amazon SNS topic using the query editor v2 by navigating to the **SNS notifications** section, **Turn on** monitoring, and create the topic with **Create SNS topic**. The query editor v2 creates the Amazon SNS topic and adds a service principal to the access policy for Amazon EventBridge. The following is an example **Access policy** that is created in the Amazon SNS topic. In the example, the AWS Region *us-west-2*, AWS account *123456789012*, and Amazon SNS topic *select-version-pdx-testunload* are used. ------ #### [ JSON ] **** ``` { "Version":"2012-10-17", "Id": "__default_policy_ID", "Statement": [ { "Sid": "Allow_Publish_Events", "Effect": "Allow", "Principal": { "Service": "events.amazonaws.com" }, "Action": "sns:Publish", "Resource": "arn:aws:sns:us-west-2:123456789012:select-version-pdx-testunload" } ] } ``` ------ When the scheduled query runs, Amazon SNS sends AWS notification emails. The following example shows an email sent to *myemail@example.com* for scheduled query *QS2-may25a* that ran on AWS Region *eu-north-1* in AWS account *123456789012* using Amazon SNS notification topic *may25a-SNS*. ``` {"version":"0","id":"8e4323ec-5258-7138-181b-91290e30ff9b","detail-type":"Scheduled Event","source":"aws.events","account":"123456789012","time":"2023-05-25T15:22:00Z", "region":"eu-north-1","resources":["arn:aws:events:eu-north-1:123456789012:rule/QS2-may25a"],"detail":{}} -- If you wish to stop receiving notifications from this topic, please click or visit the link below to unsubscribe: https://sns.eu-north-1.amazonaws.com/unsubscribe.html?SubscriptionArn=arn:aws:sns:eu-north-1:123456789012:may25a-SNS:0c1a3d05-39c2-4507-bc3d-47250513d7b0&Endpoint=myemail@example.com Please do not reply directly to this email. If you have any questions or comments regarding this email, please contact us at https://aws.amazon.com/support ``` # Troubleshooting set up of scheduling a query Consider the following if you have issues scheduling a query. **Queries not running** Check if the IAM role used in the schedule has permission to get the temporary cluster credentials. The permission for provisioned clusters is `redshift:GetClusterCredentialsWithIAM`. The permission for Redshift Serverless workgroups is `redshift-serverless:GetCredentials`. **Scheduled history not displaying** The IAM user or IAM role used to log in to the AWS console was not added into the trust policy of the IAM role used to schedule the query. When using AWS Secrets Manager for the scheduled query to connect, confirm the secret is tagged with the key `RedshiftDataFullAccess`. If the scheduled query is using an AWS Secrets Manager connection, the IAM role used to schedule the query must have the equivalent of managed policy `SecretsManagerReadWrite` attached to the role. **Query history status is `Failed`** View the SYS\$1QUERY\$1HISTORY system view for details about why the query failed. A common issue is that the database user or role that was used to run the query might not have the required privilege to run the SQL. For more information, see [Authenticating a scheduled query](query-editor-v2-schedule-query-authentication.md). The following SQL queries the SYS\$1QUERY\$1HISTORY view to return failed queries. ``` SELECT user_id, query_id, transaction_id, session_id, database_name, query_type, status, error_message, query_text FROM sys_query_history WHERE status = 'failed'; ``` To find out details for a specific failing scheduled query, see [Viewing the results of a scheduled query with AWS CloudShell](query-editor-v2-schedule-query-troubleshooting-cloudshell.md). # Viewing the results of a scheduled query with AWS CloudShell You can use AWS CloudShell to find out details about a schedule query. You must have the proper permissions to run the AWS CLI commands shown in the following procedure. **To view the results of a scheduled query** 1. On the AWS console, open the AWS CloudShell command prompt. For more information about AWS CloudShell, see [What is AWS CloudShell](https://docs.aws.amazon.com/cloudshell/latest/userguide/welcome.html) in the *AWS CloudShell User Guide*. 1. Assume the IAM role of the scheduled query. To assume the role, find the IAM role associated with the scheduled query in query editor v2 and use it in the AWS CLI command in AWS CloudShell. For example, for the role `scheduler` enter an AWS STS command to assume the role used by the scheduled query. ``` aws sts assume-role --role-arn "arn:aws:iam::123456789012:role/scheduler" --role-session-name "scheduler-test" ``` The credentials returned are similar to the following. ``` "Credentials": { "AccessKeyId": "AKIAIOSFODNN7EXAMPLE", "SecretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY", "SessionToken": "je7MtGbClwBF/2Zp9Utk/h3yCo8nvbEXAMPLEKEY...", "Expiration": "2023-08-18T18:19:44+00:00" }, "AssumedRoleUser": { "AssumedRoleId": "AROA35B2NH6WBTP7ONL4E:scheduler-test", "Arn": "arn:aws:sts::123456789012:assumed-role/scheduler/scheduler-test" } } ``` 1. Create environmental variables in the AWS CLI using the credentials displayed from assuming the IAM role. You must use these tokens before their expiration time. For example, you enter the following in AWS CloudShell. ``` export AWS_ACCESS_KEY_ID=AKIAIOSFODNN7EXAMPLE export AWS_SECRET_ACCESS_KEY=wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY export AWS_SESSION_TOKEN=je7MtGbClwBF/2Zp9Utk/h3yCo8nvbEXAMPLEKEY... ``` 1. To view the error of a failed query, run the AWS CLI command to describe a statement. The id of the SQL statement is from the **ID** shown in the **Schedule history** section of a scheduled query in the query editor v2. ``` aws redshift-data describe-statement --id 130d2620-05d2-439c-b7cf-815d9767f513 ``` In this example, the scheduled SQL `select * from users limit 100` results in a SQL error that the `users` table does not exist. ``` { "CreatedAt": "2023-08-18T17:39:15.563000+00:00", "Duration": -1, "Error": "ERROR: relation \"users\" does not exist", "HasResultSet": false, "Id": "a1b2c3d4-5678-90ab-cdef-EXAMPLE11111", "QueryString": "select * from users limit 100\n—RequestID=a1b2c3d4-5678-90ab-cdef-EXAMPLE22222; TraceID=1-633c5642-4039308d03f3a0ba53dbdf6f", "RedshiftPid": 1073766651, "RedshiftQueryId": 0, "ResultRows": -1, "ResultSize": -1, "Status": "FAILED", "UpdatedAt": "2023-08-18T17:39:16.116000+00:00", "WorkgroupName": "default" } ``` # Visualizing query results After you run a query and the results display, you can turn on **Chart** to display a graphic visualization of the current page of results. You can use the following controls to define the content, structure, and appearance of your chart: ![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) Trace Represents a set of related graphical marks in a chart. You can define multiple traces in a chart. Type You can define the trace type to represent data as one of the following: + Scatter chart for a scatter plot or bubble chart. + Bar chart to represent categories of data with vertical or horizontal bars. + Area chart to define filled areas. + Histogram that uses bars to represent frequency distribution. + Pie chart for a circular representation of data where each slice represents a percentage of the whole. + Funnel or Funnel Area chart to represent data through various stages of a process. + OHLC (open-high-low-close) chart often used for financial data to represent open, high, low, and close values along the x-axis, which usually represents intervals of time. + Candlestick chart to represent a range of values for a category over a timeline. + Waterfall chart to represent how an initial value increases or decreases through a series of intermediate values. Values can represent time intervals or categories. + Line chart to represent changes in value over time. X axis You specify a table column that contains values to plot along the X axis. Columns that contain descriptive values usually represent dimensional data. Columns that contain quantitative values usually represent factual data. Y axis You specify a table column that contains values to plot along the Y axis. Columns that contain descriptive values usually represent dimensional data. Columns that contain quantitative values usually represent factual data. Subplots You can define additional presentations of chart data. Transforms You can define transforms to filter trace data. You use a split transform to display multiple traces from a single source trace. You use an aggregate transform to present a trace as an average or minimum. You use a sort transform to sort a trace. General appearance You can set defaults for background color, margin color, color scales to design palettes, text style and sizes, title style and size, and mode bar. You can define interactions for drag, click, and hover. You can define meta text. You can define default appearances for traces, axes, legends, and annotations. **To create a chart** 1. Run a query and get results. 1. Turn on **Charts**. 1. Choose **Trace** and start to visualize your data. 1. Choose a chart style from one of the following: + Scatter + Bar + Area + Histogram + Pie + Funnel + Funnel Area + OHLC (open-high-low-close) + Candlestick + Waterfall + Line 1. Choose **Style** to customize the appearance, including colors, axes, legend, and annotations. You can add text, shapes, and images. 1. Choose **Annotations** to add text, shapes, and images. 1. Choose **Refresh** to update the chart display. Choose **Full screen** to expand the chart display. ## Example: Create a pie chart to visualize query results The following example uses the *Sales* table of the sample database. For more information, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) in the *Amazon Redshift Database Developer Guide*. Following is the query that you run to provide the data for the pie chart. ``` select top 5 eventname, count(salesid) totalorders, sum(pricepaid) totalsales from sales, event where sales.eventid=event.eventid group by eventname order by 3; ``` **To create a pie chart for the top event by total sales** 1. Run the query. 1. In the query results area, turn on **Chart**. 1. Choose **Trace**. 1. For **Type**, choose **Pie**. 1. For **Values**, choose *totalsales*. 1. For **Labels**, choose *eventname*. 1. Choose **Style** and then **General**. 1. Under **Colorscales**, choose **Categorical** and then **Pastel2**. ![\[Pie chart\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/pie-chart.png) ## Example: Create a combination chart for comparing revenue and sales Perform the steps in this example to create a chart that combines a bar chart for revenue data and a line graph for sales data. The following example uses the *Sales* table of the tickit sample database. For more information, see [Sample database](https://docs.aws.amazon.com/redshift/latest/dg/c_sampledb.html) in the *Amazon Redshift Database Developer Guide*. Following is the query that you run to provide the data for the chart. ``` select eventname, total_price, total_qty_sold from (select eventid, total_price, total_qty_sold, ntile(1000) over(order by total_price desc) as percentile from (select eventid, sum(pricepaid) total_price, sum(qtysold) total_qty_sold from tickit.sales group by eventid)) Q, tickit.event E where Q.eventid = E.eventid and percentile = 1 order by total_price desc; ``` **To create a combination chart for comparing revenue and sales** 1. Run the query. 1. In the query results area, turn on **Chart**. 1. Under *trace o*, for **Type**, choose **Bar**. 1. For **X**, choose *eventname*. 1. For **Y**, choose *total\$1price*. The bar chart displays with event names along the X axis. 1. Under **Style**, choose **Traces**. 1. For **Name**, enter *Revenue*. 1. Under **Style**, choose **Axes**. 1. For **Titles**, choose **Y** and enter *Revenue*. The label *Revenue* displays on the left Y axis. 1. Under **Structure**, choose **Traces**. 1. Choose ![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png) **Trace**. The trace 1 options display. 1. For **Type**, choose **Line**. 1. For **X**, choose *eventname*. 1. For **Y**, choose *total\$1qty\$1sold*. 1. Under **Axes To Use**, for **Y Axis** choose ![\[Plus sign icon representing an addition or new item action.\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/add-plus.png). The **Y Axis** displays *Y2*. 1. Under **Style**, choose **Axes**. 1. Under **Titles**, choose **Y2**. 1. For **Name**, enter *Sales*. 1. Under **Lines**, choose *Y:Sales*. 1. Under **Axis Line**, choose **Show** and for **Position**, choose **Right**. ![\[Revenue and sales chart\]](http://docs.aws.amazon.com/redshift/latest/mgmt/images/chart-revenue-sales.png) ## Demo: Build visualizations using Amazon Redshift query editor v2 For a demo of how to build visualizations, watch the following video. [![AWS Videos](http://img.youtube.com/vi/https://www.youtube.com/embed/-FYqTIER-6U/0.jpg)](http://www.youtube.com/watch?v=https://www.youtube.com/embed/-FYqTIER-6U) # Collaborating and sharing as a team You can share queries with your team. A team is defined for a set of users who collaborate and share query editor v2 resources. An administrator can create a team by adding a tag to an IAM role. For more information, see [Permissions required to use the query editor v2](redshift-iam-access-control-identity-based.md#redshift-policy-resources.required-permissions.query-editor-v2). # Saving and browsing for queries Before you can share your query with your team, save your query. You can view and delete saved queries. **To save a query** 1. Prepare your query and choose **Save**. 1. Enter a title for your query. 1. Choose **Save**. **To browse for saved queries** 1. Choose **Queries** from the navigation pane. 1. You can view queries that are **My queries**, **Shared by me**, or **Shared to my team**. These queries can appear as individual queries or within folders you created. # Sharing a query You can share your queries with your team. You can also view the history of saved queries and manage query versions. To share a query with your team, make sure that you have the principal tag `sqlworkbench-team` set to the same value as the rest of your team members in your account. For example, an administrator might set the value to `accounting-team` for everyone in the accounting department. For an example, see [Permissions required to use the query editor v2](redshift-iam-access-control-identity-based.md#redshift-policy-resources.required-permissions.query-editor-v2). **To share a query with a team** 1. Choose **Queries** from the navigation pane. 1. Open the context (right-click) menu of the query that you want to share and choose **Share with my team**. 1. Choose the team or teams that you want to share the query with and then choose **Save sharing options**. # Managing query versions Every time you save a SQL query, the query editor v2 saves it as a new version. You can browse earlier query versions, save a copy of a query, or restore a query. **To manage query versions** 1. Choose **Queries** from the navigation pane. 1. Open the context (right-click) menu for the query that you want to work with. 1. Choose **Version history** to open a list of versions of the query. 1. On the **Version history** page, you can do the following: + **Revert to selected** – Revert to the selected version and continue your work with this version. + **Save selected as** – Create a new query in the editor.