topic Re: Set up Loacation using widget in Data Engineering

Set up Loacation using widget

Timmes0815 — Wed, 11 Dec 2024 16:47:41 GMT

I'm struggeling using the databricks widget to set up the location in an sql create table statement.

I tried the following to set up the location:

Step1: Creating a notebook (Notebook1) to define the variable.

Location_Path = 'abfss:xxxxx@xxxx.xxx.net/Tabelname1'

Step2: Creating a notebook with widgets (Notebook2) to create the table.

Cell1:

%python

# create the widget with the location path

dbutils.widgets.text("Location_Path", "")

Cell2:

%sql

-- create table using widget

CREATE OR REPLACE TABLE schema.Tabelname1

LOCATION :Location_Path

SELECT

...

from schema.Tabelname

Step 3: Runing Notebook2 from Notebook1 using dbutils.notebook.run() function

dbutils.notebook.run("Notebook2", 600, {"Location_Path":Location_Path})

Is there documentation available how to setup the location using a test widget?

Re: Set up Loacation using widget

shan_chandra — Wed, 11 Dec 2024 17:12:38 GMT

Hi @Timmes0815 - could you please try the below example and let us know?

Location_Path = dbutils.widgets.text("Location_Path","") Location_Path = dbutils.widgets.getArgument("Location_Path") display(Location_Path) dbutils.notebook.run("Notebook2", 600, {"Location_Path":Location_Path})

Re: Set up Loacation using widget

Timmes0815 — Thu, 12 Dec 2024 12:27:42 GMT

@shan_chandra thainks for your reply. I tried it today but it didn't work. The result from display(Location_Path) is exactly the name of the location. The location is something like

'abfss://abcd@xxxxx.dfs.core.windows.net/mytable'

Puting the location into the SQL works using the :Location_Path Parameter fails. Strange is, that I've additional parameters in my SQL like "Start_Date" and "End_Date" which works fine. Only the parameter for the location is not working.

Re: Set up Loacation using widget

Timmes0815 — Thu, 19 Dec 2024 10:08:30 GMT

I finaly solved my problem by using the parameters in python F-Strings:

Location_Path = dbutils.widgets.text("Location_Path","") -- create table using widget query = f""" CREATE OR REPLACE TABLE schema.Tabelname1 LOCATION '{Location_Path}' AS SELECT ... from schema.Tabelname