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

Redshift Console UI
  1. Open the Redshift Console and from the navigator menu, choose Editor, then Query editor V2.
  2. The query editor v2 opens in a new tab. This screen will look similar to the image attached below
Redshift Query Visual Editor v2

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.

hierarchical tree-view

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.

  1. Choose Create and select Table and then choose a schema.
  2. Enter a table name and choose add field to add a column.
  3. Use a CSV file as a template for the table definition
  4. Choose Load from CSV. Browse to the file location.
  5. 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.
  6. For each column, choose the column and choose the options that you want:
  7. 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.
  8. 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.
  9. 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.

SQL Notebooks UI

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.

By Rajas Walavalkar

--

--

--

Associate Technical Architect at Quantiphi Analytics. AWS & GCP Certified. Worked on ETL, Data Warehouses, Big Data (AWS Glue, Spark), BI & Dashboarding (D&A)

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Mercury Insurance login Auto, Home, Business Insurance.

Mercury Insurance  login Auto, Home, Business Insurance.

Open Source UI libraries from our iOS Mobile team

Apache Flink Fundamentals: Basic Concepts

Tendermint to Delegate 595,000 ATOMs to Persistence’s AUDIT.one

Ansible Tower

bash_profile bootcamp

Distributed production collaboration refers to the integration of the supply side based on…

Database Object Naming Standards

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Rajas Walavalkar

Rajas Walavalkar

Associate Technical Architect at Quantiphi Analytics. AWS & GCP Certified. Worked on ETL, Data Warehouses, Big Data (AWS Glue, Spark), BI & Dashboarding (D&A)

More from Medium

Invoking Step Function From Lambda

Federated Query in Amazon Redshift for Postgres & MySQL databases

Notify Azure Cost to Slack Every Day from AWS Lambda with Python

Sql Server Migration To RDS :Instance Sizing and Migration best practices.