Using dbplyr with SMRA

What is dbplyr?

dbplyr is an R package within the tidyverse set of packages. It provides a “tidyverse friendly” way of interacting with SQL databases – such as the SMRA database.

Why use dbplyr with the SMRA database?

SQL (Structured Query Language) is the language used internally by many databases. In SPSS code, a snippet of SQL is often used to extract a basefile of SMR episodes from the SMRA database; typically specifiying start and end dates, and some fields of interest. This basefile is then saved locally before being filtered and analysed by the rest of the SPSS code.

This pattern is easy to replicate in R, as below:

library(tidyverse)
smra <- suppressWarnings(odbc::dbConnect(odbc::odbc(),
                                         dsn = "SMRA",
                                         uid = "YOUR_USERNAME",
                                         pwd = rstudioapi::askForPassword("Database password: ")))

sql_query <- "select HBTREAT_CURRENTDATE, DISCHARGE_DATE, MAIN_OPERATION, OTHER_OPERATION_1, OTHER_OPERATION_2, OTHER_OPERATION_3 from SMR01_PI WHERE DISCHARGE_DATE >= TO_DATE('2010-01-01','YYYY-MM-DD') AND DISCHARGE_DATE <= TO_DATE('2019-01-01','YYYY-MM-DD')"

df_basefile <- as_tibble(odbc::dbGetQuery(smra, sql_query, n=-1))
write_rds(df_basefile, "basefile.rds")

# all episodes with at least one "F34" procedure
df <- df_basefile %>%  
  filter_at(vars(contains("OPERATION")), any_vars(str_sub(., 1, 3) == "F34"))

On the other hand, dbplyr allows a user to write code using the standard tidyverse commands they may already be familiar with e.g. filter,mutate, group_by, and summarise. dbplyr automatically translates these into an SQL query which is then used to query the database.

This means we can easily write code that queries the database, and returns data that has already been filtered and summarised in the way we want – see the example below.

library(tidyverse)
smra <- suppressWarnings(odbc::dbConnect(odbc::odbc(),
                                         dsn = "SMRA",
                                         uid = "YOUR_USERNAME",
                                         pwd = rstudioapi::askForPassword("Database password: ")))

df <- tbl(smra, "SMR01_PI") %>%
  filter(DISCHARGE_DATE >= TO_DATE('2010-01-01','YYYY-MM-DD'),
         DISCHARGE_DATE <= TO_DATE('2019-01-01','YYYY-MM-DD')) %>%
  select(HBTREAT_CURRENTDATE, DISCHARGE_DATE,
         MAIN_OPERATION, OTHER_OPERATION_1, OTHER_OPERATION_2, OTHER_OPERATION_3) %>%
  filter_at(vars(contains("OPERATION")), any_vars(str_sub(., 1, 3) == "F34")) %>%
  collect()

Here we use only tidyverse functions. dbplyr translates them into an SQL query: when the collect() function is called, the SQL query is submitted to the database and the resultant data retrieved.

In this example, the first method transfers some 14,084,977 rows from the database, while the dbplyr example transfers only the 41,862 rows we need. This means that desktop/laptop computers have to perform less large-scale filtering, disk space is saved, network bandwidth strain is reduced, and most importantly – time is saved. See here for more detailed benchmarking on the above example.

At any point we can use the show_query() function to reveal the SQL code that dbplyr has generated. Note that although no data is stored locally until the collect() command is used, we can still take a peek at objects before retrieving them.

df <- SMR01 %>%
  filter(DISCHARGE_DATE >= TO_DATE('2018-01-01','YYYY-MM-DD'),
         DISCHARGE_DATE < TO_DATE('2018-02-01','YYYY-MM-DD')) %>%
  select(URI, DISCHARGE_DATE, LINK_NO, CIS_MARKER)
head(df)

Setup

A typical dbplyr session would start as follows.

library(tidyverse)
smra <- suppressWarnings(odbc::dbConnect(odbc::odbc(),
                                         dsn = "SMRA",
                                         uid = "YOUR_USERNAME",
                                         pwd = rstudioapi::askForPassword("Database password: ")))

SMR01 <- tbl(smra, "SMR01_PI")

The dbplyr package can be loaded explicitly with library(dbplyr), but it is also loaded by default when using library(tidyverse).

Date Selection

df <- SMR01 %>%
  filter(DISCHARGE_DATE >= TO_DATE('2018-01-01','YYYY-MM-DD'),
         DISCHARGE_DATE < TO_DATE('2018-01-02','YYYY-MM-DD')) %>%
  select(DISCHARGE_DATE, LOCATION)

df <- collect(df)

SQL commands can be passed to the database within the typical tidyverse commands by using the sql() function. The following code will return data filtered according to a new “year” variable.

df <- SMR01 %>%
  mutate(year = sql('extract(year from DISCHARGE_DATE)')) %>%
  filter(year == 2019) %>%
  select(DISCHARGE_DATE, year, LOCATION)

df <- collect(df)

Select and Filter Multiple Fields

Here data is selected for patients over the age of 65 at the Royal Alexandra Hospital (RAH).

df <- SMR01 %>%
  filter(DISCHARGE_DATE >= TO_DATE('2018-01-01','YYYY-MM-DD'),
         DISCHARGE_DATE < TO_DATE('2019-01-01','YYYY-MM-DD'),
         AGE_IN_YEARS > 65,
         LOCATION == "C418H") %>%
  select(LOCATION, AGE_IN_YEARS)

df <- collect(df)

Note that the order in which filter() and select() are applied does matter. In the above example, if the select() statement appeared before the filter() statement, the filter() step would fail as it would be unable to find the field DISCHARGE_DATE.

Select Multiple Values

Here we filter for episodes based on whether a field has one of a number of values, e.g. whether the location is RAH or QEUH. R’s %in% operator can be used as follows to match the values.

hospitals_of_interest <- c("C418H", "G405H")

df <- SMR01 %>%
  filter(LOCATION %in% hospitals_of_interest)

df <- collect(df)

Excluding Missing Values

If we wanted to exlcude records with no recorded DATE_OF_MAIN_OPERATION from the above query, we could do the following.

df <- SMR01 %>%
  filter(!is.na(DATE_OF_MAIN_OPERATION)) %>%
  select(LOCATION, DATE_OF_MAIN_OPERATION)

df <- collect(df)

Here, R’s is.na() function is used to identify episodes where the DATE_OF_MAIN_OPERATION field is empty. The exclamation mark represents negation, so the net effect of !is.na() is to exclude episodes where DATE_OF_MAIN_OPERATION is missing.

Filtering for a Substring

Often we want to identify records that have a particular set of diagnosis or procedure codes. Here we can use the str_sub function from the tidyverse stringr package, which works similarly to the CHAR.SUBSTR function from SPSS.

conditions <- c("C51", "C52")

df <- SMR01 %>%
  filter(str_sub(MAIN_CONDITION, 1, 3) %in% conditions) %>%
  select(MAIN_CONDITION)

df <- collect(df)

Sorting Cases

This is accomplished using the arrange function. The following code arranges episodes in the order typically used when combining episodes into Continuous Inpatient Stays.

df <- SMR01 %>%
  arrange(LINK_NO, CIS_MARKER, ADMISSION_DATE, DISCHARGE_DATE, ADMISSION, DISCHARGE, URI) %>%
  select(LINK_NO, CIS_MARKER, ADMISSION_DATE, DISCHARGE_DATE, ADMISSION, DISCHARGE, URI)

df <- collect(df)

Combining Text Fields

We can use R’s paste function to combine text fields.

df <- SMR01 %>%
  mutate(boards = paste("HBtreat:", HBTREAT_CURRENTDATE, "HBres:", HBRES_CURRENTDATE)) %>%
  filter(HBTREAT_CURRENTDATE != HBRES_CURRENTDATE) %>% # show those with difference
  select(boards)

df <- collect(df)

Aggregating

The group_by and summarise functions are typically used to aggregate data. Functions like n(), sum() and mean() etc can be used within the summarise function, analagous to the method in SPSS.

df <- SMR01 %>%
  filter(str_sub(MAIN_OPERATION, 1, 3) == "F34") %>%
  mutate(year = sql('extract(year from DISCHARGE_DATE)')) %>%
  group_by(year) %>%
  summarise(total = n()) %>%
  arrange(year)

df <- collect(df)

Distinct Values

The distinct() function can be used to drop duplicate cases. To generate a list of all the distinct hospitals that performed an “F34” procedure as the MAIN_OPERATION in 2014, you could use the following code.

df <- SMR01 %>%
  filter(str_sub(MAIN_OPERATION, 1, 3) == "F34") %>%
  mutate(year = sql('extract(year from DISCHARGE_DATE)')) %>%
  filter(year == 2014) %>%
  select(LOCATION) %>%
  distinct()

df <- collect(df)

Code

transfer all

query <- "select HBTREAT_CURRENTDATE, DISCHARGE_DATE, MAIN_OPERATION, OTHER_OPERATION_1, OTHER_OPERATION_2, OTHER_OPERATION_3 from SMR01_PI WHERE DISCHARGE_DATE >= TO_DATE('2010-01-01','YYYY-MM-DD') AND DISCHARGE_DATE <= TO_DATE('2019-01-01','YYYY-MM-DD')"

df_basefile <- as_tibble(odbc::dbGetQuery(smra, query, n=-1))

df <- df_basefile %>%  
  filter_at(vars(contains("OPERATION")), any_vars(str_sub(., 1, 3) == "F34"))

dbplyr

df <- tbl(smra, "SMR01_PI") %>%
  filter(DISCHARGE_DATE >= TO_DATE('2010-01-01','YYYY-MM-DD'),
         DISCHARGE_DATE <= TO_DATE('2019-01-01','YYYY-MM-DD')) %>%
  select(HBTREAT_CURRENTDATE, DISCHARGE_DATE,
         MAIN_OPERATION, OTHER_OPERATION_1, OTHER_OPERATION_2, OTHER_OPERATION_3) %>%
  filter_at(vars(contains("OPERATION")), any_vars(str_sub(., 1, 3) == "F34")) %>%
  collect()

Time Comparison

The code for both methods was run twenty times each in three different user environments: R desktop (on the local NSS network), R desktop off-site (via VPN) and on the NSS RStudioServer. The bench package was used to collate the results.

As can be seen below, the dbplyr method gives consistently shorter run times, as well as more consistent run times in all three environments.

Typically the dbplyr code returns results in roughly 50 seconds, whereas the transfer all method takes 130 to 1,150 seconds depending on the environment and network performance.

Memory and Disk Usage Comparison

It is also worth considering the memory allocated and disk space used when using each method.

The figure below compares the file size of saved results, number of rows returned, respective object sizes in memory and the local memory allocated while running the respective code blocks.