Reading data from Snowflake into Google Sheets

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.

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:

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

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:

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

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

In order to obtain your public key fingerprint run:

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

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:

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)’

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.

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store