Once we spin up the Redshift Cluster, we are excited to create the tables , load data to it and query them to see the performance of this giant. Redshift has an inbuilt Query Editor through which we can fire the queries. However in an enterprise set-up it is generally recommended to use SQL client tools like Aginity or SQL Workbench.
Redshift by default is located in the default VPC with default subnets, route tables and internet gateways enabled.
Now let us say, i need to connect to this Redshift cluster from my Aginity client tool. Before this, i need to ensure the following network settings of Redshift are enabled,
1 . Redshift cluster should be allowed to publicly accessible. This setting was part of the ‘Modify’ cluster option in the ‘Actions’ tab in the older UI, in the new UI it is located separately at the bottom as in the screengrab below. Once we enable this option, the Cluster goes into Modifying status and then becomes available.
2. The default port for Redshift is 5439. In the default security group of the VPC this in bound rule is not set. We need to add the rule to allow connections to the port 5439 from the Laptop IP or the desired IP range.
3. We will now open the Aginity Pro Client to connect to the Redshift cluster. The new connection menu looks like this.
All the fields are self -explanatory, the tricky part is the Host(required). The host details can be found under the properties tab of the cluster.
The endpoint has the :5439/databasename towards the end. This needs to be removed and the endpoint will be the <clustername.accountid.region.redshift.amazonaws.com>. After giving all the other details, test the connection. If all the above steps are taken care, you should be able to successfully connect to your Redshift Cluster from the SQL Client in your local machine !