Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Error Writing DataFrame to Warehouse via synapsesql – Worked Previously

For the past week, I've been encountering the following error when trying to read from the Lakehouse and write the DataFrame to the Warehouse using synapsesql.
Prior to that, everything was working perfectly.
df.write \
    .mode("overwrite") \
    .synapsesql(f"{silverWarehouse}.{tableSchema}.{silver_table}")
 
##Error
Caused by: com.microsoft.spark.fabric.tds.error.FabricSparkTDSSQLExecQryError: Error executing query-COPY INTO [{Warehouse_name}].[{schema_name}].[{table_name}]
FROM 'abfss://{workspace_id_hyphen}@{workspace_id}.zd9.dfs.fabric.microsoft.com/_system/artifacts/{notbook_id}/5f15f8eb-09f7-4148-aea3-e293ec52726c/user/trusted-service-user/{table_name}2654d7047d3b407281f0513a1950d92e/*.parquet'
WITH(
   FILE_TYPE = 'PARQUET',
   CREDENTIAL = (
       IDENTITY='Shared Access Signature',
       SECRET='?[REDACTED]')). A retry attempt for error code - 13807 isn't expected to change outcome.
at com.microsoft.spark.fabric.tds.utility.FabricTDSSQLUtility$.executeSQLQuery(FabricTDSSQLUtility.scala:397)
at com.microsoft.spark.fabric.tds.utility.FabricTDSSQLUtility$.execQuery(FabricTDSSQLUtility.scala:249)
at com.microsoft.spark.fabric.tds.write.processor.FabricSparkTDSSQLCommitHandler$.commitDataToWarehouse(FabricSparkTDSSQLCommitHandler.scala:116)
... 25 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Content of directory on path 
'https://{workspace_id}.zd9.dfs.fabric.microsoft.com/{workspace_id_hyphen}/_system/artifacts/{notbook_id}/5f15f8eb-09f7-4148-aea3-e293ec52726c/user/trusted-service-user/{table_name}2654d7047d3b407281f0513a1950d92e/*.parquet' cannot be listed.
data warehouse
warehouse

20 Replies

  • justinjbird's avatar
    justinjbird
    Icon for Resolver I rankResolver I
    1 year ago

    Just ruling simple things...has your SAS key expired by any chance?

  • spaceman127's avatar
    spaceman127
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous,

    If you have not changed anything so far, then I would also guess that the SAS key needs to be renewed.

    Best regards 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Thanks spaceman127 for your hint, but we are in OneLake in Fabric environnement, SAS Key is transparent layer. 

      • spaceman127's avatar
        spaceman127
        Icon for Super User rankSuper User
        1 year ago

        All right,
        That was not clear from the original question.
        It seemed as if you were asking for a storage account.
        That was a misunderstanding.

         

        Do you have any more information for us?

         

        Best regards 

         

  • BhaveshPatel's avatar
    BhaveshPatel
    Icon for Super User rankSuper User
    1 year ago

    Hi 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? 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      You can eaisly reprodure the error, try with notebook to read data from MS Fabric Lakehouse and save it to MS Fabric Warehouse

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        BhaveshPatel
        Icon for Super User rankSuper User
        1 year ago

        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 

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thanks justinjbird, but where can we change the SAS key for OneLake ?
    Think is that code mentionned work fine for long time and we don't have any issue. The purpose is to save Dataframe transformed from Lakehouse to Warehouse.
    MS Fabric offer "synapsesql" methode to do the staff, it's work fine until five day ago

  • justinjbird's avatar
    justinjbird
    Icon for Resolver I rankResolver I
    1 year ago
    You have shared the write command in your message, but looking at your error message...
     
    ##Error
    Caused by: com.microsoft.spark.fabric.tds.error.FabricSparkTDSSQLExecQryError: Error executing query-COPY INTO [{Warehouse_name}].[{schema_name}].[{table_name}]
    FROM 'abfss://{workspace_id_hyphen}@{workspace_id}.zd9.dfs.fabric.microsoft.com/_system/artifacts/{notbook_id}/5f15f8eb-09f7-4148-aea3-e293ec52726c/user/trusted-service-user/{table_name}2654d7047d3b407281f0513a1950d92e/*.parquet'
    WITH(
       FILE_TYPE = 'PARQUET',
       CREDENTIAL = (
           IDENTITY='Shared Access Signature',
           SECRET='?[REDACTED]')). A retry attempt for error code - 13807 isn't expected to change outcome.
    at com.microsoft.spark.fabric.tds.utility.FabricTDSSQLUtility$.executeSQLQuery(FabricTDSSQLUtility.scala:397)
    at com.microsoft.spark.fabric.tds.utility.FabricTDSSQLUtility$.execQuery(FabricTDSSQLUtility.scala:249)
    at com.microsoft.spark.fabric.tds.write.processor.FabricSparkTDSSQLCommitHandler$.commitDataToWarehouse(FabricSparkTDSSQLCommitHandler.scala:116)
    ... 25 more
    Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Content of directory on path 

    'https://{workspace_id}.zd9.dfs.fabric.microsoft.com/{workspace_id_hyphen}/_system/artifacts/{notbook_id}/5f15f8eb-09f7-4148-aea3-e293ec52726c/user/trusted-service-user/{table_name}2654d7047d3b407281f0513a1950d92e/*.parquet' cannot be listed.

     

    It's the read that is failing not the write, would you agree?

     

    Have you redacted these paths? The error says '{workspace_id}' is that you redacting the output or has something gone wrong with you compiling your path?

     

    It seems that the issue is somewhere else, not in the write snippet you have supplied.

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Thank you justinjbird, can you tell me how to rule SAS key for OneLake in Fabric ?

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi Anonymous ,

    I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out. We would be  happy to help.
    Thank you all the community members who participated in the discussion to help resolve the query.

    Regards,
    Shruti

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi Anonymous ,

    I hope the information provided above assists you in resolving the issue. If you have any additional questions, please feel free to reach out. We would be  happy to help.

    Thank You

  • v-sdhruv's avatar
    v-sdhruv
    Icon for Community Support rankCommunity Support
    11 months ago

    Hi Anonymous ,
    Since we didnt hear back, we would be closing this thread.
    If you need any assistance, feel free to reach out by creating a new post.

    Thank you for using Microsoft Community Forum

  • Anonymous's avatar
    Anonymous
    Not applicable
    11 months ago

    This seems to occur transciently, the only way to resolve the issue is to stop the Fabric Spark session and restart it. This seems to refresh the connection and get it working again.