Search code examples
apache-spark-sqldatabricksazure-databricks

How can I change the default value for a text input widget in Databricks?

I have a text input widget where a user can enter a date.

CREATE WIDGET TEXT FromDate DEFAULT '2021-09-27'

This works fine, except that I would like the default to always be yesterday's date.

I can, of course, do this:

SELECT CAST(date_sub(CAST(current_timestamp() as DATE), 1) AS STRING)

which returns 2021-09-27 as expected, so I was hoping that this would work for the widget:

CREATE WIDGET TEXT FromDate DEFAULT (SELECT CAST(date_sub(CAST(current_timestamp() as DATE), 1) AS STRING))

However, this results in:


Error in SQL statement: ParseException: 
no viable alternative at input 'CREATE WIDGET'(line 1, pos 7)

== SQL ==
CREATE WIDGET TEXT From DEFAULT (SELECT CAST(date_sub(CAST(current_timestamp() as DATE), 1) AS STRING))
-------^^^

How can I change the default value for a text input widget in Databricks so that it always retuns yesterday's date ?

Solution

  • Unfortunately, this is impossible - you can specify subquery only to populate the CHOICES argument of the Dropdown widget. Text widgets are accepting only literal value.

    Per docs:

    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 possible workaround would be to create widget using the Python, where there are more possibilities for calculation of the default values.