Connect Amazon Redshift Database using PostgreSQL Connector on Knime Workflow

Using Knime data analysts can use PostgreSQL Connector to create connection to Amazon Redshift database and execute SQL queries to read Redshift database table data for self-service data analysis. In this tutorial, I will show how to connect Amazon Redshift data warehouse using PostgreSQL Connector. It is also possible to install the Amazon Redshift JDBC driver on Knime. I will demonstrate this option in an other Knime tutorial.

Launch Knime
Create a new Knime workflow

On an empty workflow, Knime developers can drag and drop following nodes or components from the Node Repository windows on to the workflow surface:

Credentials Configuration (optional),
PostgreSQL Connector,
DB Table Selector and DB Reader, or
DB SQL Executor and DB Query Reader

The connections and workflow among Knime nodes will be as shown below

connect Amazon Redshift on Knime workflow using PostgreSQL Connector

In this example for Knime developers, there are two options to access Redshift cloud data warehouse and query database tables using SQL. One of them is using DB Table Selector and DB Reader as seen on the top branch. The other option is using the DB SQL Executor and DB Query Reader as displayed on the bottom branch of the workflow.

Using the Credentials Configuration is optional but best practise for security reasons.
Knime developers can store the user name and password combination using Credentials Configuration

Knime Credentials Configuration for username and password security

Then using the PostgreSQL Connector with database connection parameters like the hostname (a resolvable DNS name for the Amazon Redshift cluster or the IP address of the leader node of the cluster), port number which is 5439 by default and database name.
For a successfull database connection a valid user credential should be provided. Using "Credential" option and selecting the previously configured Credentials Configuration name from the dropdown list is one way. The second way of providing the credentials is clicking on the Username & password option box and entering the username and password manually here. In that case using the Credentials Configuration box is no more required.

PostgreSQL Connector configuration for Amazon Redshift connection

On the DB Table Selector, Knime users can directly provide the schema name and table name which they want to read from the target Amazon Redshift database. It is also possible to provide a custom SQL query by marking the Custom Query checkbox and entering the SQL query code which will be executed on Redshift database to read the required data.

Knime DB Table Selector and custome SQL query execution

Knime DB Reader node can be used to display the output data which is read by the Amazon Redshift connection and SQL query execution. After you execute the workflow right click on the DB Reader and from the context menu choose option "Knime data table" to display the queryied output data in a table view.

Knime data table to display SQL query execution on grid

Knime data users can also use DB SQL Executor to execute a custom SQL query on the connected data platform, in our case on Amazon Redshift DWH database.

Knime DB SQL Executor to execute SQL queries on Redshift database

And to display the SQL query result data set visually as a table display, DB Query Reader node can be used.

After the Knime workflow is executed successfully by right-clicking on the DB Query Reader component and selecting the Knime data table will help the Knime users to display the data in a tabular format.

Knime data table to view SQL query output

I hope this Knime tutorial is useful for the Knime developers to connect Amazon Redshift databases using built-in PostgreSQL connector without installing a Redshift driver immediately.