Learning

Access a Snowflake stage with WebDAV

Glenn Gillen

VP of Product, GTM

Learning

Wouldn't it be nice to access files in a Snowflake stage, using HTTP requests, 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 WebDAV Connector!

Snowflake 💙 WebDAV

WebDAV, is a protocol for authoring and exchanging documents, based on the HTTP 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 HTTP commands can be easily executed with an ubiquitous utility like curl.

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

Create a stage associated to your Snowflake account.
Create a WebDAV server on Snowflake with a private encrypted connection.
Upload, list, download and remove files with WebDAV 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 WEBDAV_TEST_DATABASE;
_10USE DATABASE WEBDAV_TEST_DATABASE;
_10
_10-- create a test schema
_10CREATE SCHEMA IF NOT EXISTS WEBDAV_TEST_SCHEMA;
_10USE SCHEMA WEBDAV_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 WebDAV server inside Snowflake

We are now going to create a WebDAV 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 WebDAV Connector by Ockam is available in the Snowflake Marketplace - FIXME.

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
Configure the user name and password which will be used to access the server.

Configuring the application

Configure connection details

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

Take the contents of the file webdav.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: WEBDAV_TEST_DATABASE.WEBDAV_TEST_SCHEMA.SECURE.

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 user button, enter user for the user name and password for the user password.

Start the WebDAV server

Press "Next" and wait for the WebDAV server to start. Once started, you should be able to see the WebDAV server logs.

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

Created a relay named webdav in the Ockam project.
Declares that only clients with the attribute webdav-client can access the relay.

Access a Snowflake stage with SFTP

Real-Time Data Ingestion from Kafka to Snowflake

Edit on Github

follow or visit us on