AWS: Redshift – quick start and SQL-workbench connection config

By | 10/18/2019
 

Our data-analytics team eventually started to play with an AWS Redshift cluster instead of the MariaDB RDS service.

Actually, the current task is to spin up a simple Proof of Concept Redshift’s cluster in the AWS.

Let’s do it quickly, without details – if this will go to the Production, I’ll add another post with more detailed overview.

For now, we are interested in two main parameters – the type of the node. It can be set as Dense Storage or Dense Compute, and will configure a cluster’s storage type, CPU, memory, etc:

When you launch a cluster, one option you specify is the node type. The node type determines the CPU, RAM, storage capacity, and storage drive type for each node. The dense storage (DS) node types are storage optimized. The dense compute (DC) node types are compute optimized.

  • Dense Storage: create a simple data warehouse for big data by lower price by using HDD disks
  • Dense Compute: create a “production-like” cluster with fast CPU, lot of memory and SSD-drives

For the PoC obviously chose the Dense Storage type.

Create an IAM role

Let’s start with an IAM-role creation – data-analytics will use AWS S3, so we need to grant Redshift permissions to work it.

Go to the IAM, create a new role, set its type to the Redshift – Customizable:

Attach the AmazonS3ReadOnlyAccess policy:

Save:

This role will be used later during the cluster creation.

A Redshift cluster creation

Go to the AWS Console, Redshift, click on the Quick launch cluster:

Set values to the Cluster identifier, Database name, Master user name, Master user password, and select the IAM role created above:

Wait for 5-10 minutes till its status will become Available:

Then, after the cluster is in the Available state – its DB Health still in the unknown state:

Go to the cluster, chose Modify:

Security Group

Create a new AWS Security Group, allow connections to the 5439 port:

Go back to the cluster, Modify again, and specify the new Security Group created:

SQL-workbench connection

And to test it’s working – let’s configure an SQL-workbench connection.

Install it on Arch Linux from AUR:

yaourt -S sql-workbench

Start it:

sqlworkbench &

Find an URL to download drivers here>>>.

Download it:

wget https://s3.amazonaws.com/redshift-downloads/drivers/jdbc/1.2.34.1058/RedshiftJDBC42-no-awssdk-1.2.34.1058.jar

Go to the Connection Profile, at the bottom – Manage Drivers:

Chose Redshift and the driver’s file you downloaded above:

 

Go to the AWS, Redshift’s cluster, find its Connection string:

Go to the Workbench, in the Profile set the JDBC string:

Create a test table::

create table testtable (id int, name varchar(10));

Add some data:

insert into testtable values (1, ‘val’);

Run the SELECT:

Done.