Retrieve error message from scenario execute sql statement

Options
sylvyr3
sylvyr3 Registered Posts: 21 ✭✭✭✭
edited July 16 in Using Dataiku

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

  • KimmyC
    KimmyC Dataiker Posts: 34 Dataiker
    edited July 17 Answer ✓
    Options

    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

  • sylvyr3
    sylvyr3 Registered Posts: 21 ✭✭✭✭
    Options

    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.

Setup Info
    Tags
      Help me…