topic Re: how to pull a parameter from .sql file with dbutils.notebook.run in Data Engineering

how to pull a parameter from .sql file with dbutils.notebook.run

carlos_tasayco — Thu, 06 Feb 2025 14:56:27 GMT

Hi,

I want to use this:

result = dbutils.notebook.run('/Workspace/Usersxxxxt', 600, {"environment": inputEnvironment})

this pulls from this .sql file in that path:

DROP TEMPORARY VARIABLE IF EXISTS strEnv;

DECLARE VARIABLE strEnv STRING;

SET VARIABLE strEnv = '${inputEnvironment}';

This works with .py file with that sql code inside, but not if the file is .sql.

Someone has an inside why is failing?

I already tested with another user, is not that I am not sure.

Thanks in advance

Re: how to pull a parameter from .sql file with dbutils.notebook.run

Alberto_Umana — Thu, 06 Feb 2025 15:08:32 GMT

Hi @carlos_tasayco,

When you use dbutils.notebook.run, it expects the target to be a notebook that can accept parameters and execute code within the Databricks environment. This function does not directly support running .sql files. Instead, you should place your SQL code inside a Databricks notebook (e.g., a .py file) and then use dbutils.notebook.run to execute that notebook.

Create a new Databricks notebook (e.g., run_sql_notebook.py) and place your SQL code inside it. For example:

 # run_sql_notebook.py
 inputEnvironment = dbutils.widgets.get("environment")

 sql_query = f"""
 DROP TEMPORARY VARIABLE IF EXISTS strEnv;
 DECLARE VARIABLE strEnv STRING;
 SET VARIABLE strEnv = '{inputEnvironment}';
 """
 spark.sql(sql_query)

Re: how to pull a parameter from .sql file with dbutils.notebook.run

carlos_tasayco — Thu, 06 Feb 2025 15:11:17 GMT

Thanks,

ok forget dbutils.notebook.run there is anothwer way to get a parameter from a .sql file into my notebook?

Re: how to pull a parameter from .sql file with dbutils.notebook.run

MadhuB — Thu, 06 Feb 2025 15:33:54 GMT

@carlos_tasayco There are two methods on how you can pass a variable to the other notebooks as input.

Using Widgets
using collect method like below.

# In notebook1 result = spark.sql("SELECT value FROM table").collect()[0][0] dbutils.notebook.exit(result) # In notebook2 value = dbutils.notebook.run("notebook1", timeout_seconds=60)

Let me know for anything else.