Chapter 9 Interfacing with databases in R

9.1 Introduction

9.2 Extract, Transform & Load (ETL)

Ben Baumer (2019) “A Grammar for Reproducible and Painless Extract-Transform-Load Operations on Medium Data”, Journal of Computational and Graphical Statistics (Baumer 2019)

9.3 SQL

SQL learning resources – twitter thread

Irene Steves, Using SQL in RStudio

Select Star SQL–“This is an interactive book which aims to be the best place on the internet for learning SQL.”

SQL Tutorial–from w3schools.com

Learn SQL–codeacademy.com course

9.4 APIs

Shana Pearlman (2016-09-07), What are APIs and how do APIs work?

9.4.1 General resources

Michael Clark, 2018-08-12, Engaging the Web with R

Brian Cooksey, Zapier course: An Introduction to APIs

Matthew Guay (2018-03-29), What Are Webhooks? A Simple Guide to Connecting Web Apps with Webhooks

9.4.2 APIs in R

Lucy D’Agostino McGowan, 2018-07-31, Harnessing the Power of the Web via R Clients for Web APIs – presentation at the 2018 Joint Statistical Meeting

Amanda Gadrow, Using Web APIs from R {RStudio conference 2017 presentation}

Christoph Waldhauser (2015-11-22), Accessing APIs from R (and a little R programming)

Jose Gonzalez, Using Google Maps API and R

9.4.3 Packages

9.4.3.1 {etl}

CRAN: etl: Extract-Transform-Load Framework for Medium Data – “A predictable and pipeable framework for performing ETL (extract-transform-load) operations on publicly-accessible medium-sized data set. This package sets up the method structure and implements generic functions. Packages that depend on this package download specific data sets from the Internet, clean them up, and import them into a local or remote relational database management system.”

Vignette: Using etl

Vignette: Extending etl

9.5 Web scraping

Keith McNulty, 2019-08-16, Tidy web scraping in R — Tutorial and resources

References

Baumer, Benjamin S. 2019. “A Grammar for Reproducible and Painless Extract-Transform-Load Operations on Medium Data.” Journal of Computational and Graphical Statistics 28 (2): 256–64. https://doi.org/10.1080/10618600.2018.1512867.