I am trying to transform the data, the older code is in Tibco and using uniqueConcatenate, uniqueCount functions.
I am not sure how we can achieve same output in spark scala.
uniqueConcatenate Example:
uniqueCount Example:
I tried to use collect_set, but as i need to do Over partition by another columns, which seems like not be working for me.
Please help me here !
For uniqueConcatenate you can use collect_set() function which aggregates a column into a set.
For example:
import org.apache.spark.sql.functions.{collect_set, concat_ws}
import spark.implicits._
case class Record(col1: Option[Int] = None, col2: Option[Int] = None, col3: Option[Int] = None)
val df: DataFrame = Seq(Record(Some(1), Some(1), Some(1)), Record(Some(1), None, Some(3)), Record(Some(1), Some(3), Some(3))).toDF("col1", "col2", "col3")
df.show()
/*
+----+----+----+
|col1|col2|col3|
+----+----+----+
| 1| 1| 1|
| 1|null| 3|
| 1| 3| 3|
+----+----+----+
*/
df.agg(
concat_ws(". ", collect_set("col1")).as("col1"),
concat_ws(". ", collect_set("col2")).as("col2"),
concat_ws(". ", collect_set("col3")).as("col3")
).show()
/*
+----+----+----+
|col1|col2|col3|
+----+----+----+
| 1|1. 3|1. 3|
+----+----+----+
*/
For uniqueCount, you can use countDistinct in a similar way:
import org.apache.spark.sql.functions.countDistinct
df.agg(
countDistinct("col1").as("col1"),
countDistinct("col2").as("col2"),
countDistinct("col3").as("col3")
).show()
/*
+----+----+----+
|col1|col2|col3|
+----+----+----+
| 1| 2| 2|
+----+----+----+
*/