Secure File Transfer Protocol (SFTP) Sources for Replication Flows

You want to replicate data from a file storage using a secure file transfer protocol (SFTP) connection for secure and reliable file transfers to a supported target.

Prerequisites

All objects in your replication flow have load type Initial Only (Delta loading is not supported).

The data you want to replicate is stored in CSV files, and one of the following delimiters is used:
  • Comma (,)

  • Semicolon (;)

  • Colon (:)

  • Vertical slash (|)

  • Horizontal tabulator (also known as tab or \t)

The CSV files are located in a folder, which serves as the source container for your replication flow. All files have the same schema.

Source Settings

The following properties are relevant for secure file transfer protocol (SFTP) connection as sources for a replication flow. You can review and change these properties for the replication flow itself (by choosing (Browse source settings)) or for individual replication objects (by selecting an object so that its properties get displayed in the side panel).

Property Description

Fail on Data Truncation

Enable this option if you want the replication to fail if the source data is too long.

By default, the replication fails if it encounters a string value greater than 5000 or a binary value greater than 5000. If you deactivate this property, source values that are too long are automatically truncated.

Fail on Incompatible Data

Enable this option if you want the replication to fail when it encounters values that are incompatible with the data type of the relevant target column. By default, the replication fails if it encounters incompatible data. If you deactivate this option, the system replaces incompatible values with NULL values.

Define Max. Number of Partitions

Enable this option if you want to define the maximum number of partitions manually. By default, the system uses 10 partitions. If you activate this option, an additional input field is displayed where you can enter the maximum number of partitions.

Max. Number of Partitions

Enter the maximum number of partitions to be used during the replication. By default, the system uses a maximum of 10 partitions for a replication object. However, depending on the specifics of your replication flow it may make sense to change this value (for example if you use filtering so that only a fraction of the data is actually relevant for replication). You can enter any integer number between 1 and 2147483647.

Include Subfolders

[Read-Onlly] Default value is NO. Data in subfolders is ignored during the replication.

Global Pattern

Enter a global pattern if you want to only replicate files whose names conform with the pattern.

For example, if a folder contains the following files: Test_1Pattern_Tab3.csv, Test_1Pattern_Tab2.csv, Data1.csv, and Data2.csv. If you want to replicate only the files that include the word "Pattern", you can use the pattern Pattern. This will ensure that only Test_1Pattern_Tab3.csv and Test_1Pattern_Tab2.csv are replicated.

Overwrite Source Settings at Object Level

By default, any settings that you have made at replication object level are kept intact if you make a different setting at replication flow level. To change this, enable this option.

Defining the Schema (and Related Properties)

In the step for selecting the source objects for your replication flow, you get a list of all files in the relevant folder. You then select the file whose metadata (particularly the schema) you want to use for the target.

You can edit the schema later if necessary. To do so, select the object whose schema you want to change, then choose Edit Schema (in the properties panel). A list of the source columns with their respective data type and other relevant properties (such as precision and scale) is displayed. Change these values as required, then choose Update Schema.
In addition, you can change the following other properties here:
  • File Delimiter: The existing delimiter is automatically entered as the default value. You can change it to any of the allowed values as listed above.

  • Encoding: The existing encoding value is automatically entered as the default default value. Alternatively (if the system cannot determine the existing value), UTF-8 is used as the default. You can change it to UTF-16 or ISO-8859-1.

  • File Header: By default, the system assumes that the first row in each CSV file is a header. If this is not the case, deactivate this property. The system then automatically names the columns as C(index), starting with C0.

Specify primary key columns: The source files do not have a primary key defined, while the target tables need to have a primary key. Consequently, you have to specify the primary key columns for the target. To do so, select the relevant object, then choose Configure Schema (in the Source Schema Settings section of the properties panel) and mark the relevant columns as key columns.

Examples for Global Patterns

You have the following files:

  • /datasetFolder/example.csv

  • /datasetFolder/test1.csv

  • /datasetFolder/test2.csv

  • /datasetFolder/test3.json

  • /datasetFolder/subfolder1/test4.csv

  • /datasetFolder/subfolder1/subfolder2/test5.csv

You define a global pattern as “test*.csv”. The result is that only the matching files from the dataset folder are picked up:

  • /datasetFolder/test1.csv

  • /datasetFolder/test2.csv

Replicating Source Objects Without Primary Keys

If a source object does not have a primary key, you can still use it in a replication flow by configuring the primary key columns manually. The target table requires a primary key, so you must define which columns serve as the key before you deploy the replication flow. To configure the primary key columns:
  1. Select the relevant source object.

  2. In the properties panel, go to the Source Schema Settings section and choose Configure Schema.

  3. Mark the relevant columns as key columns.

The columns you select as the primary key must contain unique, non-null values. Incorrect settings result in data inconsistency.

Data Types

The following table shows how internal data types are mapped to target data types for SFTP targets. The target data type depends on the file format: Parquet, CSV, or JSON/JSONLines.

SAP Datasphere

Parquet Data Type

CSV Representation

JSON Data Type

binary

BYTE_ARRAY

Base64-encoded

String (base64-encoded)

boolean

BOOLEAN

true or false

Boolean

date

INT32 (DATE)

YYYY-MM-DD

String (YYYY-MM-DD)

decimal(p,s)

DECIMAL(p,s)

As is

Number

decfloat16

DECIMAL(28,6)

As is

Number

decfloat34

DECIMAL(38,6)

As is

Number

float32

FLOAT

Decimal or scientific notation

Number

float64

DOUBLE

Decimal or scientific notation

Number

int8

INT32

Integer (base 10)

Number

int16

INT32

Integer (base 10)

Number

int32

INT32

Integer (base 10)

Number

int64

INT64

Integer (base 10)

Number

string

BYTE_ARRAY (STRING)

As is

String

time

INT64 (TIME microseconds)

HH:MM:SS.NNNNNNNNNN

String (HH:MM:SS.NNNNNNNNNN)

timestamp

INT64 (TIMESTAMP microseconds)

YYYY-MM-DD HH:MM:SS.NNNNNNNNNN

String (YYYY-MM-DD HH:MM:SS.NNNNNNNNNN)

uint8

INT32

Integer (base 10)

Number

uint64

INT64

Integer (base 10)

Number