Tutorial: Create and Use an SSH & SFTP Connection

This tutorial provides step-by-step instructions for using an SSH and SFTP connection, enabling you to execute remote commands and transfer files between local and remote systems.

Prerequisites

Before continuing with this tutorial, ensure you have completed the following prerequisites:

• A remote server with SSH or SFTP connectivity available

• A valid user account with appropriate access credentials for the remote server

Create a secret for the user account

1. Access the Snoweaver SSH application with the owner role or a custom role with the SNOWEAVER_SSH.APP_ADMIN application role.

2. Open the Secrets page from the side bar menu.

3. Create a new secret with the following configuration:

   Name:          learn_sw_account
   Secret Type:   Basic
   Username:      <your user account>
   Password:      <your private key or password>

   

4. Save the new secret.

5. If your private key is protected by a passphrase, create an additional secret with the passphrase:

   Name:          learn_sw_account_passphrase
   Secret Type:   String
   Secret Value:  <your passphrase>

   

Create a network rule to access the server

1. Open the Network Rules page.

2. Create a network rule with the following configuration:

   Name:             learn_sw_access_rule
   Allowed Domains:  <hostname or IP address>:<port number>

   

3. Save this new network rule.

Create an External Integration to Establish SSH Connectivity

1. Open the External Integrations page.

2. Apply the following configuration:

   Name:                   learn_sw_access_integration
   Allowed Secrets:        learn_sw_account
                           learn_sw_account_passphrase
   Allowed Network Rules:  learn_sw_access_rule

   

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.

   

   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.

   

Create a new connection

1. Open the Connections page.

2. Apply the following configuration:

   Name:                         learn_sw
   Host:                         <address of your SSH server>
   Port: (Optional)              <SSH port if it is not 22>
   Authentication Type:          <Password or Key>
   Key Type:                     <Password or Key>
   Account Secret:               learn_sw_account
   External Access Integration:  learn_sw_access_integration
   Passphrase Secret: (Optional) learn_sw_account_passphrase

   

3. Save the connection.

Execute SSH Commands Using the Connection

1. Open a Snowflake worksheet and execute the following SQL statements using a role with the necessary privileges. If you are working in a sandbox environment, you may use the AccountAdmin role or an equivalent role:

   CREATE WAREHOUSE IF NOT EXISTS LEARN_SW_WH
       WAREHOUSE_SIZE = XSMALL
       AUTO_SUSPEND = 60
       COMMENT = 'Warehouse for Snoweaver SSH learning project';
   
   -- Create a developer role with specific permissions
   CREATE ROLE IF NOT EXISTS LEARN_SW_DEVELOPER;
   GRANT USAGE ON WAREHOUSE LEARN_SW_WH TO ROLE LEARN_SW_DEVELOPER;
   
   -- Grant Snoweaver SSH operator application role
   GRANT APPLICATION ROLE SNOWEAVER_SSH.LEARN_SW_OPERATOR TO ROLE LEARN_SW_DEVELOPER;
   
   -- Assign the developer role to the user account
   GRANT ROLE LEARN_SW_DEVELOPER TO USER <Your User Account>;
   
   -- Set context to the developer role and LEARN_SW schema
   USE ROLE LEARN_SW_DEVELOPER;
   USE SCHEMA SNOWEAVER_SSH.LEARN_SW;
   
   -- generate a sample file in DATA
   CREATE OR REPLACE TEMPORARY TABLE ACCOUNTS
   AS
   SELECT '789' AS customExtIdField__c, 'Grand Hotels & Resorts Ltd' AS name,10000 AS NumberOfEmployees
   UNION
   SELECT '890','Express Logistics and Transport',5000
   UNION
   SELECT '901','University of Arizona',1000;
   
   
   COPY INTO @DATA/accounts.csv.gz
   FROM ACCOUNTS
   FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"')
   HEADER=TRUE
   OVERWRITE = TRUE
   SINGLE = TRUE ;
   

2. Execute the following SQL statements to verify the SSH connectivity:

USE ROLE LEARN_SW_DEVELOPER;
USE SCHEMA SNOWEAVER_SSH.LEARN_SW;

-- Verify SSH connection by checking the current directory
SELECT SSH('pwd') AS current_directory;

-- Test result handling
WITH SSH_TEST AS (
   SELECT SSH('foo') AS result
)
SELECT
   result:success::boolean AS command_success,
   result:data::text AS command_output
FROM SSH_TEST;

Transfer Files Using SFTP

1. Open a Snowflake worksheet and execute the following SQL statements:

   USE ROLE LEARN_SW_DEVELOPER;
   USE SCHEMA SNOWEAVER_SSH.LEARN_SW;
   
   -- Upload with default settings
   CALL UPLOAD('accounts.csv.gz', 'learn_sw/accounts.csv.gz');
   
   -- Upload with decompression enabled and two parallel threads
   CALL UPLOAD(
      file_path  => 'accounts.csv.gz',
      remote_file_path  => 'learn_sw/accounts.csv',
      decompress  => TRUE,
      parallel => 2);
   
   -- Download with default settings
   CALL DOWNLOAD('learn_sw/accounts.csv', 'learn_sw/accounts.csv');
   
   -- Download with compression disabled, overwrite enabled, and two parallel threads
   CALL DOWNLOAD(
      remote_file_path => 'learn_sw/accounts.csv',
      file_path => 'learn_sw/accounts.csv',
      compress => FALSE,
      overwrite => TRUE,
      parallel => 2);
   

2. Verify the transferred files in the remote directory and the DATA stage by executing the following SQL commands:

   -- List files in the remote directory
   SELECT SSH('ls -l learn_sw'):data::text;
   
   -- List files in the DATA stage
   LIST @DATA;