How to use widgets to pass dynamic column names in Dataframe select statement

Question

I have a Dataframe and I want to dynamically pass the columns names through widgets in a select statement in my Databricks Notebook. How can I do it?

I am using the below code

df1 = spark.sql("select * from tableraw")

where df1 has columns "tablename" and "layer"

df = df1.select("tablename", "layer")

Now, our requirement is to use the values of the widgets to select those columns, something like:

df = df1.select(dbutils.widget.get("tablename"), dbutils.widget.get("datalayer"))

Answer 1

Python / Scala

Create Widgets

%python
dbutils.widgets.text(name = "pythonTextWidget", defaultValue = "columnName")
dbutils.widgets.dropdown(name = "pythonDropdownWidget", defaultValue = "col1", choices = ["col1", "col2", "col3"])

%scala
dbutils.widgets.text("scalaTextWidget", "columnName")
dbutils.widgets.dropdown("scalaDropdownWidget", "col1", Seq("col1", "col2", "col3"))

Extract Value from Widgets

%python
textColumn = dbutils.widgets.get("pythonTextWidget")
dropdownColumn = dbutils.widgets.get("pythonDropdownWidget")

%scala
val textColumn = dbutils.widgets.get("scalaTextWidget")
val dropdownColumn = dbutils.widgets.get("scalaDropdownWidget")

Use Value to select Column

%python
from pyspark.sql.functions import col
df.select(col(textColumn), col(dropdownColumn))

%scala
import org.apache.spark.sql.functions.col
df.select(col(textColumn), col(dropdownColumn))

SQL

The Widgets in SQL work slightly different compared to Python/Scala in the sense that you cannot use them to select a column. However, widgets can be used to dynamically adjust filters.

Create Widget

%sql CREATE WIDGET text sqlTextWidget DEFAULT "ACTIVE"

%sql CREATE WIDGET DROPDOWN sqlDropdownWidget DEFAULT "ACTIVE" CHOICES SELECT DISTINCT Status FROM <databaseName>.<tableName> WHERE Status IS NOT NULL

Apply Widget Value to Filter Statement

%sql SELECT * FROM <databaseName>.<tableName> WHERE Status = getArgument("sqlTextWidget")

More background can be found on the Databricks documentation on Widgets.