Connecting to a Database Using R

Table of Contents

Setting Up the Container

The steps below will go over how to set up a container in crunchr. Make sure you are in SAFE before proceeding.

Step One: Accessing Crunchr

On the SAFE desktop, click on the "PMAP Tools" folder, and then click on "PMAP crunchr Analytics"

Step Two: Creating a Container

On the crunchr homepage, click on "Compute," and then click "Create Container." You can name your container whatever you would like. the Domain does not need to change. For "Compute Image," select "PMAP RStudio" from the drop-down. then click "Create."

Connecting to the Database

Once you open your new container, you should see an Rstudio interface. The following steps will show you what code to run in Rstudio. You can copy and paste the code from here to your RStudio console. Just make sure to make any necessary changes (database name, your jhed, etc)

Setting up the Connection

The following code establishes a connection with the CAMP_PMCoe_Projection database, for demonstration purposes. Make sure to confirm the name of your database and update the field accordingly. Also note it may not run right here in this notebook, but it should run fine after copying and pasting it into the RStudio environment.

Using SQL

You can view a list of available tables with


You can put the contents of a table into a dataframe with

sqlFetch(con, 'TableName')

You can execute any SQL query and store the results in a dataframe with

sqlQuery(con, "Query")

See below for example code

Additional Resources

RODBC Documentation