Configuring Amazon Redshift as a destination

You'll need to be an Admin in ChartMogul and Amazon AWS to complete the steps outlined in this article. Read more about user roles and permissions.

Amazon Redshift is a fast, fully managed, petabyte-scale data warehouse hosted by AWS. ChartMogul allows you 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:

Resources and further reading:

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 creates its own schema chartmogul_data under the Amazon Redshift database you'll create.
  • 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.
    Redshift_CreateCluster.png
  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.
Note the port value as you'll need it in the following steps.

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.
    Redshift_InboundRules.png
  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.
Note the Bucket name and AWS Region, as you'll need these in the final step.

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"
                ],
                "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.
    Screenshot
    of selecting an AWS credential type with two options: Access Key - Programmatic
    access and Password - AWS Management Console access
  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.
Note the Access key ID and Secret access key, as you'll need these in the final step. Alternatively, click Download.csv to save this information.

Step 8 — Add Amazon Redshift as a destination

Finally, create a new destination for Amazon Redshift:

  1. With ChartMogul, navigate to Data Platform > Data Output > Destinations.
  2. Click ADD NEW 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:
    • Activities,
    • Customers,
    • Tags, or
    • Custom attributes.
  6. Within Redshift configuration:
    • 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.
  7. Complete the following fields for S3 Storage configuration:
    • 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.

Was this article helpful?