topic _sqldf value in case of query failure in %sql cell in Data Engineering

_sqldf value in case of query failure in %sql cell

pjain — Mon, 10 Jul 2023 06:22:17 GMT

I am trying to write a code for Error Handling in Databricks notebook in case of a SQL magic cell failure. I have a %sql cell followed by some python code in next cells. I want to abort the notebook if the query in %sql cell fails. To do so I am looking at the value of _sqldf variable. I am aware that the temporary variable holds the sql results when the query is successful however I couldn't find How the magic cell will behave in case of let say SQL Server connection failure. I want to stop the execution of complete notebook in case SQL magic cell fails. As the sql REPL cannot handle the python's code, I cannot throw the exception using try/except in the same %sql cell.
Am I thinking in the right direction or is there any other way to handle the failure?

Re: _sqldf value in case of query failure in %sql cell

daniel_sahal — Mon, 10 Jul 2023 06:33:21 GMT

@pjain
Why not triggering the sql code with spark.sql? IMO it would be much easier to create error handling by using python.

Re: _sqldf value in case of query failure in %sql cell

Anonymous — Tue, 11 Jul 2023 10:12:09 GMT

Hi @pjain

We haven't heard from you since the last response from @daniel_sahal , and I was checking back to see if her suggestions helped you.

Or else, If you have any solution, please share it with the community, as it can be helpful to others.

Also, Please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.

Re: _sqldf value in case of query failure in %sql cell

pjain — Tue, 11 Jul 2023 18:35:31 GMT

Hi @daniel_sahal , this was my initial thought too but many scripts are using the %sql cell and changing the code would take more effort than error handling. Also, there are multiple queries running in a single magic cell. This is the reason why I wanted to handle the failure in the magic cell itself.

I am planning to re-evaluate this with my team and we might take the spark.sql path. Will keep this thread updated on the actions.

Re: _sqldf value in case of query failure in %sql cell

pjain — Tue, 11 Jul 2023 18:37:07 GMT

Hi @Anonymous. Thank you for checking in. I am still looking for the solutions and will update the thread with my findings.