Introduction

Let's explore different ways to obtain distinct values in every column or select multiple columns in a DataFrame using Java Spark.

First, let’s create a DataFrame with some duplicate rows and duplicate values in a few columns. We will use this DataFrame to demonstrate how to get distinct values in multiple columns.

import org.apache.spark.sql.Dataset;
import org.apache.spark.sql.Row;
import org.apache.spark.sql.SparkSession;
import org.apache.spark.sql.Encoders;
import org.apache.spark.sql.types.*;

import java.util.Arrays;
import java.util.List;

SparkSession spark = SparkSession.builder()
        .appName("Remove Duplicate Rows Example")
        .master("local")
        .getOrCreate();

List<Row> data = Arrays.asList(
        RowFactory.create("James", "Sales", 3000),
        RowFactory.create("Michael", "Sales", 4600),
        RowFactory.create("Robert", "Sales", 4100),
        RowFactory.create("Maria", "Finance", 3000),
        RowFactory.create("James", "Sales", 3000),
        RowFactory.create("Scott", "Finance", 3300),
        RowFactory.create("Jen", "Finance", 3900),
        RowFactory.create("Jeff", "Marketing", 3000),
        RowFactory.create("Kumar", "Marketing", 2000),
        RowFactory.create("Saif", "Sales", 4100)
);

StructType schema = new StructType(new StructField[]{
        new StructField("employee_name", DataTypes.StringType, false, Metadata.empty()),
        new StructField("department", DataTypes.StringType, false, Metadata.empty()),
        new StructField("salary", DataTypes.IntegerType, false, Metadata.empty())
});

Dataset<Row> df = spark.createDataFrame(data, schema);
df.show();

Output:

+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|        James|     Sales|  3000|
|      Michael|     Sales|  4600|
|       Robert|     Sales|  4100|
|        Maria|   Finance|  3000|
|        James|     Sales|  3000|
|        Scott|   Finance|  3300|
|          Jen|   Finance|  3900|
|         Jeff| Marketing|  3000|
|        Kumar| Marketing|  2000|
|         Saif|     Sales|  4100|
+-------------+----------+------+

As you notice, there are 2 rows with duplicate values in all columns, and 4 rows have duplicate values in the “department” and “salary” columns.

1. Get Distinct All Columns

In the above DataFrame, there are a total of 10 rows with one row having all values duplicated. Performing distinct on this DataFrame should give us 9, as there is one duplicate.

// Distinct all columns
Dataset<Row> distinctDF = df.distinct();
System.out.println("Distinct count: " + distinctDF.count());
distinctDF.show(false);

The distinct() function on the DataFrame returns a new DataFrame after removing the duplicate records. This example yields the below output.

Output:

Distinct count: 9
+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|James        |Sales     |3000  |
|Michael      |Sales     |4600  |
|Maria        |Finance   |3000  |
|Robert       |Sales     |4100  |
|Saif         |Sales     |4100  |
|Scott        |Finance   |3300  |
|Jeff         |Marketing |3000  |
|Jen          |Finance   |3900  |
|Kumar        |Marketing |2000  |
+-------------+----------+------+

Alternatively, you can also use the dropDuplicates() function, which returns a new DataFrame with duplicate rows removed.

Dataset<Row> df2 = df.dropDuplicates();
System.out.println("Distinct count: " + df2.count());
df2.show(false);

2. Distinct of Multiple Columns

Spark doesn’t have a distinct method that specifically takes columns for running distinct operations. However, Spark provides another signature of the dropDuplicates() function that takes multiple columns to eliminate duplicates.

Calling dropDuplicates() on a DataFrame returns a new DataFrame with duplicate rows removed.

// Distinct using dropDuplicates
Dataset<Row> dropDisDF = df.dropDuplicates("department", "salary");
System.out.println("Distinct count of department & salary: " + dropDisDF.count());
dropDisDF.show(false);

This yields the output below. Notice that it has dropped 2 duplicate records.

Output:

Distinct count of department & salary: 8
+-------------+----------+------+
|employee_name|department|salary|
+-------------+----------+------+
|Jen          |Finance   |3900  |
|Maria        |Finance   |3000  |
|Scott        |Finance   |3300  |
|Michael      |Sales     |4600  |
|Kumar        |Marketing |2000  |
|Robert       |Sales     |4100  |
|James        |Sales     |3000  |
|Jeff         |Marketing |3000  |
+-------------+----------+------+

The complete example is available at GitHub for reference.