How to Connect to Snowflake in Node.js with a Service Account
Getting started with programmatic access to your data via the SQL API.
Motivation
Sure, Snowflake is powerful data platform, but we can really crank up the heat with access to its SQL API. You may already deal with APIs that provide an abstract interface (e.g. REST) over your underlying data. But Snowflake’s API gives us direct access to our data — so let’s experience the raw, unfiltered power of SQL together.
Prerequisites
This tutorial walks through connecting an application Snowflake, which requires you to have a few capabilities:
Snowflake account creation. You will need SECURITYADMIN privileges to create a user account and grant it roles.
Snowflake SDK. You will need a supported library for your language of choice, for example the snowflake-sdk Node.js library.
Key Pair Authentication. You will need command-line access to generate an RSA key pair, plus a secret manager to store the private key.
Authentication is always annoying and painful — we’ll do our best to talk you through the common pitfalls.
Getting Started
Step 1: Create an RSA Key Pair
A key pair consists of a private and public key. As their names suggest, you should store the private key in a secret manager. The public key can be stored in plain text, and we will write it directly to Snowflake in the next step.
Generating the key pair, from Snowflake’s Key Pair Authentication docs:
Important Step: Remove the public key’s PEM encoding.
Before storing the public key in Snowflake, you need to copy the key without the "-----BEGIN PUBLIC KEY-----” start and end blocks.
Step 2: Create a Snowflake Service Account User
In this step, we need to create a user, set its public key, and grant it a role.1
You may already have another system (Okta, Azure AD, etc.) for managing service accounts and their roles, but we show those steps here for completeness.
Use the rsa_public_key.pub file contents from the previous step (again, without the start/end blocks) to set the rsa_public_key field on the user.
This is all we need to do on the Snowflake administrative side for now.
Step 3: Connect the SDK
First you’ll want to install the library (e.g. npm install snowflake-sdk). Next, let’s set up a basic connection.
We’ll assume you have a secure way to access the rsa_private_key.p8 file from your application.
What’s somewhat unique about Snowflake’s API is that we specify a role at connection time. This is a default role, and you can always assume another role later, but at least one role is required to connect.
Common Issue: Finding your Account ID
The Account ID is a combination of your org name and account name. The Account Identifier docs describe this in more detail, but an easy way to find your Account ID is by clicking on the account switcher in the lower left corner of the web UI.
Step 4: Verify Connectivity
Now that we have a connection, let’s try connecting.
If all went well (which it often doesn’t on the first try), then we can move on to the next step. If we see any errors, we’ll need to troubleshoot them. Double-check your account id, spellings on your users/role/warehouse names, and that the key is being loaded correctly.
Step 5: Execute Queries!
As a final step, let’s run a simple “show grants” query that returns a list of roles granted to the current user.
If all goes well, then it’s off to the races running programmatic queries against your Snowflake data.
For a full working example, see: snowflake-nodejs/example.js
About Spyglass
Since you’re here, let me tell you what we’ve cooked up at Spyglass. In short, we make Snowflake data access controls easy - or provide an automated and better way to do the above.
If you’ve nodded your head while reading this, reach out at spyglass.software (or demo@spyglass.software) and we’ll show you a product demo to give you a taste of the future of data access management.
You can run these commands in Snowsight UI Worksheets, Terraform, Permifrost, etc. In this tutorial we write everything as SQL for simplicity.