Логотип Help Portal
  • Начало работы

    Introduction

    • Onboarding overview
    • Project Initiation
    • Email compliance
    • Управление учетной записью

    Данные

    • Oбмена данными
    • Aдаптации данных
    • Web data collection
    • Интеграция

    Channels

    • Email onboarding
    • Smart Insight
    • Predict
  • Обучающие видео

    Resources

    • Introduction
    • Online Self-learning
    • Начало работы: видео

    Events

    • Instructor-led Training
    • Webinars
    • Seminars
    • Training Calendar
  • Руководства пользователя

    Стратегия

    • Персонализация
    • Автоматизация
    • Data Monitoring

    Channels

    • Email
    • Мобильные технологии
    • Web
    • Ads
    • SMS

    Add-ons

    • Smart Insight
    • Predict
    • Рекомендации по вознаграждениям
    • AIM
    • Relational Data
  • Статьи поддержки
    Forrester Wave CCCM (Independent Platforms) Report Q4 2019

    Новости

    • Что нового в платформе Emarsys?
    • Updates from the CSA
    • Пилотные функции
    • Статьи о защите конфиденциальности данных и GDPR

    Support

    • Как я могу получить помощь?
    • Редактирование профиля пользователя
    • Preparing for Black Friday 2019
    • Black Friday 2019 - Best practices
  • |
  • Partners

    Enhance Partners

    • Getting Started as an Emarsys Partner
    • The Emarsys Integration Platform
    • Automation Center Integrations
  • Разработчики
  • Статус системы
Нужна помощь?
Русский Deutsch English Español Français Türkçe 简体中文 Test New Chat
Войти
  • Начиная
  • Data Onboarding
  • 0 Начиная Data Onboarding

Содержимое раздела:

  • Data onboarding - Overview
  • Uploading your product data
  • Implementing the Web Extend data collection scripts
  • Google Tag Manager and data collection
  • Mobile data collection
  • Uploading your contact data
  • Uploading your sales data
  • Relational Data onboarding for clients using client-hosted databases
  • Relational Data onboarding for clients using Emarsys-hosted databases
EDIT
Expand all

Relational Data onboarding for clients using Emarsys-hosted databases

Updated: 28/11/2019 01:00
360024778974
Relational Data onboarding for clients using Emarsys-hosted databases

You can upload your business-specific, relational data to Emarsys-hosted databases. With this option, Emarsys hosts the databases where your data is stored. In case you are using Relational Data as a fully managed service it is 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 client-hosted (external) databases to Emarsys and use the data fields contained in them to create segments or populate personalization variables. For information, see Relational Data onboarding for clients using client-hosted databases.

For general information, see: Relational Data - Overview

Contents:

  • Data integration options
    • Accessing the SFTP server
    • Connecting data files to data tables
  • Import modes
  • CSV file structure
  • SFTP upload
  • Monitoring the importing process
  • Debugging your SFTP upload

Data integration options

You can upload your data in any of the following options:

  • 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). 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.

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: You can ask your access from Emarsys Support. You need to attach your public key to the requesting ticket.
  • 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

Connecting data files to data tables

To import your data into the correct database table first, you need to configure the parameters and connections between the CSV file and the table. In this way, we can identify which CSV to load into which table. 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.

This page lists all available data tables and views. Be aware, you can import data into tables only.

  1. Click the Set upload file icon at the right side of the table’s row.

2. Enable SFTP upload to the table by ticking the checkbox Allow SFTP upload.

3. In the File name field you can set the filename pattern. All files with matching filename for the given filename pattern will be loaded to the table. E.g.: If you use product* every file that starts with the word "product" will be imported to this table.

  • For the File name pattern, you can use an asterisk (*) as a wildcard character.
  • A File name field can  contain only alphanumeric characters and '.', '_', '-' or '*'.

4. By ticking the checkbox First row is header the first line in the file will be ignored from being imported. Be aware, we do not match the columns in the .csv file with the fields of the target table. The order of the columns in your .csv must match with the order of the fields in your table.

5. You can set different separators. It is recommended to use the same separator for each file.

6. Click Save to save your settings.

Import modes

Naming your .csv data files is crucial, as the loading mode of your .csv files can be controlled with the postfix of the filename. Filenames are case-sensitive, use small letters only. E.g.: filename.[loadingmode].csv

Import modes:

  • Replace: If not specified otherwise, the Replace import mode replaces the existing content of the target table with the rows in the file. e.g.: filename.csv In case of replacing, loading mechanism first loads your data into a temporary table. When loading data is finished successfully the old table is dropped and replaced with the new one.
  • Append: All rows are inserted as new records from the .csv file to the table. E.g.: filename.append.csv
  • Upsert: Inserts all new rows from the .csv file to the table. Existing rows are updated.  If there is a primary key in the .csv file. which has an exact match in the table, the record in the table will be updated with the data in the .csv file.

You can upload multiple .csv files at once, but you cannot specify the order of the processing between files and within the .csv file. Do not upload files together, if the  order of the files is important.

You can upload multiple files to the same table in the same batch only in that case, if there is no replace file among them. Otherwise, the load process will not be successful.

CSV file structure

To import your data files they must meet the following criteria:

  • Format is .csv (comma separated values) file.
  • Encoded with UTF-8 Unicode.
  • Have Windows (\r\n) or UNIX (\n) file ending. Old line endings used by classic MacOS (\r) is not supported.

To minimize loading time use smaller data files. File size hard limit for the upload is 80 GB.

The structure of the .csv data file should be as follows:

Header

  • In case your .csv file contains a header, in the Upload settings dialog tick that the First row is a header to avoid the header is imported as data.
    Be aware, we do not match the columns in the .csv file with the fields of the target table. The order of the columns in the .csv must match with the order of the fields in the data table.

Primary key field(s)

  • Ensure not to use a primary key twice, because only one is imported. In case of using multiple primary keys Emarsys cannot guarantee which primary key will be the final one, because the processing of the rows is not sequential.
  • Ordering data by primary key(s) increases the importing process significantly.

Fields

  • Fields must be separated by a character configured in the upload settings.
  • Data can be enclosed within double quotation marks (").
  • If your data contains a quotation mark, you have to use the data within double quotation marks (") and you need to escape it with a backslash (\). E.g.: "hello \" world" is imported as hello " world
  • In case to import a backslash (\) character, you need to escape it. E.g.: hello\\world is imported as hello\world.
  • To import a separator character, you must enclose the field value within double quotation marks (").
  • Ensure to use the correct data type defined by the relevant column of the table, otherwise the import will cause data loss. Pay attention to the length of the data, because text fields are truncated if longer than defined. If Relation Data setup was performed by Emarsys (Fully-managed) you can check the structure of the tables in your previous agreements. Ensure to keep the agreed structure of the columns, or your data becomes inconsistent and the import might fail.
    • DATE, use 'YYYY-MM-DD' format, e.g.: '1970-01-01'
    • DATETIME or TIMESTAMP, use 'YYYY-MM-DD hh:mm:ss' e.g.: '1970-01-01 00:00:01'

Even if your .csv file contains headers, the importing process does not match the columns in the file with the fields of the target table. The order of the columns in your .csv must match with the order of the relational data table fields.

SFTP upload

After you have successfully granted access and connected to the Emarsys-hosted SFTP server you can start uploading your data files. Only the files from the SFTP root directory are imported, any other subdirectories are ignored.

  1. Once you are ready with the upload, you must notify the system to proceed with the importing mechanism by uploading a control file named upload.finished. Ensure to upload the control file after all the .csv files. The control file can be empty. It prevents processing partially uploaded files.
  2. The importing process starts as soon as the control file uploaded (and you configured the SFTP upload properly).
  3. Wait until upload.finished disappears before you start to upload new files, otherwise you will receive “The import failed because another import was already in progress” error message.
  4. Relational Data checks for new data files in regular intervals. The successfully processed files and the files left on SFTP for long time are deleted from the server.

In case of .csv imports the SQL triggers and the foreign keys are not supported.

Monitoring the status of an import

There are two options to monitor the status of an import:

  • In the SFTP History tab you can keep track of your import process. You can see the result of the import and check the number of rows affected. If an upload failed, hover over the status to see a short explanation in the appearing tooltip.
  • You can also check the upload_result.json file. This file was created at the root of your SFTP folder when the upload finished. The file lists the result of the import for all affected tables in a JSON array.

Example: Results
Successful import:

[{
 "tableName": "activities",
"status": "finished",
"operation": "append",
"affectedRows": 2228
}]


Unsuccessful import:
[{
"tableName": "sale",
"status": "finished",
"operation": "append",
"error": "failure:query_timeout"
}]

Debugging your SFTP upload

The import process does not start

Check the following for possible reasons:

  • File format and extension is .csv.
  • Allow SFTP upload is enabled for the table at the Tables & Views tab in Relational Data.
  • Filename follows the conventions set in the SFTP upload settings.
  • Data file is uploaded to the root of your SFTP directory.
  • You uploaded the control file. E.g: upload.finished

 

The import process has been failed

Check the reason for the error at the SFTP History tab or in the upload_result.json file. 

Check the following possible errors:

  • wrong_encoding - "The import failed because of an encoding error. Please make sure the CSV file’s character encoding is UTF-8, then try to upload it again."
  • non_existent_target_table - "The import failed because the table you are trying to load into was not found. Please make sure that the table exists and try again.",
  • conflicting_files - "The import failed, because multiple files were targeting the same table in the database. Try importing the files separately.",
  • foreign_key_constraint_failed - "The import failed because of a foreign key violation. Please make sure the uploaded CSV file is up-to-date and try again.",
  • overlapping_load - "The import failed because another import was already in progress. Please try again when the previous import is finished."

 

The affected rows are different than expected

Check the following for possible reasons:

  • The import mode was not upsert:
    • In case of an upsert the number of the affected rows can be higher than the original number of rows. In some cases the update counts as 2 updated rows.
    • A row can be updated multiple times by the input rows. It can result in a "lower-than-expected" update count.
  • There were not any identical primary keys or unique constraints.
  • The data types are correct. E.g.: Strings cannot be imported into numeric fields, otherwise they will be represented as 0.
Была ли эта статья полезной?

Еще есть вопросы? Отправить запрос
В начало

Emarsys is a Leader

You may also be interested in:

Похожие статьи

  • Relational Data onboarding for clients using client-hosted databases
  • Overview:: Relational Data - Overview
  • Preparing your product data file
  • End-user guides:: Personalizing content with Relational Data
  • End-user guides:: Data Import - End-user guide
Copyright © 2019 Emarsys eMarketing Systems. All rights reserved
Legal Notice Privacy Policy Master Services Agreement Anti-spam Policy