topic Re: Parameterize Database Name in SparkSQL Notebook in Data Engineering

Parameterize Database Name in SparkSQL Notebook

Shanthan118 — Mon, 19 Aug 2024 11:55:57 GMT

I am using Notebook in SparkSql in Fabric
i have created the Notebook to load data from a Lakehouse to the other lakehouse using this SparkSQL.

Now that i created this notebook we have used only a single database but now i want to use it for other Database instead of changing the Database manually is there a way parameterize the database name.

For that,

I have created a notebook that has the database names so when i want to change the database name i can change there and call this databasenames notebook in the notebook where the tables are created or data is transfered.
NOTE: I want to use Notebook in SparkSQL only.

Can Any please give the solution to this ...

Re: Parameterize Database Name in SparkSQL Notebook

SachinNandanwar — Mon, 19 Aug 2024 12:08:58 GMT

How would you be executing the Notebook that transfers the data across lakehouses ? Will it be an Notebook Activity ? If yes then go through this post.

https://community.fabric.microsoft.com/t5/Data-Pipelines/Is-it-possible-to-pass-parameter-from-notebook-activity-to/m-p/3429814

Re: Parameterize Database Name in SparkSQL Notebook

Anonymous — Tue, 20 Aug 2024 09:44:06 GMT

Hi @Shanthan118

If you want to store the database name in a notebook then reference it by another notebook, you can use Microsoft Spark Utilities (MSSparkUtils) in Fabric. Microsoft Spark Utilities (MSSparkUtils) is a built-in package and available in PySpark (Python) Scala, SparkR notebooks, and Fabric pipelines. It is not available in SparkSQL. So if you want to use Notebook in SparkSQL only, I'm afraid it is a big challenge currently.

Microsoft Spark Utilities (MSSparkUtils) for Fabric - Microsoft Fabric | Microsoft Learn

Another alternative is to use the pipeline to trigger a notebook activity and edit the parameter values in the pipeline, which is suggested by SachinNandanwar. You could refer to Integrate a notebook section from Develop, execute, and manage notebooks - Microsoft Fabric | Microsoft Learn

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

Re: Parameterize Database Name in SparkSQL Notebook

frithjof_v — Tue, 20 Aug 2024 13:36:09 GMT

By SparkSQL, do you mean %%sql or spark.sql()?

What do you mean by Database? Do you mean Lakehouse?

Is everything inside the same workspace or in separate workspaces?

You can probably parameterize Lakehouse name and use the parameter in the creation of a dataframe, then you can run spark.sql() on that dataframe.

If you want to use %%sql cell then you could create a temp view from the dataframe first.

https://www.reddit.com/r/MicrosoftFabric/s/5jWsdCEJNy

You can get abfss path for a Lakehouse by using Notebookutils.

Re: Parameterize Database Name in SparkSQL Notebook

Shanthan118 — Thu, 29 Aug 2024 06:27:36 GMT

Hi frithjof_v

yes by database it is lakehouse.
By SparkSql I mean %%sql, i am tbe SparkSql as the Global language for the notebook
as of now everything is in same workspace.

The below screenshot shows you how i am using getting the data from the lakehosue to another lakehouse.

So we have to make the Lakehouse name Parameterized in here.

We don't to use the PySpark, the requiement is with SparkSql i.e., %%sql.

So can you provide a solution for this.
I have tried using Dataframe it worked but not when using the SparkSQL as global language

Here, for this project we have to use only SparkSql(%%sql) as the Golbal language.

Re: Parameterize Database Name in SparkSQL Notebook

frithjof_v — Thu, 29 Aug 2024 14:24:48 GMT

I don't know how to pass variables into a %%sql cell.

I only know how to pass variables into spark.sql()

You could wrap your SQL code inside spark.sql()

I think you can do it something like this (I don't have Fabric opened, so I cannot verify the syntax):

spark.sql(```your

SQL

code

use a

{variable}

here

```)

ChatGPT suggested this code instead:

# Define a variable

variable = 'some_value'

# Use the variable inside spark.sql()

query = f"""

SELECT *

FROM some_table

WHERE some_column = '{variable}'

"""

result = spark.sql(query)

Re: Parameterize Database Name in SparkSQL Notebook

frithjof_v — Thu, 29 Aug 2024 14:30:11 GMT

I've never tried, but perhaps this shows a way to pass variables into %%sql?

https://learn.microsoft.com/en-us/answers/questions/419296/spark-sql-passing-variables-synapse-(spark-pool)

https://community.databricks.com/t5/data-engineering/how-to-set-a-variable-and-use-it-in-a-sql-query/td-p/22301

Disclaimer: I don't know anything about these methods. I just found them by googling briefly.

Re: Parameterize Database Name in SparkSQL Notebook

Shanthan118 — Thu, 29 Aug 2024 14:35:58 GMT

Thank You @frithjof_v

For the Response and suggestion.
when i have also checked on internet and chatgpt i too got the same solution.
So, we have created the notebook using spark.sql() it worked.