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

Property	Description
Command type	Driver or Transforming
Required permission	SSH profile usage permission
License usage	Counted
Parallel execution	Not supported
Distributed execution	Not supported

Syntax

To list files:

sftp PROFILE ls PATH

To read file contents:

sftp PROFILE cat [format={text|csv|tsv|json}] [offset=LONG] [limit=LONG] [encoding=STR] [header=BOOL] PATH

To upload a file:

sftp PROFILE put [format={text|csv|tsv|json}] [fields=FIELD,...] [delimiter=STR] [encoding=STR] [append=BOOL] [overwrite=BOOL] [partition=BOOL] [multisession=BOOL] [maxsession=INT] PATH

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:

Field	Type	Description
type	string	Entry type: `dir` or `file`.
name	string	File or directory name.
file_size	long	File size in bytes.
uid	integer	File owner UID.
gid	integer	File group GID.
is_link	boolean	Whether the entry is a symbolic link.
perms	string	File permission string.
modified_at	timestamp	Last modification time.

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

Error code	Message	Description
40101	Invalid sftp command syntax. sftp [profile] [operator] [extended options]	The command syntax is incorrect.
40102	SSH profile does not exist.	The specified SSH profile is not registered.
40103	No permission to use the SSH profile.	The current user does not have permission to use the profile.
40104	Invalid sftp offset option. Must be a non-negative integer.	The `offset` value is not an integer.
40105	Invalid sftp limit option. Must be a non-negative integer.	The `limit` value is not an integer.
40106	Invalid sftp command operator.	The operation is not one of `ls`, `cat`, or `put`.
40107	Invalid sftp file format.	The `format` value is not one of `text`, `csv`, `tsv`, or `json`.
40108	Invalid encoding.	An unsupported encoding is specified.
40112	The partition option must be enabled when using path macros in the sftp command.	The path contains macros but `partition=t` is not specified.
40113	The multisession option must be enabled to specify the maximum number of sessions in the sftp command.	The `maxsession` option is specified but `multisession=t` is absent.
40114	The overwrite option cannot be false when using the partition option in the sftp put command.	`partition=t` and `overwrite=f` are specified at the same time.
40115	The overwrite and append options cannot be used simultaneously in the sftp put command.	Both `overwrite` and `append` are specified in the `put` operation.

Runtime errors

Error code	Message	Description	Post-processing behavior
40109	Wildcards cannot be used outside of the file name.	A wildcard is used in the directory portion of the path.	Aborts the query.
40110	The sftp target is a directory.	The target of the `cat` operation is a directory.	Aborts the query.
40111	Cannot execute the sftp command.	An error occurred while connecting to the SFTP server or processing a file.	Terminates the SSH connection.
40116	Cannot overwrite the file when the overwrite option is false in the sftp put command.	An existing file is found but `overwrite` is disabled in the `put` operation.	Aborts the query.
40117	The field list to write does not match the field list already written in the file in the sftp put command.	The fields differ from the existing header when appending in the `put` operation.	Aborts the query.
40118	An error occurred while reading the specified file in the sftp command.	An error occurred while reading the file.	Aborts the query.

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

List files on the SFTP server
```
sftp myprofile ls /data/logs
```
Lists files in the /data/logs directory using the myprofile SSH profile.
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.
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.
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.

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.

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.