Tutorial: Upload and download files using SFTP jobs
This tutorial provides step-by-step instructions for using sftp jobs to efficiently upload and download files from a remote SFTP server.
Prerequisites
Before continuing with this tutorial, ensure you have completed the following prerequisites:
Set up a SFTP server and create a test account
Note
If you have access to an SFTP server for this tutorial, you may skip this section.
To begin, either sign up for a free Azure account Try Azure free or log in to your existing Azure sandbox environment.
Navigate to the subscription configuration page and register the resource provider Microsoft.Compute
Navigate to the Storage Accounts service and select + Create to open the creation wizard.
Please configure the following settings on the initial page:
Resource group: <create a new resource group or select an existing one> Storage account name: <Use a globally unique name> Performance: Standard Redundancy: LRS
Click Next
Update the specified configuration as indicated while retaining default settings for all other parameters.
Enable hierachical namespace: checked Enable SFTP: checked
Click Review + create to proceed with the creation of this storage account.
After deploying the storage account, navigate to its home page and select SFTP.
Click Add local user
On the initial configuration page, specify the following settings:
Username: user1 SSH Key pair: checked Add key: Public key source: Generate new key pair Key name: user1
Click Next.
Click Create new to instantiate a new container named dev.
Configure the following settings on the second page:
Containers: dev Permissions: Read, Create, Write, Delete, List User Id: 1000 Home: /
Save the new local user.
Download the private key file using the link provided in the prompt for the subsequent section.
Warning
After completing the tutorial, either delete the storage account or disable the SFTP feature to avoid incurring unnecessary hourly charges.
Create a secret for the test account
Access the Admin Console with the owner role of Snoweaver or a custom role with the SNOWEAVER.APP_ADMIN application role.
Open the Secrets page.
Create a new secret with the following configuration:
Name: azr_sftp_sw_dev_key Secreet Type: Basic Username: <your container>.dev.user1 Password: <your private key>
Note
Replace the placeholder snoweaver in the screenshot with the name of the container you created earlier. The private key is located in the file downloaded in the previous section; open this file with a text editor to access the key.
Save the new secret.
Create a network rule to access the server
Create an external integration to facilitate SFTP connectivity
Open the External Integrations page.
Apply the following configuration:
Name: azr_sftp_sw_dev_access_integration Allowed Secrets: azr_sftp_sw_dev_key Allowed Network Rules: azr_sftp_sw_access_rule
Click Generate Script to create an SQL script for creating a new External Access Integration and granting Snoweaver the necessary access permissions.
Run the script in a Snowflake worksheet. The role used for execution must have the CREATE INTEGRATION privilege. For instance, use the AccountAdmin role.
Note
Currently, Snowflake does not support native applications requesting permissions to create integrations on consumer accounts. This step may be automated in the future should this restriction be lifted.
Return to Snoweaver and register the new external access integraiton.
Update the project configuration
Create a SFTP job for File Upload
Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.
Open the Jobs page.
Apply the following configuration:
Name: azr_sftp_sw_upload Job Type: SFTP Instance Type: Procedure Operation Type: Upload External Integration: azr_sftp_sw_dev_access_integration Number of Threads: 4 Auto Decompress: checked Authentication Type: Key Account Secret: azr_sftp_sw_dev_key
Specify the following job vairables:
Name
Type
Test Value
stage_file_dir
Text
sfdc
stage_file
Text
ingested_accounts.csv
Specify the following job endpoint URL:
<your container>.blob.core.windows.net
Use the following Jinja template for specifying the File Path:
{{_vars.stage_file_dir}}/{{_vars.stage_file}}.gz
Use the following Jinja template for specifying the SFTP Server File Path:
/sw/{{_vars.stage_file_dir}}/{{_vars.stage_file}}
Test the template to validate the output.
Click Make a Test Call to upload the file to the storage account.
If the upload completes successfully, the job will display the relative path of the uploaded file within the dev container of the storage account.
Save the job.
Navigate to the Home page of your Azure storage account in your web browser. Then, select Containers.
Access the dev container and locate the uploaded file by following the previously displayed relative path.
Create a SFTP job for File Download
Return to the Jobs page in Snoweaver.
Click Save as to create a new job named azr_sftp_sw_download.
In the General Configuration of the new job, set the Operation Type to Download.
Use the following Jinja template for specifying the File Path:
{{_vars.stage_file.split('.')[0]}}/{{now().isoformat()}}.{{_vars.stage_file.split('.')[1]}}
Test the template to validate the output.
Use the following Jinja template for specifying the SFTP Server File Path:
/sw/{{_vars.stage_file_dir}}/{{_vars.stage_file}}
Test the template to validate the output.
Click Make a Test Call to initiate a download of the file from the storage account.
Upon successful completion of the download, the job will display the relative path to the downloaded file within the DATA stage.
Save the job.
Navigate to the DATA stage within SNOWEAVER.LEARN_DEV using the LEARN_DEV_DEVELOPER role. Locate the downloaded file at the path specified by the test call response.