Amazon Redshift — Connect from your Windows machine

This Blog has moved from Medium to blogs.tensult.com. All the latest content will be available there. Subscribe to our newsletter to stay updated.

Redshift is a fully managed, petabyte-scale cloud-based data warehouse solution from Amazon. You can start with the creation of a Redshift cluster with one or more nodes. An Amazon Redshift cluster consists of a leader node and one or more compute nodes. The type and number of nodes depend on the volume of the data and query you make on the data. After the Redshift cluster is created, you can upload the data set and then perform data analysis/queries. Redshift can do an automated and manual backup to S3 with the help of a snapshot which restores the data to a new cluster if required.

About the Experiment

We are going to configure a single node Redshift cluster from AWS console. Then we will install SQL Workbench and a Redshift JDBC connector in a Windows 2016 Server. After connecting to Redshift from the SQL workbench we try to create a table and insert some records.

Softwares involved
Windows 2016 Server
Java 8 (jre-8u181-windows-x64.exe)
RedshiftJDBC42–1.2.16.1027.jar
Workbench-Build124.zip

Redshift Configuration

• Create a Redshift Cluster
• SQL Workbench setup (Windows 2016 Server)
• Manage Data in Redshift

Create a Redshift Cluster

1. Login to AWS web console and access Redshift service. In the dashboard click “Launch Cluster”. Enter the parameters like below. Click Continue

2) In the Node Configuration window enter Node Type. It starts with dc2.large. Here, you can’t enter a value for Memory or Storage, it fully depends on the node type you have selected. Enter Cluster Type, I entered the Single Node to keep the cost the least. Click Continue.

3) Next window, Select relevant VPC Security Group(5439/TCP should be opened) and Continue. There are many other options which you can configuration based on your requirement. Click Continue.

4) Next window review and click Launch Cluster. After few minutes cluster is created and runs like below

5) Select Configuration window and note down JDBC URL. You have to enter this URL when you configure SQL Workbench later in the set up.

SQL Work Bench Setup (Windows 2016 Server)

1) Down load and install java 8 in your Windows 2016 Server. You may test the setup & use any other Windows version.

2) Download JDBC connector from the following website. https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.16.1027/RedshiftJDBC42-1.2.16.1027.jar

3) Access www.sql-workbench.eu and download SQL workbench software. I have used Workbench-Build124.zip here.

4) Open the file Workbench-Build124.zip and click SQLWorkbench64 application like below.

5) It prompts for a directory name to extract the files. Enter the directory name Click Extract.

6) It extracts the file to the directory you specified. Now you click SQLWorkbench64 like below

7) When the Window opens Click Manage Drivers. You are going to setup JDBC connector.

8) Now select “Amazon Redshift” from the list and click the button on the right to select the .jar(RedshiftJDBC42–1.2.16.1027.jar) file you had downloaded.

9) Click file -> Connect Window which opens interface like below. Click OK after entering the required parameters, and your SQL Workbench setup is over.

Manage Data in Redshift

We will see some of the basic data operation on a Redshift cluster.

1. Create create a table.

2) Save the data.

3) Insert a record in the table

4) List the records in the table.

5) To see redshift data operations, goto Redshift dashboard and access Queries menu

Conclusion

Redshift is a fully managed, petabyte-scale cloud based data warehouse solution from Amazon. We configured a single node Redshift cluster. We installed SQL Work bench and a Redshift JDBC connector in a Windows 2016 Server. After connecting to Redshift from the SQL workbench we created a table and inserted some records.

Disclaimer

Some of the software involved in the experiment are open source or without vendor support. I suggest you to test the software and setup before you implement.

Further reading and experiments

With the help of below document and try to migrate an RDS SQL table to Redshift using Amazon datapipeline.

AWS Data Pipeline- Copy from DynamoDB Table to S3 Bucket