Using widgets in SQL notebooks in  Azure Databricks

In this article we will see how data engineers/data analysts using SQL Notebooks in Azure Databricks can use widgets to parameterize their notebooks.

Adding parameters to the queries make the queries dynamic, helping users to use the same query to drive results as per their needs.

The support of parameters in Azure Databricks is offered via widgets. 

Input widgets allow users to add parameters to their 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:

1. Building a notebook or dashboard that is re-executed with different parameters
2. Quickly exploring results of a single query with different parameters

Currently there are 4 types of widgets —

1. text: accepts string characters
2. dropdown: provides a list of values
3. combobox: provides a combination of string characters and dropdown. Basically, you have the option to either choose from the list or enter a text.
4. multiselect: provides options to select one or more options from a dropdown list

Let’s create a simple SQL notebook to understand how we can create widgets to pass values.

• Create a text widget "country" with a default value of USA

•        
  CREATE WIDGET TEXT country DEFAULT 'USA'

• Create a Dropdown widget "countries" with a default value of "USA" and multiple values to select from

•        
  CREATE WIDGET DROPDOWN countries DEFAULT 'USA' CHOICES SELECT country FROM (SELECT 'USA' AS country UNION ALL SELECT 'Canada' AS country) as dat

• Remove 'country' widget

•        
  REMOVE WIDGET country

• Remove 'countries' widget

•        
  REMOVE WIDGET countries

• Now we will look into passing widget values to a SQL query. There are 2 options for getting the widget values
  1. getArgument
  2. $parameter
  
  To use the widget values as parameters, I am going to create a dataframe. Once the data frame is created we will create a temporary view from the data Frame. As part of the last step, we will pass the widget value to the Temporary view and filter down the results. Creation of data frame and temporary view will be completed in python using the magic command %python. The filtering of the temporary view using the parameter values will be completed in SQL

•        
  
  %python
  dataframe_data = [("China","1410539758"),("Nigeria","225082083"),("India","1389637446"),("Brazil","217240060"),("United States","332838183"),("Bangladesh","165650475"),("Indonesia","277329163"),("Russia","142021981"),("Pakistan","242923845"),("Mexico","129150971")]
  dataframe_columns = ['country', 'population']
  dfPopulation = spark.createDataFrame(dataframe_data, dataframe_columns)
  display(dfPopulation)

•        
  
  %python
  dfPopulation.createOrReplaceTempView('vw_population')

•        
  SELECT * FROM vw_population

• Create a Dropdown widget 'country' based on the values from the vw_population view

•        
  CREATE WIDGET DROPDOWN country DEFAULT 'United States' CHOICES SELECT DISTINCT country FROM vw_population

• Add a where clause to the temporary view based on the value from the the 'country" filter using $parameter

•        
  SELECT population, getArgument("country") as country FROM vw_population WHERE country = getArgument("country")

• Add a where clause to the temporary view based on the value from the the 'country" filter using $parameter

•        
  SELECT population, '$country' as country FROM vw_population WHERE country = '$country'

• Create a Multi Select widget 'country_multiselect' by using values from the vw_population view

•        
  CREATE WIDGET MULTISELECT country_multiselect DEFAULT 'United States' CHOICES SELECT DISTINCT country FROM vw_population

• Create a Combo Box widget 'country_combobox' by using values from the vw_population view

•        
  CREATE WIDGET COMBOBOX country_combobox DEFAULT 'United States' CHOICES SELECT DISTINCT country FROM vw_population

• Get selected value of the Combo Box widget 'country_combobox'

•        
  SELECT '$country_combobox' As country

• Get selected value of the Multi Select widget 'country_multiselect'

•        
  SELECT '$country_multiselect' AS countries

• Remove all the widgets

•        
  
  REMOVE WIDGET country

•        
  
  REMOVE WIDGET country_combobox

•        
  REMOVE WIDGET country_multiselect

• The corresponding SQL notebook can be downloaded from this location. The following are the notebooks that need to be downloaded
  1. Using widgets in SQL Notebooks.dbc
  2. Using widgets in SQL Notebooks.sql