This is common case when working with data that your source is a remote database. Usual ways to cope this when using R is either to load all the data into R or to perform the heaviest joins and aggregations with SQL before loading the data. Both of them have cons: the former one is limited by the memory capacity and may be very slow and the later forces you to use two technologies thus is more complicated and prone to errors. Solution to these problems is to use
dbplyr to communicate with database backend. This allows user to write
dplyr code that is translated to SQL and executed at database server. One can say that this combines advantages of the two standard solutions and gets rid of their disadvantages.
This is the first part of R with remote databases series. For other parts follow the tag databases.
The reader is assumed to know basics of
dplyr and SQL. If you want to practice
dplyr first there is great series of exercises Data wrangling: Transforming available. For quick introduction to
dplyr with database backend I recommend this vignette.
Answers to the exercises are available here.
If you obtained a different (correct) answer than those listed on the solutions page, please feel free to post your answer as a comment on that page.
nycflights13. Create a connection to temporal in-memory SQLite database (the database will be created on-the-fly so you do not have to take care of this).
Upload data sets
nycflights13::planes to the database as non temporal tables.
List names of columns in
flights table (hint: There is a function in
DBI package for this).
Use SQL query to count number of flights per carrier and pull it to a local tibble.
Do the same thing using
dplyr verbs instead of SQL.
Calculate number of flights, mean and total distance per plane, discard records with
tailnum column and save it to temporal table.
List all the tables in the database (hint: There is a function in
DBI package for this).
nrow() to investigate table
planes. Do you understand why the latter two do not work?
Join the table from exercise 6 with
planes table and without pulling the data to local frame find manufacturers and models of 10 planes with the highest total flown distance.
Check what is the actual SQL query generated by the code you have created in exercise 9.