Working with query editor v2 - Amazon Redshift
Opening query editor v2Connecting to an Amazon Redshift databaseBrowsing an Amazon Redshift databaseCreating database objectsViewing query and tab historyConsiderationsChanging account settings
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Working with query editor v2

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.

Opening query editor v2

To open the query editor v2

  1. Sign in to the Amazon Web Services Management Console and open the Amazon Redshift console at https://console.amazonaws.cn/redshiftv2/.

  2. 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 Editor

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 Create Create action to create databases, schemas, tables, and functions.

  • A LoadLoad data action to load data from Amazon S3 or from a local file into your database.

  • A Save Save icon to save your query.

  • A Shortcuts Shortcuts icon to display keyboard shortcuts for the editor.

  • A More 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.

    • 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 Editor 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 Notebook 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 Queries

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 Notebooks

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 Authoring and running notebooks.

Charts Chart

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.

History History

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.

Scheduled queries Scheduled queries

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 Visual mode Visual mode icon to toggle between light mode and dark mode.

  • A Settings Settings icon to show a menu of the different settings screens.

    • An Editor preferences 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 Connections 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 Account settings Account settings icon used by an administrator to change certain settings of all users in the account. For more information, see Changing account settings.

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.

Federated user

With this method, the principal tags of your IAM role or user must provide the connection details. You configure these tags in Amazon 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.

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 Amazon Secrets Manager. This secret contains credentials to connect to your database.

Amazon 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.

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 Refresh icon. Open the context (right-click) menu for an object to see what actions you can perform.

Tree-view icons

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 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 and DROP TABLE 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 in the Amazon Redshift Database Developer Guide.

  1. Choose CreateCreate, and then choose Database.

  2. Enter a Database name.

  3. (Optional) Select Users and groups, and choose a Database user.

  4. (Optional) You can create the database from a datashare or the Amazon Glue Data Catalog. For more information about Amazon Glue, see What is Amazon Glue? in the Amazon 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 Amazon Glue Data Catalog, and choose a Choose an Amazon 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).

  5. 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 Amazon 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 Amazon Glue Data Catalog, the schema you named in Data catalog schema, and the table in the Amazon 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 Amazon Glue database.

    GRANT USAGE ON DATABASE glue-database to "IAM:MyIAMUser"

    The Amazon 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 Amazon Glue Data Catalog, see Working with Lake Formation-managed datashares as a consumer and Working with Lake Formation-managed datashares as a producer 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 in the Amazon Redshift Database Developer Guide.

  2. Use the Amazon Redshift console to accept the datashare datashare_base as a consumer for the table category2 in cluster cluster-view.

  3. 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

  4. Choose CreateCreate, and then choose Database.

  5. Enter see_datashare_base for Database name.

  6. 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

  7. 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 Amazon Glue Data Catalog

The following example describes a specific scenario that was used to create a database from an Amazon Glue Data Catalog using query editor v2. Review this scenario to learn how you can create a database from an Amazon Glue Data Catalog in your environment. This scenario uses one cluster, cluster-view to contain the database you create.

  1. Choose CreateCreate, and then choose Database.

  2. Enter data_catalog_database for Database name.

  3. Select Create using a Amazon Glue Data Catalog, and choose Choose an Amazon 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

  4. 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 Amazon 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 in the Amazon Redshift Database Developer Guide.

  1. Choose CreateCreate, and then choose Schema.

  2. Enter a Schema name.

  3. Choose either Local or External as the Schema type.

    For more information about local schemas, see CREATE SCHEMA in the Amazon Redshift Database Developer Guide. For more information about external schemas, see CREATE EXTERNAL SCHEMA in the Amazon Redshift Database Developer Guide.

  4. 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 Amazon Glue. Besides choosing the Amazon 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 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 in the Amazon Redshift Database Developer Guide.

  5. 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 and CREATE TABLE 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 CreateCreate, and choose Table.

  2. Choose a schema.

  3. Enter a table name.

  4. Choose Create Add field to add a column.

  5. Use a CSV file as a template for the table definition:

    1. Choose Load from CSV.

    2. Browse to the file location.

      If you use a CSV file, be sure that the first row of the file contains the column headings.

    3. Choose the file and choose Open. Confirm that the column names and data types are what you intend.

  6. 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.

  7. (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.

  8. 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 CreateCreate, and choose Function.

  2. For Type, choose SQL or Python.

  3. Choose a value for Schema.

  4. Enter a value for Name for the function.

  5. Enter a value for Volatility for the function.

  6. Choose Parameters by their data types in the order of the input parameters.

  7. For Returns, choose a data type.

  8. Enter the SQL program or Python program code for the function.

  9. Choose Create.

For more information about user-defined functions (UDFs), see Creating user-defined functions 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.

Considerations when working with query editor v2

Consider the following when working with query editor v2.

  • The maximum query result size is the smaller of 5 MB or 100,000 rows.

  • 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 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 in the IAM User Guide.

Changing account settings

A user with the right IAM permissions can view and change Account settings for other users in the same Amazon Web Services 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.