---
title: "dbplyr"
---

```{r warning=FALSE, message=FALSE}
library(tidyverse)
library(DBI)         # low-level database interface
library(dbplyr)
library(duckdb)     # Specific to duckDB
```


## Connecting to a database

If using remote database, would use appropriate driver (likely MySQL or PostgreSQL driver)
and would supple "connection string" that gives hostname, user account,
database name, etc.


Here we want to use DuckDB, loading a file database:

```{r}
conn <- DBI::dbConnect(duckdb::duckdb(), 
                       dbdir = "~/GitHub/gitRDS/EDS213/bren-eds213-data/database/database.duckdb"
                       )  # adapt the path as needed
```

All the magic is right here.  A data frame view of a database table.

```{r}
DBI::dbListTables(conn)
```

```{r}
species <- tbl(conn, "Species")
species
```

Or using a SQL query

```{r}
species_a <- dbGetQuery(conn, "SELECT * FROM Species WHERE Code LIKE 'a%' ")       # note the quotes!
species_a
```

**Note: This time we get a data frame!**


## There is a more R way of doing this!

Enters `dbplyr` -- your dplyr for databases

```{r}
species %>%
  filter(Relevance == "Study species") %>%
  select(Code, Scientific_name) %>%
  arrange(Scientific_name) %>%
  head(3)
```

Wait what!? How this could have worked?!

### Add show_query() to the end to see what SQL it is sending!

```{r}
species %>%
  filter(Relevance == "Study species") %>%
  select(Scientific_name) %>%
  arrange(Scientific_name) %>%
  head(3) %>% 
  show_query()
```

**Great way to learn SQL!!**

### These "tables" are not true dataframes

```{r}
str(species)  # weirdo structure, gibberish to me
dim(species)  # unknown row dimension
```

What `dbplyr` is doing behind the scenes is translating
all those dplyr operations into SQL, sending the
SQL to the database, retrieving results, etc.

```{r}
# Want a local copy that is a true data frame?  Add collect():
local_copy <- species %>% 
  collect()

str(local_copy)
dim(local_copy)
```

However you can do pretty much anything with these quasi-tables,
including grouping, summarization, joins, etc.

```{r}
species %>%
  group_by(Relevance) %>%
  summarize(num_species = n()) %>% 
  show_query()
```

Or mutating columns gets translated into SQL operations, wild!

```{r}
species %>%
  mutate(Code = paste("X", Code)) %>% 
  show_query()
```

You can even join tables:

```{r}
# loading all the necessary tables
eggs_db <- tbl(conn, "Bird_eggs")
nests_db <- tbl(conn, "Bird_nests")

left_join(nests_db, eggs_db, by="Nest_ID", keep = FALSE) %>%
  head(5)
```

:::{.callout-warning}
## Limitation: 
no way to add or update data.  **dbplyr is view only**.
If you want to add or update data, you'll need to use
DBI functions.
:::

## Close your connection

```{r}
DBI::dbDisconnect(conn, shutdown = TRUE)
```