RedshiftThe Redshift connector allows you to query your Redshift database directly within tray.io, without having to worry about data pipelines or servers.
Connecting to your Redshift instance
To allow tray.io to connect to your Redshift database, you'll need to either make your database publicly accessible or you can white list ALL of the following static IP addresses for access:
Note: be sure to make it so that your Redshift database is only accessible with a strong username and password combination when white listing these IP addresses.
To do this:
- Open up the "Security" section of your Redhift AWS. Open the "default" security group.
- Click "Add Connection Type"
- Add a new "Connection Type", using the "CIDR/IP" connection type, and one of the above IP addresses as the IP address to authorize.
Assuming that you already have a Redshift cluster set up, and have configured the access settings mentioned above correctly, we can begin authenticating with Tray.
In your Tray workflow, click on a Redshift connector (or drag one in if you haven't already), then in the right hand panel select New Authentication.
A modal should then appear asking for a series of authentication credentials. Firstly, it asks for your Host name. To get this, log on to AWS and head over to the Clusters screen, then select the one that you're trying to connect to. The Host name is the Endpoint. Copy and paste the URL into the Host field, then remove the :5439 appended to the end of the URL.
Scroll down the authentication modal and you will see more fields that need to be completed. Port has already been entered, as 5439 is the default Redshift port. Yours may be different, as you can choose the port when you first configure the cluster. To check what yours is, on the same screen you copied and pasted the Host URL from, you should see a sub-heading of Cluster Database Properties. Here, you will find your clusters port, as well as the name of the database, which is what you need next for the authentication.
Continuing down the modal, you will see that the Schema field will have already been completed. By default, a database has one schema which is public. If you have previously created a variety of schemas, but are not sure what schemas you have available, copy and paste the following query into Redshifts Query Editor to view what Schemas you have created
select * from pg_namespace;. You can then perform basic search queries to identify what tables are grouped under each schema, then enter that schema name in the Schema field to use with Tray.
The final part of authentication is entering user credentials - these user credentials are specific to the Redshift cluster and not AWS services. In the example below, I have entered the master users credentials - these were created when first configuring the cluster. If you want to use other users for authentication, but are not sure what their credentials are, use the
select * from pg_user; query in the Redshift Query Editor to retrieve user details. Remember, users will need certain access rights in order to query & manipulate the database.