Input widgets allow you to add parameters to your notebooks and dashboards. The widget API consists of calls to create various types of input widgets, remove them, and get bound values.
Widgets are best for:
- Building a notebook or dashboard that is re-executed with different parameters
- Quickly exploring results of a single query with different parameters
View the documentation for the widget API in Scala, Python, and R with the following command:
In this topic:
There are 4 types of widgets:
- Input a value in a text box.
- Select a value from a list of provided values.
- Combination of text and dropdown. Select a value from a provided list or input one in the text box.
- Select one or more values from a list of provided values.
Widget dropdowns and text boxes appear immediately below the toolbar in your notebook.
The widget API is designed to be consistent in Scala, Python, and R. The widget API in SQL is slightly different, but as powerful as the other languages. You manage widgets through the Databricks Utilities interface.
dbutils.widgets.dropdown("X123", "1", [str(x) for x in range(1, 10)])
dbutils.widgets.dropdown("1", "1", [str(x) for x in range(1, 10)], "hello this is a widget")
dbutils.widgets.dropdown("x123123", "1", [str(x) for x in range(1, 10)], "hello this is a widget")
dbutils.widgets.dropdown("x1232133123", "1", [str(x) for x in range(1, 10)], "hello this is a widget 2")
Let’s create a simple dropdown widget.
dbutils.widgets.dropdown("X", "1", [str(x) for x in range(1, 10)])
Now you can interact with the widget from the widget panel, below the notebook toolbar.
You can access the current value of the widget with the call:
Finally you can remove a widget or all widgets in a notebook:
To see detailed API documentation for each method, use
dbutils.widgets.help("<method-name>"). The help API is identical in all languages. For example:
You can create a dropdown widget by passing a unique identifying name, default value, and list of default choices, along with an optional label. Once you create it, a dropdown input widget appears at the top of the notebook. These input widgets are notebook-level entities.
If you try to create a widget that already exists, the configuration of the existing widget is overwritten with the new options.
The API to create widgets in SQL is slightly different but as powerful as the APIs for the other languages. The following is an example of creating a text input widget.
CREATE WIDGET TEXT y DEFAULT "10"
To specify the selectable values in a dropdown widget in SQL, you can write a sub-query. The first column of the resulting table of the sub-query determines the values.
The following cell creates a temporary table in R that we use to create a dropdown widget in SQL.
CREATE WIDGET DROPDOWN cuts DEFAULT "Good" CHOICES SELECT DISTINCT cut FROM diamonds
The default value specified when you create a dropdown widget must be one of the selectable values and must be specified as a string literal. To access the current selected value of an input widget in SQL, you can use a special UDF function in your query. The function is
getArgument(). For example:
SELECT COUNT(*) AS numChoices, getArgument("cuts") AS cuts FROM diamonds WHERE cut = getArgument("cuts")
getArgument is implemented as a Scala UDF and is not supported on a Table ACL-enabled high concurrency cluster. On such clusters you can use the syntax shown in Legacy input widgets in SQL.
Finally you can remove the widget with a SQL command:
REMOVE WIDGET cuts
The old way of creating widgets in SQL queries with the
$<parameter> syntax still works as before. Here is an example:
SELECT * FROM diamonds WHERE cut LIKE '%$cuts%'
If you write a SQL query, either in a SQL notebook or in
%sql magic command in a notebook with a different primary language, you cannot use
$ in an identifier because it is interpreted as a parameter. To escape a
$ in SQL command cells, use
$\. For example, to define the identifier
$foo, write it as
You can configure the behavior of widgets when a new value is selected and whether the widget panel is always pinned to the top of the notebook.
Click the icon at the right end of the Widget panel.
In the pop-up Widget Panel Settings dialog box, choose the widget’s execution behavior.
- Run Notebook
Every time a new value is selected, the entire notebook is rerun.
- Run Accessed Commands
Every time a new value is selected, only cells that retrieve the values for that particular widget are rerun. This is the default setting when you create a widget.
SQL cells are not rerun in this configuration.
- Do Nothing
Every time a new value is selected, nothing is rerun.
You can see a demo of how the Run Accessed Commands setting works in the following notebook. The
year widget is created with setting
2014 and is used in DataFrame API and SQL commands.
When you change the setting of the
year widget to
2007, the DataFrame command reruns, but the SQL command is not rerun.
When you create a dashboard from a notebook that has input widgets, all the widgets display at the top of the dashboard. In presentation mode, every time you update value of a widget you can click the Update button to re-run the notebook and update your dashboard with new values.
If you run a notebook that contains widgets, the specified notebook is run with the widget’s default values. You can also pass in values to widgets. For example:
%run /path/to/notebook $X="10" $Y="1"
This example runs the specified notebook and passes
10 into widget X and
1 into widget Y.