Cannot pass arrays to spark.sql() using named parameter markers

runninsavvy — Fri, 21 Jun 2024 21:39:59 GMT

Hello all,

I can pass strings and numbers in perfectly fine, but the issues arise when I try to pass in arrays. An example:

spark.sql("SELECT 1 IN (1, 2, 3)", args=params).show()

Works just fine, and the function is able to run. However:

spark.sql("SELECT 1 IN :test", args={"test": [1, 2, 3]}).show()

and also

spark.sql("SELECT 1 IN ARRAY(:test)", args={"test": [1, 2, 3]}).show()

give me vague syntax errors, and even just trying to reference the passed-in array in a basic sense like

spark.sql("SELECT :test", args={"test": [1, 2, 3]}).show()

gives the error "[INVALID_SQL_ARG] The argument test of 'sql()' is invalid. Consider to replace it by a SQL literal."

Is there any way to pass in an array using named parameters to pyspark.sql()? Thanks

Re: Cannot pass arrays to spark.sql() using named parameter markers

User16502773013 — Mon, 24 Jun 2024 04:06:34 GMT

The following code sample can be used in such case

val argArray = Array(1, 2, 3)

val argMap = Map("param" -> argArray.mkString(","))

spark.sql("SELECT 1 IN (SELECT explode(split(:param, ',')))",argMap).show()

runninsavvy — Mon, 24 Jun 2024 13:51:22 GMT

That worked, thanks!