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).
-
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 ) 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.
-
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
-
Select the relevant source object.
-
In the properties panel, go to the Source Schema Settings section and choose Configure Schema.
-
Mark the relevant columns as key columns.
Data Types
|
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 |