Integrating Snowflake and Posit for enhanced analytics

Snowflake and Posit have partnered to combine Posit’s powerful, user-friendly tools for data science development and publishing with the Snowflake Data Cloud, which provides scalability, cloud-native architecture, and data security and compliance. We recently shared information on upcoming features that promise to elevate our partnership, particularly in how we enable Posit users to access Snowflake in ways that work best for them and their organization.

Snowpark Container Services (Public Preview) is a new, fully-managed runtime that extends Snowflake’s processing engine to provide developers the flexibility to register and deploy containerized software and applications in Snowflake-managed infrastructure. These services open the opportunity to run analyses natively without data ever leaving the cloud.

In the near future, users can work in their preferred Integrated Development Environments (IDE) inside Posit Workbench directly alongside their data, all within the security and governance boundaries of Snowflake:

Users can directly access their Snowflake data with the odbc package, which enables a connection to any database using an ODBC driver. The first step is to install the Snowflake ODBC driver. Once the driver is installed, you can then use odbc to connect to Snowflake from R:

# Install if not already installed
install.packages(c("DBI", "dplyr", "dbplyr", "odbc"))

# Load packages
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)

# Connect to database
con <- DBI::dbConnect(odbc::odbc(),
                      dsn = "Snowflake",
                      uid = "USERNAME",
                      pwd = "Snowflak123")
                      
# Query data
sales_dat <- DBI::dbGetQuery(con, "SELECT * FROM SALES_DATA_2019")
head(sales_dat)

Available ODBC drivers also appear in the RStudio Connections Pane, where users can select the desired driver:

Then, they can create a connection from their local R session:

The odbc package enables users to explore objects and fields in the database, read and write tables, and make queries. For more details, visit the Posit Solutions site.

For those who prefer a visual approach, the RStudio Connections Pane provides a view into the objects contained within a connection. This Pane allows users to:

• Navigate and explore available data by displaying an expandable list with the hierarchy of databases, schema, tables, and fields
• Preview the top 1,000 rows of a table by clicking on the icon to the right of the table’s name
• Close the database connection by clicking on the corresponding button inside the pane

Posit commercial customers are able to access Snowflake with Posit Professional Drivers. These drivers connect to some of the most popular databases so users can:

• Explore databases using supported environments in Posit Workbench
• Develop and deploy Shiny applications that depend on database connections
• Use databases with R and Python in a production environment

Once connected to a Snowflake database, RStudio users can analyze data with their favorite R functions. The dbplyr package translates dplyr verbs to SQL. Users can keep their entire codebase in R while executing queries throughout the database. This functionality enables users to keep data in the database as much as possible while still working with familiar tools in a local development environment.

# Load packages
library(DBI)
library(dplyr)
library(dbplyr)
library(odbc)
library(ggplot2)

# Connect to database
con <- dbConnect(odbc::odbc(),
                 dsn = "Snowflake",
                 uid = "USERNAME",
                 pwd = "Snowflak123")

# Search data
odbc::dbListTables(con, catalog = "PRO_DRIVERS", schema = "PUBLIC")

# Pull data into R
sales_dat <- tbl(con, "SALES_DATA_2019") |>
  collect()

# Visualize data using ggplot2
ggplot(sales_dat, aes(x = price)) +
  geom_histogram(bins = 10,
                 fill = "#00008B") +
  labs(title = "Price of Products Sold") +
  theme_minimal()

Future enhancements

The teams at Posit and Snowflake continue working to enrich the developer experience for Posit and Snowflake customers. The joint roadmap promises more exciting innovations, and we look forward to future advancements that will strengthen our partnership and enhance our users’ analytic capabilities.

Please schedule a call to chat with our Sales and Product teams to learn more about these upcoming features, and subscribe to the blog for product update notifications.