cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
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

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.