Forum Discussion

woldea's avatar
woldea
Regular Visitor
6 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 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
data engineering

7 Replies

  • chetanhiwale's avatar
    chetanhiwale
    Resolver I
    6 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.

  • deborshi_nag's avatar
    deborshi_nag
    Super User
    6 months ago

    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. 

     

    • woldea's avatar
      woldea
      Regular Visitor
      6 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
  • tayloramy's avatar
    tayloramy
    Super User
    6 months ago

    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. 

     

     

  • v-achippa's avatar
    v-achippa
    Community Support
    6 months ago

    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's avatar
      v-achippa
      Community Support
      6 months ago

      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