Skip to main content
Version: Next

FTP/SFTP Data Connector

FTP (File Transfer Protocol) and SFTP (SSH File Transfer Protocol) are network protocols for transferring files between a client and server. FTP transmits data in plain text, while SFTP provides encrypted file transfer over SSH, making it the preferred choice for secure environments.

The FTP/SFTP Data Connector enables federated SQL query across supported file formats stored on FTP/SFTP servers.

Quickstart​

Connect to an SFTP server and query CSV files:

datasets:
- from: sftp://files.example.com/data/sales/
name: sales
params:
file_format: csv
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}

Query the data using SQL:

SELECT * FROM sales LIMIT 10;

FTP vs SFTP​

FeatureFTPSFTP
Default Port2122
EncryptionNone (plain text)SSH encryption
AuthenticationUsername/passwordUsername/password
Server identityNot verifiedHost key in known_hosts
Recommended UseInternal/trusted networksProduction and public networks
Security Recommendation

Use SFTP instead of FTP whenever possible. FTP transmits credentials and data in plain text, making it vulnerable to interception.

Configuration​

from​

Specifies the FTP or SFTP server and path to connect to.

Format: ftp://<host>/<path> or sftp://<host>/<path>

  • <host>: The server hostname or IP address
  • <path>: Path to a file or directory on the server

When pointing to a directory, Spice loads all files within that directory recursively.

Examples:

# Connect to a specific file
from: sftp://files.example.com/data/customers.parquet

# Connect to a directory (loads all files)
from: sftp://files.example.com/data/sales/

# FTP connection
from: ftp://ftp.example.com/exports/reports/

name​

The dataset name used as the table name in SQL queries. Cannot be a reserved keyword.

params​

FTP Parameters​

Parameter NameDescription
file_formatRequired when connecting to a directory. See File Formats.
ftp_userRequired. Username for FTP authentication.
ftp_passRequired. Password for FTP authentication. Use secrets syntax: ${secrets:my_ftp_pass}.
ftp_portFTP server port. Default: 21.
client_timeoutWall-clock bound for one connection attempt — TCP connect, server greeting and login together. E.g. 30s, 1m. Default: 20s.
hive_partitioning_enabledEnable Hive-style partitioning from folder structure. Default: false.

SFTP Parameters​

Parameter NameDescription
file_formatRequired when connecting to a directory. See File Formats.
sftp_userRequired. Username for SFTP authentication.
sftp_passRequired. Password for SFTP authentication. Use secrets syntax: ${secrets:my_sftp_pass}.
sftp_portSFTP server port. Default: 22.
client_timeoutWall-clock bound for one connection attempt — name resolution, TCP connect, SSH handshake and password authentication together. E.g. 30s, 1m. Default: 20s.
hive_partitioning_enabledEnable Hive-style partitioning from folder structure. Default: false.

SFTP Host Key Verification​

Spice verifies the SFTP server before it sends the password, as ssh and sftp do. It reads ~/.ssh/known_hosts of the user that runs spiced. Spice connects only when this file has an entry for the server and the host key that the server presents matches that entry. Spice never adds or changes entries in the file.

Add a server​

Run these commands as the user that runs spiced:

mkdir -p ~/.ssh
ssh-keyscan -p 22 files.example.com >> ~/.ssh/known_hosts
ssh-keygen -lf ~/.ssh/known_hosts
3072 SHA256:yJ4VCOIOOWvxDlYmmCuu5w/Cx6xrOdTOLTS6tjAws0g files.example.com (RSA)
256 SHA256:TTwqJr9zBNKiSdnBNNsNAP4XxPAg8xyxKU2toj1Vhdk files.example.com (ECDSA)
256 SHA256:+d5F7o6yAKJ9uQ+8Vz3YTnl9xsi9BHBBL2nqptG1pVc files.example.com (ED25519)

ssh-keyscan does not authenticate the server. Compare the fingerprints with the fingerprints that the server's operator gives you before you use the file. If you already connected to the server with ssh or sftp as the same user and checked its key, the file already has the entry.

  • On port 22, the entry names the host, for example files.example.com. On other ports, it names [host]:port, for example [files.example.com]:2222. ssh-keyscan -p <port> writes the correct form.
  • The entry must name the host as the dataset's from URL does. An entry for an IP address does not match a dataset that uses a host name.
  • Hashed entries (HashKnownHosts yes) work.
  • Spice accepts Ed25519, ECDSA and RSA host keys. One entry for the server is sufficient: Spice asks the server only for the key types that the file has for it.
  • Host patterns such as *.example.com, and lines that start with @cert-authority or @revoked, are not supported.

Containers and Kubernetes​

In a container, the file must exist in the home directory of the user that runs spiced. Create the file in the current directory, and compare its fingerprints with the fingerprints that the server's operator gives you:

ssh-keyscan -p 22 files.example.com > known_hosts
ssh-keygen -lf known_hosts

The spiceai/spiceai image runs spiced as user 65534 with HOME=/app, so mount the file at /app/.ssh/known_hosts:

docker run -v "$PWD/known_hosts:/app/.ssh/known_hosts:ro" spiceai/spiceai

In Kubernetes, put the file in a ConfigMap. The file holds only public keys, so it does not need a Secret:

kubectl create configmap sftp-known-hosts --from-file=known_hosts=./known_hosts

Then mount it into the spiced container. With the Spice Helm chart, add this to values.yaml. In your own pod spec, add the same entries to the pod's volumes and the spiced container's volumeMounts:

volumes:
- name: sftp-known-hosts
configMap:
name: sftp-known-hosts

volumeMounts:
- name: sftp-known-hosts
mountPath: /app/.ssh/known_hosts
subPath: known_hosts
readOnly: true

A subPath mount does not receive ConfigMap updates. Restart the pod after you change the ConfigMap.

Errors​

A dataset that Spice cannot verify fails to load with one of these errors:

ErrorCauseFix
Cannot verify the SFTP server <host>:<port> because <path> cannot be readThe file does not exist, spiced cannot read it, or the home directory of the user that runs spiced is not set.Add the server as shown above. In a container, mount the file into the home directory of the user that runs spiced.
<path> has no Ed25519, ECDSA or RSA host key for the SFTP server <host>:<port>The file has no entry for the server, or the entry is for a different port or host name.Run ssh-keyscan -p <port> <host> >> <path>, then check the fingerprints with the server's operator.
The SFTP server <host>:<port> presented the <type> host key SHA256:<fingerprint>, which does not match its entry in <path>The server's host key changed, or a different host answered. Spice did not send the password.Ask the server's operator to confirm the new fingerprint. Then remove the old entry with ssh-keygen -R <host> -f <path> (ssh-keygen -R '[<host>]:<port>' -f <path> on ports other than 22) and add the server again.
The SSH handshake with the SFTP server <host>:<port> failed: ... Only host keys of the types <path> lists for the server are acceptedThe handshake failed. One cause is that the server has no host key of the types that the file has for it.Check that the server is reachable, then add its current host keys as shown above.

Examples​

Basic SFTP Connection​

Connect to an SFTP server with username and password authentication:

datasets:
- from: sftp://sftp.example.com/data/transactions/
name: transactions
params:
file_format: parquet
sftp_user: datauser
sftp_pass: ${secrets:sftp_password}

Basic FTP Connection​

Connect to an FTP server for internal file access:

datasets:
- from: ftp://ftp.internal.local/exports/daily/
name: daily_exports
params:
file_format: csv
ftp_user: ftpuser
ftp_pass: ${secrets:ftp_password}

Reading a Single File​

When pointing to a specific file, the format is inferred from the file extension:

datasets:
- from: sftp://files.example.com/reports/quarterly_summary.parquet
name: quarterly_summary
params:
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}

Connection with Timeout​

Configure a timeout for slow or unreliable connections:

datasets:
- from: sftp://remote-server.example.com/large-datasets/
name: large_dataset
params:
file_format: parquet
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}
client_timeout: 120s

client_timeout bounds a connection attempt as a whole, not each stage of it, so a server that accepts the TCP connection and then stops responding — during the FTP greeting or login, or the SSH handshake or authentication — is abandoned once the bound expires rather than waiting indefinitely. When client_timeout is not set, a 20s bound applies.

Custom Port Configuration​

Connect to servers running on non-standard ports:

datasets:
- from: sftp://secure.example.com/data/
name: secure_data
params:
file_format: parquet
sftp_port: 2222
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}

Hive Partitioning​

Enable Hive-style partitioning to automatically extract partition columns from the folder structure:

datasets:
- from: sftp://datalake.example.com/events/
name: events
params:
file_format: parquet
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}
hive_partitioning_enabled: true

Given a folder structure like:

/events/
year=2024/
month=01/
data.parquet
month=02/
data.parquet
year=2025/
month=01/
data.parquet

Queries can filter on partition columns:

SELECT * FROM events WHERE year = '2024' AND month = '01';

Multiple Datasets from One Server​

Load different datasets from the same SFTP server:

datasets:
- from: sftp://data.example.com/sales/
name: sales
params:
file_format: parquet
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}

- from: sftp://data.example.com/inventory/
name: inventory
params:
file_format: csv
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}

Accelerated Dataset​

Enable local acceleration for faster repeated queries:

datasets:
- from: sftp://archive.example.com/historical/
name: historical_data
params:
file_format: parquet
sftp_user: ${secrets:sftp_user}
sftp_pass: ${secrets:sftp_pass}
acceleration:
enabled: true
refresh_check_interval: 1h

Secrets​

Spice integrates with multiple secret stores for secure credential management. Store FTP/SFTP credentials in a secret store and reference them using the ${secrets:key} syntax.

datasets:
- from: sftp://files.example.com/data/
name: secure_data
params:
file_format: parquet
sftp_user: ${secrets:sftp_username}
sftp_pass: ${secrets:sftp_password}

For detailed information, refer to the secret stores documentation.

Troubleshooting​

Connection Timeouts​

Connection attempts are bounded at 20s by default. If a server is reachable but slow to complete the greeting, login, or SSH handshake, raise client_timeout:

params:
client_timeout: 120s

Authentication Failures​

Verify credentials are correctly stored in your secret store and that the user has read access to the specified path on the server.

File Format Errors​

When connecting to a directory, ensure file_format is specified and matches the actual file types in the directory. Spice expects all files in a directory to have the same format.

Cookbook​

Refer to the FTP cookbook recipe for a complete working example.