Forum Discussion

woldea's avatar
woldea
Regular Visitor
8 months ago
Solved

%%tsql magic in spark notebook

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 f...
data engineering
  • Anonymous's avatar
    Anonymous
    8 months ago

    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=pyspark


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

  • woldea's avatar
    woldea
    Regular Visitor
    8 months ago
    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