Facing issue while using widget values in sql script

[Ashwathy]

I am using below code to create and read widgets. I am assigning default value.

dbutils.widgets.text("pname", "default","parameter_name")

pname=dbutils.widgets.get("pname")

I am using this widget parameter in some sql scripts. one example is given below

%sql

CREATE database if not exists ${pname}_test;

I am executing this notebook from an ADF. I am not passing any value to this parameter, so it will take default value.

Problem I am facing is, sometimes this widget value is reflecting and sometimes it is not reflecting in the sql script so it is creating "_test" database. This is happening in my dev environment. I am using exactly same code in my production environment, and there it is working fine.

Sometimes it is working all fine and sometimes I am facing this issue. Can anyone please help me to identify the issue?

[Kaniz]

Hi @Ashwathy P P​ , Which Databricks Runtime are you using?

A known issue is that a widget state may not be adequately clear after pressing Run All, even after clearing or removing the widget in the code. If this happens, you will see a discrepancy between the widget’s visual and printed states. Re-running the cells individually may bypass this issue. To avoid this issue entirely, Databricks recommends that you use ipywidgets.

If you are running Databricks Runtime 11.0 or above, you can also use ipywidgets in Databricks notebooks.

[Ashwathy]

Hi @Kaniz Fatma​ , I tried executing notebook using runtime 9.1 LTS and 10.4 LTS.

The problem is, I am using this sql scripts in python notebook. I can able to read widget values and able to print the widget values using python print statement. I am getting value as expected.

When I use same variable in a SQL script in the same notebook, this value is not reflecting there instead it is taking empty value.

I changed all my sql scripts to spark.sql() statements and now notebooks are running fine. But still I am not getting, why sql scripts are not working here.

[takomyr]

https://docs.databricks.com/notebooks/widgets.html states

In general, you cannot use widgets to pass arguments between different languages within a notebook. You can create a widget arg1 in a Python cell and use it in a SQL or Scala cell if you run one cell at a time. However, this does not work if you use Run All or run the notebook as a job.