I am trying to pass a widget with multiple comma-separated widget values into a list into an SQL query. I can get the widget value into a list like so:

    # Create a widget for user input
dbutils.widgets.text("codes_to_search", "", "Enter values separated by comma")

# Get the input values from the widget
input_values = dbutils.widgets.get("codes_to_search")

# Convert the input string into a list
input_list = input_values.split(",")

# Display the input list
input_list

However, when I try to pass the list into the query using:

and concept_name in ($(input_list))

I get told I'm using a parameter, so I need to pass using parameter syntax, but when I use the parameter syntax, it tells me I'm trying to pass a python variable, and I need to use that syntax. I'm stuck in a loop. Can anybody help?

I am trying to pass a widget with multiple comma-separated widget values into a list into an SQL query. I can get the widget value into a list like so:

    # Create a widget for user input
dbutils.widgets.text("codes_to_search", "", "Enter values separated by comma")

# Get the input values from the widget
input_values = dbutils.widgets.get("codes_to_search")

# Convert the input string into a list
input_list = input_values.split(",")

# Display the input list
input_list

However, when I try to pass the list into the query using:

and concept_name in ($(input_list))

I get told I'm using a parameter, so I need to pass using parameter syntax, but when I use the parameter syntax, it tells me I'm trying to pass a python variable, and I need to use that syntax. I'm stuck in a loop. Can anybody help?

• python
• apache-spark-sql
• widget
• databricks
• databricks-sql

2 Answers 2

I've only been able to make this work using dynamic SQL. Maybe someone smarter has a better way?

This is where most of the magic happens:

inStr = " ,".join(["'" + i + "'" for i in input_list])

This will create a string of each element in your widget, enclosed it in single quotes, and separated by commmas. So, assuming you have foo,bar in your widget, you'll get a string of 'foo','bar'. (I'm assuming your column is a string data type).

input_values = dbutils.widgets.get("codes_to_search")
input_list = input_values.split(",")
#build your in list
inStr = " ,".join(["'" + i + "'" for i in input_list])
#now build your full query
qString = "select <cols> from <your table> where <some col> in (" + inStr + ")"
#and run it
spark.sql(qString).show()

If you want to avoid the extra Python code to split the widget value into a list, you can just do the split in the SQL query itself.

NOTE: If you want to check if something is in the list, you can't use "IN", instead you'd have to use something like "ARRAY_CONTAINS" instead.

For example, here's a notebook with a widget named "param_1":

DECLARE OR REPLACE VARIABLE var_param_1 STRING;
SET VARIABLE var_param_1 = :param_1; --e.g. 'abc,def'

SELECT ARRAY_CONTAINS(SPLIT(var_param_1, ','), 'abc');
--true