Snowflake Integrations with R
All of the content listed below operates under the assumption that all of the necessary setup in the Snowflake section of the OAuth Integrations Admin Guide has already been completed.
Problem
You are building an interactive Python application which needs to act as the person visiting the application when accessing a private Snowflake resource. The content must authenticate to Snowflake using the viewer’s Snowflake credentials. For example, reading data from a Snowflake table that has row-level permissions and each content viewer has a different level of data access.
Solution
Using the Snowflake OAuth Integration you are able to authenticate with Snowflake as the logged-in user. The example application queries the supplied Snowflake database, returning the top one-hundred results from the table provided in the sidebar. When checking login history within Snowflake the successful authentication attempts are logged as having come from the logged-in user and you, the content owner.
When publishing the content to Connect make sure the following environment variables are set for the deployed content:
SNOWFLAKE_ACCOUNT
SNOWFLAKE_WAREHOUSE
SNOWFLAKE_SCHEMA
SNOWFLAKE_TABLE
When running locally you will need to set your Snowflake username and password via the following environment variables:
SNOWFLAKE_USERNAME
SNOWFLAKE_PASSWORD
renv.lock
{
"R": {
"Version": "4.4.1",
"Repositories": [
{
"Name": "CRAN",
"URL": "https://cran.rstudio.com"
}
]
},
"Packages": {
"DBI": {
"Package": "DBI",
"Version": "1.2.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"methods"
],
"Hash": "065ae649b05f1ff66bb0c793107508f5"
},
"PKI": {
"Package": "PKI",
"Version": "0.1-14",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"base64enc"
],
"Hash": "f5b9c6b2f62f1fa3dd53fd1ddccbb241"
},
"R6": {
"Package": "R6",
"Version": "2.5.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "470851b6d5d0ac559e9d01bb352b4021"
},
"Rcpp": {
"Package": "Rcpp",
"Version": "1.0.13",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"methods",
"utils"
],
"Hash": "f27411eb6d9c3dada5edd444b8416675"
},
"askpass": {
"Package": "askpass",
"Version": "1.2.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"sys"
],
"Hash": "cad6cf7f1d5f6e906700b9d3e718c796"
},
"base64enc": {
"Package": "base64enc",
"Version": "0.1-3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "543776ae6848fde2f48ff3816d0628bc"
},
"bit": {
"Package": "bit",
"Version": "4.5.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "5dc7b2677d65d0e874fc4aaf0e879987"
},
"bit64": {
"Package": "bit64",
"Version": "4.5.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"bit",
"methods",
"stats",
"utils"
],
"Hash": "e84984bf5f12a18628d9a02322128dfd"
},
"blob": {
"Package": "blob",
"Version": "1.2.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"methods",
"rlang",
"vctrs"
],
"Hash": "40415719b5a479b87949f3aa0aee737c"
},
"bslib": {
"Package": "bslib",
"Version": "0.8.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"base64enc",
"cachem",
"fastmap",
"grDevices",
"htmltools",
"jquerylib",
"jsonlite",
"lifecycle",
"memoise",
"mime",
"rlang",
"sass"
],
"Hash": "b299c6741ca9746fb227debcb0f9fb6c"
},
"cachem": {
"Package": "cachem",
"Version": "1.1.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"fastmap",
"rlang"
],
"Hash": "cd9a672193789068eb5a2aad65a0dedf"
},
"cli": {
"Package": "cli",
"Version": "3.6.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"utils"
],
"Hash": "b21916dd77a27642b447374a5d30ecf3"
},
"commonmark": {
"Package": "commonmark",
"Version": "1.9.1",
"Source": "Repository",
"Repository": "CRAN",
"Hash": "5d8225445acb167abf7797de48b2ee3c"
},
"connectapi": {
"Package": "connectapi",
"Version": "0.3.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R6",
"bit64",
"fs",
"glue",
"httr",
"jsonlite",
"lifecycle",
"magrittr",
"purrr",
"rlang",
"tibble",
"uuid",
"vctrs"
],
"Hash": "f5206247a25db881071783261b837afb"
},
"crayon": {
"Package": "crayon",
"Version": "1.5.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"grDevices",
"methods",
"utils"
],
"Hash": "859d96e65ef198fd43e82b9628d593ef"
},
"curl": {
"Package": "curl",
"Version": "5.2.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "8f27335f2bcff4d6035edcc82d7d46de"
},
"digest": {
"Package": "digest",
"Version": "0.6.37",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"utils"
],
"Hash": "33698c4b3127fc9f506654607fb73676"
},
"dplyr": {
"Package": "dplyr",
"Version": "1.1.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"R6",
"cli",
"generics",
"glue",
"lifecycle",
"magrittr",
"methods",
"pillar",
"rlang",
"tibble",
"tidyselect",
"utils",
"vctrs"
],
"Hash": "fedd9d00c2944ff00a0e2696ccf048ec"
},
"fansi": {
"Package": "fansi",
"Version": "1.0.6",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"grDevices",
"utils"
],
"Hash": "962174cf2aeb5b9eea581522286a911f"
},
"fastmap": {
"Package": "fastmap",
"Version": "1.2.0",
"Source": "Repository",
"Repository": "CRAN",
"Hash": "aa5e1cd11c2d15497494c5292d7ffcc8"
},
"fontawesome": {
"Package": "fontawesome",
"Version": "0.5.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"htmltools",
"rlang"
],
"Hash": "c2efdd5f0bcd1ea861c2d4e2a883a67d"
},
"fs": {
"Package": "fs",
"Version": "1.6.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"methods"
],
"Hash": "15aeb8c27f5ea5161f9f6a641fafd93a"
},
"generics": {
"Package": "generics",
"Version": "0.1.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"methods"
],
"Hash": "15e9634c0fcd294799e9b2e929ed1b86"
},
"glue": {
"Package": "glue",
"Version": "1.7.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"methods"
],
"Hash": "e0b3a53876554bd45879e596cdb10a52"
},
"hms": {
"Package": "hms",
"Version": "1.1.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"lifecycle",
"methods",
"pkgconfig",
"rlang",
"vctrs"
],
"Hash": "b59377caa7ed00fa41808342002138f9"
},
"htmltools": {
"Package": "htmltools",
"Version": "0.5.8.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"base64enc",
"digest",
"fastmap",
"grDevices",
"rlang",
"utils"
],
"Hash": "81d371a9cc60640e74e4ab6ac46dcedc"
},
"httpuv": {
"Package": "httpuv",
"Version": "1.6.15",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"R6",
"Rcpp",
"later",
"promises",
"utils"
],
"Hash": "d55aa087c47a63ead0f6fc10f8fa1ee0"
},
"httr": {
"Package": "httr",
"Version": "1.4.7",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"R6",
"curl",
"jsonlite",
"mime",
"openssl"
],
"Hash": "ac107251d9d9fd72f0ca8049988f1d7f"
},
"jquerylib": {
"Package": "jquerylib",
"Version": "0.1.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"htmltools"
],
"Hash": "5aab57a3bd297eee1c1d862735972182"
},
"jsonlite": {
"Package": "jsonlite",
"Version": "1.8.8",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"methods"
],
"Hash": "e1b9c55281c5adc4dd113652d9e26768"
},
"later": {
"Package": "later",
"Version": "1.3.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"Rcpp",
"rlang"
],
"Hash": "a3e051d405326b8b0012377434c62b37"
},
"lifecycle": {
"Package": "lifecycle",
"Version": "1.0.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"cli",
"glue",
"rlang"
],
"Hash": "b8552d117e1b808b09a832f589b79035"
},
"magrittr": {
"Package": "magrittr",
"Version": "2.0.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "7ce2733a9826b3aeb1775d56fd305472"
},
"memoise": {
"Package": "memoise",
"Version": "2.0.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"cachem",
"rlang"
],
"Hash": "e2817ccf4a065c5d9d7f2cfbe7c1d78c"
},
"mime": {
"Package": "mime",
"Version": "0.12",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"tools"
],
"Hash": "18e9c28c1d3ca1560ce30658b22ce104"
},
"odbc": {
"Package": "odbc",
"Version": "1.5.0.9000",
"Source": "GitHub",
"RemoteType": "github",
"RemoteHost": "api.github.com",
"RemoteRepo": "odbc",
"RemoteUsername": "r-dbi",
"RemoteRef": "HEAD",
"RemoteSha": "5a858bac2c9ef61e3d995ab82643d97aedad73e3",
"Requirements": [
"DBI",
"R",
"Rcpp",
"bit64",
"blob",
"cli",
"hms",
"lifecycle",
"methods",
"rlang"
],
"Hash": "29cdb5639e9a79e31360e6c6d4135a7d"
},
"openssl": {
"Package": "openssl",
"Version": "2.2.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"askpass"
],
"Hash": "c62edf62de70cadf40553e10c739049d"
},
"packrat": {
"Package": "packrat",
"Version": "0.9.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"tools",
"utils"
],
"Hash": "55ddd2d4a1959535f18393478b0c14a6"
},
"pillar": {
"Package": "pillar",
"Version": "1.9.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"cli",
"fansi",
"glue",
"lifecycle",
"rlang",
"utf8",
"utils",
"vctrs"
],
"Hash": "15da5a8412f317beeee6175fbc76f4bb"
},
"pkgconfig": {
"Package": "pkgconfig",
"Version": "2.0.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"utils"
],
"Hash": "01f28d4278f15c76cddbea05899c5d6f"
},
"promises": {
"Package": "promises",
"Version": "1.3.0",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R6",
"Rcpp",
"fastmap",
"later",
"magrittr",
"rlang",
"stats"
],
"Hash": "434cd5388a3979e74be5c219bcd6e77d"
},
"purrr": {
"Package": "purrr",
"Version": "1.0.2",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"cli",
"lifecycle",
"magrittr",
"rlang",
"vctrs"
],
"Hash": "1cba04a4e9414bdefc9dcaa99649a8dc"
},
"rappdirs": {
"Package": "rappdirs",
"Version": "0.3.3",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "5e3c5dc0b071b21fa128676560dbe94d"
},
"renv": {
"Package": "renv",
"Version": "1.0.7",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"utils"
],
"Hash": "397b7b2a265bc5a7a06852524dabae20"
},
"rlang": {
"Package": "rlang",
"Version": "1.1.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"utils"
],
"Hash": "3eec01f8b1dee337674b2e34ab1f9bc1"
},
"rsconnect": {
"Package": "rsconnect",
"Version": "1.3.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"PKI",
"R",
"cli",
"curl",
"digest",
"jsonlite",
"lifecycle",
"openssl",
"packrat",
"renv",
"rlang",
"rstudioapi",
"tools",
"yaml"
],
"Hash": "90dc9ac04cec50f25657b077d4aaca57"
},
"rstudioapi": {
"Package": "rstudioapi",
"Version": "0.16.0",
"Source": "Repository",
"Repository": "CRAN",
"Hash": "96710351d642b70e8f02ddeb237c46a7"
},
"sass": {
"Package": "sass",
"Version": "0.4.9",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R6",
"fs",
"htmltools",
"rappdirs",
"rlang"
],
"Hash": "d53dbfddf695303ea4ad66f86e99b95d"
},
"shiny": {
"Package": "shiny",
"Version": "1.9.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"R6",
"bslib",
"cachem",
"commonmark",
"crayon",
"fastmap",
"fontawesome",
"glue",
"grDevices",
"htmltools",
"httpuv",
"jsonlite",
"later",
"lifecycle",
"methods",
"mime",
"promises",
"rlang",
"sourcetools",
"tools",
"utils",
"withr",
"xtable"
],
"Hash": "6a293995a66e12c48d13aa1f957d09c7"
},
"sourcetools": {
"Package": "sourcetools",
"Version": "0.1.7-1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "5f5a7629f956619d519205ec475fe647"
},
"sys": {
"Package": "sys",
"Version": "3.4.2",
"Source": "Repository",
"Repository": "CRAN",
"Hash": "3a1be13d68d47a8cd0bfd74739ca1555"
},
"tibble": {
"Package": "tibble",
"Version": "3.2.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"fansi",
"lifecycle",
"magrittr",
"methods",
"pillar",
"pkgconfig",
"rlang",
"utils",
"vctrs"
],
"Hash": "a84e2cc86d07289b3b6f5069df7a004c"
},
"tidyselect": {
"Package": "tidyselect",
"Version": "1.2.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"cli",
"glue",
"lifecycle",
"rlang",
"vctrs",
"withr"
],
"Hash": "829f27b9c4919c16b593794a6344d6c0"
},
"utf8": {
"Package": "utf8",
"Version": "1.2.4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "62b65c52671e6665f803ff02954446e9"
},
"uuid": {
"Package": "uuid",
"Version": "1.2-1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R"
],
"Hash": "34e965e62a41fcafb1ca60e9b142085b"
},
"vctrs": {
"Package": "vctrs",
"Version": "0.6.5",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"cli",
"glue",
"lifecycle",
"rlang"
],
"Hash": "c03fa420630029418f7e6da3667aac4a"
},
"withr": {
"Package": "withr",
"Version": "3.0.1",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"grDevices",
"graphics"
],
"Hash": "07909200e8bbe90426fbfeb73e1e27aa"
},
"xtable": {
"Package": "xtable",
"Version": "1.8-4",
"Source": "Repository",
"Repository": "CRAN",
"Requirements": [
"R",
"stats",
"utils"
],
"Hash": "b8acdf8af494d9ec19ccb2481a9b11c2"
},
"yaml": {
"Package": "yaml",
"Version": "2.3.10",
"Source": "Repository",
"Repository": "CRAN",
"Hash": "51dab85c6c98e50a18d7551e9d49f76c"
}
}
}
app.R
library(shiny)
library(bslib)
library(DBI)
library(odbc)
library(connectapi)
library(dplyr)
<- page_sidebar(
ui title = "Snowflake",
sidebar = sidebar(
title = "Snowflake",
textInput("table", "Table", value = Sys.getenv("SNOWFLAKE_TABLE"), placeholder = Sys.getenv("SNOWFLAKE_TABLE"))
),layout_columns(
card(
card_header("snowflake"),
verbatimTextOutput("snowflake")
),
),
)
<- function(input, output, session) {
server
# check if running on Posit Connect
# note: use RSTUDIO_PRODUCT (deprecated) for Connect versions < 2025.02.0
if (Sys.getenv("POSIT_PRODUCT") == "CONNECT") {
# initialize Connect API client
<- connect()
client # read the user-session-token header
<- session$request$HTTP_POSIT_CONNECT_USER_SESSION_TOKEN
user_session_token # grab the OAuth Integration access token using the session token
<- get_oauth_credentials(client, user_session_token)
credentials <- credentials$access_token
token else {
} # use a username and password when running locally
= Sys.getenv("SNOWFLAKE_USERNAME")
USER = Sys.getenv("SNOWFLAKE_PASSWORD")
PASS
}
<- Sys.getenv("SNOWFLAKE_WAREHOUSE")
WAREHOUSE <- Sys.getenv("SNOWFLAKE_DATABASE")
DB <- Sys.getenv("SNOWFLAKE_SCHEMA")
SCHEMA <- Sys.getenv("SNOWFLAKE_ACCOUNT")
ACCOUNT # note: use RSTUDIO_PRODUCT (deprecated) for Connect versions < 2025.02.0
if (Sys.getenv("POSIT_PRODUCT") == "CONNECT") {
<- DBI::dbConnect(odbc::snowflake(), "Snowflake",
sfConn authenticator="OAUTH",
token = token,
warehouse = WAREHOUSE,
account = ACCOUNT,
database = DB,
schema = SCHEMA
)else {
} <- DBI::dbConnect(odbc::snowflake(),
sfConn authenticator="OAUTH",
uid = USER,
pwd = PASS,
warehouse = WAREHOUSE,
account = ACCOUNT,
database = DB,
schema = SCHEMA
)
}
$snowflake <- renderText ({
output<- (
whoami ::dbGetQuery(sfConn, '
DBI SELECT CURRENT_USER();
')
)= input$table
table = paste0("SELECT TOP 100 * FROM ", table)
query <- DBI::dbGetQuery(sfConn, query)
results paste0("Hello, ", whoami, ". Your results are ", results)
})
}
shinyApp(ui, server)
Running the app locally
Terminal
export SNOWFLAKE_ACCOUNT="<snowflake-account-identifier>"
export SNOWFLAKE_WAREHOUSE="<snowflake-warehouse-name>"
export SNOWFLAKE_SCHEMA="<snowflake-schema-name>"
export SNOWFLAKE_TABLE="<snowflake-table-to-return-results-from>"
# SNOWFLAKE_USERNAME and SNOWFLAKE_PASSWORD are only required when
# running locally.
SNOWFLAKE_USERNAME=<username> SNOWFLAKE_PASSWORD=<password> R -e "shiny::runApp()"