How to use the variable haiving set of values in a spark.sql?

[Daniel3]

Hi, I have a set of values to be searched from a table, for which i was trying to assign them to a variable first and then trying to use the variable in spark.sql, but i'm unable to fetch the records. Please see the image attached and correct my code please.

[Daniel3]

Hello Kaniz, Thanks!

Can I get any reference URL for this content, so that i can see the examples on how to use variables in spark SQL.

[brockb · Accepted solution]

Hi,

One way to address the example provided in your screenshot is by using a combination of a python f-string and a Common Table Expression like shown below. This is assuming that in reality the two tables are different unlike in the provided screenshot. If the query is only against a single table then there is no need for the CTE and the predicate variables can be applied directly against the table.

min_value = 10
max_value = 20
sql_statement = f"""with users_with_filter as (
select user_id from users where user_id between {min_value} and {max_value}
)
select *
from users as u1
inner join users_with_filter as u2
on u1.user_id = u2.user_id
"""
spark.sql(sql_statement).show()