This guide will walk you through using R to analyze data in Snowflake using the Posit Workbench Native App. You'll learn how to launch the Posit Workbench Native App and use the available RStudio Pro IDE. You'll also learn how to use the {dbplyr} package to translate R code into SQL, allowing you to run data operations directly in Snowflake's high-performance computing environment.

We'll focus on a healthcare example by analyzing heart failure data. We'll then guide you through launching an R session, accessing the data, and performing data cleaning, transformation, and visualization. Finally, you'll see how to generate an HTML report, build an interactive Shiny app, and write data back to Snowflake—-completing an end-to-end R analysis entirely within Snowflake.

Along the way, you will use R to analyze which variables are associated with survival among patients with heart failure. You can follow along with this quickstart guide, or look at the materials provided in the accompanying repository: https://github.com/posit-dev/snowflake-posit-quickstart-r.

Before we begin there are a few components we need to prepare. We need to:

For this analysis, we'll use the Heart Failure Clinical Records dataset. The data is available for download as a CSV from the UCI Machine Learning Repository.

We'll walk through how to download the data from UCI and then upload it to Snowflake from a CSV.

Download the data from UCI here, and then unzip the downloaded file.

Log into Snowsight, then click Create > Add Data. You can find the Create button in the upper-left corner.

Choose the Load Data into a Table option, then select your downloaded heart failure CSV. Specify an existing database or create a new one for the heart failure data (we called ours HEART_FAILURE). Then, select + Create a new table and name it HEART_FAILURE.

Once your find the database, you can load it into your Snowflake account by clicking the Get button on the right-hand side.

You should now be able to see the heart failure data in Snowsight. Navigate to Data > Databases, then select the database to which you added the data (e.g., HEART_FAILURE). Expand the database, schema, and tables until you see the HEART_FAILURE table.

We can now start exploring the data using Posit Workbench. You can find Posit Workbench as a Snowflake Native Application and use it to connect to your database.

In your Snowflake account, Go to Catalog > Apps to open the Native Apps collection. If Posit Workbench is not already installed, click Browse all apps and search for Posit Workbench to find it. Please note that the Native App must be installed and configured by an administrator.

Once Posit Workbench is installed, click on the app under Installed Apps to launch the app. If you do not see the Posit Workbench app listed, ask your Snowflake account administrator for access to the app.

After clicking on the app, you will see a page with activation steps and a blue Activate button.

To setup Oauth, click on Connections tab, and then click on Configure in section titeld Snowflake OAuth Integration. You will then be prompted to enter your username and password along with instrucions to setup OAuth.

Finally, to launch the app click on Launch app. This should take you to the webpage generated for the Workbench application. You may be prompted to first login to Snowflake using your regular credentials or authentication method.

Posit Workbench provides several IDEs, such as RStudio Pro, JupyterLab, and VS Code. For this analysis we will use an RStudio Pro IDE.

Within Posit Workbench, click New Session to launch a new session to spin up your coding environment.

When prompted, select the RStudio Pro IDE.

Next, connect to your Snowflake account from within Posit Workbench. Under Credentials, click the button with the Snowflake icon to sign in to Snowflake. Follow the sign in prompts.

If sign-in fails: verify OAuth and network policy.

If the Snowflake sign-in in RStudio doesn’t complete (the Snowflake button doesn’t turn blue) or you see 403/blocked errors:

Try again: After updating OAuth and/or network policy, return to RStudio in Posit Workbench and click the Snowflake button to sign in.

Tip: Most sign-in issues are due to incomplete OAuth configuration or a restrictive network policy. Ask an admin to review both if you don’t have permissions.

When you're successfully signed into Snowflake, the Snowflake button will turn blue and there will be a checkmark in the upper-left corner.

Click Start Session to launch the RStudio Pro IDE.

Once everything is ready, you will be able to work with your Snowflake data in the familiar RStudio Pro IDE. Since the IDE is provided by the Posit Workbench Native App, your entire analysis will occur securely within Snowflake.

This Quickstart will step you through the analysis contained in https://github.com/posit-dev/snowflake-posit-quickstart-r/blob/main/quarto.qmd. To follow along, open the file in your RStudio Pro IDE. There are two ways to do this:

1. Simple copy-and-paste Go to File > New File > Quarto Document and then copy the contents of quarto.qmd into your new file.

2. Starting a new project linked to the GitHub repo. To do this:

   1. Go to File > New Project in the RStudio IDE menu bar.
   
   2. Select Version Control in the New Project Wizard
   
   3. Select Git
   
   4. Paste the URL of the GitHub repo and click Create Project
   

   RStudio will clone a local copy of the materials on GitHub. You can use the Files pane in the bottom right-hand corner of the IDE to navigate to quarto.qmd. Click on the file to open it.

Note: SSH authentication is not available in Snowpark Container Services, so when creating projects from Git, you may need to authenticate Git operations over HTTPS, using a username and password or a personal access token.

Now that we're in a familiar R environment, we need to prepare the packages we will use. For this analysis, we will use the Tidyverse suite of packages, as well as a few others.

After we install the packages, we load them.

We will run our code in the R environment created by the RStudio Pro IDE, but the code will use data stored in our database on Snowflake.

To access this data, we will use the {DBI} and {odbc} R packages to connect to the database. We will then use {dplyr} and {dbplyr} to query the data with R, without having to write raw SQL. Let's take a look at how this works.

{DBI} is an R package that allows us to connect to databases with DBI::dbConnect().

To connect to our Snowflake database, we will use a driver provided by the {odbc} package. We will need to provide a warehouse for compute, and a database to connect to. We can also provide a schema here to make connecting to specific tables and views easier.

We will save our connection as an object, named conn, which will make it easy to use.

Once we build a connection, we can see the databases, schemas, and tables available to us in the RStudio IDE Connections pane. Click on the database icon to the right of a database to see its schemas. Click on the schema icon to the right of a schema to see its tables. Click on the table icon to the right of a table to see a preview of the table.

Once we build a connection, we can use dplyr::tbl() to create tbls. A tbl is an R object that represents a table or view accessed through a connection.

We can now use heart_failure as if it was a tibble in R. For example, we can filter rows and select columns from our data.

When we use tbls, {dbplyr} translates our R code into SQL queries. Think of any object made downstream from a tbl like a view: it contains SQL that represents a table built from the tbl. If we want to see the SQL code that {dbplyr} generates, we can run dbplyr::show_query().

To save compute, R waits to execute a query until we request the data that it represents.

To save memory, R stores the data as a temporary file in the database (instead of in R's global environment). When we inspect the data, R only returns the first few rows of this file to display.

We can trigger R to execute a query and to return the entire result as a tibble with dbplyr::collect(). Keep in mind, that if your result is large, you may not want to collect it into R.

This system:

1. Keeps our data in the database, saving memory in the R session
2. Pushes computations to the database, saving compute in the R session
3. Evaluates queries lazily, saving compute in the database

We don't need to manage the process, it happens automatically behind the scenes.

Learn more about {dbplyr} at dbplyr.tidyverse.org

You can also use {DBI} to create a new table in a database or append to an existing table.

To add a new table, use DBI::dbWriteTable(). Note that value needs to be a data.frame or tibble, which is why we passed heart_failure_young_df, the result of calling collect() on heart_failure_young.

To append to an existing table, specify append = TRUE. If appending, the name argument should refer to an existing table.

Now that we understand how R will interact with the database, we can use R to perform our analysis.

We want to understand which variables in HEART_FAILURE are associated with survival of patients with heart failure.

First we convert the column names to lowercase, so we won't need to worry about capitalization.

For now, we'll focus on just patients younger than 50. We also reduce the data to just the columns we're interested in.

Our table now looks like this.

The heart failure data provides important insights that can help us:

Visualizing clinical variables across different patient groups can help identify patterns.

We can use {ggplot2} to visually compare sodium levels across different patient groups. In this plot, we see the distribution of serum sodium based on whether the patients have diabetes and whether they survived (0) or died (1) during the follow-up period.

We can continue exploring the heart failure dataset with visualizations or create a table that concisely displays multiple pieces of information at once. For example, we can use {dplyr} verbs to calculate the median values for various clinical metrics across different patient groups.

With {dplyr}'s group_by() command, we can compute separate metrics for each combination of death_event and diabetes.

This is a useful way to examine the information for ourselves. However, if we wish to share the information with others, we might prefer to present the table in a more polished format. We can do this with commands from R's {gt} package.

The following code creates a table displaying the information in comparison.

Now that we've accumulated some insights, let's think about how we might present the results of our analysis to our colleagues.

We've conveniently written our analysis in a Quarto (.qmd) document, quarto.qmd. Quarto is an open-source publishing system that makes it easy to create data products such as documents, presentations, dashboards, websites, and books.

By placing our work in a Quarto document, we have interwoven all of our code, results, output, and prose text into a single literate programming document. This way everything can travel together in a reproducible data product.

A Quarto document can be thought of as a regular markdown document, but with the ability to run code chunks.

You can run any of the code chunks by clicking the play button above the chunk in the RStudio Pro IDE.

You can render the entire document into a polished report to share, with the Render button.

This will run all the code in the document from top to bottom in a new R session, and generate an HTML file, by default, for you to view and share.

You can learn more about Quarto here: https://quarto.org/, and the documentation for all the various Quarto outputs here: https://quarto.org/docs/guide/. Quarto works with R, Python, and Javascript Observable code out-of-the box, and is a great tool to communicate your data science analyses.

One way to share our work and allow others to explore the heart failure dataset is to create an interactive Shiny app.

We've prepared an example Shiny app in the directory: https://github.com/posit-dev/snowflake-posit-quickstart-r. Our app allows the user to explore different clinical metrics in one place.

To run the app, open app.R and then click the Run App button at the top of the script in the RStudio Pro IDE.

If you face package missing errors like this: there is no package called ‘xyz’, please install the required package by running install.packages("xyz") in the R console.

Change the metric in the sidebar to control which metric is plotted.

You can learn more about Shiny at: https://shiny.posit.co/. This example uses Shiny for R, but Shiny for Python is also available!

If you're new to Shiny, you can try it online with shinylive. It too, comes in a Python version.

R is beloved by data scientists for its intuitive, concise syntax. You can now combine this syntax with the power and peace of mind of Snowflake. The Posit Workbench Native Application provides an IDE for R within Snowflake. You can then use R's existing database packages---{DBI}, {odbc}, {dbplyr}---to access your Snowflake databases.