---
title: "databases & dplyr"
subtitle: "Lecture 18"
author: "Dr. Colin Rundel"
footer: "Sta 323 - Spring 2025"
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

Lets imagine we have a *10 GB* flat data file and that we want to select certain rows based on a particular criteria. 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* sequential data, if we make any modifications (*writing*) or the data is fragmented things are much worse. 


## Blocks

<br/>

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


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

<br/>

::: {.fragment}
So usually possible to grow our disk storage to accommodate our data. However, memory is usually the limiting resource, and if we can't fit everything into memory?
:::

<p/>

::: {.fragment}
Create *blocks* - group related data (i.e. rows) and read in multiple rows at a time. Optimal size will depend on the task and the properties of the disk.
:::


## Linear vs Binary Search

Even with blocks, any kind of querying / subsetting of rows requires a linear search, which requires $\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 a databases these "sorted" columns are refered to as *indexes*.

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


## and then?

This is just barely scratching the surface,

* Efficiency gains are not just for disk, access is access

* In general, trade off between storage and efficiency

* Reality is a lot more complicated for everything mentioned so far, lots of very smart people have spent a lot of time thinking about and implementing tools

* Different tasks with different requirements require different implementations and have different criteria for optimization


# Databases

## R & databases - the DBI package

Low level package for interfacing R with Database management systems (DBMS) that provides a common interface to achieve the following functionality:

* connect/disconnect from DB
* create and execute statements in the DB
* extract results/output from statements
* error/exception handling
* information (meta-data) from database objects
* transaction management (optional)


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


## DBI Backends

DBI is a specification, not an implementation, and there are a number of packages that implement the DBI specification for different database systems. [Backends for R-DBI](https://r-dbi.org/backends/) lists all available backends, but some notable ones include:

* `RPostgres`

* `RMariaDB`

* `RSQLite`

* `odbc`

* `bigrquery`

* `duckdb`

* `sparklyr`


## RSQLite

Provides the implementation necessary to use DBI to interface with an SQLite database.

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

this package also loads the necessary DBI functions as well (via re-exporting).

. . .

Once loaded we can create a connection to our database,

::: {.small}
```{r}
con = dbConnect(RSQLite::SQLite(), ":memory:")
str(con)
```
:::


## Example 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@comany.com"),
  salary = c(52000, 40000, 30000, 33000, 44000, 37000),
  dept   = c("Accounting", "Accounting","Sales",
             "Accounting","Sales","Sales"),
)
```
:::

. . .

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

::: {.small}
```{r}
dbWriteTable(con, name = "employees", value = employees)
dbListTables(con)
```
:::


## Removing Tables

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

<p/>

. . .

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


## Querying Tables

Databases queries are transactional (see [ACID](https://en.wikipedia.org/wiki/ACID)) and are broken up into 3 steps:

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

. . .

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

. . .

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


## For convenience

There is also `dbGetQuery()` which combines all three 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

Data can be added to an existing table via `dbAppendTable()`.

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

. . .

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


## Closing the connection

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

<p/>

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


# dplyr & databases


## Creating a database 

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

::: {.small}
```{r}
db = DBI::dbConnect(RSQLite::SQLite(), "flights.sqlite")
( 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)
```
:::

## File size

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


## What is `flight_tbl`?

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

. . .

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


## Accessing existing tables

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


## Using dplyr with sqlite 

:::: {.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. show 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()
})
```
:::
::::




## dplyr -> SQL - `show_query()`

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

```{r}
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)
  ) |> 
  show_query()
```
:::
::::

##

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

## SQL Translation 

In general, dplyr / dbplyr knows how to translate basic math, logical, and summary functions from R to SQL. dbplyr has a 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)
```
:::

. . .

::: {.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(sd(x), con=con)
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)
```
:::


## Complications?

::: {.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()
```
:::


# SQL -> R / dplyr


## Running SQL queries against R objects

There are two packages that implement this in R which take very 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 .small}
::: {.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))
```
:::
::::


## 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.rstudio.com/)

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

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