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.

  1. To begin, either sign up for a free Azure account Try Azure free or log in to your existing Azure sandbox environment.

  2. Navigate to the subscription configuration page and register the resource provider Microsoft.Compute

    ../_images/03.png

  3. Navigate to the Storage Accounts service and select + Create to open the creation wizard.

    ../_images/0b.png

  4. 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
    ../_images/128.png

  5. Click Next

  6. Update the specified configuration as indicated while retaining default settings for all other parameters.

    Enable hierachical namespace:  checked
Enable SFTP:                   checked
    ../_images/215.png

  7. Click Review + create to proceed with the creation of this storage account.

  8. After deploying the storage account, navigate to its home page and select SFTP.

    ../_images/313.png

  9. Click Add local user

  10. 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
    ../_images/414.png

  11. Click Next.

  12. Click Create new to instantiate a new container named dev.

    ../_images/514.png

  13. Configure the following settings on the second page:

    Containers:     dev
Permissions:    Read, Create, Write, Delete, List
User Id:        1000
Home:           /
    ../_images/67.png

  14. Save the new local user.

  15. 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

  1. Access the Admin Console with the owner role of Snoweaver or a custom role with the SNOWEAVER.APP_ADMIN application role.

  2. Open the Secrets page.

  3. 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>
    ../_images/810.png

    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.

  4. Save the new secret.

Create a network rule to access the server

  1. Open the Network Rules page.

  2. Create a network rule with the following configuration:

    Name:             azr_sftp_sw_access_rule
Allowed Domains:  <your container name>.blob.core.windows.net:22
    ../_images/96.png

  3. Save this new network rule.

Create an external integration to facilitate SFTP connectivity

  1. Open the External Integrations page.

  2. 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
    ../_images/108.png

  3. Click Generate Script to create an SQL script for creating a new External Access Integration and granting Snoweaver the necessary access permissions.

  4. Run the script in a Snowflake worksheet. The role used for execution must have the CREATE INTEGRATION privilege. For instance, use the AccountAdmin role.

    ../_images/1112.png

    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.

  5. Return to Snoweaver and register the new external access integraiton.

    ../_images/133.png

Update the project configuration

  1. Open the LEARN_DEV project on the Projects page.

  2. Add the newly created OAuth secret and External Access integration to the project:

    Secrets:                add azr_sftp_sw_dev_key
External Integrations:  add azr_sftp_sw_dev_access_integration
    ../_images/143.png

  3. Save the project.

Create a SFTP job for File Upload

  1. Open the LEARN_DEV project in Snoweaver with the LEARN_DEV_DEVELOPER role.

  2. Open the Jobs page.

  3. 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
    ../_images/153.png

  4. Specify the following job vairables:

    Name

    Type

    Test Value

    stage_file_dir

    Text

    sfdc

    stage_file

    Text

    ingested_accounts.csv

  5. Specify the following job endpoint URL:

    <your container>.blob.core.windows.net

  6. Use the following Jinja template for specifying the File Path:

    {{_vars.stage_file_dir}}/{{_vars.stage_file}}.gz

  7. Use the following Jinja template for specifying the SFTP Server File Path:

    /sw/{{_vars.stage_file_dir}}/{{_vars.stage_file}}

  8. Test the template to validate the output.

  9. Click Make a Test Call to upload the file to the storage account.

  10. If the upload completes successfully, the job will display the relative path of the uploaded file within the dev container of the storage account.

    ../_images/184.png

  11. Save the job.

  12. Navigate to the Home page of your Azure storage account in your web browser. Then, select Containers.

    ../_images/201.png

  13. Access the dev container and locate the uploaded file by following the previously displayed relative path.

    ../_images/216.png

Create a SFTP job for File Download

  1. Return to the Jobs page in Snoweaver.

  2. Click Save as to create a new job named azr_sftp_sw_download.

    ../_images/221.png

  3. In the General Configuration of the new job, set the Operation Type to Download.

  4. Use the following Jinja template for specifying the File Path:

    {{_vars.stage_file.split('.')[0]}}/{{now().isoformat()}}.{{_vars.stage_file.split('.')[1]}}

  5. Test the template to validate the output.

  6. Use the following Jinja template for specifying the SFTP Server File Path:

    /sw/{{_vars.stage_file_dir}}/{{_vars.stage_file}}

  7. Test the template to validate the output.

  8. Click Make a Test Call to initiate a download of the file from the storage account.

  9. Upon successful completion of the download, the job will display the relative path to the downloaded file within the DATA stage.

    ../_images/251.png

  10. Save the job.

  11. 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.

    ../_images/261.png