How to create a widget in SQL with variables?

[zc]

I want to create a widget in SQL and use it in R later. Below is my code

%sql

declare or replace date1 date = "2025-01-31";

declare or replace date2 date ;

set var date2=add_months(date1,5);

What's the correct syntax of using date2 to create a widget? I have tried

CREATE WIDGET TEXT date_end  DEFAULT date2;

CREATE WIDGET TEXT date_end  DEFAULT $date2;

CREATE WIDGET TEXT date_end  DEFAULT :date2;

and none worked.

If there are other ways to let R access a variable in SQL, I'd love to learn that too.

Thank you,

[Khaja_Zaffer]

Hello @zc 

This should work.

https://docs.databricks.com/aws/en/notebooks/widgets

[Khaja_Zaffer]

when using SQL to create a widget and set its default value to a previously declared variable, you should use a colon (:) prefix to reference the variable.

The correct syntax for your scenario is:

SQL

CREATE WIDGET TEXT date_end DEFAULT :date2;

[zc]

Thank you for responding, Khaja. But the code you posted won't work as I described in my question.

[szymon_dybczak · Accepted solution]

Hi @zc ,

Unfortunately, I think in case of sql widgets default value needs to be string literals. So above approach won't work.

Regarding your second question about accessing variables decalared in SQL in R cell, you cannot do such a thing. Here's an excerpt from documentation:

"When you invoke a language magic command, the command is dispatched to the REPL in the execution context for the notebook. Variables defined in one language (and hence in the REPL for that language) are not available in the REPL of another language. REPLs can share state only through external resources such as files in DBFS or objects in object storage."

Develop code in Databricks notebooks | Databricks Documentation

[zc]

Hi @szymon_dybczak 

Thank you for the information. If I understand correctly, you meant one cannot use a SQL variable for a SQL widget, but only hard coded strings?

[szymon_dybczak]

Hi @zc ,

Exactly, this is what I meant. What is funny though, that in python cell you can define variable and then later use it as a widget's default value. So there's a bit of inconsistency here between different types of cells.

Following works in python cell:

from datetime import datetime
from dateutil.relativedelta import relativedelta

date1_str = "2025-01-31"
date2_str = None


date1_obj = datetime.strptime(date1_str, "%Y-%m-%d").date()
date2_obj = date1_obj + relativedelta(months=5)
date2_str = date2_obj.strftime("%Y-%m-%d")

print(f"date1: {date1_str}")  # 2025-01-31
print(f"date2: {date2_str}")  # 2025-06-30

dbutils.widgets.text("date1", date1_str)
dbutils.widgets.text("date2", date2_str)

[zc]

Hi @szymon_dybczak 

Thank you for the details. I figured that part out using R as well. Turns out it's easy to share variables from Python/R to SQL, but not the other way around.

[Khaja_Zaffer]

Hello @szymon_dybczak 
Thanks for the information, I did a repo of this, indeed I was using all purpose cluster and found that while SQL commands doesnt help in creating widgets if used as date format. but it worked when I converted date into string. while microsoft didnt explicitly mentioned this, I wonder hwo did you figured it out?

[szymon_dybczak]

Hi @Khaja_Zaffer ,

Sure, I just checked what are expected data types for arguments using dbutils.widgets.help(). As you can see, for defaultValue they require String data type.

[Khaja_Zaffer]

Hello @szymon_dybczak 

Indeed it is. Sorry that I could resolve the issue at first instance. but yeah, it accepts strings no matter which widget it is. 

[Khaja_Zaffer]

2/2 and why it doesnt work any idea?