Public datasets and BigQuery w/ dplyr - Use R/Tidyverse to query remote relational databases via SQL
Channel: John Little
Date: 2021-03-01

If you know dplyr, you can query Google BigQuery and other relational databases.  The dplyr package will translate your dplyr verbs and generate SQL queries.  The DBI package will broker database connection connections.  the bigrquery package is specific to Google BigQuery and simplifies access to the BigQuery public datasets as well as other datasets.

0:00 Start
15:57 - Google BigQuery Platform & Console
24:36 - Accessing BigQuery via dplyr
27:06 - Make a DB connection -- DBI::dbConnect
29:03 - Point to relational table 
30:38 - view table schema -- glimpse()
32:38 - [traditional] SQL query
34:50 - [Transform remotely] Use dplyr to query remote relational database (e.g. BigQuery)
38:12 - show_query() 
43:33 - Visualize [locally] with ggplot2
51:47 - left_join() - invoke remote data join
53:47 - library(dbplot) - Visualize by leveraging remote compute power for minimal remote CPU processing

This Rfun Case Study demonstrate the utility R / Tidyverse workflows.  You can use the Tidyverse as a universal reproducible interface for your analysis projects.
More Rfun at https://Rfun.library.duke.edu/
Part of the DVS Workshop Series:  http://library.duke.edu/data/

LINKS
https://dplyr.tidyverse.org
https://db.rstudio.com/
https://bigrquery.r-dbi.org/

CODE:
https://github.com/libjohn/casestudy_bigquery_dbplyr
First Release: https://github.com/libjohn/casestudy_bigquery_dbplyr/releases/tag/v1.0