Forum Discussion

holgergubbels's avatar
holgergubbels
Frequent Visitor
7 months ago
Solved

PySpark Update Warehouse

Hi community, I really have problems implementing a medallion architecture in fabric - I always struggle at some point with (I thought) easiest things... It is really frustrating - and Copilot/chatg...
environment
  • holgergubbels's avatar
    holgergubbels
    7 months ago

    Hi chetanhiwale ,

    thanks for your answer! I decided to use another approach:

    - Load to landing: pipeline using copy activity, logs/watermarks via SP in Warehouse

    - Raw to Bronze: Piepline for logging, watermark, call notebook for the load.

    - same with bronze to silver

     

    It is not the best approach I think, because the spark session is always startet and temrinated for every job. But it is the best I could find... 

     

    Holger

deborshi_nag's avatar
deborshi_nag
Super User
7 months ago

Hello holgergubbels 

 

In Microsoft Fabric, Spark and SQL are two different engines. While they share the same storage (OneLake), Spark cannot directly execute a UPDATE statement or a Stored Procedure inside a Fabric Data Warehouse using standard Spark commands. 

 

The recommended approach -

 

1. To Write/Append Metadata

You can use SynapseSQL connector to write/append metadata. 

df.write.mode("overwrite").synapsesql("<warehouse>.<schema>.<table>")
 
2. To Update or call Stored Procedures
To perform a specific UPDATE or run a Stored Procedure for watermarking, you have two main choices
 
a) The Pipeline Activity
Use Fabric Pipeline as the orchestrator
i) Run your Spark Notebook.
ii) Add a Stored Procedure Activity or a Script Activity immediately after Notebook in the pipeline. 
iii) Pass parameters from the Notebook back to the Pipeline using mssparkutils.notebook.exit()
 
b) Python pyodbc
If you absolutely need to trigger a stored proc during the Spark job, you can use a Python library like pyodbc. Since Fabric Warehouses support Azure AD authentication, you can get a token via mssparkutils to avoid hardcoding credentials. 
 
Hope this helps - please appreciate by leaving a Kudos or accepting as a Solution!