Documentation

Note: This article covers the SFTP Destination setup process. It doesn’t cover the DataPrep setup for SFTP.

You can learn how to extract data from SFTP data source here.

Description

‍SFTP (Secure File Transfer Protocol) is a network protocol for securely accessing, transferring, and managing large files and sensitive data.

Setup guide

Follow our setup guide to connect SFTP to Improvado.

Choose an authentication method

• Auth by Login/Password
• Auth by Improvado Public Key
• Auth by Private Key

Important: Make sure to provide your authentication information by following our guide.

Complete configuration

On the SFTP connection page, fill in the following fields:

1. Enter a name for your Destination connection in the Title.
2. Select the Authentication type from the dropdown. Learn more: Choose an authentication method.
3. Enter the Host.
4. Enter the Port.
5. Enter the User Name.
6. (Auth by Login&Password/Private Key only) Enter the Password.
7. (Auth by Improvado Public Key/Private Key only) Upload the Private key file.
8. Enter the Folder. {%dropdown-button name="folder"%}

{%dropdown-body name="folder"%}

A forward slash ( / ) means that you need to enter the root path.

{%dropdown-end%}

9. Select the File format from the dropdown.
10. Enter the File name.
11. (Optional) Enter the Separator. {%dropdown-button name="separator"%}

{%dropdown-body name="separator"%}

The maximum length of the separator is 2 characters.

{%dropdown-end%}

12. Select the Partition by from the dropdown. {%dropdown-button name="partition-by"%}

{%dropdown-body name="partition-by"%}

Partitioning is the way of splitting data for uploading to the file.

{%dropdown-end%}

13. Select the necessary Use static IP option from the dropdown.
14. Select the necessary Use binary strings option from the dropdown. {%dropdown-button name="use-binary-strings"%}

{%dropdown-body name="use-binary-strings"%}

The "Use binary strings" determines whether string fields are encoded in binary format when loading data.

Recommended setting: "No" (default).

{%dropdown-end%}

‍

How to provide credentials to Improvado

There are three different ways to provide your credentials that you can choose depending on the type of selected authentication:

• Option #1 (Auth by Login/Password)
• Option #2 (Auth by Improvado public key)
• Option #3 (Auth by Private key)

Option #1 (Auth by Login/Password)

Provide the following information:

• Host
• Port
• User Name
• Password

Option #2 (Auth by Improvado public key)

Provide the following information:

• Host
• Port
• User Name

Also, you need to add an Improvado SSH public key to your SFTP server (in ```~/.ssh/authorized_keys``` file):

Note: Improvado SSH public key is displayed in UI on the right-hand side of the SFTP connection page in the Configuration details section:

Option #3 (Auth by Private key)

Provide the following information:

• Host
• Port
• User Name
• Private key
• (If your Private key has a passphrase) Password

Upload your SSH Private key. For example, you can run the following command:

ssh-keygen -t rsa -b 4096

Note: If SFTP is hosted in Azure Blob Storage, instructions for connecting to the storage can be found here.

‍

Additional information

Authentication type

Possible parameters:

• SFTP auth by login/password
• SFTP auth by Improvado public key
• SFTP auth by private key

See the credentials options below.

Private key

Supported key format: OpenSSH. If your private key is a .ppk file, please convert it to the OpenSSH key format.

Possible SSH key algorithms:

• RSA
• Ed25519
• DSS
• ECDSA

Folder

Possible parameters:

```/{{data_source}}/{{data_table_title}}/{{report_type}}/{{dataclass}}/{{filename}}/{{YYYY}}/{{MM}}/{{DD}}/{{YYYY_today}}/{{MM_today}}/{{DD_today}}```

• ```{{data_source}}``` is a data provider, integration, connector.
• ```{{data_table_title}}``` is an object that contains all extraction orders with the same granularity (dimensional schema).
• ```{{filename}}``` is the same as destination table name.
• ```{{report_type}}``` is a set of such fields as metrics, properties, dimensions, etc
• ```{{dataclass}}``` is the data class value (if configured).

If you use ```/{{YYYY}}/{{MM}}/{{DD}}``` settings, the data will be added to folders daily. Each new record will not delete the previous one, even for data that contains no date.

{%docs-informer warning title="Important"%}

{{YYYY}}/{{MM}}/{{DD}} partitioning is based on a date field in the exported data. If the dataset does not contain an actual date field (for example, no “date” column/field), these partition values cannot be derived and date-based partitioning will not work as expected. In that case, use {{YYYY_today}}/{{MM_today}}/{{DD_today}} to partition by the export run date instead.

{%docs-informer-end%}

{%docs-informer info%}

{{YYYY}}/{{MM}}/{{DD}} refer to the record’s date-based partition values (if applicable), while {{YYYY_today}}/{{MM_today}}/{{DD_today}} always use today’s date.

{%docs-informer-end%}

File format

Possible formats:

• csv
• csv+gzip
• json
• json+gzip
• parquet
• txt (can be enabled by adding ```.txt``` at the end of the file name)

File name

Possible parameters:

```{{workspace_id}}-{{workspace_title}}-{{data_source}}-{{report_type}}-{{data_table_title}}-{{filename}}-{{dataclass}}-{{YYYY}}-{{MM}}-{{DD}}-{{YYYY_today}}-{{MM_today}}-{{DD_today}}-{{H}}-{{M}}-{{S}}-{{chunk_id}}```

• ```{{filename}}``` is the same as destination table name

IMPORTANT: you cannot use {{DD}} for partition by month

• ```{{filename}}-{{YYYY}}-{{MM}}-{{DD}}``` – for partition by day
• ```{{filename}}-{{YYYY}}-{{MM}}``` – for partition by month

{%docs-informer info%}

{{YYYY}}/{{MM}}/{{DD}} require a date field in the exported data. If your dataset has no date field, use {{YYYY_today}}/{{MM_today}}/{{DD_today}} to include the current run date/time in the file name.

{%docs-informer-end%}

Also, you can use “_” instead of “-” or do not use any symbols at all, for example:

• ```{{filename}}_{{YYYY}}-{{MM}}-{{DD}}```
• ```{{filename}}{{YYYY}}{{MM}}{{DD}}{{H}}{{M}}{{S}}```

‍

{%docs-informer info%}

Explicitly specify ```.txt``` file format at the end of the file name to load ```.txt``` files. We'll also use the file format from File format field for this file.

{%docs-informer-end%}

Separator

Possible delimiters that can separate data in your file:

• comma
• semicolon
• tab

Partition by

Possible ways of splitting data:

• Day
• Month

Use static IP

Select Yes for Use static IP option if you allow Improvado to connect your database by the static IPs mentioned on the Destination connection page.

Select No if you have permitted access to your database from any IP. In this case, Improvado will connect your database using dynamic IPs not listed on the Destination connection page.