Learning

Access a Snowflake stage with SFTP

Glenn Gillen

VP of Product, GTM

Learning

Wouldn't it be nice to access files in a Snowflake stage, using SFTP, as if they were locally mounted on your machine? Wouldn't it be even better if it was not necessary to manage IP allow lists, open firewall ports, or setup services like PrivateLink? Would you believe me if I told you that it would take you less than 15 minutes to setup?

Introducing the Snowflake Stage Data with SFTP Connector!

Snowflake 💙 SFTP

SFTP, the Secure File Transfer Protocol, is an ubiquitous protocol for securely transferring files, based on the SSH (Secure Shell) protocol.

Snowflake is The Data Cloud and the place to support workloads such as data warehouses, data lakes, data science / ML / AI, and even cybersecurity. This centralization brings a huge amount of convenience through breaking down data silos and allowing teams to make smart data-informed decisions.

One way to import data into Snowflake is to use the snow command line client or the snowflake-connector-python Python library to upload files and have them be mapped to tables for later processing. Unfortunately, both those tools require a specific installation, and an OAuth authentication, whereas SFTP is installed on many Linux distributions and only needs SSH keys.

In this post I'm going to show you how to securely access a Snowflake stage in your account, with SFTP, in just a few minutes. We will:

Create a stage associated to your Snowflake account.
Create a SFTP server on Snowflake with a private encrypted connection.
Upload, list, download and remove files with SFTP commands.

Create a stage

This can be done with just a few commands in the Snowsight editor:


_10-- create a test database
_10CREATE DATABASE IF NOT EXISTS SFTP_TEST_DATABASE;
_10USE DATABASE SFTP_TEST_DATABASE;
_10
_10-- create a test schema
_10CREATE SCHEMA IF NOT EXISTS SFTP_TEST_SCHEMA;
_10USE SCHEMA SFTP_TEST_SCHEMA;
_10
_10-- create an internal stage
_10CREATE STAGE IF NOT EXISTS SECURE ENCRYPTION = (type = 'SNOWFLAKE_SSE');

The files contained in that stage can be listed with:


_10LIST @"SECURE";

And the stage should be empty for now!

Setup a SFTP server inside Snowflake

We are now going to create a SFTP server for the previous stage inside Snowflake. This will create a point-to-point connection between the two systems — without the need to expose any systems to the public internet!

Get the app

The Snowflake stage data with SFTP Connector by Ockam is available in the Snowflake Marketplace.

Select a warehouse

The first screen you're presented with will ask you to select the warehouse to utilize to activate the app and can choose to change the application name.

Grant account privileges

Click the Grant button to the right of this screen. The app will then be automatically granted permissions to create a warehouse and create a compute pool.

Activate app

Once the permissions grants complete, an Activate button will appear. Click it and the activation process will begin.

Launch app

After the app activates you'll see a page that summarizes the privileges that the application now has. There's nothing we need to review or update on these screens yet, so proceed by clicking the Launch app button.

Configuration

We are now going to configure the application. We first need to:

Create an Ockam project and issue an enrollment ticket
Create some SSH keys for both the SFTP server and the SFTP user.

Generating SSH keys

In order to setup both the SFTP server and the SFTP client, we will need to generate three SSH key pairs:

One pair for the user.
Two pairs for the server (we support both RSA and ED25519 signatures).

Configuring the application

Configure connection details

Click "Get started" to open the Snowflake setup screen.

Take the contents of the file sftp.ticket that we just created and paste it into "Provide the above Enrollment Ticket" form field in the "Configure app" setup screen in Snowflake.

Configure the stage access

In this screen, we need to enter:

The full name of the stage we want to access: SFTP_TEST_DATABASE.SFTP_TEST_SCHEMA.SECURE.
The name of a SFTP user which will access the server, for example user.
The public SSH key which we generated earlier: paste the content of the id_rsa.pub file.

Grant privileges

To be able to authenticate with Ockam Orchestrator and then discover the route to our outlet, the Snowflake app needs to allow outbound connections to your Ockam project.

Toggle the Grant access to egress and reach your Project button and approve the connection by pressing Connect.

Toggle the Grant access to your SSH host ED25519 private key button and paste the contents of the ssh_host_ed25519_key file.

Toggle the Grant access to your SSH host RSA private key button and paste the contents of the ssh_host_rsa_key file.

Start the SFTP server

Press "Next" and wait for the SFTP server to start. Once started, you should be able to check the SFTP server logs, and see that the server is listening on port 2222:

You can also notice a tab containing the logs for an Ockam node. That Ockam node is a portal outlet which: