21-databases.Rmd

# Databases

**Learning objectives:**

-   Use {DBI} to connect to a database and retrieve data.
-   Use {dbplyr} to translate dplyr code to SQL.

```{r 21-packages-used, message=FALSE, warning=FALSE}
library(DBI) 
library(dbplyr) 
library(tidyverse) 
```

## Database basics {-}

![](https://media.tenor.com/OUpv1OW0bJMAAAAM/database-db.gif)

-   database (db) = collection of data frames (dfs)
-   each df = "table"
-   named columns where every value is the same type
-   db tables vs dfs:
    -   db tables on disk (can be huge), dfs in memory (limited)
    -   db tables have indexes, dfs don't
    -   dbs row-oriented for fast data collection, dfs column-oriented for fast analysis

##  Connecting to a database {-}

-   {DBI} = generic SQL interface
-   Specific package for your DBMS ({RPostgres}, {RMariaDB}, {duckdb}, etc)
-   {odbc} if no specific package available

```{r}
con <- DBI::dbConnect(duckdb::duckdb())
```
 
- When using duckdb in a project
```{r,eval=FALSE,warning=FALSE,message=FALSE}
con <- DBI::dbConnect(duckdb::duckdb(), dbdir = "duckdb")
```

##  Load some data {-}

```{r}
dbWriteTable(con, "mpg", ggplot2::mpg)
dbWriteTable(con, "diamonds", ggplot2::diamonds)
```

## DBI basics {-}
```{r}
dbListTables(con)


con |> 
  dbReadTable("diamonds") |> 
  as_tibble()
```

- SQL Syntax

```{r}
sql <- "
  SELECT carat, cut, clarity, color, price 
  FROM diamonds 
  WHERE price > 15000
"
```

```{r}
as_tibble(dbGetQuery(con, sql))
```

## dbplyr basics {-}

```{r}
diamonds_db <- tbl(con, "diamonds")

diamonds_db
```

```{r}
big_diamonds_db <- diamonds_db |> 
  filter(price > 15000) |> 
  select(carat:clarity, price)

big_diamonds_db
```

```{r}
big_diamonds_db |>
  show_query()
```

- `collect()` moves data into R

```{r}
big_diamonds <- big_diamonds_db |> 
  collect()
big_diamonds
```

## SQL {-}

```{r}
dbplyr::copy_nycflights13(con)


flights <- tbl(con, "flights")
planes <- tbl(con, "planes")
```

## SQL basics {-}

-   *statements* = top level
    -   `CREATE` = new tables
    -   `INSERT` = add data
    -   `SELECT` = retrieve data
        -   aka "queries"

```{r}
flights |> show_query()

planes |> show_query()

```

-   `WHERE` = `filter()`
-   `ORDER BY` = `arrange()`

```{r}
flights |> 
  filter(dest == "IAH") |> 
  arrange(dep_delay) |>
  show_query()
```

## SELECT {-}

`SELECT` = tons of things!

-   `select()`, `rename()`, and `relocate()`

```{r}
planes |> 
  select(tailnum, type, manufacturer, model, year) |>
  show_query()


planes |> 
  select(tailnum, type, manufacturer, model, year) |> rename(year_built = year) |> 
  show_query()


planes |> 
  select(tailnum, type, manufacturer, model, year) |>
  relocate(manufacturer, model, .before = type) |> 
  show_query()
```

Not shown: `mutate()`, `summarize()` are also `SELECT`

## Subqueries {-}

Sometimes {dbplyr} uses subqueries to translate {dplyr} code

-   **subquery** = query used in `FROM` in place of a table

```{r}
flights |> 
  mutate(
    year1 = year + 1,
    year2 = year1 + 1
  ) |> 
  show_query()
```

## Joins {-}

SQL joins similar to {dplyr} joins

```{r}
flights |> 
  left_join(planes |> rename(year_built = year), by = "tailnum") |> 
  show_query()
```

## Other verbs {-}

-   `distinct()`
-   `slice_*()`
-   `intersect()`
-   `tidyr::pivot_longer()`
-   `tidyr::pivot_wider()`
-   Full list on [dbplyr website](https://dbplyr.tidyverse.org/reference/)

## Function translations {-}

How does {dbplyr} deal with `mean()` vs `median()`?

```{r}
summarize_query <- function(df, ...) {
  df |> 
    summarize(...) |> 
    show_query()
}
mutate_query <- function(df, ...) {
  df |> 
    mutate(..., .keep = "none") |> 
    show_query()
}
```

```{r}
flights |> 
  group_by(year, month, day) |>  
  summarize_query(
    mean = mean(arr_delay, na.rm = TRUE),
    median = median(arr_delay, na.rm = TRUE)
  )
```

```{r}
flights |> 
  group_by(year, month, day) |>  
  mutate_query(
    mean = mean(arr_delay, na.rm = TRUE),
  )
```

```{r}
flights |> 
  group_by(dest) |>  
  arrange(time_hour) |> 
  mutate_query(
    lead = lead(arr_delay),
    lag = lag(arr_delay)
  )
```


## Clean up {-}

```{r clean-up}
dbDisconnect(con, shutdown = TRUE) 
```

## Meeting Videos {-}

### Cohort 7 {-}

`r knitr::include_url("https://www.youtube.com/embed/0AWywckm3W4")`

<details>
  <summary> Meeting chat log </summary>
```
00:09:36	Oluwafemi Oyedele:	Hi Tim, Good Evening!!!
00:10:59	Tim Newby:	Hi Oluwafemi :-)
00:14:10	Oluwafemi Oyedele:	start
00:48:43	Oluwafemi Oyedele:	https://dbplyr.tidyverse.org/reference/
00:48:58	Oluwafemi Oyedele:	https://dbplyr.tidyverse.org/articles/dbplyr.html
00:56:01	Oluwafemi Oyedele:	https://sqlfordatascientists.com/
00:56:09	Oluwafemi Oyedele:	https://www.practicalsql.com/
00:57:28	Oluwafemi Oyedele:	stop
```
</details>


### Cohort 8 {-}

`r knitr::include_url("https://www.youtube.com/embed/ylTfwbQq1v0")`

`r knitr::include_url("https://www.youtube.com/embed/HnJ3ZY1seY4")`