How to Do Queries in RStudio Workbench

You can do all your SQL queries on the BioSense Platform by using RStudio Workbench. Here are some easy steps to follow and code examples for querying data on the BioSense Platform’s Datamart.

First, log into your RStudio Workbench account using your Access & Management Center (AMC) username and password.

Next, install and load the packages, also called libraries, that you need. These tell RStudio Workbench how to connect to the Analytic Datamart. To do this, copy and paste the next two lines into your code.

library(DBI)

library(odbc)

To run pieces of code, you can highlight the code in your R script and use Ctrl + Enter or use the Run button:

Now you can connect to the Analytic Datamart. This step uses the username and password that you’re already using to connect to RStudio Workbench, so there’s no need to re-enter them. You can even store this connection as a variable to reuse.

Copy and paste the line below to store a database connection directly to the BioSense Platform database on the Analytic Datamart.

datamart <- dbConnect(odbc::odbc(), dsn = “BioSense_Platform”)

You can confirm that you’re connected to the datamart by looking at the Environment pane on the right side of your screen.

The last step is to write your query and send it to the database. To do this, copy and paste the code below to R:

table <- dbGetQuery(datamart, “SELECT * FROM Except_Reasons”))

This example pulls information from the Except Reasons table, but you can insert any query you’d like inside the quotation marks. Once the query is complete, the table of query results will appear in your Environment pane.

And that’s it! The code shown below is enough to get you started reviewing your data in RStudio Workbench. After you copy and paste this code into RStudio, delete and retype all double-quotes (“).

library(DBI)

library(odbc)

datamart <- dbConnect(odbc::odbc(), dsn = “BioSense_Platform”)

table <- dbGetQuery(datamart, “SELECT * FROM Except_Reasons”)

write.csv(table, file =”TableName.csv”, row.names = FALSE)

The write.csv command will write the results to a .csv file that can be exported and opened as an Excel file.

TIP: After you copy and paste this code into RStudio, delete and retype all double-quotes (“).

Visit NSSP’s Onboarding page to see sample queries and learn how to use RStudio Workbench on the BioSense Platform. More questions? Contact nssp@cdc.gov.

Happy querying!