Converting R expressions to SQL

While SQL is a powerful and flexible language used to manipulate data in a database, not everyone is proficient in it. Fortunately, the R community has developed a few packages that translate familiar R syntax into SQL statements that are then executed on the database. We will look at two of them—dplyr and PivotalR.

Using dplyr

The dplyr package is a handy package designed to allow the manipulation of table-like data with a standard set of operations and transformations, no matter where the data is stored—in a data frame, data table, or database. It supports SQLite, PostgreSQL, MySQL, Amazon RedShift, Google BigQuery, and MonetDB databases.

The dplyr package provides a way to specify a set of operations to be performed on the data without actually performing the computations on the database server until we instruct R to do so, by calling the collect()function. By pooling a few operations together (as opposed to executing them one by one), the database server...