You can upload your business-specific, relational data to Emarsys-hosted databases. With this option, Emarsys hosts the databases where your data is stored and it is an Emarsys responsibility to manage and take care of all the implementation steps necessary. Emarsys implementation team offers the implementation support.
You can also connect your own external (client-hosted) databases to Emarsys and use the data fields contained in them to create segments or populate personalization variables. For information on the case clients using external databases, see Relational Data onboarding for clients using external (Client-hosted) databases.
For general information, see: Relational Data - Overview.
- Preparing your data
- Connection setup
- Naming conventions of .csv files
- Setting up your .csv files
- Debugging your SFTP upload
Preparing your data
You can integrate your business-specific 1st, 2nd, and 3rd-party data with the Emarsys Marketing Platform. First you need to upload your data files to Emarsys servers where your data is processed to internal databases hosted by Emarsys.
You can upload any data that fits in a relational database. Your data must be in a relational database, which means it contains tables with columns and rows. The tables are created by Emarsys to be able to store the data that you provide. If you want to change the format of the data to be stored, the table schema should be modified first. The modifications and the initial steps are made by the Emarsys implementation team. Emarsys databases always use mySQL.
You can import your data in the following ways:
- API: Emarsys API provides access for clients to upload their data into Emarsys servers. For optimum performance and data transfer you can use the Emarsys API, however it requires you to have some technical resources to integrate it with your application(s). RDS API can be used for internal and for external databases too. For details, see Transferring data via the API.
- SFTP: You can upload your data files into an Emarsys-hosted SFTP (Secure File Transfer Protocol) server. In this more frequently used way you can process more data (high-volume data) much faster.
Fully managed (Emarsys-hosted) connection setup
Relational Data Service (RDS) processes your data files once you have uploaded to the Emarsys SFTP server. Usually, the data is ready to be used 15 minutes after the files are uploaded. However, the loading time depends on the size of the file.
On the client side, infrastructure and IT personnel is not needed as Emarsys provides the infrastructure and hosts databases. Emarsys implementation team will create segmentation templates and personalization. In exchange, there is lead time and data load may require development.
Only Emarsys can do data structure changes and Emarsys Scripting Language (ESL) usage requires programming skills.
|Database infrastructure||Hosted and maintained by Emarsys.|
|Segment templates, data structure and personalization||Defined, implemented and modified by Emarsys.|
|Lead time and data requires development?||Yes|
|On-site IT personnel recommended?||No|
For more information, see What options are there for setup and hosting?
Accessing the SFTP server
To upload your data files to an Emarsys-hosted SFTP server, first you need to obtain the following:
- SSH public key: Generate your SSH-2 public key. For details see, Creating an SSH key on Windows, on MacOS, or on Linux.
- User account and user privileges: Your user account with your credentials is created by the Emarsys RDS Implementation Team.
- User directory: Emarsys provides you a directory to store your data.
Details to access an Emarsys-hosted SFTP server :
- URL: sftp://suitesftp.emarsys.net
- Port: 22
To have full access to the Emarsys-hosted database, you need to whitelist the Emarsys IP address: 22.214.171.124.
Uploading your data files to the SFTP server
After you have successfully granted access and connected to the Emarsys-hosted SFTP server you can start uploading your data files.
- Only .csv files are accepted. Emarsys SFTP server supports .csv format only. See Setting up your .csv files.
- Triggers are not supported in case of .csv imports.
- Use of control file is preferred. See Naming conventions of .csv files.
- Another method could be to upload files to a temp directory (or use a temporary file name and then rename it) on the SFTP server and then move it when it is there. In this case there is no need for a control file.
- File uploading time depends on the size of the data file.
Another way is to set up automatic file upload to the Emarsys SFTP server. In this case, you do not have to deal with the upload process. See Configuring automatic SFTP upload.
To minimize loading time use data files with smaller size than 5 GB. File size hard limit is 80 GB.
If the file size is huge then the file is split into smaller chunks with maximum 3 million lines. Only the split files are archived, the original huge one is not.
Configuring automatic SFTP upload
You can configure automatic data file upload to the Emarsys SFTP server using the Emarsys Marketing Platform UI. To do this, go to Add-ons -> Relational Data -> Tables & Views tab. In this list you can see all the tables and views for all connections.
At the end of each line there are two icons, the first is to Set upload file parameters.
For views and for tables in external connections the icon is disabled, so you cannot upload files for these.
2. By ticking Allow SFTP upload checkbox you can set if table can be uploaded from a .csv file.
3. In the File name field you can set the file name pattern from which file the system will import data into this table. Do not insert the special endings/postfixes "append" and "upsert" here. Those are automatically added, checked, handled.
- There is a checking method to avoid identical patterns with other tables:
- You cannot use invalid or accented characters either:
4. By ticking the checkbox First row is header the first line in the file will be skipped.
5. You can set different separators, however it is recommended to use the same separator for each file.
6. You might get a warning message regarding the
upload.finished control file. The error message can appear when control file validation is set by the implementation team.
Processing your data to Emarsys-hosted database
Once you have uploaded your .csv data files to the SFTP server, or you have set up a pattern to import data files automatically, RDS can process your data to Emarsys-hosted database.
- Importing your files to the database starts when RDS finds a control file called
upload.finishedafter every file is fully uploaded. The content of this control file is not checked, but It is the best if this file is empty. It is only used to prevent loading the files which are just half-uploaded.
- Importing data files is processed according to the timestamp at the end of the file name.
- RDS imports the data file in the following order:
- Replaced files in alphabetical order
- Append files
- Upsert files
- Only the files from the root are imported any other subdirectories are ignored.
- RDS checks for new data files every 20 seconds. There is a safety mechanism to confirms If there is no new data for a defined period (e.g. 24 hours) then emails will be cancelled to avoid sending with previous data.
- Currently it is not checked if the load was successful.
- Your data is ready to be used 15 minutes after the files are uploaded.
Successfully processed files are deleted from SFTP server and archived for 30 days. Archived data can be reached by the Emarsys 3rd level Support.
Files left on SFTP for two weeks are deleted by an automated script from each directory.
Naming conventions of .csv files
Naming your .csv data files is crucial, as files are loaded into relational data tables according to their names. One file is for one table. The name of the file must be the name of the table where it is going to be loaded, for example:
Consider the following rules, when naming your .csv data files:
- Filename is case-sensitive, use only small letters.
- Filename pattern must contain at least one * (asterisk) character.
- Use a timestamp at the end of the filename in order to keep the right order.
The following table describes the relation between the filename and the table name where it is loaded:
||Overwrites, replaces the existing content.|
||Inserts only the new records from the file based on the primary key in the table.|
||Inserts the new records and updates the existing ones based on the primary key.|
||Use of control file is preferred. Indicates that the load has been completed successfully. The content of this control file is not checked.|
if you do not specify to append or upsert data, it overwrites existing data. The load mechanism will overwrite the relational data with the new file.
You can upload multiple files to the same table in the same batch if all of them are either append or upsert. However, it is not recommended, because the order of loads in the same table is not defined within a batch.
Setting up your .csv files
Before uploading the .csv data file configure the specific format. This is usually done by providing a sample file. Ensure that your data files are UTF-8 Unicode encoded, .csv (comma separated values) files.
The following table describes the structure of the main elements and related rules of your .csv data file:
Optional element. Data processing ignores the content of the header.
Order the rows by the primary key to speed up the loading process.
This CSV guidelines will help you ensure that your data file is correctly formatted and encoded.
Debugging your SFTP upload
RDS doesn't import data files from the SFTP server
RDS can import data files in the following cases:
- Data file is encoded with UTF-8 Unicode.
- File is a .csv file.
- Filename is valid. For example: It includes lower-case characters only.
- Data fields are separated by a comma (,), or a semicolon (;), or a pipe (|) character.
- Table is set to be imported by a pattern.
- Data file is in the root directory.
- A control file exists. For example:
For more information, see Setting up your .csv files.
I cannot add data to an existing RDS data table
Ensure that the data file name is comply with the following rules:
- Name your data file as you want to name your data table.
- You can add a timestamp or other suffixes:
- To overwrite existing data for example:
- To append existing data for example:
- To update existing data for example:
- To overwrite existing data for example:
For more information, see Naming conventions of .csv files.
RDS doesn't read date and time from the data file
You can include timestamp, however table definitions specifies how data is stored.
For example, if you send 2019-01-01 12:11:10 and field type is DATE, then RDS stores 2019-01-01 only.
|Data type||“Zero” value|