%%tsql magic in spark notebook

[woldea]

I am using fabric notebook to dynamically ingest tables from lakehouse to warehouse. I need to use pySpark as notebook default language for all the cells but I want to execute SQL code on warehouse from the same notebook . I tried to bind a variable from spark cell to SQL cell using like this :

%%tsql -artifact warehouse_name -type Warehouse -bind upsert_query

EXEC(@upsert_query);

the magic %%tsql is not available to select with Pyspark language default. UsageError: Cell magic '%%tsql' not found. If I change the default language to python , I get t-sql functionality but it failed to import some modules like 

import com.microsoft.spark.fabric for other spark cells that are forced to run in pyspark using the magic 

%%pyspark . Does anyone has a workaround to handle both spark and SQL warehouse operation in the same notebook ?

Thanks

[v-achippa]

Hi @woldea,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @chetanhiwale, @deborshi_nag and @tayloramy for the prompt response. 

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @woldea,

We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[tayloramy]

Hi @woldea, 

What you can do is use synapseSQL to interact with the warehouse directly from a Spark notebook. 

THough tI stronmgly recommend doing your transformations that require spark all in a lakehouse, and then copying the final table over using a pipeline or copy job. 

[deborshi_nag]

Hello @woldea 

You're not meant to mix Spark and TSQL. If you're applying transformations in a Lakehouse, you can use a Spark notebook with %%pyspark or %%sql magic commands. 

On the other hand, if you're applying transformation into a Warehouse, you can use a Python notebook with %%tsql magic command. 

[chetanhiwale · Accepted solution]

HI @woldea , 
You can use Spark Synapse SQL connector for your usecase. 
adding same code for your reference. 

# IMPORTS

import com.microsoft.spark.fabric
from com.microsoft.spark.fabric.Constants import Constants

# Code
spark.read.option(Constants.DatabaseName, "<warehouse/lakeshouse name>").synapsesql("<T-SQL Query>")

You can these docs for refernce. 
https://learn.microsoft.com/en-us/fabric/data-engineering/spark-data-warehouse-connector?tabs=pyspar...

Hope this helps. Let me know if you have any other questions.

[tayloramy]

Hi @woldea, 

You cannot mix TSQL and PySpark notebooks I believe. 

You can however mix PySpark and SparkSQL. I recommend using a SparkSQL cell to do your SQL with. 

Spark connector for Microsoft Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

Though generally, I find it best to do all your transformations in a lakehouse, which is easier to work with, and then move your data to a warehouse after it is all nice and pretty. 

[woldea]

Thank you tayloramy!

I was able to convert my transformation into SQL and accomplished my goal in the warehouse cell. My question here is I want to run the python transformation in the same notebook but with a tsql command as the transformation impacts the warehouse.  From what I read, this can be achieved using %%tsql .Spark SQL still uses spark engine and cannot execute transformation in warehouse. So, what is the use of %%tsql and how to implement it in the notebook ? What changes are needed in the notebook setting? 

Thanks