16  Introduction to SQL with dbplyr

Goals:

• explain what a database is, how it is different from a data set, and why you might use a database.
• use the dbplyr to translate R code with dplyr to SQL queries on database tables.
• draw parallels between dplyr functions and the syntax used in SQL.

All of the dplyr functions we’ve used (both the ones from early in the semester and from the xxxx_join() family more recently) have corresponding components in SQL. SQL stands for Structured Query Language and is a very common language used with databases.

Note

Compared to dplyr, in general, SQL code is much harder to read, as SQL isn’t designed specifically for data analysis like dplyr is.

In this section, we will introduce databases and give a brief introduction to SQL for analyzing data from a database.

16.1 What is a Database

The R for Data Science textbook defines a database as “a collection of data frames,” each called a database table. There a few key differences between a data frame (what we’ve been using the entire semester) and a database table. They are summarised from R for Data Science here as:

• a database table can be larger and is stored on disk while a data frame is stored in memory so their size is more limited.
• many, but not all, data base tables are “row-oriented” while tidy data frames are “column-oriented.” However, more often, databases are column-oriented (similar to all of the data frames we have worked with in this class).

Databases are run through Database Management Systems. The R for Data Science textbook divides Database Management Systems into 3 types:

• client-server like PostgreSQL and SQL Server
• Cloud-based like Amazon’s Redshift
• In-process like SQLite

We won’t really discuss these any further, but an advanced course in database systems through the CS department would give more information about Database Management Systems (and databases in general).

How to connect to a database from R depends on the type of database management system. For our purposes, because how to connect to a Database management system depends so heavily on the type, we will focus on a database management system that is contained in the R package duckdb.

We also need a database interface to connect to the database tables in duckdb in the DBI package.

Note

This section on connecting to a database management systems may be confusing, particularly if you do not have a computer science background. But don’t let that derail your learning for this rest of this chapter, which will consist of primarily of R code from here on! The take-home message is that we need a way to connect to the system within R. It’s challenging to give specific directions because the connection depends on the type of system, so we are avoiding most of that by connecting to a database management system in the duckdb R package using functions from the DBI package.

SQL is short from Structured Query Language. We first load in the duckdb and DBI libraries and make a connection to the database management system, which we will name con:

library(DBI)
library(duckdb)
con <- DBI::dbConnect(duckdb::duckdb())

We can type in con to see what it stores:

con

We’ve created a brand-new database, so we can next add some data tables with the duckdb_read_csv() function. Compared to read_csv() from the readr package, duckdb_read_csv() has a couple of extra arguments: a conn argument giving the database management connection and a name argument giving the name that we want to give to the data table:

library(here)
duckdb_read_csv(conn = con, name = "tennis2018", 
                files = here("data/atp_matches_2018.csv"))
duckdb_read_csv(conn = con, name = "tennis2019", 
                files = here("data/atp_matches_2019.csv"))

The doListTables() function lists the names of the data tables in the database we just created:

dbListTables(con)
#> [1] "tennis2018" "tennis2019"

And, dbExistsTable() can be used to examine whether or not a data table exists in the current database:

dbExistsTable(con, "tennis2019")
#> [1] TRUE
dbExistsTable(con, "tennis2020")
#> [1] FALSE

Note that, in many practical situations, the data tables will already exist in the database you are working with, so the step of duckdb_read_csv() would not be necessary.

To use raw SQL code and query the database that we just created, we can create a string of SQL code, name it sql, and pass it to the dbGetQuery() function. We also load in the tidyverse package here to use the as_tibble() function to convert the data.frame to a tibble.

library(tidyverse)

sql <- "
  SELECT surface, winner_name, loser_name, w_ace, l_ace, minutes
  FROM tennis2019 
  WHERE minutes > 240
"
dbGetQuery(con, sql) |>
  as_tibble()
#> # A tibble: 30 × 6
#>   surface winner_name           loser_name            w_ace l_ace minutes
#>   <chr>   <chr>                 <chr>                 <int> <int>   <int>
#> 1 Hard    Joao Sousa            Guido Pella              19    18     241
#> 2 Hard    Jeremy Chardy         Ugo Humbert              29    20     244
#> 3 Hard    Roberto Bautista Agut Andy Murray               7    19     249
#> 4 Hard    Joao Sousa            Philipp Kohlschreiber    28    20     258
#> 5 Hard    Alex Bolt             Gilles Simon             11    14     244
#> 6 Hard    Milos Raonic          Stan Wawrinka            39    28     241
#> # ℹ 24 more rows

Exercise 1. Though we do not know SQL code, we can probably figure out what the code (the string that is being assigned to sql) above is doing. Which matches are being returned from our query?

Exercise 2. What is the dplyr equivalent function to WHERE in the SQL code above? What is the dplyr equivalent function to SELECT in the SQL code above?

16.2 dbplyr: A Database Version of dplyr

dbplyr is a package that will allow us to continue to write dplyr-style code to query databases instead of writing native SQL, as in the code-chunk above.

We begin by loading in the package and creating a database table object with the tbl() function. In this case, we create a database table with the tennis2019 data and name it tennis_db:

library(dbplyr)
tennis_db <- tbl(con, "tennis2019")
tennis_db
#> # Source:   table<tennis2019> [?? x 49]
#> # Database: DuckDB v1.0.0 [root@Darwin 23.5.0:R 4.4.1/:memory:]
#>   tourney_id tourney_name surface draw_size tourney_level tourney_date
#>   <chr>      <chr>        <chr>       <int> <chr>                <int>
#> 1 2019-M020  Brisbane     Hard           32 A                 20181231
#> 2 2019-M020  Brisbane     Hard           32 A                 20181231
#> 3 2019-M020  Brisbane     Hard           32 A                 20181231
#> 4 2019-M020  Brisbane     Hard           32 A                 20181231
#> 5 2019-M020  Brisbane     Hard           32 A                 20181231
#> 6 2019-M020  Brisbane     Hard           32 A                 20181231
#> # ℹ more rows
#> # ℹ 43 more variables: match_num <int>, winner_id <int>, winner_seed <chr>,
#> #   winner_entry <chr>, winner_name <chr>, winner_hand <chr>,
#> #   winner_ht <int>, winner_ioc <chr>, winner_age <dbl>, loser_id <int>,
#> #   loser_seed <chr>, loser_entry <chr>, loser_name <chr>, loser_hand <chr>,
#> #   loser_ht <int>, loser_ioc <chr>, loser_age <dbl>, score <chr>,
#> #   best_of <int>, round <chr>, minutes <int>, w_ace <int>, w_df <int>, …

Examine the print for tennis_db, which should look similar to the print for a tibble or data.frame. Let’s use some dplyr code to obtain only the matches that lasted longer than 240 minutes and keep only a few of the columns. We will name the result tennis_query1:

tennis_query1 <- tennis_db |> 
  filter(minutes > 240) |> 
  select(minutes, winner_name, loser_name, minutes, tourney_name)
tennis_query1
#> # Source:   SQL [?? x 4]
#> # Database: DuckDB v1.0.0 [root@Darwin 23.5.0:R 4.4.1/:memory:]
#>   minutes winner_name           loser_name            tourney_name   
#>     <int> <chr>                 <chr>                 <chr>          
#> 1     241 Joao Sousa            Guido Pella           Australian Open
#> 2     244 Jeremy Chardy         Ugo Humbert           Australian Open
#> 3     249 Roberto Bautista Agut Andy Murray           Australian Open
#> 4     258 Joao Sousa            Philipp Kohlschreiber Australian Open
#> 5     244 Alex Bolt             Gilles Simon          Australian Open
#> 6     241 Milos Raonic          Stan Wawrinka         Australian Open
#> # ℹ more rows

We should note that the result is still a database object: it’s not our “usual” tibble. One major difference between the database object and the usual tibble is that our tennis_query1 does not tell us how many rows are in the data (see the ?? and the specification with more rows). The code that we wrote is not actually looking in the entire data set for matches that are longer than 240 minutes: it is saving time by only performing our query on part of the database table. This is very useful behaviour for database tables that are very, very large, where code might take a long time to run.

If we want to obtain the result of our query as a tibble, we can use the collect() function:

tennis_query1 |>
  collect()
#> # A tibble: 30 × 4
#>   minutes winner_name           loser_name            tourney_name   
#>     <int> <chr>                 <chr>                 <chr>          
#> 1     241 Joao Sousa            Guido Pella           Australian Open
#> 2     244 Jeremy Chardy         Ugo Humbert           Australian Open
#> 3     249 Roberto Bautista Agut Andy Murray           Australian Open
#> 4     258 Joao Sousa            Philipp Kohlschreiber Australian Open
#> 5     244 Alex Bolt             Gilles Simon          Australian Open
#> 6     241 Milos Raonic          Stan Wawrinka         Australian Open
#> # ℹ 24 more rows

The result is a tibble that we can now use any R functions on (not just functions from dplyr and a few other packages).

The show_query() function can be used on our tennis_query1 to give the SQL code that was executed:

tennis_query1 |>
  show_query()
#> <SQL>
#> SELECT "minutes", winner_name, loser_name, tourney_name
#> FROM tennis2019
#> WHERE ("minutes" > 240.0)

We’ll do one more query:

totalaces <- tennis_db |>
  group_by(winner_name) |>
  summarise(total_aces = sum(w_ace)) |>
  arrange(desc(total_aces))
totalaces
#> # Source:     SQL [?? x 2]
#> # Database:   DuckDB v1.0.0 [root@Darwin 23.5.0:R 4.4.1/:memory:]
#> # Ordered by: desc(total_aces)
#>   winner_name        total_aces
#>   <chr>                   <dbl>
#> 1 Reilly Opelka             604
#> 2 John Isner                590
#> 3 Daniil Medvedev           511
#> 4 Alexander Zverev          496
#> 5 Milos Raonic              444
#> 6 Jan Lennard Struff        428
#> # ℹ more rows

totalaces |> show_query()
#> <SQL>
#> SELECT winner_name, SUM(w_ace) AS total_aces
#> FROM tennis2019
#> GROUP BY winner_name
#> ORDER BY total_aces DESC

Can you match some of the SQL code with the corresponding dbplyr functions used?

Exercise 3. Obtain the distribution of the surface variable by making a table of the total number of matches played on each surface in the 2019 season using dbplyr functions on tennis_db. Then, use show_query() to show the corresponding SQL code.

16.3 SQL

The purpose of this section is to explore SQL syntax a little more, focusing on its connections to dplyr.

Note

Knowing dplyr is quite helpful in learning this SQL syntax because, while the syntax differs, the concepts are quite similar.

Much of the text in this section is paraphrased from the R for Data Science textbook. There are five core components of an SQL query. The two most basic are a SELECT statement (similar to select(), and, as discussed below, mutate() and summarise()) and a FROM statement (similar to the data argument). Using the show_query() function directly on tennis_db shows an SQL query that SELECTs all columns (denoted by the *), FROM the tennis2019 database.

tennis_db |> show_query()
#> <SQL>
#> SELECT *
#> FROM tennis2019

The WHERE and ORDER BY statements control which rows are returned (similar to filter()) and in what order those rows get returned (similar to arrange()):

tennis_db |> filter(winner_hand == "L") |>
  arrange(desc(tourney_date)) |>
  show_query()
#> <SQL>
#> SELECT tennis2019.*
#> FROM tennis2019
#> WHERE (winner_hand = 'L')
#> ORDER BY tourney_date DESC

Finally, GROUP BY is used for aggregation (similar to the dplyr group_by() and summarise() combination).

tennis_db |>
  group_by(winner_name) |>
  summarise(meanace = mean(w_ace, na.rm = TRUE)) |>
  show_query()
#> <SQL>
#> SELECT winner_name, AVG(w_ace) AS meanace
#> FROM tennis2019
#> GROUP BY winner_name

In the above code chunk, remove the na.rm = TRUE argument and run the query. What do you learn?

The SQL syntax must always follow the order SELECT, FROM, WHERE, GROUP BY, ORDER BY, even though the operations can be performed in a different order than what is specified. This is one aspect that makes SQL harder to pick up than something like dplyr, where we specify what we want done in the order that we want.

Below we give a little more detail about the 5 operations.

SELECT: SELECT covers a lot of dplyr functions. In the code below, we explore how it is used in SQL to choose which columns get returned, rename columns, and create new variables:

• SELECT to choose which columns to return:

tennis_db |> select(1:4) |> show_query()
#> <SQL>
#> SELECT tourney_id, tourney_name, surface, draw_size
#> FROM tennis2019

• SELECT to rename columns:

tennis_db |> rename(tournament = tourney_name) |>
  show_query()
#> <SQL>
#> SELECT
#>   tourney_id,
#>   tourney_name AS tournament,
#>   surface,
#>   draw_size,
#>   tourney_level,
#>   tourney_date,
#>   match_num,
#>   winner_id,
#>   winner_seed,
#>   winner_entry,
#>   winner_name,
#>   winner_hand,
#>   winner_ht,
#>   winner_ioc,
#>   winner_age,
#>   loser_id,
#>   loser_seed,
#>   loser_entry,
#>   loser_name,
#>   loser_hand,
#>   loser_ht,
#>   loser_ioc,
#>   loser_age,
#>   score,
#>   best_of,
#>   round,
#>   "minutes",
#>   w_ace,
#>   w_df,
#>   w_svpt,
#>   w_1stIn,
#>   w_1stWon,
#>   w_2ndWon,
#>   w_SvGms,
#>   w_bpSaved,
#>   w_bpFaced,
#>   l_ace,
#>   l_df,
#>   l_svpt,
#>   l_1stIn,
#>   l_1stWon,
#>   l_2ndWon,
#>   l_SvGms,
#>   l_bpSaved,
#>   l_bpFaced,
#>   winner_rank,
#>   winner_rank_points,
#>   loser_rank,
#>   loser_rank_points
#> FROM tennis2019

• SELECT to create a new variable

tennis_db |> mutate(prop_first_won = w_1stIn / w_1stWon) |>
  select(prop_first_won, winner_name) |>
  show_query()
#> <SQL>
#> SELECT w_1stIn / w_1stWon AS prop_first_won, winner_name
#> FROM tennis2019

• SELECT to create a new variable that is a summary:

tennis_db |> summarise(mean_length = mean(minutes)) |>
  show_query()
#> <SQL>
#> SELECT AVG("minutes") AS mean_length
#> FROM tennis2019

GROUP BY: GROUP BY covers aggregation in a similar way as dplyr’s group_by() function:

tennis_db |> group_by(winner_name) |>
  summarise(meanlength = mean(minutes)) |>
  show_query()
#> <SQL>
#> SELECT winner_name, AVG("minutes") AS meanlength
#> FROM tennis2019
#> GROUP BY winner_name

WHERE: WHERE is used for filter(), though SQL uses different Boolean operators than R (for example, & becomes AND, | becomes or).

tennis_db |> filter(winner_age > 35 | loser_age > 35) |>
  show_query()
#> <SQL>
#> SELECT tennis2019.*
#> FROM tennis2019
#> WHERE (winner_age > 35.0 OR loser_age > 35.0)

ORDER BY: ORDER BY is used for arrange(). This one is quite straightforward:

tennis_db |> arrange(desc(winner_rank_points)) |>
  show_query()
#> <SQL>
#> SELECT tennis2019.*
#> FROM tennis2019
#> ORDER BY winner_rank_points DESC

SQL also has corresponding syntax for the xxxx_join() family of functions, but we do not have time to discuss this in detail. Note that we have really just scratched the surface of SQL. There are entire courses devoted to learning SQL syntax and more about databases in general. If you ever do find yourself in a situation where you need to learn SQL, either for a course or for a job, you should have a major head-start with your dplyr knowledge!

In much of this section, we have created code with dbplyr and seen how that code translates to SQL. In this exercise, you will instead be given SQL code and asked to write dbplyr code that achieves the same thing.

Exercise 4. Examine the SQL code below and write equivalent dbplyr code using tennis_db.

SELECT * 
FROM "tennis2019"
WHERE ("tourney_name" = 'Wimbledon')

16.4 Practice

16.4.1 Class Exercises

Class Exercise 1. Examine the SQL code below and write equivalent dbplyr code using tennis_db.

SELECT "winner_name", "loser_name", "w_ace", "l_ace", "w_ace" - "l_ace" AS "ace_diff"
FROM "tennis2019"
ORDER BY "ace_diff" DESC

Class Exercise 2. With tennis_db, create a new variable that is the difference in the winner_rank_points and loser_rank_points using a dbplyr function. Then, have your query return only the column you just created, the winner_name column, and the loser_name column. Use the show_query() function to show the corresponding SQL code.

16.4.2 Your Turn

Your Turn 1. Examine the SQL code below and write equivalent dbplyr code on tennis_db.

<SQL>
SELECT "tourney_name", MAX("minutes") AS "longest_match"
FROM "tennis2019"
GROUP BY "tourney_name"

Your Turn 2. Try to run a function from lubridate or forcats on tennis_db with mutate(). Does the function work?

Your Turn 3. Run the following code and write how the ! is translated to SQL, how the %in% symbol is translated to SQL, and how distinct() is translated to SQL.

tennis_db |> filter(winner_name != "Daniil Medvedev") |>
  show_query()

tennis_db |>
  filter(winner_name %in% c("Daniil Medvedev", "Dominic Thiem")) |>
  show_query()

tennis_db |> distinct(tourney_name) |>
  show_query()

Your Turn 4. Examine the following SQL code and write equivalent dbplyr code on tennis_db.

<SQL>
SELECT "winner_name", COUNT(*) AS "n_win"
FROM "tennis2019"
WHERE ("surface" = 'Hard')
GROUP BY "winner_name"
ORDER BY "n_win" DESC

Your Turn 5. Perform a query of your choosing on tennis_db and use the show_query() function to show the corresponding SQL code.