Amazon Redshift is a fast, fully managed, petabyte-scale data warehouse hosted by AWS. Configure Amazon Redshift as one of ChartMogul’s advanced destinations to export MRR movements, customers, custom attributes, and tags to Amazon Redshift tables. From there, you can create your own views and combine datasets for further analysis.

Here's what we cover in this article:

• Creating a Redshift cluster
• Creating a security group
• Setting up Cluster network access
• Creating a Redshift user and database
• Creating an S3 Bucket
• Creating an IAM policy
• Creating an AWS IAM user
• Adding Amazon Redshift as a destination

Resources and further reading:

• What is Amazon Redshift?
• Getting started with Amazon Redshift
• Understanding MRR Movements
• Custom Attributes
• Tags
• Developer resources: Destinations and Datasets in Destinations

Before you begin

Please review the following details before completing the steps outlined in this article.

• When creating a cluster, you'll need to choose the type and number of nodes you'll need to meet your ​CPU, RAM, storage capacity, and storage drive type requirements. Read more about clusters and nodes in Amazon Redshift.
• ChartMogul uses an S3 bucket (that you'll set up) as a staging area to send data to Amazon Redshift. ChartMogul first uploads data using the parquet data storage format to the bucket, then moves the data into Amazon Redshift tables. ChartMogul refreshes (overwrites) the data in these tables as part of its daily sync.
• If you have already configured Amazon S3 as a destination, we recommend creating a new S3 bucket for your Amazon Redshift destination.
• You'll need to select an AWS Region where you'd like Amazon to host your Amazon Redshift cluster and store your S3 bucket.
• You'll need to choose a name for the S3 bucket. Please review Amazon's bucket naming rules as part of this.

Step 1 — Create a Redshift cluster

1. Within Amazon Redshift, navigate to Clusters > Create cluster.
   
2. Within Cluster configuration, enter a name for the new cluster, e.g., chartmogul_cluster, in the Cluster identifier field.
3. Select the type and enter the number of nodes you need using the Node type drop-down and Number of nodes field.
4. Within Database configurations, enter your Redshift administrator username and password in the Admin user name and Admin user password fields.
5. Finally, click Create cluster.
6. Within the cluster, navigate to Properties > Database configurations > Port and note the port value.

Step 2 — Create a security group

We recommend creating a security group to limit access to your Amazon Redshift cluster to ChartMogul IP addresses:

1. Within the Amazon EC2 console, navigate to Network & Security > Security Groups.
2. Click Create security group.
3. Within Basic details, enter a name (e.g., ChartMogul) in the Security group name field and a short description in the Description field.
4. Within Inbound rules, click Add rule. Select Custom TCP from the Type drop-down. Enter the port value you previously noted for your cluster in the Port range field. Select Custom from the Source drop-down. Finally, enter 3.248.174.47 in the field immediately following the Source drop-down.
5. Repeat the previous step for the following IP addresses: 99.80.87.208 and 34.248.167.65.
   
6. Complete any additional setup option you may need, then click Create security group.

Step 3 — Set up Cluster network access

1. Navigate to Amazon Redshift > Clusters > select your cluster > Properties > Network and security settings > Edit.
2. Select the security group you created in the previous step from the VPC security groups drop-down and click Save changes.
3. Then, ensure the cluster is publicly available by navigating to Actions > Modify publicly accessible setting. The, select Enable > Save settings.

Step 4 — Create a Redshift user and database

Next, create a Redshift user and database that ChartMogul will use to access and save data to Redshift. It is recommended you create a new database and new user for ChartMogul. To create a new database:

1. Navigate to Amazon Redshift > Clusters > Query editor v2.
2. Add the following code, replacing <USERNAME>, <DATABASE> and <PASSWORD> with appropriate values.

   CREATE USER <USERNAME> PASSWORD '<PASSWORD>';
     CREATE DATABASE <DATABASE>;
     GRANT CREATE ON DATABASE <DATABASE> TO <USERNAME>;

3. Finally, highlight the three lines of code and click Run.

ChartMogul will create a schema and tables in the database you specified with your data. If configured correctly, your Redshift cluster will be accessible by ChartMogul using the Redshift user you specified.

Step 5 — Create an S3 bucket for ChartMogul

Next, create a new Amazon S3 bucket:

1. Within S3, navigate to Buckets > Create bucket.
   
2. Enter the name you've chosen for your S3 bucket in the Bucket name field.
3. Select the AWS Region you've chosen from the AWS Region drop-down menu.
4. Complete any additional setup option you may need, then click Create bucket.

Step 6 — Create an IAM policy

Next, create an AWS Identity and Access Management (IAM) policy that allows ChartMogul to access the S3 bucket you just created:

1. Within IAM, navigate to Policies > Create policy.
2. Click the JSON tab and add the following code, replacing <BUCKET_NAME> with the name you used to create the bucket in the previous step. Then click Next: Tags.

   {
      "Version": "2012-10-17",
      "Statement": [
          {
              "Effect": "Allow",
              "Action": [
                  "s3:GetObject",
                  "s3:PutObject",
                  "s3:PutObjectAcl",
                  "s3:ListBucket",
                  "s3:DeleteObjectVersion",
                  "s3:DeleteObject",
                  "s3:GetObjectVersion"
              ],
              "Resource": [
                  "arn:aws:s3:::<BUCKET_NAME>/*",
                  "arn:aws:s3:::<BUCKET_NAME>"
              ]
          }
      ]
   }

3. Finally, give your policy a name, e.g., ChartMogul, and click Create policy.

Step 7 — Create an AWS IAM user

Now, create an AWS Identity and Access Management (IAM) user that will have permission to access the S3 bucket. After creating the user, they'll have an Access key ID and a Secret access key, which you'll use to create the destination in ChartMogul.

1. Within IAM, go to Access management > Users > Add users
   
2. Enter a name for the user, e.g., ChartMogul, in the User name field.
   
3. Select Access key - Programmatic access as the AWS credential type.
   
4. Under Set permissions, click Attach existing policies directly, then search for the IAM policy you created in the previous step using its name and tick the box to select it. Finally, click Next.
   
5. Add any tags you'd like to the user, then click Next: Review.
6. If everything looks good, click Create user.
   

Step 8 — Add Amazon Redshift as a destination

Finally, create a new destination for Amazon Redshift:

1. With ChartMogul, navigate to Settings & Data > Destinations.
2. Click Add Destination and select Amazon Redshift.
3. Enter a unique name for your destination in the Name field, then click Next.
4. Choose how often you'd like ChartMogul to export data to your Amazon Redshift cluster, either:
   • Daily (09:00 UTC each day),
   • Weekly (09:00 UTC each Sunday), or
   • Monthly (09:00 UTC on the first day of each month).
5. Choose which datasets you'd like ChartMogul to send:
   • Source Data
     • Contact Custom Attributes
     • Contacts
     • Currency Rates
     • Custom Attributes
     • Customers
     • Notes and Call Logs
     • Invoice Line Items
     • Invoices
     • Plans
     • Opportunities
     • Opportunity Custom Attributes
       
     • Subscription Events
     • Tags
     • Tasks
     • Transactions
     • Data Sources
   • Calculated Data
     • Customers
     • MRR Movements
   
6. Within Configure Redshift:
   • Host: Navigate to Amazon Redshift > Clusters > and copy the Endpoint. Paste in the endpoint URL, removing the port and database name. For example, if the endpoint is:
     

     your-destination.mjg5k6epogid.eu-west-1.redshift.amazonaws.com:2445/dev

     The endpoint URL would be:

     your-destination.mjg5k6epogid.eu-west-1.redshift.amazonaws.com

   • Port: The port of your Redshift cluster you noted in Step 2.
   • Username: The username you set in Step 4.
   • Password: The password you set in Step 4.
   • Database: The name of the database you created in Step 4.
   • Schema Prefix (Optional): If your organization has a schema naming convention, add it here. If left blank, ChartMogul creates a schema on your behalf.
7. Complete the following fields for Configure S3 Storage:
   • Bucket Name: The name of your bucket created in Step 5.
   • AWS Region: The region of your bucket selected in Step 5.
   • Access Key ID: The Access key ID from Step 7.
8. Click Save and Test Connection.

ChartMogul will perform a test connection and, if successful, add Redshift as a new destination.