Introduction

Amazon Redshift is a fast and powerful, fully managed, petabyte-scale data warehouse service that forms part of the larger cloud-computing platform Amazon Web Services.

MoEngage <> Redshift

The MoEngage and Redshift integration exports Campaign Interaction Events data directly to your Amazon Redshift cluster.

-- create a new user with a strong password
CREATE USER <username> PASSWORD <password>;
-- create a new schema where MoEngage will dump data
-- you can skip this if you want to use existing schema
CREATE SCHEMA IF NOT EXISTS <schema_name>;
-- grant create access on the above schema or existing schema
GRANT CREATE, USAGE ON SCHEMA <schema_name> TO <username>;

Step 3: Allow MoEngage IPs to access your cluster

You can either choose to create a new security group or edit your existing one.

Create a new Security Group

From your EC2 Console, under "Network & Security", open the Security Groups page. Click on the "Create security group" button.

1. Give your Security Group a name, and an optional description.
2. Select the VPC (by default, it is already selected).
3. Add a new inbound rule with the following details:

The correct IP address to enter in the "source" field depends on your MoEngage Data Center region:

You only need to add one inbound rule. For example, if you're based in MoEngage's DC-01 servers, you need to add just the first CIDR block:

4. Leave the outbound rules empty.
5. Click on "Create security group" when done.

Edit an existing Security Group

From your EC2 Console, under "Network & Security", open the Security Groups page. From the list of your Security Groups, open the one you want to edit.

1. Click on the "Actions" button on the security group page and select "Edit inbound rules".
2. Add a new inbound rule according to the table provided above.
3. Click "Save rules" when done.

Attach the Security Group to your Redshift Cluster

1. From your Redshift Console, open your Redshift cluster. Go to the Properties tab.
2. In the "Network & Security settings" section, click on the "Edit" button.
3. Under the VPC security groups, select the Security Group you just created/edited.
4. Click "Save changes" when done.

Step 4: Create a new IAM Policy

You need to create a new IAM Policy that will allow us to transfer data from S3 to your Redshift cluster.

1. From the IAM Console, under Access Management, go to Policies. Click on the "Create policy" button.
2. On the Create Policy page, switch to the JSON tab and paste the following policy:
   
   JSON

   {
       "Version": "2012-10-17",
       "Statement": [
           {
               "Sid": "VisualEditor1",
               "Effect": "Allow",
               "Action": [
                   "s3:Get*",
                   "s3:List*"
               ],
               "Resource": [ "<bucket-arn>",
                             "<bucket-arn>/*"]
           }
       ]
   }

   The value of <bucket-arn> will change based on your MoEngage Data Center Region:
   

   Data Center Region	<bucket-arn>	Resource Array
   DC-01	arn:aws:s3:::moe-warehouse-exports-us-east-1	"Resource": ["arn:aws:s3:::moe-warehouse-exports-us-east-1", "arn:aws:s3:::moe-warehouse-exports-us-east-1/*"]
   DC-02	arn:aws:s3:::moe-warehouse-exports-eu-central-1	"Resource": ["arn:aws:s3:::moe-warehouse-exports-eu-central-1", "arn:aws:s3:::moe-warehouse-exports-eu-central-1/*"]
   DC-03	arn:aws:s3:::moe-warehouse-exports-ap-south-1	"Resource": ["arn:aws:s3:::moe-warehouse-exports-ap-south-1", "arn:aws:s3:::moe-warehouse-exports-ap-south-1/*"]
   DC-04	arn:aws:s3:::moe-warehouse-exports-us-east-2	"Resource": ["arn:aws:s3:::moe-warehouse-exports-us-east-2", "arn:aws:s3:::moe-warehouse-exports-us-east-2/*"]

   Just replace the <bucket-arn> with the one corresponding to your MoEngage Data Center region.
   
   
   Click "Next" and optionally, add tags. Then click on "Next: Review".
3. Give the Policy a name, and an optional description. Click "Create policy" when ready.

Step 5: Create a new Redshift IAM role

This role will allow our servers to write data to your Redshift clusters.

1. From the IAM Console, under Access Management, go to Roles. Click on the "Create role" button.
2. Choose the Trusted entity type as "AWS service". Under Use Case, select "Redshift" from "Use cases for other AWS services". Under the types of Redshift use cases, select "Redshift - Customizable". Click "Next" when ready.
   
3. In the next step, select the policy that you created in Step 4. Click Next when done.
   
4. Give your role a name, and review if you have attached the correct policy that you made earlier. Click on Create Role when ready.
   

Step 6: Attach this role to your Redshift cluster

From the Redshift Console, open your Redshift cluster.

1. Under the Properties tab, go to the "Associated IAM roles" section.
2. Click on the "Manage IAM roles" button, and then select the "Associate IAM roles" option.
3. From the popup modal, select the Role that you just created in Step 5.

4. Click on "Associate IAM roles" to attach this role to your Redshift cluster.

Step 7: Enable the Redshift Integration on the MoEngage App Marketplace

1. On your MoEngage Dashboard, go to the App Marketplace.
2. Search for "Redshift".
3. Go to the "Integrate" tab, and click on "+ Add Integration".

4. Fill out the connection details of your Redshift cluster.

5. Optionally, you can choose to test the connection. MoEngage will attempt to verify if the details you entered are correct. If there is an issue, an error will be shown that you can rectify.
6. Once the test connection is successful, you can save the connection details and MoEngage will begin exporting events shortly to your Redshift cluster.

Verify if you are receiving the data correctly

MoEngage will create the following two tables in your schema:

1. test_connection_moengage - We will use this table to verify if your connection details are valid from time to time.
2. moengage_events - We will dump (append) all your events in this table.

You can verify if data is correctly flowing in your warehouse by querying moengage_events table:

Sample Table Structure

The final table definition will depend on the exact configurations of your exports.

Export Frequency

We will dump the data at hourly intervals.