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.
Connect to an SFTP server and query CSV files:
Query the data using SQL:
| Feature | FTP | SFTP |
|---|---|---|
| Default Port | 21 | 22 |
| Encryption | None (plain text) | SSH encryption |
| Authentication | Username/password | Username/password or SSH keys |
| Recommended Use | Internal/trusted networks | Production and public networks |
:::tip[Security Recommendation] Use SFTP instead of FTP whenever possible. FTP transmits credentials and data in plain text, making it vulnerable to interception. :::
fromSpecifies 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 serverWhen pointing to a directory, Spice loads all files within that directory recursively.
Examples:
nameThe dataset name used as the table name in SQL queries. Cannot be a reserved keyword.
params| Parameter Name | Description |
|---|---|
file_format | Required when connecting to a directory. See File Formats. |
ftp_user | Username for FTP authentication. |
ftp_pass | Password for FTP authentication. Use secrets syntax: ${secrets:my_ftp_pass}. |
ftp_port | FTP server port. Default: 21. |
client_timeout | Connection timeout duration. E.g. 30s, 1m. No timeout when unset. |
hive_partitioning_enabled | Enable Hive-style partitioning from folder structure. Default: false. |
| Parameter Name | Description |
|---|---|
file_format | Required when connecting to a directory. See File Formats. |
sftp_user | Username for SFTP authentication. |
sftp_pass | Password for SFTP authentication. Use secrets syntax: ${secrets:my_sftp_pass}. |
sftp_port | SFTP server port. Default: 22. |
client_timeout | Connection timeout duration. E.g. 30s, 1m. No timeout when unset. |
hive_partitioning_enabled | Enable Hive-style partitioning from folder structure. Default: false. |
Connect to an SFTP server with username and password authentication:
Connect to an FTP server for internal file access:
When pointing to a specific file, the format is inferred from the file extension:
Configure a timeout for slow or unreliable connections:
Connect to servers running on non-standard ports:
Enable Hive-style partitioning to automatically extract partition columns from the folder structure:
Given a folder structure like:
Queries can filter on partition columns:
Load different datasets from the same SFTP server:
Enable local acceleration for faster repeated queries:
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.
For detailed information, refer to the secret stores documentation.
If connections frequently timeout, increase the client_timeout value:
Verify credentials are correctly stored in your secret store and that the user has read access to the specified path on the server.
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.
Refer to the FTP cookbook recipe for a complete working example.