Retrieve error message from scenario execute sql statement
Is it possible to retrieve the error message returned from a database when performing an execute_sql statement in a scenario python script?
I'm running the below in a scenario using custom python script.
scenario.execute_sql("Conn_Name", "select top 1 col_key from db_name.tbl_name")
It fails because I purposefully used a non existing database. The log shows the following information:
[2020/04/22-10:32:41.850] [FT-RunStepFutureThread-nFNhtqKi-241093] [INFO] [dip.scenario.step.sql] - [ct: 236] Exited the exec sql step {
"endedOn": 0,
"success": false,
"updatedRows": 0,
"errorMessage": "[Teradata Database] [TeraJDBC x.x.x.x] [Error 3807] [SQLState 42S02] Object \u0027db_name.tbl_name\u0027 does not exist.",
"totalRowsClipped": false,
"totalRows": 0,
"log": "",
"columns": [],
"rows": [],
"hasResultset": false
}
Is there a way I can pull out the errorMessage that was returned from the database when trying the scenario.execute_sql?
Best Answer
-
Hi,
You could use the get_details() method in the Dataiku Python API which will retrieve the full details on the scenario run.
I've attached a Python code snippet that would retrieve the results of the details of the last run of a scenario:
import dataiku from dataikuapi.dss.scenario import DSSScenario client = dataiku.api_client() dss_scenario = DSSScenario(client, dataiku.default_project_key(), "your_scenario_id") dss_scenario.get_last_runs()[0].get_details()
This returns the JSON of the output of the last run of the scenario. You would need to play around with the JSON output to narrow down that specific message. I was able to retrieve the error message by narrowing down with the following code:
dss_scenario.get_last_runs()[0].get_details()['stepRuns'][1]['result']['thrown']
Hope this is helpful!
Answers
-
Thanks, this helped quite a bit.
There might be a better approach but what I wanted to accomplish with the scenario was have some simple queries to check if any of the tables and databases in my flow were currently locked and if so wait N minutes, check again, once all queries showed tables were not locked then proceed with running the flow.