---
title: "databases & dplyr"
subtitle: "Lecture 20"
author: "Dr. Colin Rundel"
footer: "Sta 323 - Spring 2026"
format:
  revealjs:
    theme: slides.scss
    transition: fade
    slide-number: true
    self-contained: true
execute:
  echo: true
  warning: true
engine: knitr
webr:
  packages:
    - jsonlite
---

```{r setup}
#| message: False
#| warning: False
#| include: False
options(
  width=70
)

knitr::opts_chunk$set(
  fig.align = "center", fig.retina = 2, dpi = 150,
  out.width = "100%",
  warning = TRUE
)

library(dplyr)
library(RSQLite)
```

# The why of databases


## Numbers every programmer should know

| Task                                | Timing (ns)       | Timing (μs)       |
|-------------------------------------|-------------------|-------------------|
| L1 cache reference                  | 0.5               | 0.0005            |
| L2 cache reference                  | 7                 | 0.007             |
| Main memory reference               | 100               | 0.1               |
| Random seek SSD                     | 150,000           | 150               |
| Read 1 MB sequentially from memory  | 250,000           | 250               |
| Read 1 MB sequentially from SSD     | 1,000,000         | 1,000             |
| Disk seek                           | 10,000,000        | 10,000            |  
| Read 1 MB sequentially from disk    | 20,000,000        | 20,000            |
| Send packet CA->Netherlands->CA     | 150,000,000       | 150,000           |


::: {.aside}
From [jboner/latency.txt](https://gist.github.com/jboner/2841832) & [sirupsen/napkin-math](https://github.com/sirupsen/napkin-math) <br/>
Jeff Dean's original [talk](http://static.googleusercontent.com/media/research.google.com/en/us/people/jeff/stanford-295-talk.pdf) 
:::


## Implications for big data

Let's imagine we have a *10 GB* flat data file and that we want to select certain rows based on a particular criterion (filtering). 

This requires a sequential read across the entire data set.


| File Location    | Performance                        | Time        |
|:-----------------|:-----------------------------------|:------------|
| in memory        | $10~GB \times (250~\mu s / 1~MB)$  | 2.5 seconds |
| on disk (SSD)    | $10~GB \times (1~ms / 1~MB)$       | 10 seconds  |
| on disk (HD)     | $10~GB \times (20~ms / 1~MB)$      | 200 seconds |

<br/>

This is just for *reading* the sequential data, if we make any modifications (*writing*) or the data is fragmented things are much worse. 


## Blocks

#### Cost:
::: {.center}
 Disk << SSD <<< Memory
:::


#### Speed:
::: {.center}
Disk <<< SSD << Memory
:::

<br/>

::: {.fragment}
Disk is cheap but slow; memory is fast but limited. 

What do we do when our data doesn't fit in memory?
:::

::: {.fragment}
Create *blocks* — group related rows and read multiple rows at a time, loading only what's needed (skip what we dont want). 

Optimal block size depends on the task and the storage medium.
:::


## Linear vs Binary Search

Blocks reduce I/O, but any query still requires scanning all of them — a linear search requiring $\mathcal{O}(N)$ reads.

. . .

<br/>

We can do better if we are careful about how we structure our data, specifically sorting some (or all) of the columns.

* Sorting is expensive, $\mathcal{O}(N \log N)$, but it only needs to be done once. 

* After sorting, we can use a binary search for any subsetting tasks, $\mathcal{O}(\log N)$

* In databases these "sorted" columns are referred to as *indexes*.

* Indexes require additional storage, but are usually small enough to be kept in memory even when the blocks need to stay on disk.


## So why databases?

Implementing blocks, indexes, and query optimization correctly is hard:

* The optimal strategy depends on your queries — and they change

* Data is modified (inserts, updates, deletes) — structures must stay consistent

* Multiple users may access data simultaneously — concurrency must be managed

* Failures happen — data must remain correct and recoverable

::: {.fragment}
A database management system (DBMS) handles all of this for you, plus provides a standard query language (SQL) to express what you want, not how to get it.
:::


# Databases

## SQLite

SQLite is a lightweight, serverless, relational database engine:

* Self-contained — the entire database lives in a single file (or in memory)

* No separate server process — the library runs inside your application

* Accessible directly via a CLI (`sqlite3`) or through language bindings (R, Python, etc.)

* Full SQL support — joins, indexes, transactions, views, and more

* Ubiquitous — embedded in browsers, phones, operating systems, and countless apps — likely the most widely deployed database engine in the world

. . .

It is not designed for high-concurrency or multi-user write workloads, but is an excellent tool for learning SQL and for single-user / embedded use cases.

::: {.aside}
See [sqlite.org](https://www.sqlite.org/) for more details
:::


## R & databases - DBI

The DBI package provides a consistent R interface to most relational database. Rather than learning a different API for each database, DBI gives you one set of functions for:

* connecting/disconnecting
* creating and executing statements
* extracting results
* error/exception handling
* reading database metadata
* transaction management


::: {.aside}
See [r-dbi.org](https://www.r-dbi.org/) for more details
:::


## RSQLite

RSQLite provides the backend needed to use DBI with SQLite databases. Postgres, MySQL, DuckDB, and [many others](https://r-dbi.org/backends/) follow similar DBI patterns.

::: {.xsmall}
```{r}
library(RSQLite)
```
:::

`RSQLite` re-exports DBI functions so we do not need to load `DBI` as well.

. . .

Once loaded, create a connection to your database:

```{r include=FALSE}
unlink("employees.sqlite")
```

::: {.xsmall}
```{r}
con = dbConnect(RSQLite::SQLite(), "employees.sqlite")
str(con)
```
:::

::: {.aside}
If `employees.sqlite` does not exist it will be created; if it does exist the existing database will be opened.
:::


## Example Table

Let's walk through DBI operations using a simple employee table.

::: {.small}
```{r}
employees = tibble(
  name   = c("Alice","Bob","Carol","Dave","Eve","Frank"),
  email  = c("alice@company.com", "bob@company.com",
             "carol@company.com", "dave@company.com",
             "eve@company.com",   "frank@company.com"),
  salary = c(52000, 40000, 30000, 33000, 44000, 37000),
  dept   = c("Accounting", "Accounting","Sales",
             "Accounting","Sales","Sales"),
)
```
:::

. . .

::: {.small}
```{r}
# Show current tables
dbListTables(con)
```
:::

::: {.small}
```{r}
# Create a new table and populate it
dbWriteTable(con, name = "employees", value = employees)
dbListTables(con)
```
:::


## Removing Tables

::: {.small}
```{r}
# Create employs
dbWriteTable(con, "employs", employees)
dbListTables(con)
```
:::

<p/>

. . .

::: {.small}
```{r}
# Delete employs
dbRemoveTable(con,"employs")
dbListTables(con)
```
:::


## Querying Tables

Database queries are transactional (see [ACID](https://en.wikipedia.org/wiki/ACID)) and follow the steps:

::: {.small}
```{r}
(res = dbSendQuery(con, "SELECT * FROM employees"))
```
:::

. . .

::: {.small}
```{r}
dbFetch(res)
dbHasCompleted(res)
```
:::

. . .

::: {.small}
```{r}
dbClearResult(res)
```
:::


## For convenience

`dbGetQuery()` combines all the preceeding steps:

::: {.small}
```{r}
(res = dbGetQuery(con, "SELECT * FROM employees"))
```
:::

## Creating tables

`dbCreateTable()` will create a new table with a schema based on an existing data.frame / tibble, but it does not populate that table with data.

::: {.small}
```{r}
dbCreateTable(con, "iris", iris)
(res = dbGetQuery(con, "select * from iris"))
```
:::


## Adding to tables

Use `dbAppendTable()` to add data to an existing table.

::: {.small}
```{r}
dbAppendTable(con, name = "iris", value = iris)
```
:::

. . .

::: {.small}
```{r}
dbGetQuery(con, "select * from iris") |> 
  as_tibble()
```
:::


## Closing the connection

We can explicitly close the connection to the database when we're done with it, but R will automatically close it when the connection object is garbage collected.

::: {.small}
```{r}
con
dbDisconnect(con)
```
:::

<p/>

::: {.small}
```{r}
con
```
:::


# dplyr & databases


## Creating a database 

```{r include=FALSE}
unlink("flights.sqlite")
```

We will now create a new SQLite database,

::: {.mxsmall}
```{r}
db = DBI::dbConnect(RSQLite::SQLite(), "flights.sqlite") # New flights database
```
:::

and copy data using `dplyr`'s convenience functions

::: {.mxsmall}
```{r}
( flight_tbl = dplyr::copy_to(
    db, nycflights13::flights, name = "flights", temporary = FALSE) )
```
:::


## What have we created?

All of this data now lives in the database on the *filesystem* not in *memory*,

::: {.small}
```{r}
pryr::object_size(db)
pryr::object_size(flight_tbl)
```

```{r}
pryr::object_size(nycflights13::flights)
```
:::

. . .

::: {.small}
```{r}
fs::dir_info(glob = "*.sqlite") |>
  select(path, type, size)
```
:::


## What is `flight_tbl`? {.scrollable}

::: {.xsmall}
```{r}
class(nycflights13::flights)
class(flight_tbl)
```
:::

. . .

::: {.xsmall}
```{r}
str(flight_tbl)
```
:::


## Accessing existing tables

If we have a database with an existing table, we can create a reference to it using `dplyr::tbl()`,

::: {.small}
```{r}
dplyr::tbl(db, "flights")
```
:::


## Using dplyr with sqlite 

Just like with local data frames, dplyr can be used to manipulate data in the database.

:::: {.columns .small}
::: {.column width='50%'}
```{r}
(oct_21 = flight_tbl |>
   filter(month == 10, day == 21) |>
   select(origin, dest, tailnum)
)
```
:::
::: {.column width='50%' .fragment}
```{r}
dplyr::collect(oct_21)
```
:::
::::



## Laziness

dplyr / dbplyr uses lazy evaluation as much as possible, particularly when working with non-local backends.

* When building a query, we don't want the entire table, often we want just enough to check if our query is working / makes sense.

* Since we would prefer to run one complex query over many simple queries, laziness allows for verbs to be strung together.

* Therefore, by default `dplyr`

    * won't connect and query the database until absolutely necessary (e.g. showing output),

    * and unless explicitly told to, will only query a handful of rows to give a sense of what the result will look like.
    
    * we can force evaluation via `compute()`, `collect()`, or `collapse()`


## A crude benchmark

:::: {.columns .small}
::: {.column width='50%'}
```{r}
system.time({
  (oct_21 = flight_tbl |>
    filter(month == 10, day == 21) |>
    select(origin, dest, tailnum)
  )
})
```
:::
::: {.column width='50%'}
```{r}
system.time({
  print(oct_21) |> 
    capture.output() |> 
    invisible()
})
```
:::
::::

. . .

:::: {.columns .small}
::: {.column width='50%'}
```{r}
system.time({
  dplyr::collect(oct_21) |> 
    capture.output() |> 
    invisible()
})
```
:::
::::




## `show_query()` - dplyr to SQL

::: {.small}
```{r}
class(oct_21)
```

```{r}
dplyr::show_query(oct_21)
```
:::

## More complex queries

:::: {.columns .small}
::: {.column width='50%'}
```{r}
oct_21 |> 
  summarize(
    n=n(), .by = c(origin, dest)
  )
```
:::
::: {.column width='50%' .fragment}
```{r}
oct_21 |> 
  summarize(
    n=n(), .by = c(origin, dest)
  ) |> 
  dplyr::show_query()
```
:::
::::

##

::: {.small}
```{r}
oct_21 |> 
  count(origin, dest) |> 
  dplyr::show_query()
```
:::

## SQL Translation 

In general, dplyr / dbplyr knows how to translate basic math, logical, and summary functions from R to SQL. 

dbplyr has the function, `translate_sql()`, that lets you experiment with how R functions are translated to SQL. 

. . .

::: {.xsmall}
```{r}
#| error: True
con = dbplyr::simulate_dbi()
dbplyr::translate_sql(x == 1 & (y < 2 | z > 3), con=con)
dbplyr::translate_sql(x ^ 2 < 10, con=con)
dbplyr::translate_sql(x %% 2 == 10, con=con)
dbplyr::translate_sql(sd(x), con=con)
```
:::


##


::: {.xsmall}
```{r}
#| error: True
dbplyr::translate_sql(mean(x), con=con)
dbplyr::translate_sql(mean(x, na.rm=TRUE), con=con)
```
:::

. . .

::: {.xsmall}
```{r}
#| error: True

dbplyr::translate_sql(paste(x,y), con=con)
dbplyr::translate_sql(cumsum(x), con=con)
dbplyr::translate_sql(lag(x), con=con)
```
:::

## Dialectic variations?

By default `dbplyr::translate_sql()` will translate R / dplyr code into ANSI SQL, if we want to see results specific to a certain database we can pass in a connection object,

::: {.small}
```{r}
dbplyr::translate_sql(sd(x), con = db)
dbplyr::translate_sql(paste(x,y), con = db)
dbplyr::translate_sql(cumsum(x), con = db)
dbplyr::translate_sql(lag(x), con = db)
```
:::


::: {.aside}
`db` here is our SQLite connection to the flights database, so the translations are specific to SQLite's SQL dialect.
:::

## Complications?

Not all R functions have a translation to SQL, and not all translations are perfect - when `dbplyr` encounters a function it doesn't know how to translate, it will include the function verbatim and hope for the best.

::: {.small}
```{r}
#| error: True
oct_21 |> mutate(tailnum_n_prefix = grepl("^N", tailnum))
```

```{r}
oct_21 |> mutate(tailnum_n_prefix = grepl("^N", tailnum)) |> show_query()
```
:::


## Joins

```{r include=FALSE}
dplyr::copy_to(db, nycflights13::airlines, name = "airlines", temporary = FALSE, overwrite = TRUE)
airlines_tbl = dplyr::tbl(db, "airlines")
```

Join verbs (`left_join()`, `inner_join()`, etc.) work with database tables exactly as they do with data frames:

:::: {.columns .xsmall}
::: {.column width='50%'}
```{r}
flight_tbl |>
  left_join(airlines_tbl, by = "carrier") |>
  count(name, origin) |>
  arrange(desc(n))
```
:::
::: {.column width='50%' .fragment}
```{r}
flight_tbl |>
  left_join(airlines_tbl, by = "carrier") |>
  count(name, origin) |>
  arrange(desc(n)) |>
  show_query()
```
:::
::::


## Computation happens in the database {.scrollable}

::: {.medium}
All dplyr operations on a database table are translated to SQL and executed by the database — no R computation happens until you `collect()`.
:::

. . .

::: {.medium}
This means you cannot mix local objects with database tables:
:::

::: {.mxsmall}
```{r}
#| error: true
local_df = data.frame(origin = c("JFK", "LGA"))
flight_tbl |> inner_join(local_df, by = "origin")
```
:::

. . .

::: {.medium}
To work around this, either `collect()` the database table first, or copy the local object into the database:
:::

::: {.mxsmall}
```{r}
flight_tbl |> inner_join(local_df, by = "origin", copy = TRUE)
```
:::


## Window functions

::: {.medium}
Window functions compute a value for each row using values from a surrounding partition. Within a `group_by()` + `mutate()`, dplyr translates these to SQL `OVER()` clauses:
:::

:::: {.columns .mxsmall}
::: {.column width='50%'}
```{r}
flight_tbl |>
  filter(!is.na(dep_delay)) |>
  group_by(origin) |>
  mutate(delay_rank = min_rank(desc(dep_delay))) |>
  filter(delay_rank <= 3) |>
  select(origin, dest, dep_delay, delay_rank) |>
  arrange(origin, delay_rank)
```
:::
::: {.column width='50%' .fragment}
```{r}
flight_tbl |>
  filter(!is.na(dep_delay)) |>
  group_by(origin) |>
  mutate(delay_rank = min_rank(desc(dep_delay))) |>
  filter(delay_rank <= 3) |>
  select(origin, dest, dep_delay, delay_rank) |>
  arrange(origin, delay_rank) |>
  show_query()
```
:::
::::



# SQL to R / dplyr


## Running SQL queries against R objects

There are two packages that implement this in R which take different approaches,

* [`tidyquery`](https://github.com/ianmcook/tidyquery) - this package parses your SQL code using the `queryparser` package and then translates the result into R / dplyr code.

* [`sqldf`](https://github.com/ggrothendieck/sqldf) - transparently creates a database with the data and then runs the query using that database. Defaults to SQLite but other backends are available.


## tidyquery

:::: {.columns .xsmall}
::: {.column width='50%'}
```{r}
data(flights, package = "nycflights13")

tidyquery::query(
  "SELECT origin, dest, COUNT(*) AS n
   FROM flights
   WHERE month = 10 AND day = 21
   GROUP BY origin, dest"
)
```
:::
::: {.column width='50%'}
```{r}
flights |>
  tidyquery::query(
    "SELECT origin, dest, COUNT(*) AS n
     WHERE month = 10 AND day = 21
     GROUP BY origin, dest"
  ) |>
  arrange(desc(n))
```
:::
::::


## Translating to dplyr

::: {.small}
```{r}
tidyquery::show_dplyr(
  "SELECT origin, dest, COUNT(*) AS n
   FROM flights
   WHERE month = 10 AND day = 21
   GROUP BY origin, dest"
)
```
:::


## sqldf

:::: {.columns .xsmall}
::: {.column width='50%'}
```{r}
sqldf::sqldf(
  "SELECT origin, dest, COUNT(*) AS n
   FROM flights
   WHERE month = 10 AND day = 21
   GROUP BY origin, dest"
)
```
:::
::: {.column width='50%'}
```{r}
sqldf::sqldf(
  "SELECT origin, dest, COUNT(*) AS n
   FROM flights
   WHERE month = 10 AND day = 21
   GROUP BY origin, dest"
) |>
  as_tibble() |>
  arrange(desc(n))
```
:::
::::


# Conclusions


## dplyr / dbplyr vs DBI

dplyr / dbplyr translates R expressions into SQL **SELECT** queries — it is a read-only interface:

::: {.small}
| Operation                  | dplyr / dbplyr | DBI                                      |
|:---------------------------|:--------------:|:-----------------------------------------|
| Query / filter / summarize | ✓              | ✓                                        |
| Insert rows                | ✗              | ✓ (`dbAppendTable()`, `dbExecute()`)     |
| Update rows                | ✗              | ✓ (`dbExecute()`)                        |
| Delete rows                | ✗              | ✓ (`dbExecute()`)                        |
| Create / drop tables       | ✗              | ✓ (`dbCreateTable()`, `dbRemoveTable()`) |
:::

<br/>

::: {.fragment}
For anything beyond reading data, go through DBI directly.
:::


## Closing thoughts

The ability of dplyr to translate from R expression to SQL is an incredibly powerful tool making your data processing workflows portable across a wide variety of data backends.

Some tools and ecosystems that are worth learning about:

* Spark - [sparkR](https://spark.apache.org/docs/latest/api/R/index.html), [spark SQL](https://spark.apache.org/docs/latest/api/sql/index.html), [sparklyr](https://spark.posit.co/)

* [DuckDB](https://duckdb.org/)

* Apache [Arrow](https://arrow.apache.org/)
