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

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

cmilligan — Mon, 06 Feb 2023 21:34:22 GMT

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.

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

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

ChaubeyPravin — Tue, 07 Feb 2023 06:04:35 GMT

Hello,

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

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

Lakshay — Tue, 07 Feb 2023 12:32:37 GMT

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

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

cmilligan — Tue, 07 Feb 2023 13:34:13 GMT

Thanks! I added it in

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

cmilligan — Tue, 07 Feb 2023 13:34:29 GMT

@Lakshay Goel I added it in

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

Lakshay — Tue, 07 Feb 2023 16:48:40 GMT

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

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

cmilligan — Tue, 07 Feb 2023 20:54:01 GMT

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