Scala: in where clause how to get column string value and split, and intersect against another array?

user_7466054 0
Viewed 183

I have a dataframe where one column is ; separated strings, e.g. "str1;str2;str3;str4", I also have another static list "strx;stry;strz", the goal is to split the column string value and check if the split array has any intersection with the static list, and keep that row

I tried

df.where($"column".split(";").intersect(staticList).nonEmpty)

or df.where(split($"column", ";").intersect(staticList).nonEmpty)

or replace $"" with col()

or use $"".getString before calling split

I always get error split is not a member of org.apache.spark.sql.ColumnName, similar to getString. It appears the split is applied to a Column type instead of String type.

So my question is, during where or filter, how can I access the string value for a column and split it?

Thanks!!

apache-spark-sql apache-spark scala
1 Answers

It seems you're mixing up Spark's split method for Columns with Scala's split for Strings. Please see example below for how the two different split methods are used. Method array_intersect is for intersecting the split Array column with the split element-filter string.

val df = Seq(
  (1, "a;b;c;d"),
  (2, "u;v;w")
).toDF("id", "csv_str")

val filterCsvStr = "a;c;x;y"

val df2 = df.withColumn("wanted_strs", array_intersect(
    split($"csv_str", ";"),
    lit(filterCsvStr.split(";"))
  )
)

df2.show
// +---+-------+-----------+
// | id|csv_str|wanted_strs|
// +---+-------+-----------+
// |  1|a;b;c;d|     [a, c]|
// |  2|  u;v;w|         []|
// +---+-------+-----------+

df2.where(size($"wanted_strs") > 0).show
// +---+-------+-----------+
// | id|csv_str|wanted_strs|
// +---+-------+-----------+
// |  1|a;b;c;d|     [a, c]|
// +---+-------+-----------+

A couple of notes:

  1. Method array_intersect removes duplicate elements in the Array column.
  2. lit is for converting the Scala list to a Column as array_intersect expects Column-typed arguments.

If the element filters are available as values of a Column, arguments for the array_intersect will be simpler:

val df = Seq(
  (1, "a;b;c;d", "b;c;e"),
  (2, "u;v;w", "t;v")
).toDF("id", "csv_str", "filter_csv_str")

val df2 = df.withColumn("wanted_strs", array_intersect(
    split($"csv_str", ";"),
    split($"filter_csv_str", ";")
  )
)

df2.show
// +---+-------+--------------+-----------+
// | id|csv_str|filter_csv_str|wanted_strs|
// +---+-------+--------------+-----------+
// |  1|a;b;c;d|         b;c;e|     [b, c]|
// |  2|  u;v;w|           t;v|        [v]|
// +---+-------+--------------+-----------+
user_63165080
Related