dbplyr.R
In dbplyr: A 'dplyr' Back End for Databases

## ----include = FALSE----------------------------------------------------------
knitr::opts_chunk$set(collapse = TRUE, comment = "#>")
options(tibble.print_min = 6L, tibble.print_max = 6L, digits = 3)

## ----eval = FALSE-------------------------------------------------------------
#  install.packages("dbplyr")

## ----setup, message = FALSE---------------------------------------------------
library(dplyr)
con <- DBI::dbConnect(RSQLite::SQLite(), dbname = ":memory:")

## ----eval = FALSE-------------------------------------------------------------
#  con <- DBI::dbConnect(RMariaDB::MariaDB(),
#    host = "database.rstudio.com",
#    user = "hadley",
#    password = rstudioapi::askForPassword("Database password")
#  )

## -----------------------------------------------------------------------------
copy_to(con, nycflights13::flights, "flights",
  temporary = FALSE, 
  indexes = list(
    c("year", "month", "day"), 
    "carrier", 
    "tailnum",
    "dest"
  )
)

## -----------------------------------------------------------------------------
flights_db <- tbl(con, "flights")

## -----------------------------------------------------------------------------
flights_db 

## -----------------------------------------------------------------------------
flights_db %>% select(year:day, dep_delay, arr_delay)

flights_db %>% filter(dep_delay > 240)

flights_db %>% 
  group_by(dest) %>%
  summarise(delay = mean(dep_delay))

## -----------------------------------------------------------------------------
tailnum_delay_db <- flights_db %>% 
  group_by(tailnum) %>%
  summarise(
    delay = mean(arr_delay),
    n = n()
  ) %>% 
  arrange(desc(delay)) %>%
  filter(n > 100)

## -----------------------------------------------------------------------------
tailnum_delay_db

## -----------------------------------------------------------------------------
tailnum_delay_db %>% show_query()

## -----------------------------------------------------------------------------
tailnum_delay <- tailnum_delay_db %>% collect()
tailnum_delay

## ----error = TRUE-------------------------------------------------------------
nrow(tailnum_delay_db)

tail(tailnum_delay_db)