cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Set up Loacation using widget

Timmes0815
New Contributor III

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?
 

 

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions

Timmes0815
New Contributor III

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

3 REPLIES 3

shan_chandra
Databricks Employee
Databricks Employee

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
New Contributor III

@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
New Contributor III

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

 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group