Reading data from Snowflake into Google Sheets

Introduction

Recently I was asked whether it is possible to get data from Snowflake into Google Sheets. A quick search revealed that there are several tools available on the market for this. While these tools definitely provide additional value I was looking for something which is simple to set up and does not require extra tooling. This blogpost describes a method of getting data into a Google sheet directly from Snowflake via the SQL REST API.

Prerequisites

For this demo we need a warehouse ‘PUBLIC_WH’ , a table called ‘NFLX’ which resides in schema ‘PUBLIC’ in a database called ‘NFLX_DB’. The database contains information about various tv shows and movies which were available on Netflix. Data for this table can be found here: https://www.kaggle.com/shivamb/netflix-shows

Before we can use the SQL API we need to create a user ‘GSHEETS_USR’ and a role ‘NFLX_GS’. Next to that we need to give this role access to the warehouse and the table. This can be done via the following SQL commands:

USE ROLE USERADMIN;
CREATE OR REPLACE ROLE NFLX_GS;
CREATE OR REPLACE USER GSHEETS_USR PASSWORD='******' DEFAULT_ROLE='NFLX_GS' MUST_CHANGE_PASSWORD = FALSE;
USE ROLE SYSADMIN;
GRANT USAGE ON WAREHOUSE PUBLIC_WH TO ROLE NFLX_GS;
GRANT USAGE ON DATABASE NFLX_DB TO ROLE NFLX_GS;
GRANT USAGE ON SCHEMA NFLX_DB.PUBLIC TO ROLE NFLX_GS;
GRANT SELECT ON TABLE NFLX_DB.PUBLIC.NFLX TO ROLE NFLX_GS;
GRANT ROLE NFLX_GS TO USER GSHEETS_USR;
-- test if role has access to NFLX table
USE ROLE NFLX_GS;
USE WAREHOUSE PUBLIC_WH;
SELECT * FROM NFLX_DB.PUBLIC.NFLX LIMIT 10;

After successful execution you should be able to query the ‘NFLX’ table via the ‘NFLX_GS’ role as shown below:

Authentication

Everyone needs to be authenticated when using the SQL API . This can be done via OAuth or Key Pair Authentication. For this demo we will use Key Pair in combination with a JSON Web Token (JWT). The keys are generated with OpenSSL which is available for many platforms like Windows, Linux and Mac:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out private_key.p8 -nocrypt
openssl rsa -in private_key.p8 -outform PEM -pubout -out public.pem

The generated public key ‘public.pem’ needs to be assigned to the user ‘GSHEETS_USR’:

USE ROLE USERADMIN;
ALTER USER GSHEETS_USR SET RSA_PUBLIC_KEY='MIIB......';

Google Sheets

We can now switch over to Google Sheets. Open a new sheet, and click on ‘Apps Script’ in the ‘Extensions’ menu.

Replace the code in the file ‘Code.gs’ with the code below:

Almost there, we need to change / verify a few things first:

• My demo account name is ‘sfseeurope-eu-demo58’, it is used in lines 23, 47 and 48. Replace the account name with your account name.
• This account resides in region ‘eu-central-1’. Replace this if your account is in another region (line 23)
• The SHA256 public key fingerprint in line 48, see below for instructions
• The private key in line 57. Further instructions below

Public Key fingerprint

In order to obtain your public key fingerprint run:

openssl pkey -pubin -in public.pem -outform der | openssl dgst -sha256 -binary | openssl enc -base64

or locate the fingerprint via the Snowflake user property ‘RSA_PUBLIC_KEY_FP’:

USE ROLE USERADMIN;
DESC USER GSHEETS_USR;

Private Key

The private key needs to be supplied in a single line. The generated key contains new line (\n) characters, so a direct copy-paste won’t work. You can use a text editor like Visual Studio Code to replace the newline characters with escaped new line characters (\\n) so that the entire key is stored in a single line:

or you can get the same result with awk:

awk 'NF {sub(/\r/, ""); printf "%s\\n",$0;} END {printf "\n\n"}' private_key.p8

Conclusion

Assuming everything went well, we go back to the Google Sheet and type ‘=getSnowData()’ into cell A1 to get a result similar to this:

NB: To illustrate that the data is immediately ready for processing in the spreadsheet I inserted into cell B13 the formula ‘=UNIQUE(B1:B10)’ and in cell C13 : ‘=COUNTIF(B1:B10,B13)’

Closing remarks

This is not an enterprise grade solution. In this example the private key and query are stored in the spreadsheet which makes this hard to maintain and share with coworkers. However, if you quickly need to get Snowflake data into a Google sheet this might help you.