# Databases

#### 2016-06-23

As well as working with local in-memory data like data frames and data tables, dplyr also works with remote on-disk data stored in databases. Generally, if your data fits in memory there is no advantage to putting it in a database: it will only be slower and more hassle. The reason you’d want to use dplyr with a database is because either your data is already in a database (and you don’t want to work with static csv files that someone else has dumped out for you), or you have so much data that it does not fit in memory and you have to use a database. Currently dplyr supports the three most popular open source databases (sqlite, mysql and postgresql), and google’s bigquery.

Since R almost exclusively works with in-memory data, if you do have a lot of data in a database, you can’t just dump it into R. Instead, you’ll have to work with subsets or aggregates. dplyr aims to make this task as easy as possible. If you’re working with large data, it’s also likely that you’ll need support to get the data into the database and to ensure you have the right indices for good performance. While dplyr provides some simple tools to help with these tasks, they are no substitute for a local expert.

The motivation for supporting databases in dplyr is that you never pull down the right subset or aggregate from the database on your first try. Usually you have to iterate between R and SQL many times before you get the perfect dataset. But because switching between languages is cognitively challenging (especially because R and SQL are so perilously similar), dplyr helps you by allowing you to write R code that is automatically translated to SQL. The goal of dplyr is not to replace every SQL function with an R function; that would be difficult and error prone. Instead, dplyr only generates `SELECT` statements, the SQL you write most often as an analyst.

To get the most out of this chapter, you’ll need to be familiar with querying SQL databases using the `SELECT` statement. 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.

## Getting started

The easiest way to experiement with databases using dplyr is to use SQLite. This is because everything you need is already included in the R package. You won’t need to install anything, and you won’t need to deal with the hassle of setting up a database server. Doing so is really easy: just give the path and the ok to create a table.

    my_db <- src_sqlite("my_db.sqlite3", create = T)

The main new concept here is the `src`, which is a collection of types of database tables. Use `src_sqlite()`, `src_mysql()`, `src_postgres()` and `src_bigquery()` to connect to the specific types supported by dplyr.

`my_db` currently has no data in it, so we’ll import the `flights` data using the convenient `copy_to()` function. This is a quick and dirty way of getting data into a database. Because all the data has to flow through R, you should note that this is not suitable for very large datasets.

    library(nycflights13)
    flights_sqlite <- copy_to(my_db, flights, temporary = FALSE, indexes = list(
      c("year", "month", "day"), "carrier", "tailnum"))

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, by carrier and by plane. `copy_to()` also executes the SQL `ANALYZE` command: this ensures that the database has up-to-date table statistics and performs the appropriate query optimisations.

For this particular dataset, there’s a built-in `src` that will cache `flights` in a standard location:

    flights_sqlite <- tbl(nycflights13_sqlite(), "flights")
    #> Caching nycflights db at /private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite
    flights_sqlite
    #> Source:   query [?? x 16]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
    #>   <int> <int> <int>    <int>     <dbl>    <int>     <dbl>   <chr>   <chr>
    #> 1  2013     1     1      517         2      830        11      UA  N14228
    #> 2  2013     1     1      533         4      850        20      UA  N24211
    #> 3  2013     1     1      542         2      923        33      AA  N619AA
    #> 4  2013     1     1      544        -1     1004       -18      B6  N804JB
    #> ... with more rows, and 7 more variables: flight <int>, origin <chr>,
    #>   dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>

You can also use arbitrary SQL:

    tbl(my_db, sql("SELECT * FROM flights"))

## Basic verbs

Whether you’re dealing with remote or local data sources, you use the same five verbs:

    select(flights_sqlite, year:day, dep_delay, arr_delay)
    #> Source:   query [?? x 5]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day dep_delay arr_delay
    #>   <int> <int> <int>     <dbl>     <dbl>
    #> 1  2013     1     1         2        11
    #> 2  2013     1     1         4        20
    #> 3  2013     1     1         2        33
    #> 4  2013     1     1        -1       -18
    #> ... with more rows
    filter(flights_sqlite, dep_delay > 240)
    #> Source:   query [?? x 16]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
    #>   <int> <int> <int>    <int>     <dbl>    <int>     <dbl>   <chr>   <chr>
    #> 1  2013     1     1      848       853     1001       851      MQ  N942MQ
    #> 2  2013     1     1     1815       290     2120       338      EV  N17185
    #> 3  2013     1     1     1842       260     1958       263      EV  N18120
    #> 4  2013     1     1     2115       255     2330       250      9E  N924XJ
    #> ... with more rows, and 7 more variables: flight <int>, origin <chr>,
    #>   dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>
    arrange(flights_sqlite, year, month, day)
    #> Source:   query [?? x 16]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
    #>   <int> <int> <int>    <int>     <dbl>    <int>     <dbl>   <chr>   <chr>
    #> 1  2013     1     1      517         2      830        11      UA  N14228
    #> 2  2013     1     1      533         4      850        20      UA  N24211
    #> 3  2013     1     1      542         2      923        33      AA  N619AA
    #> 4  2013     1     1      544        -1     1004       -18      B6  N804JB
    #> ... with more rows, and 7 more variables: flight <int>, origin <chr>,
    #>   dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>
    mutate(flights_sqlite, speed = air_time / distance)
    #> Source:   query [?? x 17]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day dep_time dep_delay arr_time arr_delay carrier tailnum
    #>   <int> <int> <int>    <int>     <dbl>    <int>     <dbl>   <chr>   <chr>
    #> 1  2013     1     1      517         2      830        11      UA  N14228
    #> 2  2013     1     1      533         4      850        20      UA  N24211
    #> 3  2013     1     1      542         2      923        33      AA  N619AA
    #> 4  2013     1     1      544        -1     1004       -18      B6  N804JB
    #> ... with more rows, and 8 more variables: flight <int>, origin <chr>,
    #>   dest <chr>, air_time <dbl>, distance <dbl>, hour <dbl>, minute <dbl>,
    #>   speed <dbl>
    summarise(flights_sqlite, delay = mean(dep_time))
    #> Source:   query [?? x 1]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>     delay
    #>     <dbl>
    #> 1 1349.11

The most important difference is that the expressions in `select()`, `filter()`, `arrange()`, `mutate()`, and `summarise()` are translated into SQL so they can be run on the database. While the translations for the most common operations are almost perfect there are some limitations, which you’ll learn about later.

## Laziness

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:

    c1 <- filter(flights_sqlite, year == 2013, month == 1, day == 1)
    c2 <- select(c1, year, month, day, carrier, dep_delay, air_time, distance)
    c3 <- mutate(c2, speed = distance / air_time * 60)
    c4 <- arrange(c3, year, month, day, carrier)

Suprisingly, this sequence of operations never actually touches the database. It’s not until you ask for the data (e.g. by printing `c4`) that dplyr generates the SQL and requests the results from the database. Even then it only pulls down 10 rows.

    c4
    #> Source:   query [?? x 8]
    #> Database: sqlite 3.8.6 [/private/tmp/RtmpduTf9z/Rinst1430371d1558/dplyr/db/nycflights13.sqlite]
    #>
    #> # S3: tbl_sqlite
    #>    year month   day carrier dep_delay air_time distance    speed
    #>   <int> <int> <int>   <chr>     <dbl>    <dbl>    <dbl>    <dbl>
    #> 1  2013     1     1      9E         0      189     1029 326.6667
    #> 2  2013     1     1      9E        -9       57      228 240.0000
    #> 3  2013     1     1      9E        -3       68      301 265.5882
    #> 4  2013     1     1      9E        -6       57      209 220.0000
    #> ... with more rows

To pull down all the results use `collect()`, which returns a `tbl_df()`:

    collect(c4)
    #> # A tibble: 842 x 8
    #>    year month   day carrier dep_delay air_time distance    speed
    #>   <int> <int> <int>   <chr>     <dbl>    <dbl>    <dbl>    <dbl>
    #> 1  2013     1     1      9E         0      189     1029 326.6667
    #> 2  2013     1     1      9E        -9       57      228 240.0000
    #> 3  2013     1     1      9E        -3       68      301 265.5882
    #> 4  2013     1     1      9E        -6       57      209 220.0000
    #> ... with 838 more rows

You can see the query dplyr has generated by looking at the `query` component of the object:

    c4$query
    #> NULL

You can also ask the database how it plans to execute the query with `explain()`. The output for SQLite is described in more detail on the SQLite website. It’s helpful if you’re trying to figure out which indexes are being used.

    explain(c4)
    #> <SQL>
    #> SELECT *
    #> FROM (SELECT `year`, `month`, `day`, `carrier`, `dep_delay`, `air_time`, `distance`, `distance` / `air_time` * 60.0 AS `speed`
    #> FROM (SELECT `year` AS `year`, `month` AS `month`, `day` AS `day`, `carrier` AS `carrier`, `dep_delay` AS `dep_delay`, `air_time` AS `air_time`, `distance` AS `distance`
    #> FROM (SELECT *
    #> FROM `flights`
    #> WHERE ((`year` = 2013.0) AND (`month` = 1.0) AND (`day` = 1.0)))))
    #> ORDER BY `year`, `month`, `day`, `carrier`
    #>
    #> <PLAN>
    #>    addr       opcode p1    p2 p3                   p4 p5 comment
    #> 1     0         Init  0    62  0                      00    <NA>
    #> 2     1   SorterOpen  4     3  0           k(2,B,nil) 00    <NA>
    #> 3     2     OpenRead  3   100  0                   16 00    <NA>
    #> 4     3     OpenRead  5 18795  0 k(4,nil,nil,nil,nil) 00    <NA>
    #> 5     4         Real  0     1  0                 2013 00    <NA>
    #> 6     5         Real  0     2  0                    1 00    <NA>
    #> 7     6         Real  0     3  0                    1 00    <NA>
    #> 8     7     Affinity  1     3  0                  ddd 00    <NA>
    #> 9     8       SeekGE  5    40  1                    3 00    <NA>
    #> 10    9        IdxGT  5    40  1                    3 00    <NA>
    #> 11   10     IdxRowid  5     4  0                      00    <NA>
    #> 12   11         Seek  3     4  0                      00    <NA>
    #> 13   12       Column  5     0  5                      00    <NA>
    #> 14   13       Column  5     1  6                      00    <NA>
    #> 15   14       Column  5     2  7                      00    <NA>
    #> 16   15       Column  3     7  8                      00    <NA>
    #> 17   16       Column  3     4  9                      00    <NA>
    #> 18   17 RealAffinity  9     0  0                      00    <NA>
    #> 19   18       Column  3    12 10                      00    <NA>
    #> 20   19 RealAffinity 10     0  0                      00    <NA>
    #> 21   20       Column  3    13 11                      00    <NA>
    #> 22   21 RealAffinity 11     0  0                      00    <NA>
    #> 23   22       Divide 10    11 13                      00    <NA>
    #> 24   23     Multiply 15    13 12                      00    <NA>
    #> 25   24   MakeRecord  5     8 13                      00    <NA>
    #> 26   25       Column  5     0 17                      00    <NA>
    #> 27   26       Column  5     1 18                      00    <NA>
    #> 28   27       Column  5     2 19                      00    <NA>
    #> 29   28       Column  3     7 20                      00    <NA>
    #> 30   29     Sequence  4    21  0                      00    <NA>
    #> 31   30         Move 13    22  1                      00    <NA>
    #> 32   31   MakeRecord 20     3 16                      00    <NA>
    #> 33   32        IfNot 21    37  0                      00    <NA>
    #> 34   33      Compare 23    17  3         k(4,B,B,B,B) 00    <NA>
    #> 35   34         Jump 35    38 35                      00    <NA>
    #> 36   35        Gosub 26    44  0                      00    <NA>
    #> 37   36  ResetSorter  4     0  0                      00    <NA>
    #> 38   37         Move 17    23  3                      00    <NA>
    #> 39   38 SorterInsert  4    16  0                      00    <NA>
    #> 40   39         Next  5     9  1                      00    <NA>
    #> 41   40        Close  3     0  0                      00    <NA>
    #> 42   41        Close  5     0  0                      00    <NA>
    #> 43   42        Gosub 26    44  0                      00    <NA>
    #> 44   43         Goto  0    61  0                      00    <NA>
    #> 45   44         Once  0    47  0                      00    <NA>
    #> 46   45   OpenPseudo  6    13  8                      00    <NA>
    #> 47   46   OpenPseudo  7    27  3                      00    <NA>
    #> 48   47   SorterSort  4    61  0                      00    <NA>
    #> 49   48   SorterData  4    27  0                      00    <NA>
    #> 50   49       Column  7     2 13                      20    <NA>
    #> 51   50       Column  6     0  5                      20    <NA>
    #> 52   51       Column  6     1  6                      00    <NA>
    #> 53   52       Column  6     2  7                      00    <NA>
    #> 54   53       Column  6     3  8                      00    <NA>
    #> 55   54       Column  6     4  9                      00    <NA>
    #> 56   55       Column  6     5 10                      00    <NA>
    #> 57   56       Column  6     6 11                      00    <NA>
    #> 58   57       Column  6     7 12                      00    <NA>
    #> 59   58    ResultRow  5     8  0                      00    <NA>
    #> 60   59   SorterNext  4    48  0                      00    <NA>
    #> 61   60       Return 26     0  0                      00    <NA>
    #> 62   61         Halt  0     0  0                      00    <NA>
    #> 63   62  Transaction  0     0 16                    0 01    <NA>
    #> 64   63    TableLock  0   100  0              flights 00    <NA>
    #> 65   64         Real  0    15  0                   60 00    <NA>
    #> 66   65         Goto  0     1  0                      00    <NA>

### Forcing computation

There are three ways to force the computation of a query:

- `collect()` executes the query and returns the results to R.

- `compute()` executes the query and stores the results in a temporary table in the database.

- `collapse()` turns the query into a table expression.

`collect()` is the function you’ll use most. Once you reach the set of operations you want, you use collect() to pull the data into a local `tbl_df()`. If you know SQL, you can use `compute()` and `collapse()` to optimise performance.

### Performance considerations

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`.

- Printing a tbl only runs the query for the first 10 rows.

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

## SQL translation

When performing the simple mathematical operations used when filtering, mutating or summarising, translating R code to SQL (or indeed to any programming language) is relatively straightforward.

To experiment with this, use `translate_sql()`. The following examples work through some of the basic differences between R and SQL.

    # In SQLite variable names are escaped by double quotes:
    translate_sql(x)
    #> <SQL> "x"
    # And strings are escaped by single quotes
    translate_sql("x")
    #> <SQL> 'x'

    # Many functions have slightly different names
    translate_sql(x == 1 && (y < 2 || z > 3))
    #> <SQL> "x" = 1.0 AND ("y" < 2.0 OR "z" > 3.0)
    translate_sql(x ^ 2 < 10)
    #> <SQL> POWER("x", 2.0) < 10.0
    translate_sql(x %% 2 == 10)
    #> <SQL> "x" % 2.0 = 10.0

    # R and SQL have different defaults for integers and reals.
    # In R, 1 is a real, and 1L is an integer
    # In SQL, 1 is an integer, and 1.0 is a real
    translate_sql(1)
    #> <SQL> 1.0
    translate_sql(1L)
    #> <SQL> 1

dplyr knows how to convert the following R functions to SQL:

- basic math operators: `+`, `-`, `*`, `/`, `%%`, `^`
- math functions: `abs`, `acos`, `acosh`, `asin`, `asinh`, `atan`, `atan2`, `atanh`, `ceiling`, `cos`, `cosh`, `cot`, `coth`, `exp`, `floor`, `log`, `log10`, `round`, `sign`, `sin`, `sinh`, `sqrt`, `tan`, `tanh`
- logical comparisons: `<`, `<=`, `!=`, `>=`, `>`, `==`, `%in%`
- boolean operations: `&`, `&&`, `|`, `||`, `!`, `xor`
- basic aggregations: `mean`, `sum`, `min`, `max`, `sd`, `var`

The basic techniques that underlie the implementation of `translate_sql()` are described in the Advanced R book. `translate_sql()` is built on top of R’s parsing engine and has been carefully designed to generate correct SQL. It also protects you against SQL injection attacks by correctly escaping the strings and variable names needed by the database that you’re connecting to.

Perfect translation is not possible because databases don’t have all the functions that R does. The goal of dplyr is to provide a semantic rather than a literal translation: what you mean rather than what is done. In fact, even for functions that exist both in databases and R, you shouldn’t expect results to be identical; database programmers have different priorities than R core programmers. For example, in R in order to get a higher level of numerical accuracy, `mean()` loops through the data twice. R’s `mean()` also provides a `trim` option for computing trimmed means; this is something that databases do not provide. Databases automatically drop NULLs (their equivalent of missing values) whereas in R you have to ask nicely. This means the essence of simple calls like `mean(x)` will be translated accurately, but more complicated calls like `mean(x, trim = 0.5, na.rm = TRUE)` will raise an error:

    translate_sql(mean(x, trim = T))
    # Error: Invalid number of args to SQL AVG. Expecting 1

Any function that dplyr doesn’t know how to convert is left as is. This means that database functions that are not covered by dplyr can be used directly via `translate_sql()`. Here a couple of examples that will work with SQLite:

    translate_sql(glob(x, y))
    #> <SQL> GLOB("x", "y")
    translate_sql(x %like% "ab*")
    #> <SQL> "x" LIKE 'ab*'

## Grouping

SQLite lacks the window functions that are needed for grouped mutation and filtering. This means that the only really useful operations for grouped SQLite tables are found in `summarise()`. The grouped summarise from the introduction translates well - the only difference is that databases always drop NULLs (their equivalent of missing values), so we don’t supply `na.rm = TRUE`.

    by_tailnum <- group_by(flights_sqlite, tailnum)
    delay <- summarise(by_tailnum,
      count = n(),
      dist = mean(distance),
      delay = mean(arr_delay)
    )
    delay <- filter(delay, count > 20, dist < 2000)
    delay_local <- collect(delay)

Other databases do support window functions. You can learn about them in the corresponding vignette. It’s sometimes possible to simulate grouped filtering and mutation using self joins, which join the original table with a summarised version, but that topic is beyond the scope of this introduction.

## Other databases

Aside from SQLite, the overall workflow is essentially the same regardless of the database you’re connecting to. The following sections go in to more details about the peculiarities of each database engine. 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.

### PostgreSQL

`src_postgres()` has five arguments: `dbname`, `host`, `port`, `user` and `password`. If you are running a local postgresql database with the default settings you only need `dbname`. But in most cases, you’ll need all five. dplyr uses the RPostgreSQL package to connect to postgres databases. This means that you can’t currently connect to remote databases that require a SSL connection (e.g. Heroku).

For example, the following code allows me to connect to a local PostgreSQL database that contains a copy of the `flights` data:

    flights_postgres <- tbl(src_postgres("nycflights13"), "flights")

PostgreSQL is a considerably more powerful database than SQLite. It has:

- a much wider range of built-in functions

- support for window functions, which allow grouped subset and mutates to work.

The following examples show how we can perform grouped filter and mutate operations with PostgreSQL. Because you can’t filter on window functions directly, the SQL generated from the grouped filter is quite complex; so they instead have to go in a subquery.

    daily <- group_by(flights_postgres, year, month, day)

    # Find the most and least delayed flight each day
    bestworst <- daily %>%
      select(flight, arr_delay) %>%
      filter(arr_delay == min(arr_delay) || arr_delay == max(arr_delay))
    bestworst$query

    # Rank each flight within a daily
    ranked <- daily %>%
      select(arr_delay) %>%
      mutate(rank = rank(desc(arr_delay)))
    ranked$query

### MySQL and MariaDB

You can connect to MySQL and MariaDB (a recent fork of MySQL) using `src_mysql()`, mediated by the RMySQL package. Like PostgreSQL, you’ll need to provide a `dbname`, `username`, `password`, `host`, and `port`.

In terms of functionality, MySQL lies somewhere between SQLite and PostgreSQL. It provides a wider range of built-in functions, but it does not support window functions (so you can’t do grouped mutates and filters).

### BigQuery

BigQuery is a hosted database server provided by Google. To connect, you need to provide your `project`, `dataset` and optionally a project for `billing` (if billing for `project` isn’t enabled). After you create the src, your web browser will open and ask you to authenticate. Your credentials are stored in a local cache, so you should only need to do this once.

BigQuery supports only one SQL statement: SELECT. Fortunately this is all you need for data analysis. Within SELECT, BigQuery provides comprehensive coverage at a similar level to PostgreSQL.

## Picking a 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. Don’t bother with MySQL/MariaDB: it’s a pain to set up and the documentation is subpar. 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.