With our Relational Data Service you can connect your own client-hosted, (external) databases to Emarsys and use the data fields contained in them to create segments or populate personalization variables. You can create multiple RDS connections even to the same database.
You can also upload your business-specific, relational data to Emarsys-hosted databases. For information, see Relational Data onboarding for clients using Emarsys-hosted databases.
For general Relational Data information, see: Relational Data - Overview.
Contents
Preparing your database
-
Tables and views
When you connect an external database to Emarsys, we will automatically pull all the tables and views from the database and display them on the Relational Data page, Tables & Views tab. Tables and views are displayed only, if the schema is defined with theCurrentSchemaconnection parameter. If it's omitted, the default schema is public. Ensure that your schema name is set correctly. For details, see Amazon Redshift.
Each table or view can only be used with one set of reference fields for personalization (which are linked by AND) therefore you need to create as many views as you will need in your database before you start. -
Duplicates
Duplicate contacts are rejected by our connectors, so you will need to deduplicate your database before you start. -
Timezones
Make sure that in your database you specify the timestamp and date/time with the timezone, otherwise Emarsys applies CET (Europe/Vienna) timezone. You cannot define it as a Connection Parameter.
Connection setup
Before you start, note the following restrictions:
- The supported mySQL versions are: 5.6, 5.7 and 8.0.
- With Redshift connection, there is no need for a CA certificate.
- In order for us to have full access to your database, you need to whitelist our IP: 185.4.123.112.
The first step in using Relational Data is to set up the connection between your database and Emarsys. To do this, go to Add-ons -> Relational Data -> Connections tab.
In the top right corner of the Connections tab, click the three dots and select the type of connection you would like to set up.
The following connection types are available:
Some of the connection types require a CA certificate. Emarsys accept these certificates in Base64 (ASCII) encoded PEM format, only. Read more about CA certificates here.
Besides the format of your CA certificate Emarsys validates the server certificate against the CA certificate and checks the expiration date of both as well.
We can only establish TLS connections with password authentication, furthermore, it is recommended to use strong passwords and change them frequently. Moreover, we can only establish the connection from a fixed IP address (185.4.123.112), therefore, we can ensure proper security measures on a network level too if the password is secure enough.
MySQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your MySQL database, the following roles must be assigned to the database user you share with Emarsys:
-
SELECT,SHOW VIEW
If you want to use MyAdmin, you should additionally assign the following roles to the database user you share with Emarsys:
-
ALTER,CREATE,CREATE VIEW,DELETE,DROP,INSERT,INDEX,UPDATE
MyAdmin
The edit and delete options are only available with connections where the database is not hosted by Emarsys.
The information and controls on the MyAdmin page are maintained by phpMyAdmin, not Emarsys.
Amazon Redshift
You can create a single or even multiple RDS connections by adding your Amazon Redshift schema.
Define the schema name in the Connection parameters field, as follows: currentschema=<schema_name>. For public schemas, leave the field blank.
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Amazon Redshift database, the following role should be assigned to the database user you share with Emarsys:
SELECT
PostgreSQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your PostgreSQL database, the following role should be assigned to the database user you share with Emarsys:
SELECT
Google BigQuery
The Google BigQuery database connector can be used to pull data into Emarsys for segmentation purposes only. It can not be used for personalization fields.
This is because BigQuery is optimized for massive datasets and its queries take 2-5 seconds, regardless of the size of the dataset. Using it for personalization queries would have a negative impact on email sending performance.
Make sure you fill in all fields marked with an asterisk. You can also import all the information in a JSON file directly from your Google Cloud Console. For detailed instructions on how to do this, click here.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Google BigQuery database, the following roles should be assigned to the database user you share with Emarsys:
-
BigQuery Data Viewer,BigQuery Job User
Microsoft SQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Microsoft SQL database, the following role should be assigned to the Emarsys user:
-
DELETE,INSERT,SELECT,UPDATE,VIEW DEFINITION
Azure SQL
Make sure you fill in all fields marked with an asterisk.
When you have added all necessary data, you can Test your connection and if all works, click Save to create your connector.
In your Azure SQL database, the following role should be assigned to the Emarsys user:
-
DELETE,INSERT,SELECT,UPDATE,VIEW DEFINITION
Reference fields
Reference fields are the means by which you determine which records are made available for personalization.
When you come to define a personalization variable in your message content, you will be asked to provide the values for these fields. The fields and values you provide will act as filter criteria (linked by AND) and will link to the data records that you can then use to personalize the content.
Select the desired reference fields from the drop-down, add a default value if needed, and click Save when finished.
The default value will be used if the reference field contains no value in a given record.
There is no need for a default value for unique value fields such as ID, order, price, etc. However, defining a default value might be helpful if you need a fallback value, for example when you have language settings.
The fields here only filter the database records. For the actual personalization variable, you can include any field contained in the record.
When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.