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?