SQL commands vs dplyr verbs

One of the advantages of learning about tidy data and dplyr is that with dplyr verbs you can replicate a lot of what SQL does.

Establish a connection with the SQLite database

We will use the European Soccer Database that has more than 25,000 matches and more than 11,000 players. We first need to establish a connection with the SQL database. Unlike dataframes that we just load into memory, the size of some SQL databases prohibits loading the entire database into memory. Although this soccer database is a locally saved file, we would use a similar connection into any SQL database over the internet

# set up a connection to sqlite database
football_db <- DBI::dbConnect(
  drv = RSQLite::SQLite(),
  dbname = "database.sqlite"
)

The general code for connecting to a remote database is:

connection_to_db <- DBI::dbConnect(
  drv = [database driver, eg odbc::odbc()],
  dbname = "database_name",
  user = "User_ID",
  password = "Password",
  host = "host name", (default=local connection)
  port = "port number" (default=local connection)
)

That’s pretty much it - R now has a direct connection to the database and you can start making queries.

Database objects or tibbles?

Now, an SQL database will typically contain multiple tables. You can think of these tables as R data frames (or tibbles). What are the tables in the soccer database? We can browse the tables in the database using DBI::dbListTables()

DBI::dbListTables(football_db)

## [1] "Country"           "League"            "Match"            
## [4] "Player"            "Player_Attributes" "Team"             
## [7] "Team_Attributes"   "sqlite_sequence"

We can easily set these tables up as database objects using dplyr

countries <- dplyr::tbl(football_db, "Country")
leagues <- dplyr::tbl(football_db, "League")
matches <- dplyr::tbl(football_db, "Match")
teams <- dplyr::tbl(football_db, "Team")
team_attributes <- dplyr::tbl(football_db, "Team_Attributes")
players <- dplyr::tbl(football_db, "Player")
player_attributes <- dplyr::tbl(football_db, "Player_Attributes")

Each of these tables are SQL database objects in your R session which you can manipulate in the same way as a dataframe.

class(countries)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

When you define these tables, you are not physically downloading them, just creating a bare minimum extract to work with.

IF you wanted to handle these as normal dataframes or tibbles, you can simply pipe the database objects to as_tibble()

player_attributes_df <- player_attributes %>% as_tibble()
class(player_attributes)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

class(player_attributes_df)

## [1] "tbl_df"     "tbl"        "data.frame"

Notice the difference between player_atributes, a database object, and player_atributes_df, a ‘regular’ dataframe/tibble.

Now that we have player attributes as a dataframe, we can handle it the usual way and, e.g., build a scatterplot/correlation matrix with ggpairs()

player_attributes_df %>% 
  filter(!is.na(preferred_foot)) %>% 
  select(preferred_foot, ball_control, overall_rating) %>% 
  ggpairs(aes(colour=preferred_foot, alpha = 0.3))+
  scale_colour_manual(values = c("#67a9cf","#ef8a62"))+
  scale_fill_manual(values = c("#67a9cf","#ef8a62"))+
  theme_bw()

Querying the database with dbplyr

To create the ggpairs() plot we had to convert a database table to a dataframe, load it all in the computer’s memory, and then use ggplot. The beauty of working with databases is that we do NOT have to load everything into memory. Instead, all dplyr calls are evaluated lazily, generating SQL code that is only sent to the database when you request the data.

Let us look at an example. What if we wanted to calculate the average number of goals per league (country) per season and then plot those averages. This seems like a data wrangling exercise we would do with dplyr

# write dplyr code that will calculate average number of goals per country per season
goals_per_match <-  matches %>%
  group_by(country_id, season) %>%
  summarise(avg_goals = mean(home_team_goal + away_team_goal)) %>%
 
  #do a left_join, so we know the country's name rather than the country's ID
  left_join(countries, by = c("country_id"="id")) %>%
  arrange(desc(avg_goals)) %>% 
  ungroup()

What kind of an object is goals_per_match?

#what kind of an object is goals_per_match?
class(goals_per_match)

## [1] "tbl_SQLiteConnection" "tbl_dbi"              "tbl_sql"             
## [4] "tbl_lazy"             "tbl"

goals_per_match is not a dataframe (tibble), but rather a query to an SQLite database table.

Generate the actual SQL commands

We are familiar with all the dplyr verbs (filter, select, group_by, summarise, arrange, etc.), but SQL has its own commands, all of which are written in capital letters (is SQL constantly angry and shouting? Who knew…). We can generate the actual SQL commands using dbplyr::sql_render() or dplyr::show_query()

# Generate actual SQL commands: We can either use dbplyr::sql_render() or dplyr::show_query()
dbplyr::sql_render(goals_per_match)

## <SQL> SELECT *
## FROM (SELECT `LHS`.`country_id` AS `country_id`, `LHS`.`season` AS `season`, `LHS`.`avg_goals` AS `avg_goals`, `RHS`.`name` AS `name`
## FROM (SELECT `country_id`, `season`, AVG(`home_team_goal` + `away_team_goal`) AS `avg_goals`
## FROM `Match`
## GROUP BY `country_id`, `season`) AS `LHS`
## LEFT JOIN `Country` AS `RHS`
## ON (`LHS`.`country_id` = `RHS`.`id`)
## )
## ORDER BY `avg_goals` DESC

goals_per_match %>% show_query()

## <SQL>
## SELECT *
## FROM (SELECT `LHS`.`country_id` AS `country_id`, `LHS`.`season` AS `season`, `LHS`.`avg_goals` AS `avg_goals`, `RHS`.`name` AS `name`
## FROM (SELECT `country_id`, `season`, AVG(`home_team_goal` + `away_team_goal`) AS `avg_goals`
## FROM `Match`
## GROUP BY `country_id`, `season`) AS `LHS`
## LEFT JOIN `Country` AS `RHS`
## ON (`LHS`.`country_id` = `RHS`.`id`)
## )
## ORDER BY `avg_goals` DESC

Other references

• Databases using R
• Introduction to dbplyr
• dbplyr : A Path to More Inclusive Data Transformations at the ACLU
• SQL Tutorial: Solving a Murder Mystery