Redshift Setup Guidelink

Follow our setup guide to connect Redshift to Fivetran.

---

Prerequisiteslink

To connect a Redshift data warehouse to Fivetran, you need the following:

• Access to the AWS console to whitelist Fivetran's IP address
• Redshift Superuser. Redshift user accounts can only be created and removed by a database superuser.
• Fivetran account owner permission to add destinations
• CREATE permissions for Redshift limited user

---

Setup instructionslink

Choose connection method link

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 create a rule in a security group that allows Fivetran access to your destination port and database instance.

Configure your firewall and/or other access control systems to allow incoming connections to your host and port from Fivetran's IPs for your region.

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.

1. In the destination setup form, select the Connect via an SSH tunnel option.

2. Copy Fivetran's public SSH key.

3. Add the public key to the authorized_keys file of your SSH server. The key must be all on one line, so make sure that you don't introduce any line breaks when cutting and pasting.

Find endpoint details link

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 link

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 link

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.

     

(Optional) Authenticate using IAM link

By default, Fivetran uses the database user's credentials to authenticate the requests in the Redshift cluster. You can opt to authenticate using AWS IAM.

1. In the destination setup form, find the automatically-generated External ID and make a note of it. You will need it to create an IAM role in AWS.

   NOTE: The automatically-generated External ID is tied to your account. If you close and re-open the setup form, the ID will remain the same.

2. Create an IAM Policy for Fivetran:

   i. Open your Amazon IAM console.

   ii. Go to Access management > Policies, and then select Create policy.

   iii. In the Create policy window, go to the JSON tab.

   iv. Copy the following policy and paste it in the JSON tab:

         {
         "Version": "2012-10-17",
         "Statement": [
               {
                     "Sid": "VisualEditor0",
                     "Effect": "Allow",
                     "Action": [
                     "redshift:GetClusterCredentials",
                     "redshift:JoinGroup",
                     "redshift:CreateClusterUser"
                     ],
                     "Resource": "*"
               }
         ]
         }
   

   v. Click Review Policy.

   vi. Name the policy "Fivetran-Redshift-Access".

   vii. Click Create Policy.

3. Create an IAM role for Fivetran:

   i. Go to Access management > Roles and then select Create role.

   ii. In the Create role window, select Another AWS account.

   iii. In the Account ID field, enter Fivetran's account ID, 834469178297.

   iv. In Options, check the Require external ID checkbox.

   v. Enter the External ID you found in your destination setup form.

   vi. Click Next: Permissions.

   vii. Select the "Fivetran-Redshift-Access" policy that you created.

   viii. Click Next: Tags. Entering tags is optional, but you must click through the step.

   ix. Click Next: Review.

   x. Name your new role "Fivetran", then click Create Role.

   xi. Select the Fivetran role you just created.

   xii. In the Summary section, make a note of the Role ARN.

   NOTE: You can specify permissions for the Role ARN that you designate for Fivetran. Giving selective permissions to this Role will allow Fivetran to only sync what it has permissions to see.

Connect as Master or Limited user link

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. Depending on your authentication type, do the following:

   • Password authentication: Execute the following query to create a user (replace <password> with a password of your choice):

     CREATE USER fivetran PASSWORD <password>;
     

   • IAM authentication: We recommend that you create a user without any password. Execute the following query:

     CREATE USER fivetran PASSWORD disable;
     

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;
     

   You will need the limited user's credentials to complete the destination setup in Fivetran.

Complete Fivetran configuration link

1. Log in to your Fivetran account.

2. Go to the Manage Account page.

3. In the Destinations tab, click +Destination.

4. On the Add Destination To Fivetran page, enter a destination name of your choice.

5. Click Continue.

6. Select Redshift as the destination type.

7. In the destination setup form, enter the Host name you found in Step 2.

8. Enter the Port number you found in Step 2.

9. Enter the Database name you found in Step 6.

10. Enter the User name you found in Step 6.

11. Choose the Authentication Type: PASSWORD or IAM. The default type is PASSWORD.

    • If you choose PASSWORD, then enter the Password you created for your Redshift cluster.

      NOTE: This password is not the same as your AWS password.

    • If you choose IAM, then enter the Role ARN you created for your Redshift cluster in Step 5.

12. Choose your Connection method: Connect directly or Connect via an SSH. If you choose Connect via an SSH tunnel, enter the following details:

    • SSH Host
    • SSH Port
    • SSH User
    • (Optional) Enable the Require TLS through tunnel toggle if you want to use TLS.

13. Choose the Data processing location.

14. Choose your Timezone.

15. Click Save and Test. Your Redshift cluster is now connected.

Fivetran tests and validates the Redshift connection. On successful completion of the setup tests, you can sync your data using Fivetran connectors to the Redshift destination.

Setup testslink

Fivetran performs the following Redshift connection tests:

• The Database Host Connection Test validates the database credentials you provided in the setup form. The test verifies that the host is not private and then checks the connectivity to the host.

• The SSH Tunnel Test validates the SSH tunnel details you provided in the setup form and then checks the connectivity to the instance using the SSH Tunnel if you are connecting using an SSH tunnel.

• The Connection Test connects to your database instance and executes queries to check if we have the permissions to access the information_schema and pg_catalog schemas.

• The Query Concurrency Test validates if you have allocated a query concurrency of four or above to Fivetran.

• The Permission Test checks if we have permissions to create schemas and temporary tables on your Redshift database.

  NOTE: The tests may take a couple of minutes to finish running.

---

Related articleslink

description Destination Overview

assignment Release Notes

settings API Destination Configuration

home Documentation Home