Writing Job Results to a FTP(S) Server

This article explains how to send job results directly to your FTP(S) server.

Table of Contents

Prerequisites

  • Basic knowledge of Treasure Data, including the toolbelt.
  • An FTP server

Basic Usage



Untitled-3
Our Premium plan includes advanced security features, which includes a list of static IPs Treasure Data is using. You can limit the access to your endpoint by using these IPs. Please contact support@treasuredata.com if you need it too.

Get your credential for your FTP(S) server

First, please obtain your credential to access your FTP(S) server.

Write the query

Please visit the Treasure Data Console query editor page and compile your query.

Specify the Result Export target

In the same window, click the Add button in the Result Export section and select FTP from the drop down menu. Please fill out all the information, including your Host, Port, Username, Password.

If you want to connect to FTP Server with FTPS(explicit) or FTPES(implicit), please check Use SSL? and select proper options.



Execute the query

Finally, either save the query with a name and run or just run the query. Upon successful completion of the query, its result will be automatically imported to the specified Container destination:

Usage from CLI

You can also use CLI for Result Export to FTP(S).

Example,

CLI for FTP

$ td query --result '{"type":"ftp","host":"xx.xx.xx.xx","port":21,"username":"xxxx","password":"xxxxx","path_prefix":"/path/to/file","file_ext":".csv","sequence_format":"","header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF"}' -d sample_datasets "select * from www_access" -T presto

CLI for FTPS (Implicit mode)

$ td query --result '{"type":"ftp","host":"xx.xx.xx.xx","port":990,"username":"xxxx","password":"xxxxx","passive_mode":true,"ascii_mode":true,"ssl":true,"ssl_explicit":false,"ssl_verify":false,"ssl_verify_hostname":false,"path_prefix":"/path/to/file","file_ext":".csv","sequence_format":"","header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF"}' -d sample_datasets "select * from www_access" -T presto

CLI for FTPES (Explicit mode)

$ td query --result '{"type":"ftp","host":"xx.xx.xx.xx","port":21,"username":"xxxx","password":"xxxxx","passive_mode":true,"ascii_mode":true,"ssl":true,"ssl_explicit":true,"ssl_verify":false,"ssl_verify_hostname":false,"path_prefix":"/path/to/file","file_ext":".csv","sequence_format":"","header_line":true,"quote_policy":"MINIMAL","delimiter":",","null_string":"","newline":"CRLF"}' -d sample_datasets "select * from www_access" -T presto

Legacy mode(Deprecated)

Untitled-3
We kept this feature for backwards compatibility and will stop providing in the near future.

For On-demand Jobs

For on-demand jobs, just add the --result option to the td query command. After the job is finished, the results are sent to the specified FTP server.

$ td query \
  --result 'ftp://user:password@domain.com/path.csv' \
  -w -d testdb \
  "SELECT code, COUNT(1) AS cnt FROM www_access GROUP BY code"
Untitled-3
If a port number is not specified, the default value is 21.

The request body is in CSV format (RFC 4180) where the first line is a header with the column names, new line is CRLF, text encoding is UTF-8, and quotation character is double quotes.

_c0,_c1
a,b
c," d "
e,f

For Scheduled Jobs

For scheduled jobs, just add the --result option when scheduling a job. After every job run, the results are sent in the same manner as on-demand jobs.

$ td result:create myftp ftp://user:password@domain.com/
$ td sched:create hourly_count_example "0 * * * *" \
  -d testdb \
  --result myftp:path.csv \
  "SELECT code, COUNT(1) AS cnt FROM www_access GROUP BY code"

Last modified: Nov 22 2016 02:04:41 UTC

If this article is incorrect or outdated, or omits critical information, please let us know. For all other issues, please see our support channels.