Synapse Notebook Exception Handling

Ravi 0 Reputation points
2023-03-15T16:43:09.82+00:00

Hello,

I would like to make sure that any DML/DDL command running in SQL cell (%%sql) doesn't fail, and if so, I need to capture the error and store in a database. For an example if the following command returns an error while creating table, it should fail the notebook in a clean way and create a log entry in my custom log table.

%%sql

CREATE TABLE TEST (COL1 VARCHAR5(0))

Thanks.

Azure Synapse Analytics
Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
4,451 questions
{count} votes

1 answer

Sort by: Most helpful
  1. ShaikMaheer-MSFT 38,126 Reputation points Microsoft Employee
    2023-03-17T10:58:57.9733333+00:00

    Hi Ravi,

    Thank you for posting query in Microsoft Q&A Platform.

    You can consider using spark.sql() to run your SQL and then use try and except blocks. In try block use spark.sql() function to run your SQL and if any exception then execution will go to except block. In except consider having code which writes your error details into some table as per your requirement.

    For some reason, after writing error details to table, if still you want to make sure cell to fail, then write code to intentionally fail in except block at the end.

    Below is sample code:

    try:
        # sql code to run
        spark.sql("")
    
    except Exception as e:
        #write code here to log error details to some log table.
    
        #below code to make cell intentionally to fail. That we can make sure cell to fail
        raise Exception("Intentional failure")
    

    Hope this helps. Please let me know if any further queries.


    Please consider hitting Accept Answer button. Accepted answers help community as well.