exclude: true

```{r, message=FALSE, warning=FALSE, include=FALSE}
options(
  htmltools.dir.version = FALSE, # for blogdown
  width = 80,
  tibble.width = 80
)

knitr::opts_chunk$set(
  fig.align = "center"
)

htmltools::tagList(rmarkdown::html_dependency_font_awesome())


library(dplyr)
```

```{r include = FALSE, error=TRUE}
path = here::here("flights.sqlite")
if (file.exists(path))
  unlink(path)
```



---

## Creating a database 

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

--

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

---

## What have we created?

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

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

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

```{bash}
ls -lah *.sqlite
```


---

## What is `flight_tbl`?

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

--

```{r}
str(flight_tbl)
```


---

## Accessing existing tables

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


---

## Using dplyr with sqlite 

.pull-left[
```{r}
(oct_21 = flight_tbl %>%
   filter(month == 10, day == 21) %>%
   select(origin, dest, tailnum)
)
```
]

--

.pull-right[
```{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

.pull-left[
```{r}
system.time({
  (oct_21 = flight_tbl %>%
    filter(month == 10, day == 21) %>%
    select(origin, dest, tailnum)
  )
})
```
]

--

.pull-right[
```{r}
system.time({
  print(oct_21) %>% 
    capture.output() %>% 
    invisible()
})
```
]

--

<br/>

<div>
.pull-left[
```{r}
system.time({
  dplyr::collect(oct_21) %>% 
    capture.output() %>% 
    invisible()
})
```
]
</div>




---

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

```{r}
class(oct_21)
```

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

---

## More complex queries

```{r}
oct_21 %>% group_by(origin, dest) %>% summarize(n=n())
```

```{r}
oct_21 %>% group_by(origin, dest) %>% summarize(n=n()) %>% show_query()
```

---

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

```{r error=TRUE}
dbplyr::translate_sql(x == 1 & (y < 2 | z > 3))
dbplyr::translate_sql(x ^ 2 < 10)
dbplyr::translate_sql(x %% 2 == 10)
```

```{r error=TRUE}
dbplyr::translate_sql(mean(x))
dbplyr::translate_sql(mean(x, na.rm=TRUE))
```


---

```{r error=TRUE}
dbplyr::translate_sql(sd(x))
dbplyr::translate_sql(paste(x,y))
dbplyr::translate_sql(cumsum(x))
dbplyr::translate_sql(lag(x))
```

---

## 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,

```{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?

```{r error=TRUE}
oct_21 %>% mutate(tailnum_n_prefix = grepl("^N", tailnum))
```

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

---
class: middle, center

# 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 teh data and then runs the query using that database. Defaults to SQLite but other backends are available.

---

## tidyquery

.pull-left[
```{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"
)
```
]

--

.pull-right[
```{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

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

---

## sqldf

.pull-left[
```{r}
sqldf::sqldf(
  "SELECT origin, dest, COUNT(*) AS n
   FROM flights
   WHERE month = 10 AND day = 21
   GROUP BY origin, dest"
)
```
]

--

.pull-right[
```{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))
```
]


---
class:center, middle

# Query performance

---

## Setup

To give us a bit more variety, I am going to add one more table to our SQLite database - `nycflights13::planes` which has details on the characteristics of the planes in the dataset as identified by their tail numbers.

```{r}
dplyr::copy_to(db, nycflights13::planes, name = "planes", temporary = FALSE)
```

<br/>

All of the following code will be run in the SQLite commandline interface, to make sure you have the database make sure you've created the database and copied both the flights and planes tables into the db. 

The database can then be opened from the terminal tab using,
```shell
sqlite3 flights.sqlite
```

---

As before we should set a could of configuration options so that our output is readable, we include `.timer on` so that we get time our queries.

```sql
sqlite> .headers on
sqlite> .mode column
sqlite> .timer on
```

--

.small[

```sql
sqlite> select * from flights limit 10;
year  month  day  dep_time  sched_dep_time  dep_delay  arr_time  sched_arr_time  arr_delay  carrier  flight  tailnum  origin  dest  air_time  distance  hour  minute  time_hour   
----  -----  ---  --------  --------------  ---------  --------  --------------  ---------  -------  ------  -------  ------  ----  --------  --------  ----  ------  ------------
2013  1      1    517       515             2.0        830       819             11.0       UA       1545    N14228   EWR     IAH   227.0     1400.0    5.0   15.0    1357034400.0
2013  1      1    533       529             4.0        850       830             20.0       UA       1714    N24211   LGA     IAH   227.0     1416.0    5.0   29.0    1357034400.0
2013  1      1    542       540             2.0        923       850             33.0       AA       1141    N619AA   JFK     MIA   160.0     1089.0    5.0   40.0    1357034400.0
2013  1      1    544       545             -1.0       1004      1022            -18.0      B6       725     N804JB   JFK     BQN   183.0     1576.0    5.0   45.0    1357034400.0
2013  1      1    554       600             -6.0       812       837             -25.0      DL       461     N668DN   LGA     ATL   116.0     762.0     6.0   0.0     1357038000.0
2013  1      1    554       558             -4.0       740       728             12.0       UA       1696    N39463   EWR     ORD   150.0     719.0     5.0   58.0    1357034400.0
2013  1      1    555       600             -5.0       913       854             19.0       B6       507     N516JB   EWR     FLL   158.0     1065.0    6.0   0.0     1357038000.0
2013  1      1    557       600             -3.0       709       723             -14.0      EV       5708    N829AS   LGA     IAD   53.0      229.0     6.0   0.0     1357038000.0
2013  1      1    557       600             -3.0       838       846             -8.0       B6       79      N593JB   JFK     MCO   140.0     944.0     6.0   0.0     1357038000.0
2013  1      1    558       600             -2.0       753       745             8.0        AA       301     N3ALAA   LGA     ORD   138.0     733.0     6.0   0.0     1357038000.0
Run Time: real 0.051 user 0.000258 sys 0.000126
```

```sql
sqlite> select * from planes limit 10;
tailnum  year  type                     manufacturer      model      engines  seats  speed  engine   
-------  ----  -----------------------  ----------------  ---------  -------  -----  -----  ---------
N10156   2004  Fixed wing multi engine  EMBRAER           EMB-145XR  2        55            Turbo-fan
N102UW   1998  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N103US   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N104UW   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N10575   2002  Fixed wing multi engine  EMBRAER           EMB-145LR  2        55            Turbo-fan
N105UW   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N107US   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N108UW   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N109UW   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
N110UW   1999  Fixed wing multi engine  AIRBUS INDUSTRIE  A320-214   2        182           Turbo-fan
Run Time: real 0.001 user 0.000159 sys 0.000106
```
]

---

## Exercise 1

Write a query that determines the total number of seats available on all of the planes that flew out of New York in 2013.

---

## Options

* incorrect 

```sql
sqlite> select sum(seats) from flights natural left join planes;

sum(seats)
----------
614366    
Run Time: real 0.148 user 0.139176 sys 0.007804
```

--

* join and select

```sql
sqlite> select sum(seats) from flights left join planes using (tailnum);

sum(seats)
----------
38851317  
Run Time: real 0.176 user 0.167993 sys 0.007354
```

* select then join

```sql
sqlite> select sum(seats) from (select tailnum from flights) left join (select tailnum, seats from planes) using (tailnum);

sum(seats)
----------
38851317  
Run Time: real 0.174 user 0.166085 sys 0.007122
```

---

## `EXPLAIN QUERY PLAN`

```sql
sqlite> explain query plan select sum(seats) from flights left join planes using (tailnum);

QUERY PLAN
|--SCAN flights
`--SEARCH planes USING AUTOMATIC COVERING INDEX (tailnum=?)
```

```sql
sqlite> explain query plan select sum(seats) from (select tailnum from flights) left join (select tailnum, seats from planes) using (tailnum);

QUERY PLAN
|--MATERIALIZE SUBQUERY 2
|  `--SCAN planes
|--SCAN flights
`--SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (tailnum=?)
```

--

Key things to look for:

* `SCAN` - indicates that a full table scan is occurring 

* `SEARCH` - indicates that only a subset of the table rows are visited

* `AUTOMATIC COVERING INDEX` - indicates that a temporary index has been created for this query

---

## Adding indexes

```sql
sqlite> create index flight_tailnum on flights (tailnum);
Run Time: real 0.241 user 0.210099 sys 0.027611
```

```sql
sqlite> create index plane_tailnum on planes (tailnum);
Run Time: real 0.003 user 0.001407 sys 0.001442
```

--

```sql
sqlite> .indexes
flight_tailnum  plane_tailnum
```

---

## Improvements?

```sql
sqlite> select sum(seats) from flights left join planes using (tailnum);
sum(seats)
----------
38851317  
Run Time: real 0.118 user 0.115899 sys 0.001952
```

```sql
sqlite> select sum(seats) from (select tailnum from flights) left join (select tailnum, seats from planes) using (tailnum);
sum(seats)
----------
38851317  
Run Time: real 0.131 user 0.129165 sys 0.001214
```

--

```sql
sqlite> explain query plan select sum(seats) from flights left join planes using (tailnum);
QUERY PLAN
|--SCAN flights USING COVERING INDEX flight_tailnum
`--SEARCH planes USING INDEX plane_tailnum (tailnum=?)
```

```sql
sqlite> explain query plan select sum(seats) from (select tailnum from flights) left join (select tailnum, seats from planes) using (tailnum);
QUERY PLAN
|--MATERIALIZE SUBQUERY 2
|  `--SCAN planes
|--SCAN flights USING COVERING INDEX flight_tailnum
`--SEARCH SUBQUERY 2 USING AUTOMATIC COVERING INDEX (tailnum=?)
```

---

## Filtering

```sql
sqlite> select origin, count(*) from flights where origin = "EWR";
origin  count(*)
------  --------
EWR     120835
Run Time: real 0.034 user 0.028124 sys 0.005847
```

```sql
sqlite> explain query plan select origin, count(*) from flights where origin = "EWR";
QUERY PLAN
`--SCAN flights
```

--


```sql
sqlite> select origin, count(*) from flights where origin != "EWR";
origin  count(*)
------  --------
LGA     215941
Run Time: real 0.036 user 0.029798 sys 0.006171
```

```sql
sqlite> explain query plan select origin, count(*) from flights where origin != "EWR";
QUERY PLAN
`--SCAN flights
```

--

```sql
sqlite> create index flights_orig_dest on flights (origin, dest);
Run Time: real 0.267 user 0.232886 sys 0.030270
```

---

## Filtering w/ indexes

```sql
sqlite> select origin, count(*) from flights where origin = "EWR";
origin  count(*)
------  --------
EWR     120835
Run Time: real 0.007 user 0.006419 sys 0.000159

sqlite> select origin, count(*) from flights where origin != "EWR";
origin  count(*)
------  --------
JFK     215941
Run Time: real 0.020 user 0.019203 sys 0.000497
```

--

```sql
sqlite> explain query plan select origin, count(*) from flights where origin = "EWR";
QUERY PLAN
`--SEARCH flights USING COVERING INDEX flights_orig_dest (origin=?)

sqlite> explain query plan select origin, count(*) from flights where origin != "EWR";
QUERY PLAN
`--SCAN flights USING COVERING INDEX flights_orig_dest
```

---

## `!=` alternative

```sql
sqlite> select origin, count(*) from flights where origin > "EWR" OR origin < "EWR";
origin  count(*)
------  --------
JFK     215941
Run Time: real 0.022 user 0.021148 sys 0.001290
```

```sql
sqlite> explain query plan select origin, count(*) from flights where origin > "EWR" OR origin < "EWR";
QUERY PLAN
`--MULTI-INDEX OR
   |--INDEX 1
   |  `--SEARCH flights USING COVERING INDEX flights_orig_dest (origin>?)
   `--INDEX 2
      `--SEARCH flights USING COVERING INDEX flights_orig_dest (origin<?)
```

---

## What about dest?

```sql
sqlite> select dest, count(*) from flights where dest = "LAX";
dest  count(*)
----  --------
LAX   16174
Run Time: real 0.017 user 0.016513 sys 0.000237
```

```sql
sqlite> explain query plan select dest, count(*) from flights where dest = "LAX";
QUERY PLAN
`--SCAN flights USING COVERING INDEX flights_orig_dest
```

--

```sql
sqlite> select dest, count(*) from flights where dest = "LAX" AND origin = "EWR";
dest  count(*)
----  --------
LAX   4912
Run Time: real 0.003 user 0.000729 sys 0.000778
```

```sql
sqlite> explain query plan select dest, count(*) from flights where dest = "LAX" AND origin = "EWR";
QUERY PLAN
`--SEARCH flights USING COVERING INDEX flights_orig_dest (origin=? AND dest=?)
```

---

## Group bys

.pull-left[ .small[
```sql
sqlite> select carrier, count(*) from flights group by carrier;
carrier  count(*)
-------  --------
9E       18460
AA       32729
AS       714
B6       54635
DL       48110
EV       54173
F9       685
FL       3260
HA       342
MQ       26397
OO       32
UA       58665
US       20536
VX       5162
WN       12275
YV       601
Run Time: real 0.172 user 0.114274 sys 0.018946

sqlite> explain query plan select carrier, count(*) from flights group by carrier;
QUERY PLAN
|--SCAN flights
`--USE TEMP B-TREE FOR GROUP BY
```
]

```sql
sqlite> create index flight_carrier on flights (carrier);
Run Time: real 0.131 user 0.113260 sys 0.014691
```
] ]

--

.pull-right[ .small[
```sql
sqlite> select carrier, count(*) from flights group by carrier;
carrier  count(*)
-------  --------
9E       18460
AA       32729
AS       714
B6       54635
DL       48110
EV       54173
F9       685
FL       3260
HA       342
MQ       26397
OO       32
UA       58665
US       20536
VX       5162
WN       12275
YV       601
Run Time: real 0.023 user 0.022521 sys 0.000411

sqlite> explain query plan select carrier, count(*) from flights group by carrier;
QUERY PLAN
`--SCAN flights USING COVERING INDEX flight_carrier
```
] ]

---

## Why not index all the things?

* As mentioned before, creating an index requires additional storage (memory or disk)

* Additionally, when adding or updating data - indexes also need to be updated, making these processes slower (read vs. write tradeoffs)

* Index order matters - `flights (origin, dest)`, `flights (dest, origin)` are not the same and similarly are not the same as separate indexes on `dest` and `origin`.


