SFTP

Writes batches of records as files in a directory on an SFTP server. A reader of the directory never sees part of a file.

Example

Signs in as smeltr with the private key in the worker-host variable SFTP_PRIVATE_KEY, and connects only to the server holding the pinned host key.

{
  "id": "partner-inbox",
  "type": "sftp",
  "host": "sftp.example.com",
  "username": "smeltr",
  "privateKey": "SFTP_PRIVATE_KEY",
  "hostKey": "ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAID3o82ivVxzYDbjz/HQTXSvCvoEb7fBpGzXPVbqK+q63",
  "directory": "/srv/inbox"
}

Before you start

Create the directory on the server. It must exist, as a bucket must; the destination makes the folders a prefix names under it as needed.
Have an account on the server that may create files and folders in the directory and rename files there.
Get the server's host key: a line such as ssh-ed25519 AAAA…. Copy it from the server's /etc/ssh/ssh_host_ed25519_key.pub, or run ssh-keyscan -t ed25519 sftp.example.com and drop the host name the line starts with. ssh-keyscan trusts the network it asks over, so check its answer against the server's file.
Put the account's password, or its private key, in a credential. The private key is an unencrypted RSA, ed25519 or ECDSA key in OpenSSH's format.

Settings

It also takes the settings every destination takes.

host string required

The server's host name or address.

Examples

sftp.example.com203.0.113.10

port integer default 22

The server's SSH port.

Examples

22228022

username string required

The user it signs in as, who needs write access to directory.

Examples

smeltrlogship

password credential default none

The credential holding the user's password. Set exactly one of password and privateKey.

Examples

SFTP_PASSWORD{"secret": "sftp-password","version": 1}

privateKey credential default none

The credential holding the user's private key: an unencrypted RSA, ed25519 or ECDSA key in OpenSSH's format.

Examples

SFTP_PRIVATE_KEY{"secret": "sftp-private-key","version": 1}

hostKey string required

The server's public key as OpenSSH writes it, ssh-ed25519 AAAA…, without a host name: it connects to that server and no other. Copy it from the server's /etc/ssh/ssh_host_ed25519_key.pub, or from ssh-keyscan -t ed25519 sftp.example.com, dropping the host name it starts with; keyscan trusts the network it asks over, so check its answer against the server's file.

Example

ssh-ed25519 AAAAC3NzaC1lZDI1NTE5AAAAID3o82ivVxzYDbjz/HQTXSvCvoEb7fBpGzXPVbqK+q63

directory string default

The directory it writes into: absolute, or under the user's home directory. Empty is the home directory. It must exist, as a bucket must; the folders a prefix names under it are made as needed.

Examples

/srv/inboxuploads/smeltr

prefix string default %Y/%m/%d/%H/

What each object's key starts with, for a record with no __destination.prefix. %Y, %m, %d, %H and %M are the write time, UTC, and no other % is allowed. A key never starts with /.

Examples

events/%Y/%m/%d/firewall/dt=%Y-%m-%d/hour=%H/acme/audit/

format string default ndjson

What each object holds.

ndjson: Writes one JSON document per line.
json: Writes one JSON array of every record.
parquet: Writes one Parquet file.
avro: Writes one Avro file.
csv: Writes one CSV file.
text: Writes one line of text per record, from field.
bytes: Writes each source record's bytes as read, before pipeline processing, one per line: __source.bytes unless field names another bytes field.

field string default _raw for text, __source.bytes for bytes

The field each line of a text or bytes object holds. Only with format text or bytes.

Example

message

newlines string default escape

What a bytes object does with a record holding a line break. Only with format bytes.

escape: Writes \, newline and carriage return as \\, \n and \r, which a source with framing escapedLines reverses.
refuse: Refuses the record.

Advanced: Delivery

compression string default gzip

How it compresses each object. A parquet object compresses each column inside the file instead, and its key ends with .parquet whatever the compression. An avro file compresses inside the file, and its key ends with .avro. Its gzip is Avro's deflate. Avro has no lz4.

none: Writes it uncompressed. The key ends with the format's own extension, such as .ndjson.
gzip: Compresses it with gzip and appends .gz to the key, such as .ndjson.gz.
snappy: Compresses it with framed Snappy and appends .sz to the key, such as .ndjson.sz.
lz4: Compresses it with framed LZ4 and appends .lz4 to the key, such as .ndjson.lz4.
zstd: Compresses it with Zstandard and appends .zst to the key, such as .ndjson.zst.

inFlight integer default 4

How many parts of one file upload at once. 1 to 64.

Examples

812

maxObjectBytes size default 64 MiB

The largest file it puts in the bucket, compressed. Set it to what the tools that read the bucket handle best. 64 KiB to 64 GiB.

Examples

128 MiB2 GiB32 MB

bufferBytes size default 256 MiB

Memory it may use on each worker for records not yet in the bucket. Files are finished early when it fills, so files reach maxObjectBytes only when it holds that many records before compression. 4 MiB to 32 TiB.

Examples

1 GiB4 GiB

stallAfterSecs integer default 30

Seconds one call to the store, or one finishing of the files, may take before it counts as stalled. 1 to 120, and at least 15 on SFTP.

Examples

60120

fileIntervalSecs integer default 60

The longest records wait, in seconds, before they land in the bucket. A longer wait makes larger files; records are acknowledged only once they land, so a source is held open up to this long, and after a crash reads that much again. 5 to 3600.

Examples

30240

Fields it reads

Field	Type	Holds
`__destination.prefix`	`str`	What an S3, Google Cloud Storage or Azure Blob Storage destination's object key starts with, taken as it is. Null means the destination's own prefix, with its time placeholders filled in.

How it works

The destination keeps one file open per prefix, and for Parquet, Avro and CSV per record type of the routes feeding it, across batches, at <directory>/<prefix><random id>.<extension>. It finishes the file when it reaches maxObjectBytes on disk, when fileIntervalSecs passes, when bufferBytes fills on the worker, or when a 65th file opens and this one was written least recently, and acknowledges a batch once every file holding its records has landed. It makes the folders the prefix names first when they are not there.

It writes a file under a hidden temporary name in the same folder, a dot, its own name and a tag, ending in .tmp, such as .3f9c…e1.ndjson.gz.4182-7-123456789.tmp. Once it has written every byte, it renames the file to its own name, so a reader sees the whole file or none of it. An SFTP source skips these temporary files.

Two files never share a name, so records written again after a failure make new files. On a failure the destination deletes the files it already finished, best effort; one it cannot delete holds records that land twice. When the rename fails, the destination removes the temporary file; a connection lost while writing can leave one behind, which is safe to delete.

One SSH connection carries the destination's writes. It connects only to a server that shows the pinned host key, and asks the server for that key's algorithm alone. The destination closes a connection that stops answering, and the next write opens a new one.

Verify

Send a record with a unique marker through the source, then list the directory under the prefix for a new file and look for the marker in it.

Troubleshooting

the server's host key is not the one configured: The server showed another key than hostKey. Check that host names the right server and that hostKey is its current key.
the server refused the password given for user …: With a private key, the text names it in place of the password. Check username, and that the credential holds that user's password, or a private key whose public half the server accepts for that user.
no connection within 30s: Connecting and signing in did not finish. Check that the worker host can reach host on port.
writing … no answer within …: The server stopped answering while the worker made a folder, wrote the named file or renamed it. With stallAfterSecs at 30 or less, an unreachable host shows this too, before the 30s connection limit runs out.
Writing fails with a file not found: Create directory on the server.
Writing fails with permission denied: Check that the user may create files and folders in directory and rename files there.

SFTP

Example

Before you start

Settings

Advanced: Delivery

Fields it reads

How it works

Verify

Troubleshooting

See also