sftp

Uses an SSH profile to list files on a remote SFTP server, read file contents, or upload query results as a file.

Command properties

Syntax

To list files:

To read file contents:

To upload a file:

Options

format={text|csv|tsv|json}

File format. (Default: text)

• text: Reads or writes one line as one record.
• csv: Reads or writes the file in CSV format.
• tsv: Reads or writes the file in tab-separated format.
• json: Reads or writes the file in JSON format.

offset=LONG

Number of records to skip in the cat operation. Accepts a non-negative integer. (Default: 0)

limit=LONG

Maximum number of records to read in the cat operation. Accepts a non-negative integer.

encoding=STR

File encoding. (Default: utf-8)

header=BOOL

Whether to use the first row as the header in CSV or TSV format files for the cat operation. (Default: f)

fields=FIELD,...

List of fields to write in the put operation. Separate multiple fields with commas (,).

delimiter=STR

Field delimiter for text format in the put operation. (Default: space)

append=BOOL

Whether to append content to an existing file in the put operation. Cannot be used together with the overwrite option. (Default: f)

overwrite=BOOL

Whether to overwrite an existing file in the put operation. Cannot be used together with the append option. (Default: f)

partition=BOOL

Whether to use time-based macros (e.g., {logtime:yyyyMMdd}) in the path to split files by partition in the put operation. (Default: f)

multisession=BOOL

Whether to use multiple SFTP sessions simultaneously in the put operation. (Default: f)

maxsession=INT

Maximum number of sessions that can be open simultaneously in the put operation. Requires multisession=t.

Target

PROFILE

SSH profile name. Specifies an SSH connect profile registered in the system.

PATH

Path to a file or directory on the SFTP server. In the cat operation, wildcards (*) can be used in the file name. In the put operation with partition=t, path macros (e.g., {logtime:yyyyMMdd}) can be used.

Output fields

Output fields for the ls operation:

Output fields for the cat operation vary depending on the format option. In text format, one line of text is assigned to the line field. In csv and tsv formats, fields are assigned based on headers or column indices. In json format, JSON key-value pairs are assigned as fields. In all formats, the original file path is assigned to the _file field.

Error codes

Parsing errors

Runtime errors

Description

The sftp command communicates with a remote SFTP server using an SSH profile registered in the system. Three operations are supported:

• ls: Lists files and directories at the specified path, with directories listed before files and both sorted alphabetically. The ls operation acts as a driver query.
• cat: Reads the contents of a file at the specified path and converts them into records. Wildcards (*) can be used in the file name. The cat operation acts as a driver query.
• put: Writes input records to a file at the specified path. The put operation acts as a transforming query and passes input records to the next command unchanged.

In the put operation, enabling the partition option allows time-based macros in the path so that files are split according to the _time field value of each record. Enabling the multisession option allows separate SFTP sessions per partition for writing to multiple files simultaneously.

Query parameters (${}) can be used in the profile name and file path.

Examples

1. List files on the SFTP server

   sftp myprofile ls /data/logs
   

   Lists files in the /data/logs directory using the myprofile SSH profile.

2. Read a text file from the SFTP server

   sftp myprofile cat /data/logs/access.log
   

   Reads /data/logs/access.log in text format and converts each line into a record.

3. Read a CSV file with a header row

   sftp myprofile cat format=csv header=t /data/logs/report.csv
   

   Reads the CSV file using the first row as field names.

4. Read multiple JSON files using a wildcard

   sftp myprofile cat format=json /data/logs/*.json
   

   Reads all files with a .json extension in the /data/logs/ directory.

5. Upload query results to the SFTP server as a CSV file

   table duration=1d web_logs
   | stats count by method
   | sftp myprofile put format=csv fields=method,count /data/report.csv
   

   Uploads query results in CSV format to the SFTP server.

6. Upload files split by partition

   table duration=7d web_logs
   | sftp myprofile put format=json partition=t overwrite=t /data/logs/{logtime:yyyyMMdd}/access.json
   

   Uploads JSON files split into date-based directories according to each record's timestamp.