New to Dataiku DSS? Try out our NEW Quick Start Programs today and get onboarded on the product in just one hour! Let's go

Retrieve error message from scenario execute sql statement

Solved!
sylvyr3
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?

1 Solution
KimmyC
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!

View solution in original post

2 Replies
KimmyC
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!

View solution in original post

sylvyr3
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.

A banner prompting to get Dataiku DSS