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.

[Kaniz]

Hi @Daniel3 , Hi  , Certainly! It looks like you’re trying to use variables in your Spark SQL query.

Let’s address this and improve your code.

String Formatting:

You can use Python’s string formatting to insert variables into your query.

Using Parameters:

Another approach is to use parameters. You can set the parameters and refer to them in your query.

Note on Performance:

• Be cautious when using variables in Spark SQL queries. The query optimizer may not have complete information about the variables during query plan generation.
• If performance is critical, consider using hard-coded values or optimizing your query execution plan.

Remember to adapt the code to your specific use case and data set. If you encounter any issues or need further assistance, feel free to ask! 😊

[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()