dm: Relational Data Models

# Demo for presentation at the 16th Berlin R meetup

# {dm} facilitates working with multiple tables

options(tibble.print_min = 6)
options(tibble.print_max = 6)

##
##
##
## Why?
## --------------------------------------------------------------------
##
##
##

library(nycflights13)

# Example dataset: tables linked with each other
?flights
?airports
?airlines
?planes
?weather

library(tidyverse)
flights_base <-
  flights %>%
  select(year, month, day, carrier, tailnum, origin, dest)
flights_base

# carrier column also present in `airlines`, this table contains
# additional information
airlines

flights_base %>%
  left_join(airlines)

# single source of truth: updating in one single location
airlines[airlines$carrier == "UA", "name"] <- "United broke my guitar"

# ...propagates to related records
flights_base %>%
  left_join(airlines)

# Same for airplanes?
planes

flights_base %>%
  left_join(planes)

flights_base %>%
  left_join(planes) %>%
  count(is.na(type))

# Take a closer look at the join
flights_base %>%
  left_join(planes, by = "tailnum")

# Same for airports?
airports

try(
  flights_base %>%
    left_join(airports)
)

# Need to specify join variables!
flights_base %>%
  left_join(airports, by = c("origin" = "faa"))

# cleanup
rm(airlines)

##
##
##
## Keys
## --------------------------------------------------------------------
##
##
##

# Row identifiers
t1 <- tibble(a = 1, b = letters[1:3])
t1
t2 <- tibble(a = 1, c = 1:2)
t2

# What happens here?
left_join(t1, t2)

# When joining, the column(s) must be unique in at least one
# participating table!

# Ensure uniqueness:
airlines %>%
  count(carrier)

airlines %>%
  count(carrier) %>%
  count(n)

planes %>%
  count(tailnum) %>%
  count(n)

# dm shortcut:
planes %>%
  dm::check_key(tailnum)

try(
  planes %>%
    dm::check_key(engines)
)

airports %>%
  dm::check_key(faa)

# FIXME: add dm function that explains why not key candidate

# Why is name not a key candidate for airports?
try(
  airports %>%
    dm::check_key(name)
)

airports %>%
  add_count(name) %>%
  filter(n > 1) %>%
  arrange(name)

# Cleanup
rm(t1, t2)

##
##
##
## Data model
## --------------------------------------------------------------------
##
##
##

library(dm)

# Compound object: tables, relationships, data
dm_nycflights13(cycle = TRUE)

dm_nycflights13(cycle = TRUE) %>%
  dm_draw()

# Selection of tables
dm_nycflights13(cycle = TRUE) %>%
  dm_select_tbl(flights, airlines) %>%
  dm_draw()

dm_nycflights13(cycle = TRUE) %>%
  dm_select_tbl(airports, airlines) %>%
  dm_draw()

try(
  dm_nycflights13() %>%
    dm_select_tbl(bogus)
)

# Accessing tables
dm_nycflights13() %>%
  tbl("airlines")

# Table names
dm_nycflights13() %>%
  src_tbls()

# NB: [, $, [[ and names() also work

# Analogy: parallel vectors in the global environment
x <- 1:5
y <- x + 1
z <- x * y
w <- map(z, ~ runif(.))
tibble(x, y, z, w)

tibble(x = 1:5) %>%
  mutate(y = x + 1) %>%
  mutate(z = x * y) %>%
  mutate(w = map(z, ~ runif(.)))

##
##
##
## Filtering for data models
## --------------------------------------------------------------------
##
##
##

dm_nycflights13()

# Filtering on a table returns a dm object with the filter condition(s) stored
(dm_nyc_filtered <-
  dm_nycflights13() %>%
  dm_filter(airlines, carrier == "AA"))

# Apply all filters and retrieve an "updated" `dm`
dm_nyc_filtered %>%
  dm_apply_filters()

# If a filter condition is phrased wrongly it will only fail, once the filter is being applied
(dm_nyc_fail <-
  dm_nycflights13() %>%
  dm_filter(airports, origin == "EWR"))
try(
  tbl(dm_nyc_fail, "flights")
)
# Mind: when accessing table from a `dm` (using one of: `tbl()`, `[[.dm()`, `$.dm()`),
# only the necessary filter conditions are applied:
tbl(dm_nyc_fail, "weather")

dm_nycflights13() %>%
  dm_filter(flights, origin == "EWR") %>%
  dm_apply_filters()

# ... which then can be filtered on another table
dm_nycflights13() %>%
  dm_filter(airlines, name == "American Airlines Inc.") %>%
  dm_filter(airports, name != "John F Kennedy Intl") %>%
  dm_apply_filters()

aa_non_jfk_january <-
  dm_nycflights13() %>%
  dm_filter(airlines, name == "American Airlines Inc.") %>%
  dm_filter(airports, name != "John F Kennedy Intl") %>%
  dm_filter(flights, month == 1) %>%
  dm_apply_filters()
aa_non_jfk_january

# ... and processed further
aa_non_jfk_january %>%
  tbl("planes")

##
##
##
## Copy to database
## --------------------------------------------------------------------
##
##
##

# FIXME: SQLite with many rows

# FIXME: Make work with planes table

# FIXME: remove all_connected = TRUE for now, redo example above

# All operations are designed to work locally and on the database
nycflights13_sqlite <-
  dm_nycflights13() %>%
  dm_select_tbl(-planes) %>%
  dm_filter(flights, month == 1) %>%
  dm_apply_filters() %>%
  copy_dm_to(dbplyr::src_memdb(), ., unique_table_names = TRUE)

nycflights13_sqlite

nycflights13_sqlite %>%
  dm_draw()

nycflights13_sqlite %>%
  dm_get_tables() %>%
  map(dbplyr::sql_render)

# Filtering on the database
nycflights13_sqlite %>%
  dm_filter(airlines, name == "American Airlines Inc.") %>%
  dm_filter(airports, name != "John F Kennedy Intl") %>%
  dm_filter(flights, day == 1) %>%
  tbl("flights")

# ... and the corresponding SQL statement
nycflights13_sqlite %>%
  dm_filter(airlines, name == "American Airlines Inc.") %>%
  dm_filter(airports, name != "John F Kennedy Intl") %>%
  dm_filter(flights, day == 1) %>%
  tbl("flights") %>%
  dbplyr::sql_render()

##
##
##
## Joining tables
## --------------------------------------------------------------------
##
##
##

dm_nycflights13() %>%
  dm_join_to_tbl(airlines, flights, join = left_join)

dm_nycflights13() %>%
  dm_join_to_tbl(flights, airlines, join = left_join)

nycflights13_sqlite %>%
  dm_join_to_tbl(airlines, flights, join = left_join)

aa_non_jfk_january %>%
  dm_join_to_tbl(flights, airlines, join = left_join)

# FIXME: Multi-joins

try(
  dm_nycflights13() %>%
    dm_join_to_tbl(airports, airlines, join = left_join)
)

try(
  dm_nycflights13() %>%
    dm_join_to_tbl(flights, airports, airlines, join = left_join)
)

##
##
##
## Build up data model from scratch
## --------------------------------------------------------------------
##
##
##

# Linking the weather table

# Determine key candidates
weather %>%
  enum_pk_candidates()

weather %>%
  enum_pk_candidates() %>%
  count(candidate)

# It's tricky:
weather %>%
  unite("slot_id", origin, year, month, day, hour, remove = FALSE) %>%
  count(slot_id) %>%
  filter(n > 1)

weather %>%
  count(origin, time_hour) %>%
  filter(n > 1)

weather %>%
  count(origin, format(time_hour)) %>%
  filter(n > 1)

# This looks like a good candidate:
weather %>%
  count(origin, format(time_hour, tz = "UTC")) %>%
  filter(n > 1)

# FIXME: Support compound keys (#3)

# Currently, we need to create surrogate keys:
weather_link <-
  weather %>%
  mutate(time_hour_fmt = format(time_hour, tz = "UTC")) %>%
  unite("origin_slot_id", origin, time_hour_fmt, remove = FALSE)

flights_link <-
  flights %>%
  mutate(time_hour_fmt = format(time_hour, tz = "UTC")) %>%
  unite("origin_slot_id", origin, time_hour_fmt, remove = FALSE)

# one option to create a `dm` is to use `as_dm()`:
nycflights13_dm <- as_dm(list(airlines = airlines, airports = airports, flights = flights_link, planes = planes, weather = weather_link))

# Copy to this environment
airlines_global <- airlines
airports_global <- airports
planes_global <- planes

global <-
  dm_from_src(src_df(env = .GlobalEnv))
global

global %>%
  dm_rename_tbl(
    airlines = airlines_global,
    airports = airports_global,
    planes = planes_global,
    flights = flights_link,
    weather = weather_link
  ) %>%
  dm_select_tbl(airlines, airports, planes, flights, weather)

# or better:
nycflights13_tbl <-
  global %>%
  dm_select_tbl(
    airlines = airlines_global,
    airports = airports_global,
    planes = planes_global,
    flights = flights_link,
    weather = weather_link
  )


nycflights13_tbl

nycflights13_tbl %>%
  dm_draw()

# Adding primary keys
nycflights13_pk <-
  nycflights13_tbl %>%
  dm_add_pk(weather, origin_slot_id) %>%
  dm_add_pk(planes, tailnum) %>%
  dm_add_pk(airports, faa) %>%
  dm_add_pk(airlines, carrier)

nycflights13_pk %>%
  dm_draw()

# FIXME: Model weak constraints, show differently in diagram (#4)

# Adding foreign keys
nycflights13_fk <-
  nycflights13_pk %>%
  dm_add_fk(flights, origin_slot_id, weather, check = FALSE) %>%
  dm_add_fk(flights, tailnum, planes, check = FALSE) %>%
  dm_add_fk(flights, origin, airports) %>%
  dm_add_fk(flights, dest, airports, check = FALSE) %>%
  dm_add_fk(flights, carrier, airlines)

nycflights13_fk %>%
  dm_draw()

# Color it!
dm_get_available_colors()

nycflights13_fk %>%
  dm_set_colors(airlines = , planes = , weather = , airports = "blue") %>%
  dm_draw()

##
##
##
## Import a dm from a database, including key constraints
## --------------------------------------------------------------------
##
##
##

try({
  # Import
  dm_pq <-
    dm_nycflights13() %>%
    dm_select_tbl(-planes) %>%
    dm_filter(flights, month == 1) %>%
    copy_dm_to(src_postgres(), ., temporary = FALSE)

  dm_from_pq <-
    dm_learn_from_db(src_postgres())
})