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 clusters, 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 tab.

The query editor page has a navigator menu where you choose Database to work with data in your cluster, Queries to work with saved queries, and Charts to work with saved charts. The navigator menu is similar to the following.


                    Navigator icons

When working in the Database view, you have the following controls:

  • The Cluster field displays the name of the cluster you are currently connected to. The Database field displays the databases within the cluster. 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, 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 
                        Settings preferences icon to edit your preferences.

  • A 
                        Save Save icon to save your query.

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

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

Connecting to an Amazon Redshift database

To connect to a database, choose the cluster name in the tree-view panel. If prompted, enter the connection parameters.

When you connect to a cluster and its databases, you provide a Database name and User name. You also provide parameters required for one of the following authentication methods:

Database user name and password

With this method, also provide a 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, generates a temporary password to connect to the database.

When you select a cluster 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).

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 object browser 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. (Optional) Select Authorize user, and choose a Database user.

  4. Choose Create schema.

    The new schema appears in the object browser 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 select 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 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 cluster 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 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 Amazon Redshift cluster, 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 privileges to load data from 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.