Introduction to dbplyr

Getting started

To use databases with dplyr you need to first install dbplyr:

install.packages("dbplyr")

You’ll also need to install a DBI backend package. The DBI package provides a common interface that allows dplyr to work with many different databases using the same code. DBI is automatically installed with dbplyr, but you need to install a specific backend for the database that you want to connect to.

Five commonly used backends are:

• RMySQL connects to MySQL and MariaDB

• RPostgreSQL connects to Postgres and Redshift.

• RSQLite embeds a SQLite database.

• odbc connects to many commercial databases via the open database connectivity protocol.

• bigrquery connects to Google’s BigQuery.

If the database you need to connect to is not listed here, you’ll need to do some investigation (i.e. googling) yourself.

In this vignette, we’re going to use the RSQLite backend which is automatically installed when you install dbplyr. SQLite is a great way to get started with databases because it’s completely embedded inside an R package. Unlike most other systems, you don’t need to setup a separate database server. SQLite is great for demos, but is surprisingly powerful, and with a little practice you can use it to easily work with many gigabytes of data.

Connecting to the database

To work with a database in dplyr, you must first connect to it, using DBI::dbConnect(). We’re not going to go into the details of the DBI package here, but it’s the foundation upon which dbplyr is built. You’ll need to learn more about if you need to do things to the database that are beyond the scope of dplyr.

library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), path = ":memory:")

The arguments to DBI::dbConnect() vary from database to database, but the first argument is always the database backend. It’s RSQLite::SQLite() for RSQLite, RMySQL::MySQL() for RMySQL, RPostgreSQL::PostgreSQL() for RPostgreSQL, odbc::odbc() for odbc, and bigrquery::bigquery() for BigQuery. SQLite only needs one other argument: the path to the database. Here we use the special string ":memory:" which causes SQLite to make a temporary in-memory database.

Most existing databases don’t live in a file, but instead live on another server. That means in real-life that your code will look more like this:

con <- DBI::dbConnect(RMySQL::MySQL(), 
  host = "database.rstudio.com",
  user = "hadley",
  password = rstudioapi::askForPassword("Database password")
)

(If you’re not using RStudio, you’ll need some other way to securely retrieve your password. You should never record it in your analysis scripts or type it into the console. Securing Credentials provides some best practices.)

Our temporary database has no data in it, so we’ll start by copying over nycflights13::flights using the convenient copy_to() function. This is a quick and dirty way of getting data into a database and is useful primarily for demos and other small jobs.

copy_to(con, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

As you can see, the copy_to() operation has an additional argument that allows you to supply indexes for the table. Here we set up indexes that will allow us to quickly process the data by day, carrier, plane, and destination. Creating the right indices is key to good database performance, but is unfortunately beyond the scope of this article.

Now that we’ve copied the data, we can use tbl() to take a reference to it:

flights_db <- tbl(con, "flights")

When you print it out, you’ll notice that it mostly looks like a regular tibble:

flights_db 
#> # Source:   table<flights> [?? x 19]
#> # Database: sqlite 3.19.3 []
#>    year month   day dep_time sched_dep_time dep_delay arr_time
#>   <int> <int> <int>    <int>          <int>     <dbl>    <int>
#> 1  2013     1     1      517            515      2.00      830
#> 2  2013     1     1      533            529      4.00      850
#> 3  2013     1     1      542            540      2.00      923
#> 4  2013     1     1      544            545     -1.00     1004
#> 5  2013     1     1      554            600     -6.00      812
#> 6  2013     1     1      554            558     -4.00      740
#> # ... with more rows, and 12 more variables: sched_arr_time <int>,
#> #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
#> #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
#> #   minute <dbl>, time_hour <dbl>

The main difference is that you can see that it’s a remote source in a SQLite database.

Generating queries

To interact with a database you usually use SQL, the Structured Query Language. SQL is over 40 years old, and is used by pretty much every database in existence. The goal of dbplyr is to automatically generate SQL for you so that you’re not forced to use it. However, SQL is a very large language and dbplyr doesn’t do everything. It focusses on SELECT statements, the SQL you write most often as an analyst.

Most of the time you don’t need to know anything about SQL, and you can continue to use the dplyr verbs that you’re already familiar with:

flights_db %>% select(year:day, dep_delay, arr_delay)
#> # Source:   lazy query [?? x 5]
#> # Database: sqlite 3.19.3 []
#>    year month   day dep_delay arr_delay
#>   <int> <int> <int>     <dbl>     <dbl>
#> 1  2013     1     1      2.00      11.0
#> 2  2013     1     1      4.00      20.0
#> 3  2013     1     1      2.00      33.0
#> 4  2013     1     1     -1.00     -18.0
#> 5  2013     1     1     -6.00     -25.0
#> 6  2013     1     1     -4.00      12.0
#> # ... with more rows

flights_db %>% filter(dep_delay > 240)
#> # Source:   lazy query [?? x 19]
#> # Database: sqlite 3.19.3 []
#>    year month   day dep_time sched_dep_time dep_delay arr_time
#>   <int> <int> <int>    <int>          <int>     <dbl>    <int>
#> 1  2013     1     1      848           1835       853     1001
#> 2  2013     1     1     1815           1325       290     2120
#> 3  2013     1     1     1842           1422       260     1958
#> 4  2013     1     1     2115           1700       255     2330
#> 5  2013     1     1     2205           1720       285       46
#> 6  2013     1     1     2343           1724       379      314
#> # ... with more rows, and 12 more variables: sched_arr_time <int>,
#> #   arr_delay <dbl>, carrier <chr>, flight <int>, tailnum <chr>,
#> #   origin <chr>, dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>,
#> #   minute <dbl>, time_hour <dbl>

flights_db %>% 
  group_by(dest) %>%
  summarise(delay = mean(dep_time))
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> # Source:   lazy query [?? x 2]
#> # Database: sqlite 3.19.3 []
#>   dest  delay
#>   <chr> <dbl>
#> 1 ABQ    2006
#> 2 ACK    1033
#> 3 ALB    1627
#> 4 ANC    1635
#> 5 ATL    1293
#> 6 AUS    1521
#> # ... with more rows

However, in the long-run, I highly recommend you at least learn the basics of SQL. It’s a valuable skill for any data scientist, and it will help you debug problems if you run into problems with dplyr’s automatic translation. If you’re completely new to SQL you might start with this codeacademy tutorial. If you have some familiarity with SQL and you’d like to learn more, I found how indexes work in SQLite and 10 easy steps to a complete understanding of SQL to be particularly helpful.

The most important difference between ordinary data frames and remote database queries is that your R code is translated into SQL and executed in the database on the remote server, not in R on your local machine. When working with databases, dplyr tries to be as lazy as possible:

• It never pulls data into R unless you explicitly ask for it.

• It delays doing any work until the last possible moment: it collects together everything you want to do and then sends it to the database in one step.

For example, take the following code:

tailnum_delay_db <- flights_db %>% 
  group_by(tailnum) %>%
  summarise(
    delay = mean(arr_delay),
    n = n()
  ) %>% 
  arrange(desc(delay)) %>%
  filter(n > 100)

Suprisingly, this sequence of operations never touches the database. It’s not until you ask for the data (e.g. by printing tailnum_delay) that dplyr generates the SQL and requests the results from the database. Even then it tries to do as little work as possible and only pulls down a few rows.

tailnum_delay_db
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> # Source:     lazy query [?? x 3]
#> # Database:   sqlite 3.19.3 []
#> # Ordered by: desc(delay)
#>   tailnum delay     n
#>   <chr>   <dbl> <int>
#> 1 N11119   30.3   148
#> 2 N16919   29.9   251
#> 3 N14998   27.9   230
#> 4 N15910   27.6   280
#> 5 N13123   26.0   121
#> 6 N11192   25.9   154
#> # ... with more rows

Behind the scenes, dplyr is translating your R code into SQL. You can see the SQL it’s generating with show_query():

tailnum_delay_db %>% show_query()
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
#> <SQL>
#> SELECT *
#> FROM (SELECT *
#> FROM (SELECT `tailnum`, AVG(`arr_delay`) AS `delay`, COUNT() AS `n`
#> FROM `flights`
#> GROUP BY `tailnum`)
#> ORDER BY `delay` DESC)
#> WHERE (`n` > 100.0)

If you’re familiar with SQL, this probably isn’t exactly what you’d write by hand, but it does the job. You can learn more about the SQL translation in vignette("sql-translation").

Typically, you’ll iterate a few times before you figure out what data you need from the database. Once you’ve figured it out, use collect() to pull all the data down into a local tibble:

tailnum_delay <- tailnum_delay_db %>% collect()
#> Warning: Missing values are always removed in SQL.
#> Use `AVG(x, na.rm = TRUE)` to silence this warning
tailnum_delay
#> # A tibble: 1,201 x 3
#>   tailnum delay     n
#>   <chr>   <dbl> <int>
#> 1 N11119   30.3   148
#> 2 N16919   29.9   251
#> 3 N14998   27.9   230
#> 4 N15910   27.6   280
#> 5 N13123   26.0   121
#> 6 N11192   25.9   154
#> # ... with 1,195 more rows

collect() requires that database does some work, so it may take a long time to complete. Otherwise, dplyr tries to prevent you from accidentally performing expensive query operations:

• Because there’s generally no way to determine how many rows a query will return unless you actually run it, nrow() is always NA.

• Because you can’t find the last few rows without executing the whole query, you can’t use tail().

nrow(tailnum_delay_db)
#> [1] NA

tail(tailnum_delay_db)
#> Error: tail() is not supported by sql sources

You can also ask the database how it plans to execute the query with explain(). The output is database dependent, and can be esoteric, but learning a bit about it can be very useful because it helps you understand if the database can execute the query efficiently, or if you need to create new indices.

Creating your own database

If you don’t already have a database, here’s some advice from my experiences setting up and running all of them. SQLite is by far the easiest to get started with, but the lack of window functions makes it limited for data analysis. PostgreSQL is not too much harder to use and has a wide range of built-in functions. In my opinion, you shouldn’t bother with MySQL/MariaDB: it’s a pain to set up, the documentation is subpar, and it’s less featureful than Postgres. Google BigQuery might be a good fit if you have very large data, or if you’re willing to pay (a small amount of) money to someone who’ll look after your database.

All of these databases follow a client-server model - a computer that connects to the database and the computer that is running the database (the two may be one and the same but usually isn’t). Getting one of these databases up and running is beyond the scope of this article, but there are plenty of tutorials available on the web.