topic Pass date value as parameter in Databricks SQL notebook in Data Engineering

Pass date value as parameter in Databricks SQL notebook

sp1 — Mon, 16 Jan 2023 06:02:07 GMT

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"

);

Re: Pass date value as parameter in Databricks SQL notebook

daniel_sahal — Mon, 16 Jan 2023 06:34:03 GMT

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.

Re: Pass date value as parameter in Databricks SQL notebook

Chaitanya_Raju — Mon, 16 Jan 2023 12:55:54 GMT

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!!

Re: Pass date value as parameter in Databricks SQL notebook

Anonymous — Mon, 16 Jan 2023 18:41:26 GMT

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.

Re: Pass date value as parameter in Databricks SQL notebook

sp1 — Mon, 16 Jan 2023 22:11:56 GMT

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"

);

Re: Pass date value as parameter in Databricks SQL notebook

Asifpanjwani — Fri, 26 Apr 2024 20:58:25 GMT

@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