CODEWITHSUNDEEP
pythonsqlapache-sparkpysparkapache-spark-sql
zesla
zesla

Reputation: 11793

How to construct query using like operator for multiple conditions from a python list in spark sql?

I have spark sql query which requires using like operator. for example:

spark.sql("select * from tbl where name like  '%apple%' ")

Now I have a long list of values

name_list = ['apple', 'orange', 'banana', .......]

My question is how I can build my query from the long python list. What I need is a query like below:

spark.sql("select * from tbl where name like '%apple%' or name like '%orange%' or .... ")

The python list is long and can change. I certainly do not want to hard code everything. Wondering if there is any concise way to achieve that? Thanks!

Upvotes: 0

Views: 813

Answers (1)

blackbishop
blackbishop

Reputation: 32680

You can build the or condition using the list of names like this:

import functools

condition = functools.reduce(
    lambda acc, x: f"{acc} or name like '%{x}%'",
    name_list[1:],
    f"name like '%{name_list[0]}%'"
)

spark.sql(f"select * from tbl where {condition}")

Upvotes: 1

Related Questions