Community Conundrum 25:Feature Visualization is now live! Read More

Retrieve error message from scenario execute sql statement

Level 2
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?

2 Replies
Dataiker
Dataiker

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!

Level 2
Author

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.