In this lab you will launch a new Redshift Cluster, setup connectivity and configure a JDBC Client tool.
To launch this cluster and configure security automatically using cloud formation, use the following link and skip ahead to Configure Client Tool.
When choosing a Region for your cluster, consider US-WEST-2 (Oregon). While most of these labs can be done in any Region, some labs query data in S3 which is located in US-WEST-2.
The template will use the default CIDR block of 0.0.0.0/0 which provides access from any IP Address. It is a best practice to replace this a range of IP addresses which should have access. For the purpose of these labs, replace it with your IP Address x.x.x.x/32.
Create or identify a VPC where you will launch your Redshift cluster. For many customers, the default VPC, Subnets, and Security Group are sufficient. If you are using the defaults, you can skip ahead to Subnet Group. For our purposes we will create a new VPC to isolate the traffic and create a new public subnet where the Redshift cluster will be deployed.
By default the VPC does not support DNS Hostnames. Enable it now by clicking Actions –> Edit DNS hostnames.
Click the enable check box.
To allow machines in your subnet to access the internet, we will create an Internet Gateway. Once created, select the Internet Gateway and attach it to the VPC created earlier.
Now, create two subnets with a default route to the previously created VPC in two different Availability Zones to improve fault tolerance.
To ensure the subnet has a way to connect to the internet, create a Route Table with the default route pointed to the internet gateway and with the new subnets added.
Create a Security Group associated to the VPC you created earlier. Edit the Security Group to create a rule which allows incoming connections to Redshift from your IP Address as well as a self-referencing rule for All Traffic.
Now, create a Redshift Cluster Subnet Group containing the two subnets you created earlier by clicking the add all subnets for this VPC button.
In order for Redshift to have access to S3 to load data, create an IAM Role with the type “Redshift” and the use-case of “Redshift - Customizable” and attach the AmazonS3ReadOnlyAccess and AWSGlueConsoleFullAccess policies to the role.
Finally, navigate to the Amazon Redshift Dashboard and click on the “Create Cluster” button.
Cluster Configuration - Choose the node type and set the number of nodes. For these labs a dc2.large node type with 4 nodes will be suitable.
Cluster Details - Enter values as appropriate for your organization. Note the Master user password as you will not be able to retrieve this value later.
Cluster Permissions - Select the Role which you identified or created earlier to associate to the cluster, and click Add IAM role
Additional Configuration - Disable Use defaults and choose the VPC, Subnet Group, and VPC Security group you identified or created earlier.
Leave the remaining settings with their default values. Click Create Cluster to launch the Redshift cluster.
Generic package for all systemslink to download the latest version of the SQL Workbench/J product. Note: SQL Workbench/J requires Java 8 or later to be installed on your system.
cdto the extract directory, type
bash sqlworkbench.shand press enter.
[File | Manage Drivers].
Amazon Redshiftand set the driver Library location to where you downloaded the Redshift JDBC Driver. Click Ok.
select * from pg_user
If you are done using your cluster, please think about decommissioning it to avoid having to pay for unused resources.