Learn more about SFTP Server Import Integration.

You can send job results directly to your SFTP server.

For sample workflows of this, view Treasure Boxes.

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt

  • An SFTP server


Basic Usage


You can limit access to your endpoint by using a list of static IPs. Contact support@treasuredata.com if you need it.


Get your credential for your SFTP server

Obtain your credential to access your SFTP server.

Write the query

Go to the TD Console query editor page and compile your query.

Specify the Result Export target

In the same window, select Add in the Result Export section and select SFTP from the drop-down menu. Edit all the information, including your Host, Port, Username, Private Keyfile, and Passphrase of keyfile (if available).

The key type ed25519 is not supported but the ecdsa key type is supported.

Rename Remote File after Upload

If you select the “Rename file after upload finish” option, SFTP result output renames the file on the remote SFTP server from “.xxx.tmp” to “.xxx” after all the data is transferred. Some MA tools will try to import data when a file with a specific name exists on the SFTP server. The temp name option is useful for such cases.


Execute the Query

Either save the query with a name and run or just run the query. Upon successful completion of the query, the query result is automatically imported to the specified container destination.

Usage from CLI

You can also use CLI for Result Export to SFTP.


secret_key_file case

The following example has the escaped newline with backslash in the key.

$ td query --result '{"type":"sftp","host":"xx.xx.xx.xx","port":22,"username":"xxxx","secret_key_file":"{\"content\":\"-----BEGIN RSA PRIVATE KEY-----\nABCDEFJ\nABCDEFJ\n-----END RSA PRIVATE KEY-----\"}","secret_key_passphrase":"xxxxxx", "user_directory_is_root":true,"path_prefix":"/path/to/file.csv","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF"}' -d sample_datasets "select * from www_access" -T presto


user/password case

Example,

$ td query --result '{"type":"sftp","host":"xx.xx.xx.xx","port":22,"auth_method":"Password","username":"xxxx","password":"xxxxx","user_directory_is_root":true,"path_prefix":"/path/to/file.csv","rename_file_after_upload":false,"header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF"}' -d sample_datasets "select * from www_access" -T presto

FAQ for the SFTP Data Connector


I can’t connect to my SFTP server, what can I do?

  • Check what is valid protocol. If you intend to SFTP, you can use this Data Connector for SFTP. If FTP/FTPS, try to connect with FTP Data Connector.

    • If you are using a firewall, check your accepted IP range/port. Server administrators sometimes change the default port number from TCP/22 for security reasons.

    • Be sure that your private key has an OpenSSH format. We don’t support other formats like “PuTTY”.

    • We do not support the default format of the private key since OpenSSH 7.8. Please re-generate the key using '-m PEM' option.