Snowflake

Setup for Snowflake connection

Setting up a connection to your Snowflake data warehouse may require a login using Azure Active Directory (now Entra ID). This can be accomplished using an Azure access token method.

Creating the snowflake token uses the az_app_id_pbi_dataflow environment variable, please see the qiverse.azure package vignette for more information on how to set this up.

However, in order to use the token to connect to Snowflake, you have to install the Snowflake ODBC Driver, with further instructions on how to install and configure this available in the Snowflake documentation.

Some key settings for your Snowflake configuration dialog is as follows:

• User: [your Active Directory user name]

• Server: [xxxxx.australia-east.azure.snowflakecomputing.com]

• Database: [your main Database]

• Warehouse: [your Snowflake warehouse name]

• Role: [your main role here]

• Tracing(0-6): 4

• Authenticator: oauth

The other fields may be left blank, but test your connection to see if the above settings are correct. Consult the Snowflake documentation if you run into any issues.

For the qiverse.snowflake package, the function snowflake_con references one environmental variable:

• snowflake_server - “xxxxx.australia-east”

This is to be set to the first part of the server URL.

Connecting to Snowflake

Before running any queries on Snowflake, the connection to the database needs to be established. This can be done via the snowflake_con function.

# Create Snowflake azure token
library(qiverse.azure)
tk <- get_az_tk('sf')

# Establish connection to Snowflake
library(qiverse.snowflake)
con <- snowflake_con(
  server_name = 'exampleserver-australiaeast', 
=======
  token = tk
)

Using the token from get_az_tk, this is passed through to authenticate the connection to the Snowflake data warehouse based on the server_name argument.

Once connected, you are able to run any SQL code onto the Snowflake database. Please note that if your query result contains a large amount of data, this will all be downloaded to your R in-memory. This may cause some performance issues.

We can test our connection with the following code:

DBI::dbGetQuery(con, DBI::SQL("SELECT 1"))

A returned result indicates that the connection has been established.

library(tidyverse)

library(qiverse.azure) # https://github.com/AUS-DOH-Safety-and-Quality/qiverse/qiverse.azure
library(qiverse.snowflake) # https://github.com/AUS-DOH-Safety-and-Quality/qiverse/qiverse.snowflake

snowflake_query <- function(query_string) {
  # Create Snowflake azure token
  tk <- qiverse.azure::get_az_tk('sf')
  # Establish connection to Snowflake
  con <- snowflake_con(
    server_name = 'mysnowflakeserver.australia-east', 
    token = tk
  )
  # Set the Snowflake warehouse here
  DBI::dbGetQuery(con, DBI::SQL("USE WAREHOUSE SNOWFLAKE_WAREHOUSE_NAME"))
  # Run the query
  table <- DBI::dbGetQuery(con, DBI::SQL(query_string))
  # Return the results of the query
  return(table)
}

my_query <- "
  SELECT *
  FROM MY_TABLE
" 

snowflake_query(my_query)

Ingesting an R table held in-memory into Snowflake

This package also includes a function to assist with ingesting in-memory R data into your Snowflake data warehouse. The ingest_to_snowflake function converts your data into a parquet format, initialises an empty table into Snowflake, uploads the parquet format into a staging area and then overwrites the table in Snowflake with the data.

An example use of this function is as follows:

# Create Snowflake azure token
library(qiverse.azure)
tk <- get_az_tk('sf')

# Connect to Snowflake
library(qiverse.snowflake)
con <- snowflake_con(token = tk)

# Create temporary table
temp_data <- data.frame(Alpha = c('a', 'b', 'c'), Number = 1:3)

# Ingest Example To Snowflake
ingest_to_snowflake(
  data = temp_data,
  con = con,
  database_name = 'EXAMPLE_DATABASE',
  schema_name = 'EXAMPLE',
  table_name = 'TEST'
)