# Amazon Redshift Destination

[Amazon Redshift](https://aws.amazon.com/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.

{{< info >}}
See the [Warehouse Schema]({{< ref "destinations/warehouse-destinations/warehouse-schema.md" >}}) guide for more information on how the events are mapped to the tables in Redshift.
{{< /info >}}

Find the open source code for this destination in the [GitHub repository](https://github.com/rudderlabs/rudder-transformer/tree/main/src/v0/destinations/rs).

{{< warning >}}
If you are using the [PgBouncer connection pooler](https://www.pgbouncer.org/) with Amazon Redshift, note that RudderStack supports only the session pooling mode and **not** the [transaction and statement pooling modes](https://www.pgbouncer.org/features.html).
{{< /warning >}}

## Set up Redshift cluster {#setting-up-a-redshift-cluster}

Before adding Redshift as a destination in RudderStack, creating a new Redshift cluster depending on the instance type is recommended.

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

### Choose Redshift instance type {#choosing-the-redshift-instance-type}

Amazon Redshift provides the following two cluster types:

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

See this [Redshift guide](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-clusters.html#working-with-clusters-overview) for more information on the cluster and node types in Redshift.

### Create new Redshift cluster {#creating-a-new-redshift-cluster}

1. Open the Redshift console.

{{< image src="images/dw-integrations/redshift/redshift-console.webp" alt="Redshift console" >}}

2. Click the **Create Cluster** option:

{{< image src="images/dw-integrations/redshift/create-cluster.webp" alt="Redshift create cluster option" >}}

3. Enter the cluster details. First, fill in the **Cluster identifier** and choose the instance type:

{{< image src="images/3 (4) (1) (1).webp" alt="Cluster Identifier" >}}<span class="imageTitle">Redshift cluster configuration settings</span>

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

{{< image src="images/4 (1) (1).webp" alt="Enter the number of nodes" >}}

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

{{< image src="images/5 (7).webp" alt="Add Source" >}}

{{< warning >}}
For security purposes, it is recommended that you choose a strong password.
{{< /warning >}}

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

{{< info >}}
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.
{{< /info >}}

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 tables in it.

{{< warning >}}
The username and password obtained while [creating the Redshift cluster](#creating-a-new-redshift-cluster) should be strictly used for administration purposes. 

RudderStack recommends creating a new user to enable access to Redshift. This also helps you keep the queries separate as well as maintain an audit log.
{{< /warning >}}

1. Click 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**:

{{< image src="images/6 (1).webp" alt="Query editor" >}}

2. To opt for an [IAM role-based authentication](#connection-settings) (recommended) while setting up the Redshift destination in RudderStack, you can create a user **without** a password and grant it access to the required database.

{{< tabs tabTotal="2" >}}
{{% tab tabName="Redshift Serverless toggled on" %}}

```sql
-- create new user with the Redshift IAM role name
CREATE USER "IAMR:<role-name>" PASSWORD disable; /

-- grant user schema creation permissions on the database
GRANT CREATE ON DATABASE '<YOUR_DATABASE>' TO "IAMR:<role-name>";
```

{{< warning >}}
Replace `<role_name>` with the name of the Redshift IAM role. See **Step 9** of [Set up new IAM role]({{< ref "destinations/aws-iam-role-for-rudderstack/redshift-iam-role.md#set-up-new-iam-role" >}}) for more information on obtaining the name.
{{< /warning >}}

{{% /tab %}}
{{% tab tabName="Redshift Serverless toggled off" %}}

```sql
-- create a new user named "rudder"
CREATE USER rudder PASSWORD disable;

-- grant user schema creation permissions on the database
GRANT CREATE ON DATABASE '<YOUR_DATABASE>' TO rudder;
```

{{% /tab %}}
{{< /tabs >}}

3. Run the below queries to create a new user with a password (required if [IAM role-based authentication](#connection-settings) is not toggled on) and grant access to the required database:

```sql
-- create a new user named "rudder"
CREATE USER rudder PASSWORD '<YOUR_PASSWORD>';

-- granting user schema creation permissions on the database
GRANT CREATE ON DATABASE '<YOUR_DATABASE>' TO rudder;
```

## Setting up network and security access

{{< success >}}
This section is listed for EC2-VPC. However, EC2-Classic works similarly.
{{< /success >}}

### IPs to be allowlisted

{{< customreadfile "/includes/iplist.md" >}}

### 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:

{{< image src="images/7 (3).webp" alt="Go to EC2" >}}

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

{{< image src="images/8 (1) (1).webp" alt="Create Security Group" >}}

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

{{< image src="images/9 (1).webp" alt="Security group name" >}}

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

{{< image src="images/10.webp" alt="Port range" >}}

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

{{< image src="images/11.webp" alt="Properties" >}}

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

{{< image src="images/12.webp" alt="Network and security" >}}

7. Finally, click **Modify cluster** to finish the **Network and Security** setup.

{{< image src="images/13.webp" alt="Modify cluster" >}}

{{< info >}}
The Redshift cluster needs to be publicly accessible. Refer to this [Redshift guide](https://aws.amazon.com/premiumsupport/knowledge-center/redshift-cluster-private-public/) for more information on how to set this property.
{{< /info >}}

## 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](https://app.rudderstack.com), set up the data source. Then, select **Redshift** from the list of destinations.
2. Assign a name to your destination and then click **Continue**.

### Connection settings

- **Use IAM for authentication**: Turn on this setting to use the RudderStack IAM role for authentication. For more information on creating a RudderStack IAM role for Redshift, see [this guide]({{< ref "destinations/aws-iam-role-for-rudderstack/redshift-iam-role.md" >}}).

You will see the following settings depending on whether **Use IAM for authentication** is toggled on or off:

{{< tabs tabTotal="2" >}}
{{% tab tabName="IAM Authentication toggled ON" %}}
- **Cluster ID**: Enter your AWS cluster ID.
- **Cluster region**: Enter your AWS cluster region.
- **Database**: Enter the name of your Redshift database where RudderStack stores the data.
- **User**: Enter the user name with the required read/write access to the above database. See **Step 2** of [Setting user permissions in Redshift]({{< ref "destinations/warehouse-destinations/redshift.md#setting-user-permissions-in-redshift" >}}) for obtaining the value for the **User** field.
- **IAM role ARN**: Enter the ARN of the RudderStack IAM role.
- **Namespace**: Enter the schema name where RudderStack creates all the tables. If you don't specify any namespace, RudderStack sets its value as the source name, by default.

{{< warning >}}
You cannot change the namespace later.
{{< /warning >}}

{{% /tab %}}
{{% tab tabName="IAM Authentication toggled OFF" %}}
- **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.

{{< info >}}
See **Step 3** of [Setting user permissions in Redshift]({{< ref "destinations/warehouse-destinations/redshift.md#setting-user-permissions-in-redshift" >}}) for obtaining the **User** and **Password** values.
{{< /info >}}

- **Namespace**: Enter the schema name where RudderStack creates all the tables. If you don't specify any namespace, RudderStack sets its value as the source name, by default.

{{< warning >}}
You cannot change the namespace later.
{{< /warning >}}

{{% /tab %}}
{{< /tabs >}}

- **Use Redshift Serverless**: Turn on this setting to use [Redshift Serverless](https://docs.aws.amazon.com/redshift/latest/mgmt/working-with-serverless.html). If turned on, you will see the following setting that replaces the **Cluster ID** setting listed above:

  - **Workgroup name**: Enter your [Redshift Serverless workgroup name](https://docs.aws.amazon.com/redshift/latest/mgmt/serverless-console-workgroups.html#serverless-workgroup-describe).

### SSH connection settings

{{< info >}}
This feature is available only for the [Enterprise](https://www.rudderstack.com/enterprise-quote/) plan users.
{{< /info >}}

SSH tunneling is a method of transferring data over an encrypted SSH connection. You can use it to add encryption to your legacy applications and achieve compliance with regulations like HIPAA, PCI-DSS, etc., without having to modify the existing applications.

RudderStack lets you connect to your Redshift database securely over an SSH connection by configuring these settings:

{{< image src="images/warehouse-destinations/ssh-connection.webp" alt="Redshift edit configuration" >}}

- **SSH Connection**: Enable this setting to use the SSH connection while connecting to your Redshift database.
- **SSH Host**: Enter the IP address of your bastion host.
- **SSH Port**: Enter the port for the above host.
- **SSH User**: Enter the username you use to access the bastion host.
- **SSH Public Key**: Copy the public key provided in this field and add it to the `authorized_keys` file on your bastion host. Rudderstack will use the private key corresponding to this public key to establish the connection successully.

To enable the SSH connection for an existing Redshift destination, navigate to the destination's **Configuration** tab, select **Edit configuration** and enable the **SSH connection** setting.

### Sync settings

- **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 Redshift destination:

- **Use RudderStack-managed object storage**: Turn on this setting to use RudderStack-managed buckets for object storage.

{{< warning >}}
This option is applicable only for RudderStack-hosted data planes. For [self-hosted data planes]({{< ref "get-started/rudderstack-open-source/data-plane-setup/_index.md" >}}), you will have to specify your own object storage configuration settings.
{{< /warning >}}

If **Use RudderStack-managed object storage** is toggled off in the dashboard, you will see the following settings:

- **Staging S3 Storage Bucket Name**:  Enter your staging S3 bucket name. See [How RudderStack stores data in an object storage platform]({{< ref "destinations/warehouse-destinations/faq.md#how-does-rudderstack-store-data-in-an-object-storage-platform" >}}) for more information.
- **Prefix**: If specified, RudderStack creates a folder in the S3 bucket with this name and pushes all data within that folder. For example, `s3://<bucket_name>/<prefix>/`.
- **Role based authentication**: Turn on this toggle to use the [RudderStack IAM role]({{< ref "destinations/aws-iam-role-for-rudderstack/_index.md" >}}) for authentication.

{{< warning >}}
RudderStack recommends turning on this setting as the access keys-based authentication method is now deprecated.
{{< /warning >}}

You will see the following settings depending on whether **Role based authentication** is toggled on or off:

{{< tabs tabTotal="2" >}}
{{% tab tabName="Role based authentication toggled ON" %}}
- **IAM Role ARN**: Enter the ARN of the IAM role created by following [this guide]({{< ref "destinations/aws-iam-role-for-rudderstack/_index.md" >}}) and using the [S3 permissions]({{< ref "destinations/streaming-destinations/amazon-s3.md#s3-permissions-for-warehouse-destinations" >}}).
{{% /tab %}}
{{% tab tabName="Role based authentication toggled OFF" %}}

{{< danger >}}
AWS does not recommend access key credentials-based authentication. 
{{< /danger >}}

- **AWS Access Key ID**: Enter the AWS access key ID.
- **AWS Secret Access Key**: Enter the secret access key to authorize RudderStack to write to your S3 bucket.

For more information on obtaining these credentials, [set up an IAM user]({{< ref "destinations/streaming-destinations/amazon-s3.md#option-2-create-iam-user-and-provide-credentials" >}}) using the permissions listed [here]({{< ref "destinations/streaming-destinations/amazon-s3.md#s3-permissions-for-warehouse-destinations" >}}).
{{% /tab %}}
{{< /tabs >}}

- **Enable server-side encryption for S3**: Turn on this setting to enable server-side encryption for your S3 bucket.
- **Clean up object storage files after successful sync**: Turn on this toggle to delete the object storage files after the sync has completed successfully.

### Advanced settings

{{< customreadfile "/includes/warehouse-advanced-settings.md" >}}

## S3 permissions

As an alternative to providing AWS credentials for S3 access, set up permissions listed in this section to allow RudderStack to write to your S3 bucket.

### RudderStack-hosted data plane

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

```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:

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

2. Create an IAM user with programmatic access keys and attach the above created IAM policy. Copy the ARN of this user.
3. 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:

```json
{
  "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"
      ]
    }
  ]
}
```

4. Finally, add the programmatic access credentials to the environment of your data plane:

```bash
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](https://docs.aws.amazon.com/redshift/latest/dg/c_Compression_encodings.html). RudderStack explicitly sets the [runlength](https://docs.aws.amazon.com/redshift/latest/dg/c_Runlength_encoding.html) encoding for Boolean columns.

## FAQ 

#### How are reserved words handled by RudderStack?

There are some limitations when it comes to using [reserved words](http://docs.aws.amazon.com/redshift/latest/dg/r_pg_keywords.html) 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`'.

{{< info >}}
For a more comprehensive FAQ list, refer to the [Warehouse FAQ]({{< ref "destinations/warehouse-destinations/faq.md" >}}) guide.
{{< /info >}}