spark filter string column by contains one of list of strings

Question

I need to achieve something similar to: Checking if values in List is part of String in spark. I.e. there is a dataframe of:

abcd_some long strings
goo bar baz

and an Array of desired words like ["some", "bar"].

An UDF with this code would work just fine, however, I would like to have something more efficient. Is there a way to express the FILTER my_col CONTAINS ONE OF [items] using the SQL DSL? Perhaps by dynamically constructing a REGEX?

NOTE: it is not an exat match rather a regular 'CONTAINS' / LIKE '%thing%'. I.e. not an exact match. Otherwise the isIn operator would work.

edit

probably generating some SQL code dynamically is the most efficient way.

def orFilterGeneratorMultiContains(filterPredicates:Seq[String], column:String):Column = {

    col(column).contains(filterPredicates(0)) or col(column).contains(filterPredicates(1)) // TODO iterate
  }
  def filterToDesiredApps(filterPredicates:Seq[String], column:String)(df:DataFrame):DataFrame={
      df.filter(orFilterGeneratorMultiContains(filterPredicates, column))
  }

So is need to still figure out how to properly iterate the expression.

edit 2

However, this turns out to be a bit tricky:

import org.apache.spark.sql.functions.col

val column = col("foo")
val interstingTHings = Seq("bar", "baz", "thing3")

interstingTHings.foldLeft(column) { (filteredOrColumnExpression, predicateItem) =>
  // TODO how to properly nest the OR operator?
  // filteredOrColumnExpression.contains(predicateItem) // generates: Contains(Contains(Contains('foo, bar), baz), thing3)
  filteredOrColumnExpression or filteredOrColumnExpression.contains(predicateItem) // generates: ((('foo || Contains('foo, bar)) || Contains(('foo || Contains('foo, bar)), baz)) || Contains((('foo || Contains('foo, bar)) || Contains(('foo || Contains('foo, bar)), baz)), thing3)) 
  //     TODO but what y really would need is:
  //      col(column).contains("bar") or col(column).contains("baz") or col(column).contains("thing3")
}.explain(true)

as it does not generate the correct OR nested filter conditions.

Are you trying to test all items?.. Could you please sample input and desired output?.. — stack0114106
– stack0114106, Commented Jan 13, 2019 at 12:12
Indeed. All Items. But the list of items will be rather small. — Georg Heiler
– Georg Heiler, Commented Jan 13, 2019 at 13:00

Prasanna V · Accepted Answer · 2020-04-07 06:14:59Z

3

Wouldn't rlike work in this case?

df.filter(col("foo").rlike(interestingThings.mkString("|"))

answered Apr 7, 2020 at 6:14

Prasanna V

312 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bi Rico · Accepted Answer · 2019-01-13 10:50:42Z

2

You have the right idea, but I think you want to use || not or. Something like:

def orFilterGeneratorMultiContains(filterPredicates:Seq[String], column:String): Column = {
  val coi = col(column)
  filterPredicates.map(coi.contains).reduce(_ || _)
}

answered Jan 13, 2019 at 10:50

Bi Rico

25.9k3 gold badges57 silver badges75 bronze badges

1 Comment

Georg Heiler Over a year ago

Indeed. interstingTHings.map(column.contains).reduce(_ or _).explain(true) is what I want. And || or or are equivalent (as far as I know) for the spark-scala-SQL-DSL.

Collectives™ on Stack Overflow

spark filter string column by contains one of list of strings

edit

edit 2

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

edit

edit 2

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related