There are many ways to query data with R. This document shows you three of the most common ways to query data:

  1. Using DBI
  2. Using dplyr syntax
  3. Using R Notebooks

Database connection

Connect to your database. In this example, we connect to an Oracle by using the odbc package. This connection requires a database driver and a data source name (DSN) that have both been configured by the system administrator. Load the DBI package and the tidyverse.

library(DBI)
library(dplyr)
library(dbplyr)
con <- dbConnect(odbc::odbc(), "Oracle DB")

1. Query using DBI

You can query your data with DBI by using the dbGetQuery function. Simply paste your SQL code into the R function as a quoted string. This method is sometimes referred to as pass through SQL code, and is probably the simplest way to query your data. Care should be used to escape your quotes as needed. For example, 'yes' is written as \'yes\'.

dbGetQuery(con,'
  select "month_idx", "year", "month",
  sum(case when "term_deposit" = \'yes\' then 1.0 else 0.0 end) as subscribe,
  count(*) as total
  from "bank"
  group by "month_idx", "year", "month"
')

2. Query using dplyr syntax

You can write your code in dplyr syntax, and dplyr will translate your code into SQL. There are several benefits to writing queries in dplyr syntax: You can keep the same consistent language both for R objects and database tables; no knowledge of SQL or the specific SQL variant is required; and you can take advantage of the fact that dplyr is lazy in its evaluation. dplyr syntax is easy to read, but you can always inspect the SQL translation with the show_query function.

q1 <- tbl(con, "bank") %>%
  group_by(month_idx, year, month) %>%
  summarise(
    subscribe = sum(ifelse(term_deposit == "yes", 1, 0)),
    total = n())
show_query(q1)
<SQL>
SELECT "month_idx", "year", "month", SUM(CASE WHEN ("term_deposit" = 'yes') THEN (1.0) ELSE (0.0) END) AS "subscribe", COUNT(*) AS "total"
FROM ("bank") 
GROUP BY "month_idx", "year", "month"

3. Query using an R Notebooks

It is not well known that you can run SQL code in an R Notebook code chunk. To use SQL, open an R Notebook in the RStudio IDE under the File > New File menu. Start a new code chunk with {sql}, and specify your connection with the connection=con code chunk option. If you want to send the query output to an R dataframe use output.var = "mydataframe" in the code chunk options. When you specify output.var you will be able to use the output in subsequent R code chunks. In this example, we use the output in ggplot.

SELECT "month_idx", "year", "month", SUM(CASE WHEN ("term_deposit" = 'yes') THEN (1.0) ELSE (0.0) END) AS "subscribe",
COUNT(*) AS "total"
FROM ("bank") 
GROUP BY "month_idx", "year", "month"
library(ggplot2)
ggplot(mydataframe, aes(total, subscribe, color = year)) +
  geom_point() +
  xlab("Total contacts") +
  ylab("Term Deposit Subscriptions") +
  ggtitle("Contact volume")

The benefits to using SQL in a code chunk are that you can paste your SQL code without any modification. For example, you do not have to escape quotes. If you are using the proverbial spaghetti code that is hundreds of lines long, then a SQL code chunk might be a good option. Another benefit is that the SQL code in a code chunk is highlighted, making it very easy to read. For more information on SQL engines, see knitr language engines.

Summary

There is no single best way to query data with R. You have many methods to chose from and each method has its advantages. Here are some of the advantages using the methods described in this article.

Method Advantages
DBI::dbGetQuery - Fewer dependencies required
dplyr syntax - Use the same syntax for R and database objects
- No knowledge of SQL required
- Code is standard across SQL variants
- Lazy evaluation
R Notebook SQL engine - Copy and paste SQL – no formatting required
- SQL syntax is highlighted

You can download the R Notebook for these examples here.

LS0tCnRpdGxlOiBEYXRhYmFzZSBRdWVyaWVzIFdpdGggUgphdXRob3I6IE5hdGhhbiBTdGVwaGVucwpvdXRwdXQ6IGh0bWxfbm90ZWJvb2sKZGF0ZTogJzIwMTctMTAtMTgnCi0tLQoKVGhlcmUgYXJlIG1hbnkgd2F5cyB0byBxdWVyeSBkYXRhIHdpdGggUi4gVGhpcyBkb2N1bWVudCBzaG93cyB5b3UgdGhyZWUgb2YgdGhlIG1vc3QgY29tbW9uIHdheXMgdG8gcXVlcnkgZGF0YToKCjEuIFVzaW5nIERCSQoyLiBVc2luZyBkcGx5ciBzeW50YXgKMy4gVXNpbmcgUiBOb3RlYm9va3MKCiMjIyBEYXRhYmFzZSBjb25uZWN0aW9uCgpDb25uZWN0IHRvIHlvdXIgZGF0YWJhc2UuIEluIHRoaXMgZXhhbXBsZSwgd2UgY29ubmVjdCB0byBhbiBPcmFjbGUgYnkgdXNpbmcgdGhlIGBvZGJjYCBwYWNrYWdlLiBUaGlzIGNvbm5lY3Rpb24gcmVxdWlyZXMgYSBkYXRhYmFzZSBkcml2ZXIgYW5kIGEgZGF0YSBzb3VyY2UgbmFtZSAoRFNOKSB0aGF0IGhhdmUgYm90aCBiZWVuIGNvbmZpZ3VyZWQgYnkgdGhlIHN5c3RlbSBhZG1pbmlzdHJhdG9yLiBMb2FkIHRoZSBgREJJYCBwYWNrYWdlIGFuZCB0aGUgYHRpZHl2ZXJzZWAuCgpgYGB7ciwgbWVzc2FnZT1GQUxTRX0KbGlicmFyeShEQkkpCmxpYnJhcnkoZHBseXIpCmxpYnJhcnkoZGJwbHlyKQpjb24gPC0gZGJDb25uZWN0KG9kYmM6Om9kYmMoKSwgIk9yYWNsZSBEQiIpCmBgYAoKIyMjIDEuIFF1ZXJ5IHVzaW5nIERCSQoKWW91IGNhbiBxdWVyeSB5b3VyIGRhdGEgd2l0aCBgREJJYCBieSB1c2luZyB0aGUgYGRiR2V0UXVlcnlgIGZ1bmN0aW9uLiBTaW1wbHkgcGFzdGUgeW91ciBTUUwgY29kZSBpbnRvIHRoZSBSIGZ1bmN0aW9uIGFzIGEgcXVvdGVkIHN0cmluZy4gVGhpcyBtZXRob2QgaXMgc29tZXRpbWVzIHJlZmVycmVkIHRvIGFzICpwYXNzIHRocm91Z2ggU1FMIGNvZGUqLCBhbmQgaXMgcHJvYmFibHkgdGhlIHNpbXBsZXN0IHdheSB0byBxdWVyeSB5b3VyIGRhdGEuIENhcmUgc2hvdWxkIGJlIHVzZWQgdG8gZXNjYXBlIHlvdXIgcXVvdGVzIGFzIG5lZWRlZC4gRm9yIGV4YW1wbGUsIGAneWVzJ2AgaXMgd3JpdHRlbiBhcyBgXCd5ZXNcJ2AuCgpgYGB7cn0KZGJHZXRRdWVyeShjb24sJwogIHNlbGVjdCAibW9udGhfaWR4IiwgInllYXIiLCAibW9udGgiLAogIHN1bShjYXNlIHdoZW4gInRlcm1fZGVwb3NpdCIgPSBcJ3llc1wnIHRoZW4gMS4wIGVsc2UgMC4wIGVuZCkgYXMgc3Vic2NyaWJlLAogIGNvdW50KCopIGFzIHRvdGFsCiAgZnJvbSAiYmFuayIKICBncm91cCBieSAibW9udGhfaWR4IiwgInllYXIiLCAibW9udGgiCicpCmBgYAoKIyMjIDIuIFF1ZXJ5IHVzaW5nIGRwbHlyIHN5bnRheAoKWW91IGNhbiB3cml0ZSB5b3VyIGNvZGUgaW4gYGRwbHlyYCBzeW50YXgsIGFuZCBkcGx5ciB3aWxsIHRyYW5zbGF0ZSB5b3VyIGNvZGUgaW50byBTUUwuIFRoZXJlIGFyZSBzZXZlcmFsIGJlbmVmaXRzIHRvIHdyaXRpbmcgcXVlcmllcyBpbiBgZHBseXJgIHN5bnRheDogWW91IGNhbiBrZWVwIHRoZSBzYW1lIGNvbnNpc3RlbnQgbGFuZ3VhZ2UgYm90aCBmb3IgUiBvYmplY3RzIGFuZCBkYXRhYmFzZSB0YWJsZXM7IG5vIGtub3dsZWRnZSBvZiBTUUwgb3IgdGhlIHNwZWNpZmljIFNRTCB2YXJpYW50IGlzIHJlcXVpcmVkOyBhbmQgeW91IGNhbiB0YWtlIGFkdmFudGFnZSBvZiB0aGUgZmFjdCB0aGF0IGRwbHlyIGlzIGxhenkgaW4gaXRzIGV2YWx1YXRpb24uIGBkcGx5cmAgc3ludGF4IGlzIGVhc3kgdG8gcmVhZCwgYnV0IHlvdSBjYW4gYWx3YXlzIGluc3BlY3QgdGhlIFNRTCB0cmFuc2xhdGlvbiB3aXRoIHRoZSBgc2hvd19xdWVyeWAgZnVuY3Rpb24uCgpgYGB7cn0KcTEgPC0gdGJsKGNvbiwgImJhbmsiKSAlPiUKICBncm91cF9ieShtb250aF9pZHgsIHllYXIsIG1vbnRoKSAlPiUKICBzdW1tYXJpc2UoCiAgICBzdWJzY3JpYmUgPSBzdW0oaWZlbHNlKHRlcm1fZGVwb3NpdCA9PSAieWVzIiwgMSwgMCkpLAogICAgdG90YWwgPSBuKCkpCnNob3dfcXVlcnkocTEpCmBgYAoKIyMjIDMuIFF1ZXJ5IHVzaW5nIGFuIFIgTm90ZWJvb2tzCgpJdCBpcyBub3Qgd2VsbCBrbm93biB0aGF0IHlvdSBjYW4gcnVuIFNRTCBjb2RlIGluIGFuIFtSIE5vdGVib29rXShodHRwOi8vcm1hcmtkb3duLnJzdHVkaW8uY29tL3Jfbm90ZWJvb2tzLmh0bWwpIGNvZGUgY2h1bmsuIFRvIHVzZSBTUUwsIG9wZW4gYW4gW1IgTm90ZWJvb2tdKGh0dHA6Ly9ybWFya2Rvd24ucnN0dWRpby5jb20vcl9ub3RlYm9va3MuaHRtbCkgaW4gdGhlIFJTdHVkaW8gSURFIHVuZGVyIHRoZSBgRmlsZSA+IE5ldyBGaWxlYCBtZW51LiBTdGFydCBhIG5ldyBjb2RlIGNodW5rIHdpdGggYHtzcWx9YCwgYW5kIHNwZWNpZnkgeW91ciBjb25uZWN0aW9uIHdpdGggdGhlIGBjb25uZWN0aW9uPWNvbmAgY29kZSBjaHVuayBvcHRpb24uIElmIHlvdSB3YW50IHRvIHNlbmQgdGhlIHF1ZXJ5IG91dHB1dCB0byBhbiBSIGRhdGFmcmFtZSB1c2UgYG91dHB1dC52YXIgPSAibXlkYXRhZnJhbWUiYCBpbiB0aGUgY29kZSBjaHVuayBvcHRpb25zLiBXaGVuIHlvdSBzcGVjaWZ5IGBvdXRwdXQudmFyYCB5b3Ugd2lsbCBiZSBhYmxlIHRvIHVzZSB0aGUgb3V0cHV0IGluIHN1YnNlcXVlbnQgUiBjb2RlIGNodW5rcy4gSW4gdGhpcyBleGFtcGxlLCB3ZSB1c2UgdGhlIG91dHB1dCBpbiBnZ3Bsb3QuCgpgYGB7c3FsLCBjb25uZWN0aW9uPWNvbiwgb3V0cHV0LnZhciA9ICJteWRhdGFmcmFtZSJ9ClNFTEVDVCAibW9udGhfaWR4IiwgInllYXIiLCAibW9udGgiLCBTVU0oQ0FTRSBXSEVOICgidGVybV9kZXBvc2l0IiA9ICd5ZXMnKSBUSEVOICgxLjApIEVMU0UgKDAuMCkgRU5EKSBBUyAic3Vic2NyaWJlIiwKQ09VTlQoKikgQVMgInRvdGFsIgpGUk9NICgiYmFuayIpIApHUk9VUCBCWSAibW9udGhfaWR4IiwgInllYXIiLCAibW9udGgiCmBgYAoKCgpgYGB7cn0KbGlicmFyeShnZ3Bsb3QyKQpnZ3Bsb3QobXlkYXRhZnJhbWUsIGFlcyh0b3RhbCwgc3Vic2NyaWJlLCBjb2xvciA9IHllYXIpKSArCiAgZ2VvbV9wb2ludCgpICsKICB4bGFiKCJUb3RhbCBjb250YWN0cyIpICsKICB5bGFiKCJUZXJtIERlcG9zaXQgU3Vic2NyaXB0aW9ucyIpICsKICBnZ3RpdGxlKCJDb250YWN0IHZvbHVtZSIpCmBgYAoKVGhlIGJlbmVmaXRzIHRvIHVzaW5nIFNRTCBpbiBhIGNvZGUgY2h1bmsgYXJlIHRoYXQgeW91IGNhbiBwYXN0ZSB5b3VyIFNRTCBjb2RlIHdpdGhvdXQgYW55IG1vZGlmaWNhdGlvbi4gRm9yIGV4YW1wbGUsIHlvdSBkbyBub3QgaGF2ZSB0byBlc2NhcGUgcXVvdGVzLiBJZiB5b3UgYXJlIHVzaW5nIHRoZSBwcm92ZXJiaWFsICpzcGFnaGV0dGkgY29kZSogdGhhdCBpcyBodW5kcmVkcyBvZiBsaW5lcyBsb25nLCB0aGVuIGEgU1FMIGNvZGUgY2h1bmsgbWlnaHQgYmUgYSBnb29kIG9wdGlvbi4gQW5vdGhlciBiZW5lZml0IGlzIHRoYXQgdGhlIFNRTCBjb2RlIGluIGEgY29kZSBjaHVuayBpcyBoaWdobGlnaHRlZCwgbWFraW5nIGl0IHZlcnkgZWFzeSB0byByZWFkLiBGb3IgbW9yZSBpbmZvcm1hdGlvbiBvbiBTUUwgZW5naW5lcywgc2VlIFtrbml0ciBsYW5ndWFnZSBlbmdpbmVzXShodHRwOi8vcm1hcmtkb3duLnJzdHVkaW8uY29tL2F1dGhvcmluZ19rbml0cl9lbmdpbmVzLmh0bWwpLgoKIyMjIFN1bW1hcnkKClRoZXJlIGlzIG5vIHNpbmdsZSBiZXN0IHdheSB0byBxdWVyeSBkYXRhIHdpdGggUi4gWW91IGhhdmUgbWFueSBtZXRob2RzIHRvIGNob3NlIGZyb20gYW5kIGVhY2ggbWV0aG9kIGhhcyBpdHMgYWR2YW50YWdlcy4gSGVyZSBhcmUgc29tZSBvZiB0aGUgYWR2YW50YWdlcyB1c2luZyB0aGUgbWV0aG9kcyBkZXNjcmliZWQgaW4gdGhpcyBhcnRpY2xlLgoKTWV0aG9kICAgICAgICAgICAgICAgIHwgQWR2YW50YWdlcwotLS0tLS0tLS0tLS0tLS0tLS0tLS0tfC0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLS0tLQpEQkk6OmRiR2V0UXVlcnkgICAgICAgfCAtIEZld2VyIGRlcGVuZGVuY2llcyByZXF1aXJlZApkcGx5ciBzeW50YXggICAgICAgICAgfCAtIFVzZSB0aGUgc2FtZSBzeW50YXggZm9yIFIgYW5kIGRhdGFiYXNlIG9iamVjdHNcCiAgICAgICAgICAgICAgICAgICAgICAgIC0gTm8ga25vd2xlZGdlIG9mIFNRTCByZXF1aXJlZFwKICAgICAgICAgICAgICAgICAgICAgICAgLSBDb2RlIGlzIHN0YW5kYXJkIGFjcm9zcyBTUUwgdmFyaWFudHNcCiAgICAgICAgICAgICAgICAgICAgICAgIC0gTGF6eSBldmFsdWF0aW9uClIgTm90ZWJvb2sgU1FMIGVuZ2luZSB8IC0gQ29weSBhbmQgcGFzdGUgU1FMIC0tIG5vIGZvcm1hdHRpbmcgcmVxdWlyZWRcCiAgICAgICAgICAgICAgICAgICAgICAgIC0gU1FMIHN5bnRheCBpcyBoaWdobGlnaHRlZAoKKllvdSBjYW4gZG93bmxvYWQgdGhlIFIgTm90ZWJvb2sgZm9yIHRoZXNlIGV4YW1wbGVzIFtoZXJlXShodHRwOi8vcnB1YnMuY29tL253c3RlcGhlbnMvMzE4NTg2KS4qCg==