cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Pass date value as parameter in Databricks SQL notebook

sp1
New Contributor II

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"

);

1 ACCEPTED SOLUTION

Accepted Solutions

daniel_sahal
Honored Contributor III

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.

View solution in original post

6 REPLIES 6

daniel_sahal
Honored Contributor III

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.

Kaniz
Community Manager
Community Manager

Hi @Sujatha Pillaiโ€‹  (Customer)โ€‹, We havenโ€™t heard from you since the last response from @Daniel Sahalโ€‹  (Customer)โ€‹ , and I was checking back to see if his suggestions helped you.

Or else, If you have any solution, please share it with the community, as it can be helpful to others.

Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.

Chaitanya_Raju
Honored Contributor

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
Not applicable

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. Image

sp1
New Contributor II

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"

);

Kaniz
Community Manager
Community Manager

Hi @Sujatha Pillaiโ€‹โ€‹, I appreciate your attempt to choose the best answer for us. I'm glad you got your query resolved. @Daniel Sahalโ€‹, Thank you for giving an excellent answer ๐Ÿ˜€.

Welcome to Databricks Community: Lets learn, network and celebrate together

Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections. 

Click here to register and join today! 

Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.