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."
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)
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.
library(RODBC) print(Sys.setenv(JHED = 'YourJHED')) #Put your JHED here print(Sys.setenv(JHED_PWD = rstudioapi::askForPassword(""))) server="ESMPMDBPR4.WIN.AD.JHU.EDU" db_name="CAMP_PMCoe_Projection" #Change this pwd = Sys.getenv("JHED_PWD") stopifnot(nzchar(pwd)) JHED = Sys.getenv("JHED") stopifnot(nzchar(pwd)) connectionString <- paste0("Driver=freeTDS;TDS_Version=8.0;",'Server=',server,';Port=1433;Database=', db_name,'; Uid=win\\',Sys.getenv("JHED"), ';Pwd=',Sys.getenv("JHED_PWD")) con <- odbcDriverConnect(connection=connectionString)
Error in library(RODBC): there is no package called 'RODBC' Traceback: 1. library(RODBC)
You can view a list of available tables with
You can put the contents of a table into a dataframe with
You can execute any SQL query and store the results in a dataframe with
See below for example code
#View the tables you can access tabs <- sqlTables(con) #Prints out list of tables; use tables w/ dbo schema View(tabs) #Put the contents of a table into a dataframe df <- sqlFetch(con, 'encounters') View(df) #Execute a query and put the results in a dataframe df2 <- sqlQuery(con, "SELECT TOP 20 * FROM dbo.encounters") View(df2) close(con) #close the connection when done
Error in sqlTables(con): could not find function "sqlTables" Traceback: