I was working on a project where I needed to download a file from a remote SFTP server, and upload it to Databricks. There are multiple ways to achieve this such as netmiko, pysftp, and paramiko scp. Python has several libraries for interacting with SFTP servers, but the solution that I ended up using is a python script with paramiko client.
What is SFTP?
SFTP (SSH File Transfer Protocol) is a secure file transfer protocol that operates over the SSH (Secure Shell) protocol. It enables users to access, transfer, and manage files securely over a network, and can be accessed via free tools such as Cyberduck, FileZilla, and WinSCP. While newer protocols like HTTP/3 and WebDAV have come out, SFTP continues to be a relevant tool for secure file transfers.
What is paramiko?
According to paramiko.org documentation, paramiko is a python implementation of SSHv2. It provides both client and server-side functionality. To connect to the remover server and transfer the file or perform any operations, we need to create a paramiko client as shown below.
SSH_Client= Paramiko.SSHClient()Connecting to the Remote Server
Once we have created a paramiko client, we can then use the username and password for authentication to connect to the server.
ssh_client = paramiko.SSHClient()
ssh_client.set_missing_host_key_policy(paramiko.AutoAddPolicy())
ssh_client.connect(hostname=host,port=port,username=username,password=password)The best practice is to store your login credentials in an encrypted format, such as in Azure Key Vault, rather than hardcoding them. This not only enhances security, but also eliminates the need to update credentials across multiple code files whenever the username or password changes.
Transferring Files
To transfer files, we need to first create an SFTP session between the client and the server, using the SSHClient that we have created earlier.
ftp = ssh_client.open_sftp()Then, do the API call to import the file from SFTP to DBFS or another local system.
dbfs_file_path = "/dbfs/xxxyyy.csv" # update
ftp_file_path = "/Import/xxxyyy.csv" # update
files = ftp.get(ftp_csv_path, dbfs_file_path)If you need to import a file from local system to SFTP, use the function ftp.put
files = ftp.put(dbfs_file_path, ftp_csv_path)Once the file transfer is done, close the session by calling close() function.
ftp.close()
ssh_client.close()For more on SSH sessions with paramiko, check out this tutorial video by DevOps Journey, which also covers additional settings such as look_for_keys and AutoAddPolicy.
