Untitled

Accessing the text’s sample flights DB.

library(tidyverse)
library(DBI)

library(mdsr)
db <- dbConnect_scidb("airlines")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
airports <- tbl(db, "airports")

## Warning in .local(conn, statement, ...): Decimal MySQL column 2 imported as
## numeric

## Warning in .local(conn, statement, ...): Decimal MySQL column 3 imported as
## numeric

planes <- tbl(db, "planes")

Accessing Ken’s MariaDB version of the flights data

db <- DBI::dbConnect(RMariaDB::MariaDB(), 
                     dbname = "airlines", 
                     host = "mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com", 
                     user = "mdsr_public", 
                     password = "ImhsmflMDSwR")
flights <- tbl(db, "flights")
carriers <- tbl(db, "carriers")
flights

## # Source:   table<flights> [?? x 21]
## # Database: mysql
## #   [mdsr_public@mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com:NA/airlines]
##     year month   day dep_time sched_dep_time dep_delay arr_time sched_arr_time
##    <int> <int> <int>    <int>          <int>     <int>    <int>          <int>
##  1  2010    10     1        1           2100       181      159           2320
##  2  2010    10     1        1           1920       281      230           2214
##  3  2010    10     1        3           2355         8      339            334
##  4  2010    10     1        5           2200       125       41           2249
##  5  2010    10     1        7           2245        82      104           2347
##  6  2010    10     1        7             10        -3      451            500
##  7  2010    10     1        7           2150       137      139           2337
##  8  2010    10     1        8             15        -7      538            537
##  9  2010    10     1        8             10        -2      643            645
## 10  2010    10     1       10           2225       105      831            642
## # ... with more rows, and 13 more variables: arr_delay <int>, carrier <chr>,
## #   tailnum <chr>, flight <int>, origin <chr>, dest <chr>, air_time <int>,
## #   distance <int>, cancelled <int>, diverted <int>, hour <int>, minute <int>,
## #   time_hour <dttm>

carriers

## # Source:   table<carriers> [?? x 2]
## # Database: mysql
## #   [mdsr_public@mdsr.cdc7tgkkqd0n.us-east-1.rds.amazonaws.com:NA/airlines]
##    carrier name                                        
##    <chr>   <chr>                                       
##  1 02Q     Titan Airways                               
##  2 04Q     Tradewind Aviation                          
##  3 05Q     Comlux Aviation, AG                         
##  4 06Q     Master Top Linhas Aereas Ltd.               
##  5 07Q     Flair Airlines Ltd.                         
##  6 09Q     Swift Air, LLC                              
##  7 0BQ     DCA                                         
##  8 0CQ     ACM AIR CHARTER GmbH                        
##  9 0GQ     Inter Island Airways, d/b/a Inter Island Air
## 10 0HQ     Polar Airlines de Mexico d/b/a Nova Air     
## # ... with more rows

Loading my CS 262 PostgreSQL databases

Use one of these two configurations.

Load the data.

db <- DBI::dbConnect(RPostgres::Postgres(), 
                     host = DB_HOST, 
                     port = 5432,
                     user = DB_USER,
                     password = DB_PASSWORD)

dbListTables(db)

## [1] "pg_stat_statements" "partjob"            "job"               
## [4] "supplier"           "part"

supplier <- tbl(db, "supplier")
part <- tbl(db, "part")
job <- tbl(db, "job")
partjob <- tbl(db, "partjob")

Query the data.

part %>% 
  show_query()

## <SQL>
## SELECT *
## FROM "part"

part %>%
  left_join(supplier, by = c("supplierid" = "id")) %>%
  show_query()

## <SQL>
## SELECT "LHS"."id" AS "id", "LHS"."name" AS "name.x", "price", "supplierid", "RHS"."name" AS "name.y", "loginid", "password"
## FROM "part" AS "LHS"
## LEFT JOIN "supplier" AS "RHS"
## ON ("LHS"."supplierid" = "RHS"."id")

partjob %>%
  inner_join(part, by = c("partid" = "id")) %>%
  inner_join(job, by = c("jobid" = "id")) %>%
  select(name.x, name.y, quantity, date, address) %>%
  show_query()

## <SQL>
## SELECT "name.x", "name.y", "quantity", "date", "address"
## FROM (SELECT "partid", "jobid", "quantity", "date", "LHS"."name" AS "name.x", "price", "supplierid", "RHS"."name" AS "name.y", "address"
## FROM (SELECT "partid", "jobid", "quantity", "date", "name", "price", "supplierid"
## FROM "partjob" AS "LHS"
## INNER JOIN "part" AS "RHS"
## ON ("LHS"."partid" = "RHS"."id")
## ) "LHS"
## INNER JOIN "job" AS "RHS"
## ON ("LHS"."jobid" = "RHS"."id")
## ) "q01"