Forum Discussion
Error Writing DataFrame to Warehouse via synapsesql – Worked Previously
Hi Anonymous,
here is the Doc. for OneLake SAS Key.
https://learn.microsoft.com/en-us/fabric/onelake/onelake-shared-access-signature-overview
https://learn.microsoft.com/en-us/fabric/onelake/how-to-create-a-onelake-shared-access-signature
I think that helps you.
Best regards
Super UserHi Anonymous
This is not assisting us in the first place. What does it mean by silver warehouse? At first, was there a bronze table working before? Can you share some screenshots?
Thanks "BahveshPatel" for your reponse. Sorry, some data and screenshot are sensitives so i can't share that.
But i can explain again the issue :
We work with medallion architecture, we use spark notebook to read the data from "bronze lakehouse", and then transforme it and finally we wrote the transformed dataframe in "Fabric Warehouse". This warehouse we name it "Silver warehouse".
In the notebook, we use "synapsesql" methode to write the dataframe to the warehouse (the source code shared with you). This methode run without any issue until 5 days ago. indicate that we can not list parquet file, and seems to SAS Key to change, but nothing changed in our environnement Fabric.
Is that clear enough for you ? is that help you to figure out the issue ?
Béchir
- BhaveshPatel
Hi Anonymous
Due to change into synapsesql, means that synapse sql should not be used going forward ( depreciate ) we need to use Databricks Delta Lake Open Source table in broze+ silver warehouse. synapse sql is using data lake whereas databricks delta lake is using _delta_log( Transaction log ) with parquet files. You should not use spark notebook for everything.
Python --> Apache Spark ( Data Lake ) --> Databricks Delta Lake ( Pyspark initially and subsequent use Spark SQL )
Also, Lakehouse = Fabric Warehouse = Power BI / SSAS Tabular Semantic Model
Thank you, Bhavesh Patel, for your response.
However, your answer seems to be outside the scope of our current setup. We are working entirely within a Microsoft Fabric environment.
The objective is to write data from a Microsoft Fabric Lakehouse to a Microsoft Fabric Warehouse using a Fabric Notebook. Databricks is not our scope.
The synapsesql function was working correctly, and according to Microsoft, it has not been deprecated.
- BhaveshPatel
Initially, Databricks came up with Delta Lake Open Source and subsequently Microsoft came up with same Delta Engine and called it Microsoft Fabric.
Lakehouse = Fabric Warehouse = Power BI Semantic Model.
All three are same and doing the same purpose of Data Engineering, Data Analytics and Data Science.
synapsesql function is using only parquet files whereas Delta Lake is using parquet file + _delta_log.
Anonymous I encountered the same issue where the behavior was inconsistent. I found a reliable workaround:
Instead of: df.write.mode("overwrite").synapsesql(f"{silverWarehouse}.{tableSchema}.{silver_table}")
I switched to writing the data to a lakehouse first: df.write.format("delta").mode("overwrite")..save("abfss://[email protected]/lh.Lakehouse/Tables/schema/df")
Then, I inserted a Copy Data activity in the pipeline: Source would be lakehouse table, and Destination would be the target table in the warehouse which we original wanted to save as. To fully replace the table (instead of appending), add "TRUNCATE TABLE schema.df" as a pre-copy script under the destination tab.
Hi, BhaveshPatel --
You sound certain that Synapse / ASA will be deprecated (what you meant by "depreciate", I trust).
1) What makes that certain, to you? (Is there an announcement we haven't seen?)2) If ASA is going away ... you're promoting Azure Databricks, instead of Fabric? We already have Databricks -- that would be good news -- but some think Fabric is next.
