02-dplyr-basics.Rmd

---
title: "dplyr Basics"
output: html_notebook
---

## 2.1 - Create a table variable

1. Load the `dplyr`, `DBI` and `dbplyr` libraries
```{r, dplyr}
library(dplyr)
library(dbplyr)
library(DBI)
```

2. *(Optional)* Open a connection to the database if it's currently closed
```{r}
con <- dbConnect(odbc::odbc(), "Postgres Dev")
```

3. Use the `tbl()` and `in_schema()` functions to create a reference to a table
```{r}
tbl(con, in_schema("datawarehouse", "airport"))
```

4. Load the reference, not the table data, into a variable
```{r}
airports <- tbl(con, in_schema("datawarehouse", "airport"))
```

5. Call the variable to see preview the data in the table
```{r}
airports
```

6. Set up the pointers to the other of the tables
```{r}
flights <- tbl(con, in_schema("datawarehouse", "vflight"))
carriers <- tbl(con, in_schema("datawarehouse", "carrier"))
```

## 2.2 - Under the hood 

1. SQL statement that actually runs when we ran `airports` as a command
```{r}
show_query(airports)
```

2. Easily view the resulting query by adding `show_query()` in another piped command
```{r}
airports %>%
  show_query()
```

3. Insert `head()` in between the two statements to see how the SQL changes
```{r}
airports %>%
  head() %>%
  show_query()
```

4. Use `sql_render()` and `simulate_mssql()` to see how the SQL statement changes from vendor to vendor
```{r}
airports %>%
  head() %>%
  sql_render(con = simulate_mssql()) 
```

## 2.3 -  Un-translated R commands

1. Preview how `Sys.time()` is translated
```{r}
airports %>%
  mutate(today = Sys.time()) %>%
  show_query()
```

2. Use PostgreSQL's native commands, in this case `now()`
```{r}
airports %>%
  mutate(today = now()) %>%
  show_query()
```

3. Run the `dplyr` code to confirm it works
```{r}
airports %>%
  mutate(today = now()) %>%
  select(today) %>%
  head()
```

## 2.4 -Using bang-bang

1. Preview how `Sys.time()` is translated
```{r}
airports %>%
  mutate(today = Sys.time()) %>%
  show_query()
```

2. Preview how `Sys.time()` is translated when prefixing `!!`
```{r}
airports %>%
  mutate(today = !!Sys.time()) %>%
  show_query()
```

3. Preview how `Sys.time()` is translated when prefixing `!!`
```{r}
airports %>%
  mutate(today = !!Sys.time()) %>%
  select(today) %>%
  head()
```

## 2.5 - knitr SQL engine

1. Copy the result of the latest `show_query()` exercise
```{r}
airports %>%
  mutate(today = !!Sys.time()) %>%
  show_query()
```

2. Paste the result in this SQL chunk
```{sql, connection = con}
SELECT "airport", "airportname", "city", "state", "country", "lat", "long", '2018-01-26T14:50:10Z' AS "today"
FROM datawarehouse.airport
```

## 2.6 - Basic aggregation

1. How many records are in the **airport** table?
```{r}
tbl(con, in_schema("datawarehouse", "airport"))  %>%
  tally()
```

2. What is the average character length of the airport codes? How many characters is the longest and the shortest airport name?
```{r}
airports %>%
  summarise(
    avg_airport_length = mean(str_length(airport), na.rm = TRUE),
    max_airport_name = max(str_length(airportname), na.rm = TRUE),
    min_airport_name = min(str_length(airportname), na.rm = TRUE),
    total_records = n()
  )
```

3. How many records are in the **carrier** table?
```{r}

```

4. How many characters is the longest **carriername**?
```{r}

```

5. What is the SQL statement sent in exercise 4?
```{r}

```

6. Close the connection
```{r}
dbDisconnect(con)
```