Set up Loacation using widget

[Timmes0815]

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]

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]

@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 · Accepted solution]

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