Pass date value as parameter in Databricks SQL notebook

[sp1]

I want to pass yesterday date (In the example 20230115*.csv) in the csv file. Don't know how to create parameter and use it here.

CREATE OR REPLACE TEMPORARY VIEW abc_delivery_log

USING CSV

OPTIONS (

 header="true",

 delimiter=",",

 inferSchema="true",

 path="abfss://selfserv-xxxxx@xxxxx/xxxxx/abc_delivery_log_20230115080013.csv"

);

[daniel_sahal · Accepted solution]

You can use dbutils.widgets to pass a parameter to your notebook (https://docs.databricks.com/notebooks/widgets.html) then use Python to create and execute your parametrized sql code.

[Chaitanya_Raju]

Hi @Sujatha Pillai​ 

You can try using the below SQL code using widgets

CREATE WIDGET TEXT pathname DEFAULT "pathvalue"
 
CREATE OR REPLACE TEMPORARY VIEW abc_delivery_log
USING CSV
OPTIONS (
 header="true",
 delimiter=",",
 inferSchema="true",
 path = getArgument('pathname')
);

and using widgets you can pass the variable value

Happy Learning!!

[Anonymous]

SQL has a function for current_timestamp, which you can cast as a date to get todays date. You don't need to pass anything to the notebook for this logic to work.

[sp1]

Thanks for all your replies.

I have used widget using date and it's working as expected.

Here is the code:

drop view if exists inputdate1;

create view inputdate1 as select date_format(current_date()-1, 'yyyyMMdd') AS date UNION ALL select date_format(current_date(), 'yyyyMMdd') AS date;

%python

dataframe = sqlContext.sql('select * from inputdate1') 

values = [row.date for row in dataframe.select('date').collect()]

default_value = str(min(values))

values_str = [str(value) for value in values]

dbutils.widgets.dropdown("date", default_value, values_str)

CREATE OR REPLACE TEMPORARY VIEW abc_delivery_log

USING CSV

OPTIONS (

 header="true",

 delimiter=",",

 inferSchema="true",

 path="abfss://selfserv-xxxxx@xxxxx/xxxxx/abc_delivery_log_delivery_log_${date}*.csv"

);

[Asifpanjwani]

@Retired_mod @sp1 @Chaitanya_Raju @daniel_sahal 

Hi Everyone,

I need the same scenario on SQL code, because my DBR cluster not allowed me to run python code

Error: Unsupported cell during execution. SQL warehouses only support executing SQL cells.

I appreciate your help

Thanks