Amazon Redshift

Step-by-step guide on setting up Amazon Redshift as a destination in RudderStack.

Amazon Redshift is the world's fastest cloud data warehouse. It allows you to handle large analytical workloads with best-in-class performance, speed, and efficiency. With Redshift, you don't have to worry about the scale of your data or the cost of running queries on them.

RudderStack lets you configure Redshift as a destination to which you can send your event data seamlessly.

Refer to the Warehouse Schemas guide for more information on how the events are mapped to the tables in Redshift.

Find the open source transformer code for this destination in the GitHub repository.

Setting up a Redshift cluster

Before adding Redshift as a destination in RudderStack, it is recommended that you create a new Redshift cluster depending on the type of instance needed.

The following sections contain step-by-step instructions on setting up a Redshift cluster.

Choosing the Redshift instance type

Amazon Redshift provides two types of clusters: Dense Compute and Dense Storage clusters.

• Dense Compute clusters maximize CPU usage, resulting in an increased query performance. However, there is a trade-off with respect to the storage.

• Dense Storage clusters maximize storage for customers with hundreds of millions of rows of data. However, there is a trade-off in the CPU usage, resulting in a lower query performance.

Refer to this Redshift guide for more information on the cluster and node types in Redshift.

Creating a new Redshift cluster

Follow the steps below to create a new Redshift cluster:

1. Open the Redshift Console as shown:

Redshift Console

1. Click on the Create Cluster option, as shown:

1. Enter the cluster details. First, fill in the Cluster identifier and choose the instance type, as shown:

Redshift cluster configuration settings

1. Enter the number of nodes for your cluster. This will primarily depend on the amount of data you expect to work with.

1. Enter the database name, and create the admin user with the name of your choice.

For security purposes, it is recommended that you choose a strong password.

1. Finish creating the cluster by allowing the default options for Additional Configurations.

As a part of this setup, you also need to edit the VPC network and configure the security settings. More information on these aspects in available in the following sections.

With the Redshift cluster now created and ready to use, the next sections cover the necessary steps to set up the necessary user permissions and set up Redshift as a destination in RudderStack.

Setting user permissions in Redshift

This section contains the steps to create a new user to access the Redshift cluster and create temporary tables in it.

The username and password provided earlier while creating the Redshift cluster should be strictly used for administration purposes. RudderStack will create a different user to enable access to Redshift. This also helps RudderStack keep the queries separate as well as maintain an audit log.

1. Click on the Editor option in the left pane. You can run the queries to create a new user to access the Redshift cluster in the Query editor, as shown:

1. The queries to create a new user are listed below:

-- create a user named "rudder" RudderStack can use to access Redshift
CREATE USER rudder PASSWORD '<password goes here>';

-- granting schema creation permission to the "rudder" user on the database you chose earlier
GRANT CREATE ON DATABASE "<database name goes here>" TO "rudder";

1. Log into the Redshift cluster with the newly created user credentials.

Use the newly-created user credentials while configuring Redshift as a destination in the RudderStack dashboard.

Setting up network and security access

This section is listed for EC2-VPC. However, EC2-Classic works similarly.

IPs to be whitelisted

To enable network access to RudderStack, you will need to whitelist the following RudderStack IPs:

• 3.216.35.97

• 34.198.90.241

• 54.147.40.62

• 23.20.96.9

• 18.214.35.254

• 35.83.226.133

• 52.41.61.208

• 44.227.140.138

• 54.245.141.180

• 3.66.99.198

• 3.64.201.167

If you have your deployment in the EU region, you can whitelist only the following two IPs:

• 3.66.99.198

• 3.64.201.167

All the outbound traffic is routed through these RudderStack IPs.

Adding a security group

Follow these steps to add a security group and assign it to your Redshift cluster:

1. Go to EC2 from the services on your AWS console, as shown:

1. Go to Security Groups under Network & Security, followed by Create Security Group.

1. Enter the details of the security group. The Security group name will be used to select the group later.

1. Add an Inbound rule with IPs listed above, and enter the Redshift port as 5439 in the Port range field, as shown:

1. Next, go to the Redshift cluster and select Properties, where you can modify the network and security rules of the cluster.

1. Edit the Network and security option and choose the VPC security group that you selected earlier.

1. Finally, click on Modify cluster to finish the Network and Security setup.

The Redshift cluster needs to be publicly accessible. Refer to this Redshift guide for more information on how to set this property.

Configuring Redshift destination in RudderStack

To send event data to Redshift, you first need to add it as a destination in RudderStack and connect it to your data source. Once the destination is enabled, events will automatically start flowing to Redshift via RudderStack.

To configure Redshift as a destination in RudderStack, follow these steps:

1. In your RudderStack dashboard, set up the data source. Then, select Redshift from the list of destinations.
2. Assign a name to your destination and then click on Next.

Connection Settings

Enter the following credentials in the Connection Credentials page:

• Host: The host name of your Redshift service.

• Port: The port number associated with the Redshift database instance.

• Database: The database name in your Redshift instance where the data will be sent.

• User: The name of the user with the required read/write access to the above database.

• Password: The password for the above user.

• Namespace: Enter the schema name where RudderStack will create all the tables. If you don't specify any namespace, RudderStack will set this to the source name, by default.

• Sync Frequency: Specify how often RudderStack should sync the data to your Redshift database.

• Sync Starting At: This optional setting lets you specify the particular time of the day (in UTC) when you want RudderStack to sync the data to the warehouse.

• Exclude Window: This optional setting lets you set a time window when RudderStack will not sync the data to your database.

Configuring the object storage

RudderStack lets you configure the following object storage configuration settings while setting up your Azure Synapse destination:

• Use RudderStack-managed object storage: Enable this setting to use RudderStack-managed buckets for object storage.

This option is applicable only for RudderStack-hosted data planes. For self-hosted data planes, you will have to specify your own object storage configuration settings.

• Choose your storage provider: If Use RudderStack-managed object storage is disabled in the dashboard, you can enter the relevant Amazon S3 bucket storage settings.

S3 Permissions

The alternative to providing AWS credentials for S3 access, is to set up permissions for your bucket as specified in the following section:

RudderStack-hosted data plane

You need to edit your bucket policy to allow RudderStack to write to your bucket with the following JSON:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::422074288268:user/s3-copy"
      },
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:PutObjectAcl",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::YOUR_BUCKET_NAME/*",
        "arn:aws:s3:::YOUR_BUCKET_NAME"
      ]
    }
  ]
}

Self-hosted data plane

1. Create an IAM policy with the following JSON:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Action": "*",
      "Resource": "arn:aws:s3:::*"
    }
  ]
}

1. Create an IAM user with programmatic access keys and attach the above created IAM policy. Copy the ARN of this user.

2. Edit your bucket policy to allow the data plane to write to your bucket with the following JSON. Make sure you edit the account id and user ARN with your AWS Account ID and the above created user ARN:

{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Effect": "Allow",
      "Principal": {
        "AWS": "arn:aws:iam::ACCOUNT_ID:user/USER_ARN"
      },
      "Action": [
        "s3:GetObject",
        "s3:PutObject",
        "s3:PutObjectAcl",
        "s3:ListBucket"
      ],
      "Resource": [
        "arn:aws:s3:::YOUR_BUCKET_NAME/*",
        "arn:aws:s3:::YOUR_BUCKET_NAME"
      ]
    }
  ]
}

1. Finally, add the programmatic access credentials to the environment of your data plane, as shown:

RUDDER_AWS_S3_COPY_USER_ACCESS_KEY_ID=<above created user access key>
RUDDER_AWS_S3_COPY_USER_ACCESS_KEY=<above created user access key secret>

Column compression encoding

Compression encoding specifies the type of compression applied to a column of data values as rows are added to a table.

If not specified, Redshift automatically assigns compression encoding. RudderStack explicitly sets the runlength encoding for Boolean columns.

FAQ

How are reserved words handled by RudderStack?

There are some limitations when it comes to using reserved words in a schema, table, or column names. If such words are used in event names, traits or properties, they will be prefixed with a _when RudderStack creates tables or columns for them in your schema.

Besides, integers are not allowed at the start of the schema or table name. Hence, such schema, column or table names will be prefixed with a _.

For instance, '25dollarpurchase' will be changed to '_25dollarpurchase'.

Contact us

For queries on any of the sections covered in this guide, you can contact us or start a conversation in our Slack community.