Joining Two DataFrames in Scala Spark

By Syed Wahaj

When working with Apache Spark in Scala, you might often need to join two DataFrames to combine their data based on a common column. In this article, we’ll explore various methods to join DataFrames in Scala Spark. We’ll cover different types of joins and provide code examples for each method.

Setting Up

Before we dive into the join operations, let’s set up a sample environment by creating two DataFrames that we’ll use for joining.

import org.apache.spark.sql.{SparkSession, DataFrame}

// Create a SparkSession
val spark = SparkSession.builder()
  .appName("DataFrameJoinExample")
  .master("local")
  .getOrCreate()

// Create the first DataFrame
val df1 = spark.createDataFrame(Seq(
  (1, "John"),
  (2, "Jane"),
  (3, "Alice")
)).toDF("id", "name")

// Create the second DataFrame
val df2 = spark.createDataFrame(Seq(
  (1, "London"),
  (2, "Paris"),
  (4, "Berlin")
)).toDF("id", "city")

In the code snippet above, we created two DataFrames: df1 with columns “id” and “name,” and df2 with columns “id” and “city.”

Inner Join

An inner join returns only the matching rows from both DataFrames based on a common column.

val innerJoinDF = df1.join(df2, Seq("id"), "inner")
innerJoinDF.show()

In this example, we perform an inner join using the join() method. We specify the common column “id” and the join type “inner.” The resulting DataFrame, innerJoinDF, contains only the rows where the “id” column matches in both DataFrames.

Left Join

A left join returns all the rows from the left DataFrame and the matching rows from the right DataFrame. If there is no match, it returns null values for the right DataFrame’s columns.

val leftJoinDF = df1.join(df2, Seq("id"), "left")
leftJoinDF.show()

In this example, we perform a left join using the join() method. We specify the common column “id” and the join type “left.” The resulting DataFrame, leftJoinDF, includes all the rows from df1 and the matching rows from df2. If there is no match, the “city” column is filled with null values.

Right Join

A right join returns all the rows from the right DataFrame and the matching rows from the left DataFrame. If there is no match, it returns null values for the left DataFrame’s columns.

val rightJoinDF = df1.join(df2, Seq("id"), "right")
rightJoinDF.show()

In this example, we perform a right join using the join() method. We specify the common column “id” and the join type “right.” The resulting DataFrame, rightJoinDF, includes all the rows from df2 and the matching rows from df1. If there is no match, the “name” column is filled with null values.

Full Outer Join

A full outer join returns all the rows from both DataFrames, filling null values for columns with no match.

val fullOuterJoinDF = df1.join(df2, Seq("id"), "full_outer")
fullOuterJoinDF.show()

In this example, we perform a full outer join using the join() method. We specify the common column “id” and the join type “full_outer.” The resulting DataFrame, fullOuterJoinDF, includes all the rows from both DataFrames, filling null values for unmatched columns.

Cross Join

A cross join, also known as a Cartesian product, returns all possible combinations of rows between the two DataFrames.

val crossJoinDF = df1.crossJoin(df2)
crossJoinDF.show()

In this example, we perform a cross join using the crossJoin() method. The resulting DataFrame, crossJoinDF, contains all possible combinations of rows between df1 and df2.

Conclusion

Joining DataFrames is a common operation when working with Apache Spark in Scala. By using methods like join(), crossJoin(), and specifying the appropriate join type, you can combine DataFrames based on a common column.

Consider the join type carefully based on your use case and the desired result. Inner joins, left joins, right joins, full outer joins, and cross joins each have their specific purposes and produce different outcomes.

Now you have a solid understanding of how to join DataFrames in Scala Spark. Apply these techniques to handle complex data transformations and combine data from multiple sources efficiently.

Happy coding with Apache Spark!