Spark Scala : Fetch distinct text values within each cell

user_16927029 0
Viewed 61

I am new to scala and I have been having trouble fetching distinct text values from each cell of a row. My dataframe looks somewhat like this below. My intent is to eliminate duplicate skills for each candidate id.

candidate_id skills join_date location
1789s3 java; c++ ; java 2012-09-22 Mumbai
agduch23 ppt ; ppt ; miner 2018-02-02 Banglore
sgdtev office 365; 2019-03-10 Noida

My final resultant dataframe should look somewhat like this -

candidate_id skills join_date location
1789s3 java; c++ 2012-09-22 Mumbai
agduch23 ppt; miner 2018-02-02 Banglore
sgdtev office 365; 2019-03-10 Noida

I use the following command in SQL to do this.

string_agg(ARRAY_TO_STRING(ARRAY((select distinct skill from unnest(split(skills_agg, '; ')) as skill)), '; ')) as skills_distinct

Is there a way I can do this in scala without using sql.

Thanks in advance

apache-spark-sql apache-spark scala
1 Answers

If you're using Spark 3.0 and greater, you can remove duplicate by splitting your string to an array with split, then use function array_distinct to remove duplicates and finally rebuild string with concat_ws, as follow:

import org.apache.spark.sql.functions.{array_distinct, col, concat_ws, split}

dataframe.withColumn("skills", concat_ws("; ", array_distinct(split(col("skills"), "; ")))

You can find all the functions you can use in Scala API functions' Scaladoc

user_68077690
Related