Working with query editor v2 - Amazon Redshift

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 AWS Management Console and open the Amazon Redshift console at https://console.aws.amazon.com/redshift/.

  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:

Database 
                            Database

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 Database 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 
                                    Load Load data action to load data from Amazon S3 into your databases.

  • A 
                                    Save Save icon to save your query.

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

  • 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. You can share a saved query 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. You can share a notebook with your team. For more information, see Working with SQL notebooks (preview).

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. You can share a chart with your team. For more information, see Visualizing query results.

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.

    • 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 provide a Database name. You also provide parameters required for one of the following authentication methods:

Federated user

With this method, the principal tags of your IAM role or IAM 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 to query editor v2 as a federated user.

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.

Temporary credentials

With this method, query editor v2, provide a User name for the database. query editor v2 generates a temporary password to connect to the database.

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.

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.

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.

The hierarchical tree-view panel is similar to the following. Open the context (right-click) menu for an icon to see what actions you can perform.


                    Tree-view icons

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.

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. Choose Create database.

    The new database displays in the tree-view panel.

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

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.

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

Loading sample data

The query editor v2 comes with sample data and queries available to be loaded into a sample database and corresponding schema.

To load sample data, choose the 
                External 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 queries in your Queries folder.

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

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.

Loading data from Amazon S3

You can load data into an existing table from Amazon S3.

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

  1. Confirm that the table is already created in the database where you want to load data. The query editor v2 can only load data into an existing table.

  2. Choose 
                        LoadLoad data.

  3. In S3 URIs, choose Browse S3 to look for the Amazon S3 bucket that contains the data to load.

  4. If the specified Amazon S3 bucket isn't in the same AWS Region with the target table, then choose the S3 file location for the AWS Region where the data is located.

  5. Choose This file is a manifest file if the Amazon S3 file is actually a manifest containing multiple Amazon S3 bucket URIs.

  6. Choose an IAM role that has the required permissions to load data from Amazon S3.

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

    For PARQUET and ORC, there isn't any file option to configure.

  8. Choose a compression method to compress your file. The default is no compression.

  9. (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 and Data load operations in the Amazon Redshift Database Developer Guide.

  10. Confirm or choose the location of the Target table including database, schema, and table name where the data is loaded.

  11. 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_LOAD_ERRORS to get more details. For information about COPY command errors, see STL_LOAD_ERRORS in the Amazon Redshift Database Developer Guide.

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

  • View the KMS key ARN used to encrypt query editor v2 resources.

Working with SQL notebooks (preview)

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

You can use SQL notebooks to organize, annotate, and share multiple SQL queries in a single document. You can add multiple SQL query and Markdown cells to a SQL notebook. SQL 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 SQL notebooks with team members.

To use the SQL notebook feature, you must add a policy for the SQL notebook (preview) feature to a principal (an IAM user or IAM role) that already has one of the query editor v2 managed policies. For more information, see Accessing the query editor v2.

For a demo of notebooks, watch the following video.

To create a SQL notebook

  1. Choose and then choose Notebook.

    By default, a SQL query cell appears in the new SQL notebook.

  2. (Optionally) Choose Rename and enter a name for the SQL notebook.

  3. In the SQL query cell, do one of the following:

    • Enter a query.

    • Paste a query that you copied.

  4. (Optionally) Choose Add markdown to add a Markdown cell where you can provide descriptive or explanatory text using standard Markdown syntax.

  5. (Optionally) Choose Add SQL and Add markdown to insert additional SQL and Markdown text cells.

To open a notebook

  1. From the navigator menu, choose Notebooks.

  2. Choose the SQL notebook that you want to open and double-click it.

To share a SQL notebook with your team

  • Choose Share to team-name.