Forum Discussion

AnnOminous's avatar
AnnOminous
Microsoft Employee
1 year ago
Solved

Can't write data to Fabric DB - MashupException.Error

I'm trying to write data from a Dataflow (not Gen2) to a SQL DB in Fabric. The query is set to refresh incrementally, but when I try to publish/refresh the query, I get the following error message: ...
data engineering
  • BhaveshPatel's avatar
    BhaveshPatel
    1 year ago

    Hi AnnOminous 

     

    You need to use Notebooks only ( Advanced User ). Also You need to understand Data Lake and Delta Lake with Apache Spark. To answer the question,This is writing data back to Fabric SQL ( INSERT ). This is a script.

    # Create DataFrame to insert into the Azure/Fabric SQL  table ( Writing Data to Azure SQL / Fabric SQL Table )
     
    df1 = spark.createDataFrame([('WL2456', 'Luke1293', 'Skywalker','Null','Null',-3,'Unknown','Unknown','Null')], ['DeviceID', 'DeviceName', 'DeviceDesc','OperatorName','OperatorDesc','FacilityID','FacilityName','FacilityDesc','DeviceSerialNo'])

    # Insert the rows into the Azure SQL / Fabric SQL table
    df1.write \
        .option('user', user) \
       .option('password', pswd) \
       .jdbc('jdbc:sqlserver://' + sqlserver + ':' + port + ';database=' + database, 'dbo.Devices', mode = 'append' )
       
       
    df1.show()



    Cheers
    Bhavesh Patel



     

BhaveshPatel's avatar
BhaveshPatel
Super User
1 year ago

You can not perform Dataflow to Fabric SQL. For that you need to have Dataflow Gen 2. Also, You need to have Apache Spark with Delta Lake ( Lakehouse ). 

 

Dataflow Gen 2 = Fabric SQL = Fabric Datawarehouse = Semantic Model = Unified Data Structure

AnnOminous's avatar
AnnOminous
Microsoft Employee
1 year ago

BhaveshPatel, I tried setting it up using just DFG2 pointing to my Fabric SQL instance, but I'm still getting the same error message.

Can you provide a little more detail why I also need Spark with Delta Lake?

  • BhaveshPatel's avatar
    BhaveshPatel
    Super User
    1 year ago

    Hi AnnOminous 

     

    You need to use Notebooks only ( Advanced User ). Also You need to understand Data Lake and Delta Lake with Apache Spark. To answer the question,This is writing data back to Fabric SQL ( INSERT ). This is a script.

    # Create DataFrame to insert into the Azure/Fabric SQL  table ( Writing Data to Azure SQL / Fabric SQL Table )
     
    df1 = spark.createDataFrame([('WL2456', 'Luke1293', 'Skywalker','Null','Null',-3,'Unknown','Unknown','Null')], ['DeviceID', 'DeviceName', 'DeviceDesc','OperatorName','OperatorDesc','FacilityID','FacilityName','FacilityDesc','DeviceSerialNo'])

    # Insert the rows into the Azure SQL / Fabric SQL table
    df1.write \
        .option('user', user) \
       .option('password', pswd) \
       .jdbc('jdbc:sqlserver://' + sqlserver + ':' + port + ';database=' + database, 'dbo.Devices', mode = 'append' )
       
       
    df1.show()



    Cheers
    Bhavesh Patel



     

    • AnnOminous's avatar
      AnnOminous
      Microsoft Employee
      1 year ago

      BhaveshPatel, Sorry, why can't I just write an INSERT command in T-SQL to write the data from the staging table to the "permanent" table once the incremental refresh has updated the staging table with the latest data?

      • AnnOminous's avatar
        AnnOminous
        Microsoft Employee
        1 year ago

        BhaveshPatel, got it, thank you again. If this is for advanced users then, pretty obviously, I'm just starting to work with it, so I don't see it as a feasible solution for me at this time.

         

        Frustrating that MS is building a data management framework that doesn't support T-SQL for what seems to me to be a straightforward process, but I appreciate the information you provided, helps me understand it a bit better.

        One last request, if I may - understanding the amount of information required to understand this well can't be captured in this forum, are there any links you recommend to help me understand datalakes and Apache Spark better? I've done some searching but am still trying to find some good sites for learning these things.