• Deutsch Español Français Русский Türkçe 简体中文
    1. Emarsys
    2. Getting Started
    3. Data Onboarding

    Relational Data onboarding for clients using client-hosted databases
    Updated

    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 the CurrentSchema connection 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.

    Securing connections: TLS/SSL

    Emarsys supports server-side Transport Layer Security (TLS), successor of the Secure Sockets Layer (SSL) protocol. Server-side SSL is a requirement so Emarsys can guarantee secure server connection and provide encrypted communication. This is the same mechanism that is used to secure HTTPS traffic for websites all over the Internet. A trusted party “signs” the certificate of a server, ensuring authenticity and encryption. Trusted parties are identified by root certificates. For brief information on root certificates, click here.

    Emarsys validates the server certificate against the certificate authority (CA) and checks the expiration date of both as well. With server certificate validation Emarsys ensures the authenticity of the database server and prevents domain thefts and person-in-the-middle attacks.

    You can only connect to a server whose server certificate is signed by the CA that you configure on the Connection Details page (Add-ons -> Relational Data -> Connections tab and click Edit). It is the root CA certificate that is required here, not the SQL server certificate. If the server certificate was signed by an intermediate CA instead of the root CA directly, the entire CA chain must be pasted beginning with the root CA certificate in the CA Certificate text field. The whole certificate chain is always needed, but in most cases it is just a single root CA certificate.

    On the client side Emarsys currently only support password authentication. To ensure a proper level of security, it is recommended to use strong passwords and change them frequently. Moreover, we  establish connections from a fixed IP address (185.4.123.112), therefore, you can ensure proper security measures on the network level too.

    Connection types with CA certificate

    The following connection types require a CA certificate:

    SSL configuration links

    CA certificate Format

    The accepted format for your CA certificate is Privacy-Enhanced Mail (PEM).

    Troubleshooting

    In case of any certificate issue perform the following general troubleshooting steps:

    1. Generate a new server certificate and set the database to use the new certificate.
    2. Make sure that the whole certificate chain, beginning with the Root CA and ending one step short of the server certificate is pasted into the CA certificate text field.
    3. Check the expiration date of the server certificate and each CA certificate using the OpenSSL tool with the following command:
      openssl x509 -in cert.pem -noout -text
    4. Click Test to test your connection when you have added all necessary data.

    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:

    Which type of connection setup are you interested in?

    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.

    1. After clicking Create MySQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk:
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. 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 view option () is available only with MySQL connections, regardless of whether the database is external or internal (hosted by Emarsys). This option always takes you to the MyAdmin page, where your can make the necessary settings.

    The information and controls on the MyAdmin page are maintained by phpMyAdmin, not Emarsys. 

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Create Redshift connection you can set up your connection in the next window.
    2. You can create a single or even multiple RDS connections by adding your Amazon Redshift schema. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. You can define the schema name in the Connection parameters field, as follows: currentschema=<schema_name>.
      For public schemas, leave the field blank.
    4. Test your connection, when you have added all necessary data.
    5. 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

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Create PostgreSQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host Id of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. 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

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    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.

    1. After clicking Create BigQuery connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Project ID – Enter the your project Id.
      • Dataset – Give a descriptive name to your database.
      • Client Email – Enter the email address of your client.
      • Private key – Enter your privatekey.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. 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

    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 are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking MsSQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Port – Enter the port number.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. 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

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    1. After clicking Azure SQL connection you can set up your connection in the next window.
    2. Make sure you fill in all fields marked with an asterisk.
      • Host – Enter the host name of your connection.
      • Database name – Give a descriptive name to your database.
      • Username – Enter your username.
      • Password  – Enter your password.
    3. Test your connection, when you have added all necessary data.
    4. 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

    When you are done, you can now continue and create segments with Relational Data or personalize content with Relational Data.

    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. 

    To select the reference fields click the icon by the connection.

    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.

    Congratulations, you are done!