Data Warehouse

The mParticle integration with Databricks allows you to forward your data from mParticle to Databricks. Databricks is a Delta Lake platform built on Apache Spark and facilitates both distributed data storage and computation. When connected to Databricks, arbitrary work, and SQL queries can be scheduled to run against a configured Compute Cluster or SQL Warehouse, whose capacity can be tailored according to your needs.

Prerequisites

Before setting up the Databricks integration within mParticle, you must configure the following within your Databricks account:

• A SQL Warehouse to run future queries.

• A dedicated Service Principal to allow mParticle to upload data to your Databricks catalog.

  • When creating a Service Principle, you will create a Client ID / Client Secret pair. These credentials will be used by mParticle to generate OAuth access tokens to upload data to your catalog on your behalf.
  • When creating your new service principal, make sure to grant it access to the Catalog or Schema you create for this integration. If not, the access token that mParticle generates on behalf of the Service Principal won’t allow access to your Databricks resources.
• A Catalog and Schema within your Databricks workspace.

1. Create a SQL Warehouse

A SQL Warehouse within Databricks is a computational resource that allows you to run SQL queries on your data. You will need to create a SQL Warehouse that mParticle can use when forwarding your data.

To create a new SQL warehouse:

1. Log into your Databricks account.
2. Under SQL in the left hand nav, select SQL Warehouse.
3. Click Create SQL warehouse.

4. Enter a meaningful name for your new warehouse, and select a Cluster size appropriate for your organization’s needs.
5. Under Auto stop, enter a time duration appropriate for your organization. Note that “cold” cluster start-up times can reach several minutes, which may impact how efficiently mParticle is able to forward data to Databricks.
6. Under Type, select Serverless.
7. Click Create.

To learn more about SQL warehouses and their configuration settings, visit the Databricks documentation: Create a SQL warehouse.

2. Create a new Service Principal

A service principal in Databricks is an API-only identity used to grant automated tools and applications, like mParticle, secure access to your data catalogs. mParticle will use the service principal you create to authenticate itself when forwarding your data to Databricks.

To create a new Service Principal:

1. Navigate to Settings, and select Identity and access under Workspace admin.

2. Click Manage under Service principals.
3. Click Add service principal.
4. Make sure to enable Databricks SQL access and Workspace access under Entitlements. You can enable these for an existing service principal by navigating to the Configurations tab when viewing the service principal and enabling both settings before clicking Update.

5. After creating your new service principal, go to the Secrets tab on the Service principal details page, and click Generate secret. Save your Client ID and Client Secret, as you will need to enter them when configuring your connection to Databricks in mParticle.

To learn more about Service Principals, visit the Databricks documentation: Manage service principals.

3. Create a new Catalog

All data in Databricks is organized within Catalogs. Catalogs contain Schemas that define the structure of your data, and tables that contain the data itself.

To create a new catalog:

1. Navigate to Catalog in the left hand nav of your Databricks workspace.
2. Click Create Catalog.
3. Under Type, select Standard, or whichever type best suits your organization’s use case. The mParticle Databricks integration supports all catalog types.
4. Click Create.
5. After creating and opening your catalog, go to the Permissions tab.

6. Click Grant, enter the service principal you created in 2 Create a new Service Principal under Principals, and enable the following privileges to ensure mParticle can generate the necessary tables in your catalog:

   • USE CATALOG
   • USE SCHEMA
   • READ VOLUME
   • WRITE VOLUME
   • CREATE TABLE
   • CREATE VOLUME

You can learn more about catalogs in the Databricks documentation: What are catalogs in Databricks?

4. Create a new Schema

Within the Databricks data hierarchy, a schema is a subcomponent of a catalog that defines in more granularity how your data is organized and structured.

To create a new schema:

1. Navigate to Catalog in the left hand nav of your Databricks workspace.
2. Select the catalog you created in the previous step, and click Create schema.
3. Enter a meaningful name and description before clicking Create.
4. Since you already granted read/write privileges to your service principal for the catalog containing this schema, you don’t need to grant it privileges here. However, depending on how your organization has structured your Databricks workspace, you may choose instead to set these privileges at the schema scope instead of the catalog scope.

It is also possible to create a new schema using the Databricks SQL Editor.

Configure your Databricks integration in mParticle

1. Create an outbound configuration for Databricks

To create an outbound configuration for Databricks within mParticle:

1. Log into your mParticle account.
2. From the Overview Map in the new UI, click Add under Outputs. If you’re using the Classic UI, navigate to Setup > Outputs in the left hand nav bar.
3. Go to the Data Warehouse tab and use the Add Data Warehouse dropdown menu to add a new Databricks configuration.

4. Hover over Databricks and click Configure.

5. Enter a unique Configuration name, and check Use same settings for Development & Production, if you want to use the same configuration settings for both your development and production data.
6. Click Save.
7. You will be taken to the Settings tab for your new configuration. Under Databricks Parameters, enter the following:

• Deployment Name: The Databricks deployment name of the workspace containing the SQL Warehouse, Service Principal, and Catalog/Schema you created in the Prerequisites section.

  • If you log into your Databricks account, the URL in your browser will resemble https://<deployment-name>.databricks.com, where <deployment-name> is the deployment name.
• Warehouse ID: The ID of the SQL Warehouse you created in 1 Create a SQL Warehouse.
• Catalog Name: The name of the Catalog you created in 3 Create a new Catalog.
• Schema Name: The name of the Schema you created in 4 Create a new Schema.
• Client ID: The Client ID you generated for your service principal in 2 Create a new Service Principal.
• Client Secret: The Client Secret you generated for your service principal in 2 Create a new Service Principal.

• Event Stats Threshold: the number of events that must be reached before mParticle begins adding additional events to their own dedicated table.

  • Until this threshold is reached, all events will be uploaded to a common table.

2. Create a connection with your new Databricks output

1. From your mParticle account, navigate to Connections > Connect.
2. Select one of your configured inputs that you want to forward data to Databricks from
3. Click Connect Output, and select Databricks from your list of configured outputs.
4. Set Connection Status to Active or Inactive. You can always activate a connection later after completing the configuration, but only active connections will forward data.
5. If you enable Send Batches without Events, mParticle will forward all batches to Databricks, even if they only contain user data with no event data.

6. For feed connections only: If you enable Split Partner Feed Data by Event Name, mParticle will separate partner feed data by each unique event name. If you leave this setting disabled, mParticle will place all data from a single partner feed into a single table.

   • This setting only applies to partner feed data. If you create a connection to Databricks using one of the other platform inputs, mParticle will place data from that input into a single table.

7. For feed connections only: Enter an optional, custom name for the table mParticle will add your data to.

   • If left blank, mParticle will use the name of the partner.
   • This setting is ignored if Split Partner Feed Data by Event Name is enabled.
8. Click Add Connection.

Data mapping

All Databricks tables that mParticle generates are created within the schema you created in step 4 of the prerequisites. Databricks refers to databases and schemas interchangeably. The schema you create when configuring this integration serves as the main database that will contain the actual tables of data forwarded from mParticle. For more information, read about schemas in the Databricks documentation.

When mParticle adds data to a table in your Databricks schema, all the main objects and fields listed in the mParticle JSON schema are automatically mapped to objects and fields within Databricks. This includes complex objects or collections, allowing you to forward any event data from mParticle to Databricks.

For example, see the following sample CommerceEvent with a ProductAction field after it has been forwarded to Databricks:

The same associated event data that was available in mParticle is queryable within Databricks.

What tables will my data be added to?

When determining which table a given event will be added to in Databricks, mParticle employs a cache that tracks all events forwarded to Databricks in the given workspace within the last 30 days.

The Event Stats Threshold configuration setting is cross-referenced with this cache to determine how many events must be forwarded to Databricks before mParticle begins adding them to their own, dedicated table.

If a given event type’s frequency exceeds the configured Event Stats Threshold, then those events will start to be uploaded to their own dedicated table. Until the threshold is reached, events are uploaded to the common table with the name [your-schema-name]_otherevents table.

Partner Feed connection settings

The only exception to how mParticle adds your data to Databricks tables pertains to Feed connections. There are two special settings for Databricks Feed connections that can influence the tables events are added to.

Databricks Table Name

When creating a Databricks connection, you can specify a name for a table that mParticle will create to store your feed data in. If you leave this setting blank, mParticle creates a table with the name set to the partner feed name. This setting is only applicable to feed inputs.

If Split Partner Feed Data by Event Name is enabled, this setting is ignored.

Split Partner Feed Data by Event Name

When creating a Databricks connection, you can enable a setting called Split Partner Feed Data by Event Name. If enabled, then mParticle will create a separate table for each unique event name forwarded to Databricks. If this setting is disabled, then all Partner feed data is added to a single table.

Data forwarding

When forwarding event data to Databricks, mParticle generates an OAuth access token using the Service Principal credentials you set up in 2 Create a new Service Principal. After authenticating to Databricks with the OAuth access token, mParticle creates a new Unity Catalog Volume called mparticle_staging under the schema you set up in 4 Create a new schema. This Unity Catalog Volume acts as a staging area for your data before it’s ultimately loaded into the appropriate Databricks table.

All data ingested into mParticle that is to be forwarded to Databricks is written to parquet files, which are uploaded to the staging Unity Catalog Volume in Databricks. mParticle then automatically issues the necessary commands to load the parquet data into the respective tables, as well as clean-up any previously-loaded files.

Upload frequency

mParticle forwards data to Databricks in bulk. By default, uploads occur every 90 minutes or until 100,000 messages have accumulated in the upload queue, whichever comes first.

Accessing your data in Databricks

Once data has been loaded into a given table in your Databricks workspace, it can be easily queried using standard SQL syntax. This can be accomplished from within Databricks’ SQL Editor.

Settings reference

Configuration settings

Setting name	Type	Required?	Encrypted?	Default setting	Description
Deployment Name	string	yes	no	null	The databricks deployment that’s associated with the given Service Principal and SQL Warehouse. For example: if your Server Hostname is 1234.cloud.databricks.com, the Deployment Name that you should enter would be 1234.
Warehouse ID	string	yes	no	null	The SQL Warehouse ID upon which to execute SQL statements.
Service Principal Client ID	string	yes	no	null	The dedicated Service Principal’s Client ID, which will be used to generate OAuth Access Tokens to facilitate future uploads.
Service Principal Client Secret	string	yes	yes	null	The dedicated Service Principal’s Client Secret, which will be used to generate OAuth Access Tokens to facilitate future uploads.
Catalog Name	string	yes	no	null	The default catalog for statement execution.
Schema Name	string	yes	no	null	The default schema for statement execution.
Events Threshold	int	yes	no	10000	The threshold to determine the number of events that need to be seen before we start forwarding them to their own, dedicated table. Until this threshold is reached, events will be uploaded to a common table.

Connection settings

Setting name	Type	Required	Default	Input	Description
Databricks Table Name	string	no	null	Feed	Table name for this partner feed. If not set, the partner name will be used. Only applicable to feeds inputs, no effect on apps inputs. If “Split Partner Feed Data by Event Name” checkbox is enabled, this setting is not used.
Split Partner Feed Data by Event Name	boolean	no	false	Feed	If enabled, split partner feed data by event name. Otherwise load data into the same table.
Send Batches without Events	boolean	no	true	All	If enabled, an event batch that contains no events will be forwarded.