Exploring Amazon Redshift Query Editor v2
Introduction
The query editor v2 is a separate web-based SQL client application that you can use to author and run queries on the Amazon Redshift data warehouse. You can also visualize your results in charts and collaborate by sharing your queries with others on your team. The query editor v2 is a replacement for the previous query editor.
It can also be leveraged to edit and run queries, visualize results, and share your work with your team. In this 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.
Navigating to the Query Editor v2
- Open the Redshift Console and from the navigator menu, choose Editor, then Query editor V2.
- The query editor v2 opens in a new tab. This screen will look similar to the image attached below
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 can 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.
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 is shown in the image here.
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.
Creating Database Tables
You can create a table based on a CSV file that you can specify or define each column of the table.
- Choose Create and select Table and then choose a schema.
- Enter a table name and choose add field to add a column.
- Use a CSV file as a template for the table definition
- Choose Load from CSV. Browse to the file location.
- If you use a CSV file, be sure that the first row of the file contains the column headings. Choose the file and choose Open. Confirm that the column names and data types are what you intend.
- For each column, choose the column and choose the options that you want:
- Choose a value for Encoding. Choose a Default value. 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.
- Choose Open query in editor to continue specifying options to define the table or choose Create table to create the table.
Working with SQL notebooks (currently in preview as of Jan 2022)
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 an 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. You can visit the AWS documentation for the same over here Accessing the query editor v2
You can create SQL notebooks directly from the console and add the relevant SQL queries in these notebooks. Once the SQL notebooks are created then you can share the notebooks.
References
- Announcing preview of SQL Notebooks support : https://aws.amazon.com/about-aws/whats-new/2021/12/sql-notebooks-amazon-redshift-query-editor-v2/
- Authoring and running queries : https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-query-run.html
- Visualizing results on Query Editor v2 : https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-charts.html
- Collaborating with other Users using Query Editor v2 : https://docs.aws.amazon.com/redshift/latest/mgmt/query-editor-v2-team.html
