Built with Tutify.io

    AIStudyLabs

    Spark SQL - Get Distinct Multiple Columns

    Spark SQL - Get Distinct Multiple Columns

    In this Spark SQL tutorial, you will learn different ways to get the distinct values in every column or selected multiple columns in a DataFrame using methods available on DataFrame and SQL function using Scala examples.

    Before we start, first let's create a DataFrame with some duplicate rows and duplicate values on a few columns. We use this DataFrame to demonstrate how to get distinct multiple columns.

    
  import spark.implicits._

  val simpleData = Seq(("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)
  )
  val df = simpleData.toDF("employee_name", "department", "salary")
  df.show()

    Yields below 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|
+-------------+----------+------+

    On the above table, I've highlighted all duplicate rows, As you notice we have 2 rows that have duplicate values on all columns and we have 4 rows that have duplicate values on "department" and "salary" columns.

    <a id="h-1-get-distinct-all-columns"></a>

    1. Get Distinct All Columns

    On the above DataFrame, we have a total of 10 rows and one row with all values duplicated, performing distinct on this DataFrame should get us 9 as we have one duplicate.

    
  //Distinct all columns
  val distinctDF = df.distinct()
  println("Distinct count: "+distinctDF.count())
  distinctDF.show(false)

    distinct() function on DataFrame returns a new DataFrame after removing the duplicate records. This example yields the below 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 run dropDuplicates() function which returns a new DataFrame with duplicate rows removed.

    
  val df2 = df.dropDuplicates()
  println("Distinct count: "+df2.count())
  df2.show(false)

    <a id="h-2-spark-distinct-of-multiple-columns"></a>

    2. Spark Distinct of Multiple Columns

    Spark doesn't have a distinct method that takes columns that should run distinct on however, Spark provides another signature of dropDuplicates() function which takes multiple columns to eliminate duplicates.

    Note that calling dropDuplicates() on DataFrame returns a new DataFrame with duplicate rows removed.

    
  //Distinct using dropDuplicates
  val dropDisDF = df.dropDuplicates("department","salary")
  println("Distinct count of department & salary : "+dropDisDF.count())
  dropDisDF.show(false)

    Yields below output. If you notice the output, It dropped 2 records that are duplicates.

    
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  |
+-------------+----------+------+

    <a id="h-3-spark-source-code-to-get-distinct-multiple-columns"></a>

    3. Spark Source Code to get distinct multiple columns

    
package com.aistudylabs.spark.dataframe.functions.aggregate

import org.apache.spark.sql.SparkSession
import org.apache.spark.sql.functions._

object SQLDistinct extends App {

  val spark: SparkSession = SparkSession.builder()
    .master("local[1]")
    .appName("AIStudyLabs.com")
    .getOrCreate()

  spark.sparkContext.setLogLevel("ERROR")

  import spark.implicits._

  val simpleData = Seq(("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)
  )
  val df = simpleData.toDF("employee_name", "department", "salary")
  df.show()

  //Distinct all columns
  val distinctDF = df.distinct()
  println("Distinct count: "+distinctDF.count())
  distinctDF.show(false)

  val df2 = df.dropDuplicates()
  println("Distinct count: "+df2.count())
  df2.show(false)

  //Distinct using dropDuplicates
  val dropDisDF = df.dropDuplicates("department","salary")
  println("Distinct count of department & salary : "+dropDisDF.count())
  dropDisDF.show(false)
  
}

    The complete example is available at GitHub for reference.

    <a id="h-4-conclusion"></a>

    4. Conclusion

    In this Spark SQL article, you have learned distinct() method which is used to get the distinct values of all columns and also learned how to use dropDuplicate() to get the distinct and finally learned using dropDuplicate() function to get distinct of multiple columns.

    Happy Learning !!

    <a id="h-related-articles"></a>

    Related Articles