cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

Reference a single item tuple using .format() in spark.sql()

cmilligan
Contributor II

I'm trying to pass the elements of a tuple into a sql query using .format(). This works fine when I have multiple items in my tuple, but when using a single item in a tuple I get an error.

tuple1 = (1,2,3)
tuple2 = (5,)
 
combo = tuple1 + tuple2
 
print(combo)
 
query = ("""
 
select
case
  when column in {tuple1} then 1
  when column in {tuple2} then 2
end as check
 
from table
 
where column in {combo}
 
""".format(tuple1 = tuple1, tuple2 = tuple2, combo = combo))
 
print(query)

Whenever I print the query it keeps the comma at the end of the single tuple which is what I think is causing the error.

Result 

I do want to continue to use a tuple because there are scenarios where I need to concatenate multiple tuples together.

1 ACCEPTED SOLUTION

Accepted Solutions

Lakshay
Esteemed Contributor
Esteemed Contributor

For a single-value tuple, you need to use a check to modify the SQL query as well as how the tuple is passed. The single-value tuples will always have a comma at the end. You need to modify the query to be able to pass only the value from the tuple. The new query would look something like

query = ("""

select key,

case

 when column in {tuple1} then 1

 when column in ({tuple2}) then 2

end as check

from table

""".format(tuple1 = tuple1, tuple2 = tuple2[0], combo = combo))

View solution in original post

6 REPLIES 6

ChaubeyPravin
New Contributor II

Hello,

can you please add some code or snippet where you are getting error, it will be more clear to understand your problem.

Thanks! I added it in

Lakshay
Esteemed Contributor
Esteemed Contributor

Could you please post the code and the error that you are getting?

@Lakshay Goel​ I added it in

Lakshay
Esteemed Contributor
Esteemed Contributor

For a single-value tuple, you need to use a check to modify the SQL query as well as how the tuple is passed. The single-value tuples will always have a comma at the end. You need to modify the query to be able to pass only the value from the tuple. The new query would look something like

query = ("""

select key,

case

 when column in {tuple1} then 1

 when column in ({tuple2}) then 2

end as check

from table

""".format(tuple1 = tuple1, tuple2 = tuple2[0], combo = combo))

Thanks for the reply. I think I'll keep digging around a little trying to find something that a user can update without having to worry if there is one value or multiple

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!