AWS: Redshift – quick start and SQL-workbench connection configuration
Tags: aws, database, bigdata
Published: 2019-10-18 11:51:29

---
title: AWS: Redshift – quick start and SQL-workbench connection configuration
published: true
date: 2019-10-18 11:21:07 UTC
tags: AWS,Databases,bigdata
canonical_url: https://rtfm.co.ua/en/aws-redshift-quick-start-and-sql-workbench-connection-config/
---

![](https://rtfm.co.ua/wp-content/uploads/2014/11/aws-logo-square-02-e1417012834176-1.png)Our data-analytics team eventually started to play with an [AWS Redshift](https://docs.aws.amazon.com/en_us/redshift/latest/mgmt/welcome.html) 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 a 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_:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_143854.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_143854.png)

Attach the `AmazonS3ReadOnlyAccess` policy:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144103.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144103.png)

Save:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144132.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144132.png)

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_:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144312.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144312.png)

Set values to the _Cluster identifier_, _Database name,_ _Master user name_, _Master user password_, and select the IAM role created above:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144617.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144617.png)

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

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144819.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_144819.png)

Then, after the cluster is in the _Available_ state – its _DB Health_ still in the _unknown_ state:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_145831.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_145831.png)

Go to the cluster, chose _Modify_:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_145933.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_145933.png)

#### Security Group

Create a new _AWS Security Group_, allow connections to the _5439_ port:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_150337.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_150337.png)

Go back to the cluster, _Modify_ again, and specify the new _Security Group_ created:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_150415-1.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_150415-1.png)

### SQL-workbench connection

And to test it’s working – let’s configure an [SQL-workbench](https://www.sql-workbench.eu/) connection.

Install it on Arch Linux from AUR:

```
$ yaourt -S sql-workbench
```

Start it:

```
$ sqlworkbench &
```

Find an URL to download drivers [here>>>](https://docs.aws.amazon.com/en_us/redshift/latest/mgmt/configure-jdbc-connection.html).

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_:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191018_131032.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191018_131032.png)

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

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153106.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153106.png)

Go to the AWS, Redshift’s cluster, find its _Connection string_:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153209.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153209.png)

Go to the Workbench, in the _Profile_ set the _JDBC_ string:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153405.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_153405.png)

Create a test table::

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

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154221.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154221.png)

Add some data:

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

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154343.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154343.png)

Run the `SELECT`:

[![](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154416.png "AWS: Redshift - быстрый запуск кластера")](https://rtfm.co.ua/wp-content/uploads/2019/10/Screenshot_20191010_154416.png)

Done.

### Similar posts

- <small>10/10/2019</small> [Vertica: a simple cluster set up and configuration on AWS EC2](https://rtfm.co.ua/en/vertica-a-simple-cluster-set-up-and-configuration-on-aws-ec2/)  
- <small>03/29/2019</small> [Redis: replication, part 1 – an overview. Replication vs Sharding. Sentinel vs Cluster. Redis topology.](https://rtfm.co.ua/en/redis-replication-part-1-overview-replication-vs-sharding-sentinel-vs-cluster-redis-topology/) 
- <small>03/29/2019</small> [Redis: replication, part 2 – Master-Slave replication, and Redis Sentinel](https://rtfm.co.ua/en/redis-replication-part-2-master-slave-replication-and-redis-sentinel/)