Input widgets allow you to add parameters to your notebooks and dashboards. The widget API consists of calls to create different 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
Widgets enable you to parameterize your notebooks. The Databricks widget API allows you to create, remove and access values of widgets.
You can view the documentation for the widget API in Scala, Python or R with the following command:
- There are 4 types of widgets to select from:
Text- Input your value of choice in the text box
Dropdown- Choose a value from the list of provided values.
Combobox- Combination of text and dropdown. You can choose a value from your provided list, or input a new one via the text box.
Multiselect- Select 1 or multiple values from the list of provided values.
We have designed the widget API to be consistent in Scala, Python and R. The widget API in SQL is slightly different but as powerful as other languages. Manage widgets through the Databricks Utilities - dbutils interface (as seen below).
sqlContext.read.format("com.databricks.spark.csv") .option("header","true") .option("inferSchema", "true") .load("/databricks-datasets/Rdatasets/data-001/csv/ggplot2/diamonds.csv") .registerTempTable("diamonds")
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")
Widgets in Scala/Python/R¶
To see detailed API documentation for each method use
dbutils.widgets.help("methodName"). Note that the help API is identical in all languages. For example:
The above document suggests that you can create a dropdown widget by passing a unique identifying name, default value and list of default choices and an optional label. Once created, a dropdown input widget will appear at the top of the notebook. Note that these input widgets are notebook level concepts.
If you try to create a widget that already exists, the configuration of the widget will be overwritten with new options.
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 on the top bar. You can access the current value of the widget with following call
Finally you can remove your widget (or all widgets in a notebook):
Widgets in SQL¶
The API to create widgets in SQL is as powerful as other languages. Following is an example of creating a text input widget.
CREATE WIDGET TEXT y DEFAULT "10"
To specify default values of dropdown widgets in SQL you can write a sub-query. The first column of the resulting table of the sub-query will determine default choices.
In the following cell, we first create a temporary table in R and then we use it to create a dropdown widget in SQL.
CREATE WIDGET DROPDOWN cuts DEFAULT "Good" CHOICES select distinct cut from diamonds
Note that the default value specified when creating dropdown widgets must be one of the default choices. 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 numChocies, getArgument("cuts") as cuts from diamonds where cut = getArgument("cuts")
And finally we can remove the widgets we have created with a SQL command:
REMOVE WIDGET cuts
Legacy input widgets in SQL¶
The old way of creating SQL queries still works as before. Here is an example:
select * from diamonds where cut like '%$cuts%'
Configuring Widget Settings¶
The execution behavior of Widgets are configurable. Widget settings are accessible on the right end of the Widget bar via the icon.
In the pop-up Settings dialog box, you can choose the Widget’s execution behavior.
Here are the available choices:
- Run Notebook - Every time a user selects a new value through the widget, the entire notebook will rerun.
- Run Accessed Commands - Every time a user selects the widget, only cells that retrieve the values for that particular widget will rerun. Please note: SQL cells will not be rerun in this configuration.
- Do Nothing
Widgets in Dashboards¶
Using Widgets in Dashboards When you create a dashboard from a notebook that has input widgets, all the widgets will appear at the top of the dashboard. In presentation mode, every time you update value of a widget you can click on the update button to re-run the notebook and update your dashboard with new values.
Using Widgets with %run¶
If you do use Running a Notebook from Another Notebook on a notebook that contains widgets, it will run the specified notebook with the widget’s default values. You can also pass in values to widgets using Running a Notebook from Another Notebook.
%run /path/to/notebook $X="10" $Y="1"
The above example will run the specified notebook and pass “10” into widget X and “1” into widget Y.