Paramter Type while Executing Rest API of Execute a SQL statement POST /api/2.0/sql/statements/

A4Ashish — Mon, 26 Aug 2024 11:59:46 GMT

Hi,
So previously I was using Old Preview Databricks API version. Now I have switched to v1 of the API which usage
Execute a SQL statement POST /api/2.0/sql/statements/

I wanted to know how to pass the parameter class when my value is list of string for Eg
OLD Method
SQL - Select * from Table where columnName in ({{someName}})
parameter - new ParametersList {Name = "someName", Value = someNameList, Type = "enum", MultiValuesOptions = new MultiValuesListParameter { Prefix = "", Suffix = "", Separator = "," }, EnumOptions = string.Join("\n", someNameList)}

Now I need to convert this in new Databricks API
SQL - Select * from Table where columnName in (:someName)
parameter - new ParametersList {Name = "someName", Value = someNameList, Type = "string")}

this new parameter structure I go from the official link here
Execute a SQL statement | Statement Execution API | REST API reference | Azure Databricks

Im not sure how to pass this parameter as the request is getting Bad request in response, I mainly wanted to know what to give Type when Value is a list

Re: Paramter Type while Executing Rest API of Execute a SQL statement POST /api/2.0/sql/statements/

szymon_dybczak — Mon, 26 Aug 2024 19:34:00 GMT

Hi @A4Ashish ,

Currently complex data types are not supported in parameters. Have a look at below snippet from documentation:

https://docs.databricks.com/api/workspace/statementexecution/executestatement

"If the type is given, parameters will be checked for type correctness according to the given type. A value is correct if the provided string can be converted to the requested type using the cast function. The exact semantics are described in the section cast function of the SQL language reference."

So, according to documentation the only valid values are ones that can be casted to the target type using cast function. Now, take a look on below matrix from cast function documentation. As you can see, you cannot cast string to array using cast function, thus you cannot use it in this API.

topic Re: Paramter Type while Executing Rest API of Execute a SQL statement POST /api/2.0/sql/statements/ in Data Engineering

Paramter Type while Executing Rest API of Execute a SQL statement POST /api/2.0/sql/statements/

Re: Paramter Type while Executing Rest API of Execute a SQL statement POST /api/2.0/sql/statements/