Redshift Setup Guide

---

In Redshift

Choose connection method

Decide whether to connect to your Redshift warehouse directly or using an SSH tunnel. For more information, see our destination connection options documentation.

Connect directly

If you connect directly, you must to create a rule in a security group that allows Fivetran access to your destination port and database instance.

Connect using an SSH tunnel

If you connect using an SSH tunnel, Fivetran connects to a separate server in your network that provides an SSH tunnel to your Redshift warehouse. You must then configure your tunnel server's security group to allow Fivetran access and configure the warehouse instance's security to allow access from the tunnel.

You must connect through SSH if your warehouse is contained within an inaccessible subnet.

Find endpoint details

To allow Fivetran to connect to your Redshift cluster, you must provide us with your host and port details.

1. Open the Redshift console.

2. In the left menu, click Clusters.

3. Select the cluster you want Fivetran to connect to.

   

4. Click Properties.

   

5. In the Connection details pane's Endpoint field, click Copy to copy the endpoint details. You will need them to complete the destination setup in Fivetran.

   

   Be sure to separate the port and remove the preceding colon (:) from the host string.

Allow Fivetran to connect

1. In the Redshift console, click Clusters.

2. Select the cluster you want Fivetran to connect to.

3. Click Properties.

   

4. Scroll down to the Network and security section.

5. In the VPC security group field, click the security group to open it. Make a note of the security group ID.

   

6. In the Security Groups window, click Inbound rules.

   The security group you clicked in the previous view should be pre-selected here. Ensure that you selected the same security group from the previous screen.

7. Click Edit inbound rules.

   

8. In the Edit Inbound rules window, follow the steps below to create custom TCP rules for each of Fivetran's IPs in your region:

   • Select Custom TCP in the drop-down menu.
   • Enter your Redshift port number.
   • Enter the Fivetran IP address.
   • Click Add rule.

   

Allocate query concurrency

You must allocate a query concurrency of 4 or above to Fivetran. To modify the query concurrency of an existing queue:

1. Open the Redshift console and click Configurations > Workload management.

2. In the Workload management window, select the parameter group.

3. In the Workload queues pane, click Edit workload queues to change the query concurrency. Use one of the following methods:

   • Modify the Concurrency on main column value for the queue.

     

   • In the JSON section, edit the configuration and modify the query_concurrency parameter for the queue.

     

Connect as Master or Limited user

You can connect as either a "Master" or "Limited" user. The master username inherently has the CREATE permissions that Fivetran needs to function, so we recommend connecting as a master user. If you don't want to connect as a master user, you must create a new limited user for Fivetran.

Master User

1. In the Redshift console, select the cluster you want Fivetran to connect to.

2. Click Properties.

   

3. In the Properties tab, scroll down to the Database configurations section.

4. Copy Database name and the Master user name values. You will need them to complete the destination setup in Fivetran.

   

Limited User

To connect as a limited user, you must create a Redshift user for Fivetran.

1. Connect to Redshift using a SQL client tool.

2. Execute the following query to create a password, replacing <password> with a password of your choice.

   CREATE USER fivetran PASSWORD <password>;
   

3. Execute the following query to grant the fivetran user the following privileges (Replace <database> with your database name).

   • CREATE: Allows the user to create new schemas in the database
   • TEMPORARY: Allows the user to create temporary tables while using the database

   GRANT CREATE, TEMPORARY ON DATABASE <database> TO fivetran;
   

Your Redshift cluster is now connected as a limited user.

---

In Fivetran

1. In the top left corner of your Fivetran dashboard, click on your warehouse name to view the drop-down menu.
2. Scroll to the bottom of the drop-down menu and click Manage account.
3. In the Destinations tab, click +Destination.
4. On the Add Destination To Fivetran page, enter a short memorable name for your destination.
5. Click Continue.
6. On the Select Destination Type page, click I already have a destination.
7. Select Redshift from the available destination options.
8. Enter the Host name you found in Step 2.
9. Enter the Port number you found in Step 2.
10. Enter the User name you found in Step 5.
11. Enter the Password that you created for your Redshift cluster.

    Note: This password is not same as your AWS password.

12. Enter the Database name you found in Step 5.
13. Choose your Connection method. You can choose to Connect directly or Connect via an SSH.
14. Choose the Data processing location.
15. Choose your Timezone.
16. Click Save and Test. Your Redshift cluster is now connected.