16  Advanced data import II: databases

Objectives

• Motivate working directly with databases. Large volumes of data often live in databases instead of flat files, and repeatedly exporting CSV snapshots is time-consuming. Learn why connecting directly to a database gives you immediate access to current data and avoids that delay.
• Explain how database tables differ from tibbles. Database tables live on disk, can be arbitrarily large, and usually have indexes to speed up queries. Classical databases prioritize writing over analysis, whereas column-oriented systems like duckdb are optimized for analytical queries.
• Connect to a database with DBI. The DBI package provides a uniform interface to many database systems. Learn to call DBI::dbConnect() with a driver from a package like RPostgres, RMariaDB, or duckdb, supplying host, port, and credentials. We will use duckdb, since it runs in-process and needs no server.
• Load and inspect tables. Use dbWriteTable() to copy a tibble into a database, dbListTables() to see what tables exist, and dbReadTable() to read a whole table back into R.
• Execute SQL queries via DBI. Write simple SELECT ... FROM ... WHERE ... statements and run them with dbGetQuery(). Understand the core SQL clauses and how they map to dplyr verbs.
• Use dbplyr for lazy pipelines. dbplyr translates dplyr code into SQL. Create a lazy table with tbl(), compose pipelines with verbs like filter() and summarize(), view the generated SQL with show_query(), and retrieve results with collect(). Recognize that a lazy query and an actual data frame are not interchangeable until you collect().

Notes

Why work with databases?

When a colleague hands you data by exporting a CSV, you have to go back and ask for a new export every time your question changes. Connecting directly to a database avoids that entirely: rather than working from a snapshot, you write a query that retrieves exactly the current data you need. A database table looks like a data frame, a collection of named columns, but differs in a few important ways. Data frames live in memory; database tables live on disk and can be arbitrarily larger than your computer’s RAM. Most databases maintain indexes that let them locate rows quickly without scanning the whole table. And row-oriented databases are built to record individual transactions quickly, while column-oriented systems like duckdb are built for exactly the kind of broad analytical query this course focuses on.

Setting up a connection

DBI defines a low-level interface for connecting to many different database systems. You create a connection with DBI::dbConnect(), supplying a driver from a DBMS-specific package along with connection details. Connecting to a real PostgreSQL server, for example, looks like this (not run here, since it needs an actual server to connect to):

con <- DBI::dbConnect(
  RPostgres::Postgres(),
  host = "db.example.com",
  port = 5432,
  dbname = "mydb",
  user = "username",
  password = "secret"
)

This course uses duckdb instead, an in-process database that lives entirely within R and needs no server or credentials at all, which makes it ideal for learning:

library(tidyverse)
library(DBI)
library(duckdb)

con <- dbConnect(duckdb())

Passing a dbdir argument to duckdb() points the database at a file on disk instead, so its contents persist between R sessions rather than disappearing (as an in-memory database does) the moment you disconnect.

Loading and inspecting tables

A brand-new database is empty, so you need to load data into it before you can query anything. dbWriteTable() copies a tibble into the database: the connection first, then a table name, then the data frame itself.

dbWriteTable(con, "mpg", ggplot2::mpg)

dbListTables() lists what’s actually in the database, and dbReadTable() reads an entire table back into R as an ordinary data frame.

dbListTables(con)

[1] "mpg"

dbReadTable(con, "mpg") |> as_tibble()

# A tibble: 234 × 11
   manufacturer model      displ  year   cyl trans drv     cty   hwy fl    class
   <chr>        <chr>      <dbl> <int> <int> <chr> <chr> <int> <int> <chr> <chr>
 1 audi         a4           1.8  1999     4 auto… f        18    29 p     comp…
 2 audi         a4           1.8  1999     4 manu… f        21    29 p     comp…
 3 audi         a4           2    2008     4 manu… f        20    31 p     comp…
 4 audi         a4           2    2008     4 auto… f        21    30 p     comp…
 5 audi         a4           2.8  1999     6 auto… f        16    26 p     comp…
 6 audi         a4           2.8  1999     6 manu… f        18    26 p     comp…
 7 audi         a4           3.1  2008     6 auto… f        18    27 p     comp…
 8 audi         a4 quattro   1.8  1999     4 manu… 4        18    26 p     comp…
 9 audi         a4 quattro   1.8  1999     4 auto… 4        16    25 p     comp…
10 audi         a4 quattro   2    2008     4 manu… 4        20    28 p     comp…
# ℹ 224 more rows

Running SQL queries

If you already know SQL, DBI::dbGetQuery() runs a query string directly and returns the result as a data frame. A query is built from clauses like SELECT, FROM, WHERE, GROUP BY, and ORDER BY.

sql <- "SELECT manufacturer, model, year, hwy
        FROM mpg
        WHERE hwy > 30
        ORDER BY hwy DESC"

dbGetQuery(con, sql) |> head()

  manufacturer      model year hwy
1   volkswagen      jetta 1999  44
2   volkswagen new beetle 1999  44
3   volkswagen new beetle 1999  41
4       toyota    corolla 2008  37
5        honda      civic 2008  36
6        honda      civic 2008  36

SQL gives you complete control, but writing it by hand is verbose for anything beyond a simple query. dbplyr lets you write the dplyr code you already know instead.

Lazy queries with dbplyr

tbl() creates a lazy table: something that represents a database table and behaves like one for the purpose of writing dplyr code, without actually pulling any data into R yet.

mpg_db <- tbl(con, "mpg")
mpg_db

# Source:   table<mpg> [?? x 11]
# Database: DuckDB 1.4.1 [Joshua_Patrick@Windows 10 x64:R 4.5.1/:memory:]
   manufacturer model      displ  year   cyl trans drv     cty   hwy fl    class
   <chr>        <chr>      <dbl> <int> <int> <chr> <chr> <int> <int> <chr> <chr>
 1 audi         a4           1.8  1999     4 auto… f        18    29 p     comp…
 2 audi         a4           1.8  1999     4 manu… f        21    29 p     comp…
 3 audi         a4           2    2008     4 manu… f        20    31 p     comp…
 4 audi         a4           2    2008     4 auto… f        21    30 p     comp…
 5 audi         a4           2.8  1999     6 auto… f        16    26 p     comp…
 6 audi         a4           2.8  1999     6 manu… f        18    26 p     comp…
 7 audi         a4           3.1  2008     6 auto… f        18    27 p     comp…
 8 audi         a4 quattro   1.8  1999     4 manu… 4        18    26 p     comp…
 9 audi         a4 quattro   1.8  1999     4 auto… 4        16    25 p     comp…
10 audi         a4 quattro   2    2008     4 manu… 4        20    28 p     comp…
# ℹ more rows

You compose a query with ordinary dplyr verbs, and dbplyr silently translates each one into SQL behind the scenes, only actually running anything once you call collect().

summary_db <- mpg_db |>
  filter(displ >= 2.0) |>
  group_by(manufacturer) |>
  summarise(avg_hwy = mean(hwy, na.rm = TRUE), n = n()) |>
  arrange(desc(avg_hwy))

summary_db |> show_query()

<SQL>
SELECT manufacturer, AVG(hwy) AS avg_hwy, COUNT(*) AS n
FROM (
  SELECT mpg.*
  FROM mpg
  WHERE (displ >= 2.0)
) q01
GROUP BY manufacturer
ORDER BY avg_hwy DESC

summary_db |> collect()

# A tibble: 15 × 3
   manufacturer avg_hwy     n
   <chr>          <dbl> <dbl>
 1 honda           29       1
 2 volkswagen      27.4    22
 3 hyundai         26.9    14
 4 pontiac         26.4     5
 5 audi            26.2    14
 6 subaru          25.6    14
 7 nissan          24.6    13
 8 toyota          23.3    29
 9 chevrolet       21.9    19
10 ford            19.4    25
11 mercury         18       4
12 dodge           17.9    37
13 jeep            17.6     8
14 lincoln         17       3
15 land rover      16.5     4

show_query() prints the SQL dbplyr generated, without running it; collect() actually sends that SQL to the database, retrieves the result, and hands it back as a genuine tibble. Until you call collect(), you’re working with a description of a query, not with data (see Example 15.4).

Basic SQL structure

Even though dbplyr hides most of SQL from you, it helps to recognize its shape. A query is built from, at minimum, these clauses, always in this order: SELECT (which columns, and any computed expressions), FROM (which table), WHERE (which rows), GROUP BY (how to collapse rows into groups), and ORDER BY (how to sort the result). SQL keywords are case-insensitive, but convention writes them in uppercase to stand out from table and column names.

Fringe cases and common pitfalls

ExampleExample 15.1

A lazy query’s mean() quietly matches SQL’s behavior, not R’s.

dbWriteTable(con, "nas", tibble(x = c(1, 2, NA, 4)))
nas_db <- tbl(con, "nas")

nas_db |> summarise(avg = mean(x)) |> collect()   # no na.rm at all

Warning: Missing values are always removed in SQL aggregation functions.
Use `na.rm = TRUE` to silence this warning
This warning is displayed once every 8 hours.

# A tibble: 1 × 1
    avg
  <dbl>
1  2.33

mean(c(1, 2, NA, 4))                              # the equivalent plain R calculation

[1] NA

In plain R, mean() propagates NA unless you explicitly add na.rm = TRUE. In a dbplyr query, mean() gets translated to SQL’s AVG(), which silently ignores NULL values by design, so the lazy query above returns the same answer as mean(x, na.rm = TRUE) would, without you ever asking for that behavior. dbplyr does warn about this the first time you hit it in a session, but the warning is easy to miss, and the two functions named mean() (R’s and SQL’s, standing behind it) genuinely disagree about what to do with a missing value. Get in the habit of writing na.rm = TRUE explicitly in a lazy query, the same as you would anywhere else, so the behavior doesn’t depend on which engine happens to be running the calculation.

ExampleExample 15.2

Filtering on a column you just created can force dbplyr to build a nested subquery.

mpg_db |>
  mutate(kpl = hwy * 0.425) |>
  filter(kpl > 12) |>
  show_query()

<SQL>
SELECT q01.*
FROM (
  SELECT mpg.*, hwy * 0.425 AS kpl
  FROM mpg
) q01
WHERE (kpl > 12.0)

In dplyr, you can create kpl with mutate() and immediately filter() on it in the very next line, because each step runs in order. SQL’s WHERE clause, though, is evaluated before its SELECT clause, so a plain, single-level query has no way to filter on an alias defined in that same SELECT. dbplyr works around this automatically by wrapping the first query as a subquery and filtering the outer query instead, which is exactly the nested SELECT ... FROM (SELECT ...) structure you see above. This is a good reason to actually read show_query()’s output occasionally: a pipeline that looks simple in dplyr can compile into something considerably less simple in SQL, for reasons that have nothing to do with your code being wrong.

ExampleExample 15.3

show_query() can print syntactically plausible SQL for a function that doesn’t actually exist in the database.

my_custom_fun <- function(x) x * 2 + 1

weird <- mpg_db |> mutate(x = my_custom_fun(hwy))
weird |> show_query()

<SQL>
SELECT mpg.*, my_custom_fun(hwy) AS x
FROM mpg

weird |> collect()

Error in `collect()`:
! Failed to collect lazy table.
Caused by error in `dbSendQuery()`:
! Catalog Error: Scalar Function with name my_custom_fun does not exist!
Did you mean "array_push_front"?

LINE 1: SELECT mpg.*, my_custom_fun(hwy) AS x
                      ^
ℹ Context: rapi_prepare
ℹ Error type: CATALOG
ℹ Raw message: Scalar Function with name my_custom_fun does not exist!
Did you mean "array_push_front"?

LINE 1: SELECT mpg.*, my_custom_fun(hwy) AS x
                      ^

dbplyr doesn’t know what my_custom_fun() is, so it does the only thing it can: it writes the function call into the generated SQL literally, exactly as written, and hopes the database recognizes it. show_query() happily shows you that SQL, since generating it doesn’t require running anything. Only collect() (or printing the lazy table, which collects a preview behind the scenes) actually sends the query to the database, which is the point at which it fails with a real “function does not exist” error. A lazy query that looks fine when you inspect its SQL is not the same guarantee as a lazy query that will actually run.

ExampleExample 15.4

A lazy table doesn’t know its own row count until you collect it.

lazy <- mpg_db |> filter(hwy > 30)
class(lazy)

[1] "tbl_duckdb_connection" "tbl_dbi"               "tbl_sql"              
[4] "tbl_lazy"              "tbl"                  

nrow(lazy)

[1] NA

collected <- collect(lazy)
class(collected)

[1] "tbl_df"     "tbl"        "data.frame"

nrow(collected)

[1] 22

nrow() on a lazy table returns NA, not an error and not a real count, because dbplyr has only recorded what query would count the matching rows, not actually run it. Counting the rows would mean executing the query, which is exactly the work collect() is for. Code that assumes a lazy table behaves identically to a real data frame (checking nrow() to decide whether to proceed, for instance) needs a collect() first, or an explicit tally()/count() query, to get an actual number back.

Recap

Term	Definition
dbConnect()	Opens a connection to a database, given a driver and connection details.
dbWriteTable() / dbReadTable()	Copy a tibble into a database table, or read an entire table back into R.
dbGetQuery()	Runs a raw SQL query string and returns the result as a data frame.
tbl()	Creates a lazy table representing a database table, without pulling any data into R.
show_query()	Prints the SQL a lazy dplyr pipeline would run, without running it.
collect()	Actually sends a lazy query to the database and returns the result as a tibble.
AVG() vs. mean()	SQL’s AVG() silently ignores NULLs, unlike R’s mean(), which propagates NA unless told na.rm = TRUE.
Subquery	A nested SELECT dbplyr generates automatically when SQL’s clause order can’t otherwise express a dplyr pipeline (such as filtering on a just-created column).
Untranslated function	A function dbplyr doesn’t recognize is passed through into the generated SQL literally, and only fails once the database actually tries to run it.

Check your understanding

NoteProblems

1. What is the practical difference between calling show_query() and calling collect() on the same lazy dplyr pipeline?
2. A teammate computes tbl(con, "sales") |> summarise(avg_price = mean(price)) |> collect() on a column with some missing prices, and gets a number back rather than NA. Explain why, given that the equivalent plain-R calculation on a vector with a missing value would return NA.
3. mpg_db |> mutate(kpl = hwy * 0.425) |> filter(kpl > 12) |> show_query() produces a query with a nested SELECT inside another SELECT, even though the dplyr code has no explicit nesting. Why does dbplyr generate a subquery here?
4. You write mpg_db |> mutate(z = my_function(hwy)) using a function that doesn’t exist in SQL. At what point does this actually fail, and why not sooner?
5. Why does nrow() return NA on a lazy table instead of the actual number of matching rows?

TipSolutions

1. show_query() only displays the SQL a pipeline would run, without contacting the database at all. collect() actually sends that SQL to the database, executes it, and returns the result as a real tibble in R’s memory. You can call show_query() as many times as you like with no cost; collect() is the step that does the actual work.

2. mean() inside a dbplyr pipeline gets translated to SQL’s AVG() function, which silently ignores NULL (missing) values as part of its standard behavior, unlike R’s own mean(), which propagates NA unless you add na.rm = TRUE. The teammate’s query is quietly behaving like mean(price, na.rm = TRUE) even though nothing in the code asked for that.

3. SQL evaluates a query’s WHERE clause before its SELECT clause, so a single-level query has no way to filter on kpl, an alias that’s only defined in the SELECT. dbplyr works around this by generating an inner query that computes kpl, wrapping it as a subquery, and filtering that outer result instead, since by then kpl is an ordinary column of the subquery’s output.

4. It fails when the query is actually executed against the database, which happens at collect() (or when printing the lazy table, since that collects a preview). show_query() alone doesn’t fail, because generating the SQL text doesn’t require the database to check whether every function mentioned in it actually exists; dbplyr just writes an unrecognized function call into the SQL literally and lets the database be the one to reject it.

5. nrow() would need to actually count the matching rows, which means running the query, and a lazy table hasn’t been run yet; it’s only a description of a query. NA signals “unknown until you execute this,” rather than dbplyr guessing or silently running the query just to answer nrow(). Calling collect() first (or running an explicit counting query) gives you a real number.