AWS Redshift Integration: Define Your Own Schema

The Redshift integration with Analytics is available for Enterprise customers only. If interested, please contact us. You are required to grant Redshift access to Analytics by creating a standalone VPC Security Group, attaching the Security Group to your Redshift Cluster, and creating a read-only Redshift account for Analytics to use.

If there are additional enrichments required, such as joining with user property tables or deriving custom user_ids, please contact us.

Instructions

In order to perform the following steps you must have administrative access to the AWS Console as well as your Redshift database.

Start In Analytics

1. In Analytics, click on the gear icon and select Project Settings. 

2. Select the Data Sources tab. 

3. Select New Data Source.

4. Select Amazon Redshift and click Connect

Connection Information

1. The following fields can be found by accessing your AWS Console.
   *Note: The Auto-Generated password is a password that Analytics has randomly generated. If you prefer to use your own password value, please replace the value in that field to your preferred password.
2. Enter the Full JDBC URL by accessing your AWS console and selecting Clusters on the left side navigation. Click on the name of your Redshift Cluster to get this detailed view.
   
3. Enter the Schema. Click here for addition information about schemas
4. Enter the Table Name that contains your event data.
5. You can use the default Analytics Username, or choose to modify that to whatever fits your naming convention.
6. Copy the Auto-Generated Password or create your own. This will be the Analytics user password that you will use later on.
7. Click Next. 

Create Security Group

The next step is to grant access to your Redshift cluster when accessed from Indicative's IP addresses. This involves creating a new Security Group in your VPC.

1. Go to https://console.aws.amazon.com/vpc/home

2. Click Security Groups on the left side

3. Click Create Security Group

4. Fill out Name tag and copy the Group name and Description from Analytics.  Make sure you choose the VPC that also includes your Redshift cluster. Click “Yes, Create” to continue.  We recommend using the values below.

5. Add Analytics' IP addresses to the newly created Security Group

a. Select your newly created Security Group

b. Click the Inbound Rules tab

c. Click the Edit button

d. Add the following three rules to allow access to your Redshift cluster to Analytics' IPs:

• Type: Redshift; Source: 54.227.242.108/32
• Type: Redshift; Source: 104.196.66.86/32
• Type: Redshift; Source: 35.227.102.123/32
• Type: Redshift; Source: 35.227.125.106/32

Prepare Redshift Cluster

1. Go to https://console.aws.amazon.com/redshift

2. Click the name of your Redshift cluster

3. Go to Cluster > Modify

4. Select the Indicative security group, in addition to your existing security groups.

5. Set "Publicly accessible" to "Yes".

6. Select an Elastic IP from the list.

7. When complete, your cluster status should look like this:

Grant Permissions

1. Connect to your Redshift cluster using your existing administrator username and password

2. Create a new user for Analytics

a. Copy the Create a new user for Analytics command from the Analytics screen and execute the SQL command.

3. Grant access to the Analytics user. 

a. For your Redshift schema, copy the For your Redshift Schema command from Analytics and execute the SQL command.

b. For the tables in your schema you'd like Analytics to access copy the Grant Specific Tables command from Analytics and execute the SQL command.

c. Alternatively to grant access to all tables in a schema, copy the Grant All Tables command from Analytics and execute the SQL command.

Event Modeling

1. Events Field - enter the name of the field that should be used to derive your Analytics event names
2. Timestamp - enter the name of the field that should be used for querying in Analytics.
3. Click Next

User Modeling

For more information on User Identification (Aliasing), please refer to this article.

1. If you choose to enable Aliasing:
   1. Unauthenticated ID - Input the field used to identify anonymous users.
   2. Authenticated ID - Input the field used to identify known users.
2. I you choose to disable Aliasing, press Disabled:
   1. Unauthenticated ID - Enter the field used to identify your users. All users must have a value for this field.
3.  Press Next

Scheduling

1. Select the Schedule Interval to adjust the frequency at which new data is available in Analytics.
2. Set the Schedule Time for when the data should be extracted from your BigQuery environment. It is critical that 100% of the data is available by this time to avoid loading partial data.
3. Select Save

Waiting for Data

Once you see this screen, you're all set! A Solutions Engineer will begin preparing your data, and you should be notified via email when ready.

Advanced Settings

For additional advanced settings such as excluding certain events and properties, please refer to this page

If you have any questions or concerns about the above Integration, please contact your Customer Support Manager, or email support@mparticle.com.