Data Warehouse

mParticle’s Data Warehouse integration with Amazon Redshift forwards all your incoming data to a Redshift cluster, allowing you to query the raw data directly.

The integration performs the following tasks in your Redshift cluster:

1. Creates a table for each custom event name and each eCommerce event name with a volume above a defined threshold. Less common events are recorded in a single table, labeled otherevents.
2. Adds new columns to tables when new events or user attributes are received.
3. Creates a view unioning all tables within each schema, and a view in the PUBLIC schema that unions the views of all schema. Updates these views as new tables are created.
4. If enabled, performs data hygeine on your cluster every 24 hours to purge expired data and run vacuum and analyze commands.

By default, the integration begins loading current data into Redshift from the time it is enabled. You can work with your mParticle Customer Service Manager to load historical data.

Enable the Integration

Redshift Database Setup

1. Create a Database and Schema

Create your Redshift Database. The database should be in the same region as your mParticle pod. For example, if your mParticle pod is US1 or US2, create your Redshift database in us_east-1. Choosing a different region increases latency between mParticle and Redshift.

Within your database, create a schema to store your data.

2. Create groups and users to manage permissions

Once your database is ready, you need to create groups with permissions to read and write data to the database.

create group readwritegroup; 
create group readonlygroup;

grant all on schema your_schema_name to group readwritegroup;

Be sure to replace your_schema_name with your own name.

mParticle uses these groups to grant permissions for new tables it creates in the database. You must use the names readwritegroup and readonlygroup. You can also use these groups to control permissions for mParticle data in Redshift.

Create a user in each group. mParticle uses the dataloader user to create tables and write to the schema. The readonlyuser is used to run queries from the mParticle dashboard.

create user dataloader in group readwritegroup password 'Aabcde123!';

create user readonlyuser in group readonlygroup password 'Abcde123!';

mParticle Setup

After adding Amazon Redshift from the integrations Directory, you can find the settings UI at Setup > Data Warehouse.

From the main page for your Redshift configuration, select the Settings tab to provide the necessary settings to get your Redshift integration working.

To forward data subject erasure requests to Amazon Redshift, set the Forwarding Status toggle to Active and select I understand after reading the disclaimer. Once the status has been set to Active, erasure requests are sent to Amazon Redshift immediately upon being received by mParticle.

Configuration Settings

Once your Data Warehose integration is configured, connect individual inputs to the Amazon Redshift output from the Connections page. You must connect every input you want to store data for.

Connection Settings

Data Schema

Each common custom event name and eCommerce event name have their own table in Redshift, and all other event names (e.g., session-start, session-end) are stored in a single “otherevents” table. The naming conversion of the table names are as follows.

• A custom event name will have a table named event_[event type]_[event name]. For example, a custom event of event type Navigation named SignUp will have a table named event_navigation_signup.
• An eCommerce event name will have a table named ecomm_[event name].
• Consent events have tables named consent_given and consent_rejected
• All other events are stored in a table named otherevents.

mParticle also creates two views, which can be used to get aggregated views of your app data.

• Under each schema, a view called eventsview that unions all tables under the schema.
• Under the “public” schema, a view called eventsview that unions all eventsview views from all schema in the database.

Common Columns

Each table has the following common columns.

eCommerce Only Columns

Event and User Attribute Columns

Each individual event name table (not including otherevents) also has one column per event attribute, named like: “ea[attribute_name]”, and one column per user attribute, named like “ua[attribute_name]“.

The benefit of these individual attribute columns is that you don’t have to use slower JSON parse functions in your query to extract attribute values from either EventAttributes or UserAttributes columns. If you wish, individual User Attribute columns can be turned off by disabling the Send user attribute columns setting.

Partner Feed Data

Events from each connected Partner Feed will be stored under a single table unless the Split Partner Feed Data by Event Name checkbox is enabled. You can choose the table name for each Feed in the Connection Settings.

Device Application Stamp Forwarding

Events can be forwarded with a Device Application Stamp stored in the device ID column. You can enable this in the settings page for your data warehouse configuration by toggling the Store Device Stamp checkbox.

Error Handling

mParticle loads data into Redshift via Amazon S3 and can tolerate the Redshift cluster being unavailable for up to 30 days, depending on data volume. In the event of extended downtime on your cluster, a data replay can be arranged.