File, FTP, SFTP Requirements

This section describes the requirements, access privileges, and other features of Fivetran HVR when using File/FTP/SFTP for replication.

Supported Platforms

Learn about the File/FTP/SFTP versions compatible with HVR on our Platform Support Matrix page (6.1.0, 6.1.5, and 6.2.0).

Data Management

Learn how HVR maps data types between source and target DBMSes or file systems on the Data Type Mapping page.

Introduction

HVR supports different kinds of file locations:

regular file locations (a directory on a local file system);
FTP file locations;
SFTP file locations.

Generally, the behavior of HVR replication is the same for all of these kinds of file locations. For all of them, capture is defined with action Capture and integration is defined with action Integrate. All other file location parameters are supported and behave normally.

HVR can connect to an FTP or SFTP file location using a file protocol in two ways:

connecting with this protocol directly from the hub machine;
or first connecting to a remote machine with HVR's remote protocol, and them connecting to the file location from that machine using FTP or SFTP.

These two options have small difference in the timing and latency of capture jobs:

direct file capture jobs check for new files once a second;
indirect capture (capture from a non-local file location) jobs check for new files every 10 seconds.

Also, if action Capture is defined without the DeleteAfterCapture parameter, the capture job may have to wait for up to a minute before capturing new files. This happens because these jobs rely on comparing timestamps, but the file timestamps in the FTP protocol have low granularity (minutes not seconds).

A proxy server to connect to FTP or SFTP can be configured with File_Proxy_* location properties.

HVR uses the cURL library to communicate with the file systems.

Integrate Limitations

By default, for file-based target locations, HVR does not replicate the delete operation performed at the source location.

FTPS: Server Authentication

FTP and WebDAV support certificates for authentication. These are held in the certificate directory HVR_HOME/etc/cert. The following files are included or can be easily created:

ca-bundle.crt: Used by HVR to authenticate SSL servers, like FTPS.
To override this, create a new file named host.pub_cert in this same certificate directory. No authentication is done if neither file is found. Delete or relocate both files to disable FTPS authentication.
The ca-bundle.crt file can be copied from the **/usr/share/ssl/certs/" directory on Unix/Linux.
host.pub_cert: Used by HVR to override ca-bundle.crt for server verification for host.
FTP connections can be unencrypted or they can have three types of encryption; this is called FTPS, and should not be confused with SFTP. These FTPS encryption types are SSL/TLS implicit encryption (standard port: 990), SSL explicit encryption and TLS explicit encryption (standard port: 21).

If the FTP/SFTP connection is made via a remote HVR machine, then the certificate directory on the remote HVR machine is used, not the one on the hub machine.

FTPS: Client Authentication

Normally, clients (HVR) are authenticated using a username and password. In addition to this, you can use client-side SSL certificates.
To set this up, you need to have a public/private key pair in PEM format. Place them in the HVR_CONFIG/etc/cert directory as client.pub_cert and client.priv_key, and define the following actions:

Group	Table	Action/Parameters
File/FTP	*	Environment Name=HVR_SSL_CLIENT_CERT, Value=client.pub_cert
File/FTP	*	Environment Name=HVR_SSL_CLIENT_KEY, Value=client.priv_key
File/FTP	*	If you have a password/phrase on your key, set: Environment Name=HVR_SSL_CLIENT_KEY_PASSWD, Value=passwd

You can encrypt the password for HVR by running: shell hvrcrypt client passwd

If you have a PKCS#12 or PFX file, you can convert it to PEM format using openssl: shell openssl pkcs12 -in cert.p12 -clcerts -nokeys -out client.pub_cert shell openssl pkcs12 -in cert.p12 -nocerts -out client.priv_key

SFTP: Server Authentication

A file (named HVR_CONFIG/files/known_hosts) is used internally during SFTP connections. It is updated the first time HVR connects to reach the SFTP machine. On Unix or Linux this file can be initialized by copying it from HOME/.ssh/known_hosts.