- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
);
- Labels:
-
CSV
-
Databricks SQL
-
Date
-
DateValue
-
SQL
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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!!
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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"
);
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
04-26-2024 01:58 PM
@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