Synapse Notebook Exception Handling

Question

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.

Answer 1

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.