Skip to main content

Redshift Setup Guide

Justin Stevenson
Updated

Redshift Setup Guide

Follow our setup guide to connect your Redshift data warehouse to Fivetran.

Prerequisites

To connect Redshift to Fivetran, you need the following:

  • Access to the AWS console to safelist
  • Redshift . Redshift user accounts can only be created and removed by a database superuser.
  • Fivetran role with the permissions
  • CREATE permissions for Redshift limited user

Redshift provisioned data warehouse - Setup instructions

Choose connection method

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

NOTE: You must connect through SSH if your Redshift cluster is not publicly accessible.

Connect directly

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

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 data warehouse. You must then configure your tunnel server's security group to allow Fivetran access and configure the instance's security to allow access from the tunnel.

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

To connect using SSH, do the following:

  1. In the , 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.

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

If you select PrivateLink as a connection method, Fivetran uses to move your data securely between our system and your destination.

To set up PrivateLink for your Redshift destination, follow the instructions in the .

Find endpoint details

IMPORTANT: If you selected PrivateLink as a connection method, skip this step and follow our instructions on how to .

  1. .

  2. In the left menu, click Clusters.

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

    Redshift vpc clusters

  4. Click Properties.

    Redshift vpc cluster 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.

    Redshift_vpc_endpoint

    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.

    Redshift vpc cluster 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.

    Redshift cluster properties vpc

  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.

    Redshift setup-click-inbound-edit

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

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

    Redshift setup2-add-redshift-security-rule

Enable Automatic WLM

In Automatic WLM, the system calculates the optimal memory and concurrency of your queues. To enable automatic WLM, do the following:

  1. In the Redshift console, click Configurations > Workload management.

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

    Redshift_switch_wlm_mode

  3. In the Workload management tab, verify the WLM mode. Click Switch WLM mode.

    Redshift_auto_wlm

  4. In the Concurrency settings window, select Automatic WLM and click Save.

If you want to use Manual WLM, you must allocate a query concurrency of 4 or above to Fivetran. To modify the query concurrency of an existing queue, do the following:

  1. In the Workload management tab, go to the Workload queues pane.

  2. Click Edit workload queues to change the query concurrency.

  3. Use one of the following methods:

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

      Redshift_vpc_concurrency_auto

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

      Redshift_vpc_concurrency_json

(Optional) Authenticate using IAM

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

  1. In the , 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 .

    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",
                  "Resource": [
                  "arn:aws:redshift:{region}:{account-id}:dbuser:{cluster-name}/{dbuser-name}",
                  "arn:aws:redshift:{region}:{account-id}:dbname:{cluster-name}/{database-name}"
                  ]
            }
      ]
      }
content_copy

    v. Replace {region}, {account-id}, and {cluster-name} with the values for your AWS Region, account, and cluster.

    vi. Replace {dbuser-name} with the user name used to log in to the cluster database.

    vii. Replace {database-name} with the name of the database that the user will log in to.

    viii. Click Review Policy.

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

    x. 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. Click Edit trust relationship.

    Redshift_edit_trust_relationship

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

        {
    "Version": "2012-10-17",
    "Statement": [
        {
        "Effect": "Allow",
        "Principal": {
            "AWS": [
            "arn:aws:iam::834469178297:user/salame_access",
            "arn:aws:iam::834469178297:user/gcp_donkey"
            ]
        },
        "Action": "sts:AssumeRole",
        "Condition": {
            "StringEquals": {
            "sts:ExternalId": "{externalId}"
            }
        }
        }
    ]
    }   
content_copy

    xiv. Replace {externalId} with your External ID. You can find the External ID in the destination setup form.

    xv. Click Update Trust Policy.

    xvi. 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.

IMPORTANT: If you use an SSH Tunnel to connect, you must enter the Cluster ID and Cluster region details in the destination setup form.

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.

    Redshift_vpc_cluster_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.

    Redshift_vpc_master_user

Limited User

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

  1. .

  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>;
content_copy

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

      CREATE USER fivetran PASSWORD disable;
content_copy

  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;
content_copy

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

(Optional) Create VPC endpoint

If you use , you must create a VPC endpoint to allow access to the S3 buckets and copy data into the tables.

To create a VPC endpoint, do the following:

  1. Sign in to the AWS Management Console and open the .

  2. Click Endpoints.

  3. Click Create Endpoint.

    Select_create_endpoint_button_vpc

  4. In the Service category options, select AWS services.

  5. In the Service Name section, select your S3 service to grant access.

  6. In the VPC drop-down menu, select the VPC you use in the Redshift cluster.

    Select_s3_service_vpc

  7. In the Configure route tables section, select the Route Table ID associated with the VPC.

  8. In the Policy section, select Full Access to allow access to S3 services.

    Select_full_access_vpc

  9. Click Create endpoint.

For more information about VPC endpoints, see .

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

AWS PrivateLink allows VPCs and AWS-hosted or on-premises services to communicate with one another without exposing traffic to the public internet. PrivateLink is the most secure connection method. Learn more in .

Fivetran uses PrivateLink to move your data securely between our system and your Redshift destination.

Prerequisites

To set up AWS PrivateLink, you need:

  • A Fivetran instance configured to run in AWS
  • A Redshift destination in one of

Perform the following steps to obtain the Endpoint URL that you need to .

Set up Amazon Redshift-managed VPC endpoint and grant Fivetran cluster access

  1. Follow the steps in the to set up an Amazon Redshift-managed VPC endpoint.

  2. When authorizing access to additional AWS accounts in the Amazon Redshift console, go to Clusters and enter 834469178297 in the AWS account ID field to grant Fivetran cluster access.

    Grant Fivetran cluster access

  3. Select Grant access to all VPCs.

  4. Click Grant Access.

Provide credentials to Fivetran Support

Send the following information to our :

  • your AWS account ID
  • your cluster identifier

Specify endpoint URL in setup form

Once we provide you with an endpoint URL, you should specify it in the Host field of the setup form when .

Complete Fivetran configuration

  1. Log in to your Fivetran account.

  2. Go to the , then click + Add Destination.

  3. On the Add destination to your account page, enter a Destination name of your choice.

  4. Click Add.

  5. Select Redshift as the destination type.

  6. In the Host field in destination setup form, enter one of the following values:

    • the host name you found in - if you select Connect directly or Connect via an SSH as a connection method
    • the endpoint URL we - if you select Connect via Private Link as a connection method

  7. In the Port field in destination setup form, enter one of the following values:

    • the host name you found in - if you select Connect directly or Connect via an SSH as a connection method
    • the default Redshift port 5439 - if you select Connect via Private Link as a connection method

  8. Enter the Database name you found in .

  9. Enter the User name you found in .

  10. 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 .
  1. Choose your Connection method:
  • Connect directly
  • Connect via an SSH
  • Connect via Private Link

    NOTE: The Connect via Private Link option is only available for Business Critical accounts.

  1. If you choose Connect via an SSH tunnel, enter the following details:
  • SSH Host
  • SSH Port
  • SSH User

  1. (Optional) If you choose Connect via an SSH tunnel or Connect via Private Link, enable the Require TLS through tunnel toggle if you want to use TLS.

  2. (Optional) If you use IAM authentication and an SSH tunnel to connect, enter your Cluster ID and then choose your Cluster region.

    NOTE: If we auto-detect the cluster region, the Cluster region field won't be visible in the setup form.

  3. (Optional) Choose the Cluster region. Fivetran uses the data staging bucket in the cluster region you select.

    IMPORTANT: If you use VPC security policies, select the same region as the Redshift cluster. If you do not select the cluster region, we will use your data processing location as the cluster region.

  4. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our .

  5. Choose your Time zone.

  6. (Optional for Business Critical accounts) To enable , set the Use Failover toggle to ON, and then select your Failover Location and Failover Region. Make note of the IP addresses of the secondary region and safelist these addresses in your firewall.

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

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

Redshift Serverless - Setup instructions

Choose connection method

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

NOTE: You must connect through SSH if your Redshift cluster is not publicly accessible.

Connect directly

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

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.

For more information, see .

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 data warehouse. You must then configure your tunnel server's security group to allow Fivetran access and configure the instance's security to allow access from the tunnel. You must have three subnets associated to your workgroup.

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

To connect using SSH, do the following:

  1. In the , 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.

IMPORTANT: You must have a Business Critical plan to use AWS PrivateLink.

If you select PrivateLink as a connection method, Fivetran uses to move your data securely between our system and your destination.

To set up PrivateLink for your Redshift destination, follow the instructions in the .

Find endpoint details

  1. Open the .

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

    Redshift serverless vpc namespace

  3. Select your workgroup.

    Redshift serverless vpc workgroup

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

    Redshift_serverless vpc_endpoint

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

Allow Fivetran to connect

  1. Select the namespace you want Fivetran to connect to.

  2. Select your workgroup.

  3. In the Data Access tab, scroll down to the Network and security section.

  4. In the VPC security group field, click the security group to open it and make a note of the security group ID.

    Redshift serverless vpc security group

  5. 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.

  6. Click Edit inbound rules.

    Redshift setup-click-inbound-edit

  7. In the Edit Inbound rules window, follow the steps below to create custom TCP rules for each of in your region:

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

    Redshift setup2-add-redshift-security-rule

(Optional) Authenticate using IAM

IAM AUTHENTICATION FOR REDSHIFT SERVERLESS IS NOT SUPPORTED AS OF NOW

IMPORTANT: If you use an SSH Tunnel to connect, you must enter the Cluster ID and Cluster region details in the destination setup form.

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. Select the namespace you want Fivetran to connect to.

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

    Redshift_serverless_master_user

Limited User

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

  1. .

  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>;
content_copy

    • IAM authentication: Execute the following query to create a user (replace <rolename> with the role you created in ) without any password:

        CREATE USER IAMR:<rolename> PASSWORD disable;
content_copy

  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 IAMR:<rolename>;
content_copy

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

(Optional) Create VPC endpoint

If you use , you must create a VPC endpoint to allow access to the S3 buckets and copy data into the tables.

To create a VPC endpoint, do the following:

  1. Sign in to the AWS Management Console and open the .

  2. Click Endpoints.

  3. Click Create Endpoint.

    Select_create_endpoint_button_vpc

  4. In the Service category options, select AWS services.

  5. In the Service Name section, select your S3 service to grant access.

  6. In the VPC dropdown menu, select the VPC you use in the Redshift cluster.

    Select_s3_service_vpc

  7. In the Configure route tables section, select the Route Table ID associated with the VPC.

  8. In the Policy section, select Full Access to allow access to S3 services.

    Select_full_access_vpc

  9. Click Create endpoint.

For more information about VPC endpoints, see .

Complete Fivetran configuration

  1. Log in to your Fivetran account.
  2. Go to the , then click + Add Destination.
  3. On the Add destination to your account page, enter a Destination name of your choice.
  4. Click Add.
  5. Select Redshift as the destination type.
  6. Enter the Host name you found in .
  7. In the Port field, enter the host name you found in .
  8. Enter the Database name you found in .
  9. Enter the User name you found in .
  10. 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 .

  1. Choose your Connection method:
  • Connect directly
  • Connect via an SSH
  • Connect via Private Link
  1. If you choose Connect via an SSH tunnel, enter the following details:
  • SSH Host
  • SSH Port
  • SSH User

  1. (Optional) If you choose Connect via an SSH tunnel, enable the Require TLS through tunnel toggle if you want to use TLS.

  2. (Optional) If you use IAM authentication and an SSH tunnel to connect, enter your Cluster ID and then choose your Cluster region.

    NOTE: If we auto-detect the cluster region, the Cluster region field won't be visible in the setup form.

  3. (Optional) Choose the Cluster region. Fivetran uses the data staging bucket in the cluster region you select.

    IMPORTANT: If you use VPC security policies, select the same region as the Redshift cluster. If you do not select the cluster region, we will use your data processing location as the cluster region.

  4. Set the Connect to Redshift Serverless toggle to ON.

  5. Choose the Data processing location. Depending on the plan you are on and your selected cloud service provider, you may also need to choose a Cloud service provider and cloud region as described in our .

  6. Choose your Time zone.

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

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

Setup tests

Fivetran performs the following Redshift connection tests:

  • The Validate Cluster Region Test validates the cluster region you provided in the setup form. We skip this test if you don't specify the cluster region.
  • The Verify Host/Cluster Details Test validates the cluster details you provided in the setup form. We perform this test only if you use IAM authentication and an SSH tunnel to connect.
  • 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. We do not perform this test for Redshift Serverless destinations.
  • 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.

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk