Snowflake Integrations with R

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

Shiny

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)

ui <- page_sidebar(
  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")
    ),
  ),
)

server <- function(input, output, session) {

  # check if running on Posit Connect
  if (Sys.getenv("RSTUDIO_PRODUCT") == "CONNECT") {
    # initialize Connect API client
    client <- connect()
    # read the user-session-token header
    user_session_token <- session$request$HTTP_POSIT_CONNECT_USER_SESSION_TOKEN
    # grab the OAuth Integration access token using the session token
    credentials <- get_oauth_credentials(client, user_session_token)
    token <- credentials$access_token
  } else {
    # use a username and password when running locally 
    USER = Sys.getenv("SNOWFLAKE_USERNAME")
    PASS = Sys.getenv("SNOWFLAKE_PASSWORD")
  }

  WAREHOUSE <- Sys.getenv("SNOWFLAKE_WAREHOUSE")
  DB <- Sys.getenv("SNOWFLAKE_DATABASE")
  SCHEMA <- Sys.getenv("SNOWFLAKE_SCHEMA")
  ACCOUNT <- Sys.getenv("SNOWFLAKE_ACCOUNT")
  if (Sys.getenv("RSTUDIO_PRODUCT") != "CONNECT") {
    sfConn <- DBI::dbConnect(odbc::snowflake(), "Snowflake",
                           authenticator="OAUTH",
                           token = token,
                           warehouse = WAREHOUSE,
                           account = ACCOUNT,
                           database = DB,
                           schema = SCHEMA
  )
  } else {
    sfConn <- DBI::dbConnect(odbc::snowflake(), 
                             authenticator="OAUTH",
                             uid = USER,
                             pwd = PASS,
                 warehouse = WAREHOUSE,
                             account = ACCOUNT,
                             database = DB,
                             schema = SCHEMA
    )
  }
  
  output$snowflake <- renderText ({
    whoami <- (
      DBI::dbGetQuery(sfConn, '
        SELECT CURRENT_USER();
      ')
    )
    table = input$table
    query = paste0("SELECT TOP 100 * FROM ", table)
    results <- DBI::dbGetQuery(sfConn, query)
    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()"