Left joins
As well as manipulating single data frames, sparklyr allows you to join two data frames together. A full treatment of how to join tables together using dplyr syntax is given in the Joining Data with dplyr course. For the rest of this chapter, you'll see some examples of how to do this using Spark.
A left join takes all the values from the first table, and looks for matches in the second table. If it finds a match, it adds the data from the second table; if not, it adds missing values. The principle is shown in this diagram.
Left joins are a type of mutating join, since they simply add columns to the first table. To perform a left join with sparklyr, call left_join(), passing two tibbles and a character vector of columns to join on.
left_join(a_tibble, another_tibble, by = c("id_col1", "id_col2"))
When you describe this join in words, the table names are reversed. This join would be written as "another_tibble is left joined to a_tibble".
This exercise introduces another Spark DataFrame containing terms that describe each artist. These range from rather general terms, like "pop", to more niche genres such as "swiss hip hop" and "mathgrindcore".
This is a part of the course
“Introduction to Spark with sparklyr in R”
Exercise instructions
A Spark connection has been created for you as spark_conn. Tibbles attached to the track metadata and artist terms stored in Spark have been pre-defined as track_metadata_tbl and artist_terms_tbl respectively.
- Use a left join to join the artist terms to the track metadata by the
artist_idcolumn.- The table to be joined to,
track_metadata_tbl, comes first. - The table that joins the first,
artist_terms_tbl, comes next. - Assign the result to
joined.
- The table to be joined to,
- Use
sdf_dim()to determine how many rows and columns there are in the joined table.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
# track_metadata_tbl and artist_terms_tbl have been pre-defined
track_metadata_tbl
artist_terms_tbl
# Left join artist terms to track metadata by artist_id
joined <- ___(___, ___, by = ___)
# How many rows and columns are in the joined table?
___