โ01-15-2023 10:02 PM
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"
);
โ01-15-2023 10:34 PM
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.
โ01-15-2023 10:34 PM
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.
โ01-16-2023 04:01 AM
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.
โ01-16-2023 04:55 AM
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!!
โ01-16-2023 10:41 AM
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.
โ01-16-2023 02:11 PM
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"
);
โ01-17-2023 03:50 AM
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 ๐.
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.