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

[runninsavvy]

Hello all,

I am attempting to use named parameter markers as shown in this article: https://docs.databricks.com/en/sql/language-manual/sql-ref-parameter-marker.html#named-parameter-mar...

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 

[User16502773013 · Accepted solution]

Hello @runninsavvy ,

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]

That worked, thanks!