Set up Loacation using widget

Timmes0815 · ‎12-11-2024

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

AS

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?

shan_chandra · ‎12-11-2024

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})

Timmes0815 · ‎12-12-2024

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

Timmes0815 · ‎12-19-2024

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

View solution in original post