cancel
Showing results for 
Search instead for 
Did you mean: 

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

Daniel3
New Contributor II

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.

1 ACCEPTED SOLUTION

Accepted Solutions

brockb
New Contributor III
New Contributor III

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

 

View solution in original post

3 REPLIES 3

Kaniz
Community Manager
Community Manager

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
New Contributor II

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
New Contributor III
New Contributor III

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

 

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.