Skip to main content

Error handling in Databricks

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 12.2 and above

Error components​

When Databricks raises an error it includes the following components:

  • Error Condition

    A descriptive, human-readable, string unique to the error condition.

    Some error conditions include subconditions.

    For example: TABLE_OR_VIEW_NOT_FOUND, and INCOMPLETE_TYPE_DEFINITION.ARRAY.

    For a list of all error conditions see Error Conditions.

  • SQLSTATE

    A five character long string, grouping error conditions into a standard format supported by many products and APIs.

    For example: '42P01'

    For a full list of all SQLSTATEs used by Databricks see SQLSTATEs.

  • Parameterized Message

    The error message with placeholders for the parameters.

    For example : TABLE_OR_VIEW_NOT_FOUND includes the following message:

    The table or view <relationName> cannot be found.

    You can use the parameterized message to render an error message by mapping message parameter values to the parameter tags <parameter>.

  • Message Parameters

    A map of parameters and values that provide additional information about the error. For example: 'relationName' -> 'main.default.tab1'.

  • Message

    The completely rendered error message, including the error condition and the SQLSTATE, with the parameters filled in. For example:

    [TABLE_OR_VIEW_NOT_FOUND] The table or view `does_not_exist` cannot be found. Verify the spelling and correctness of the schema and catalog.
    If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog.
    To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS. SQLSTATE: 42P01; line 1 pos 14;
    'Project [*]
    +- 'UnresolvedRelation [does_not_exist], [], false
warning

Message and Parameterized Message are not stable across releases. The message text may be changed or localized without notice. To programmatically handle an error condition, use the Error Condition, SQLSTATE, and Message Parameters instead.

Handling error conditions​

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 14.2 and above

Preview

This feature is in Public Preview.

Databricks provides language specific APIs to handle error conditions.

SQL​

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime 16.3 and above

For SQL use the compound statement syntax to define condition handlers.

  • DECLARE EXIT HANDLER: Defines a handler for a set of specified error conditions, SQLSTATEs, or any SQLEXCEPTION.
  • GET DIAGNOSTICS: Retrieves the error condition, SQLSTATE, and message parameters of the error condition intercepted by the handler.

Python​

For Python use pySparkException

  • PySparkException.getErrorClass(): Returns the error condition of the exception as a string.
  • PySparkException.getMessageParameters(): Returns the message parameters of the exception as a dictionary.
  • PySparkException.getSqlState(): Returns the SQLSTATE of the expression as a string.

Scala​

For Scala use SparkThrowable

  • getErrorClass(): Returns an error condition as a string.
  • getMessageParameters(): Returns a message parameters as a map.
  • getSqlState(): Returns an SQLSTATE as a string.

Examples​

  • Catch any exception and display error condition, message parameters and SQLSTATE. Also display the default error message

    SQL
    BEGIN
      DECLARE EXIT HANDLER FOR SQLEXCEPTION
        BEGIN
          DECLARE error_condition STRING;
          DECLARE sqlstate STRING;
          DECLARE msg_args MAP<STRING, STRING>;
          DECLARE msg STRING;

          GET DIAGNOSTICS CONDITION 1
            error_condition = CONDITION_IDENTIFIER,
            sqlstate        = RETURNED_SQLSTATE,
            msg_args        = MESSAGE_ARGUMENTS,
            msg             = MESSAGE_TEXT;

          VALUES('Error Condition   : ' || error_condition         ),
                ('Message arguments : ' || cast(msg_args AS STRING)),
                ('SQLSTATE          : ' || sqlstate                ),
                (msg                                               );
        END;
      SELECT * FROM does_not_exist;
    END;

    Result

      Error Condition   : TABLE_OR_VIEW_NOT_FOUND
      Message arguments : {'relationName': '`does_not_exist`'}
      SQLSTATE          : 42P01
      [TABLE_OR_VIEW_NOT_FOUND] The table or view `does_not_exist` cannot be found. Verify the spelling and correctness of the schema and catalog.
      If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog.
      To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS. SQLSTATE: 42P01; line 1 pos 14;
      'Project [*]
      +- 'UnresolvedRelation [does_not_exist], [], false

  • Catch the SQLSTATE 42P01 only and display a custom message:

    SQL
    BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE '42P01'
        BEGIN
          DECLARE msg_args MAP<STRING, STRING>;
          GET DIAGNOSTICS CONDITION 1 msg_args = MESSAGE_ARGUMENTS;
          VALUES('I\'m so sorry, but I cannot find: ' || msg_args['relationName']);
        END;
      SELECT * FROM does_not_exist;
    END;

    Result

    I'm so sorry, but I cannot find: `does_not_exist`

  • Catch the error condition TABLE_OR_VIEW_NOT_FOUND only and display a custom message:

    SQL
    BEGIN
      DECLARE EXIT HANDLER FOR TABLE_OR_VIEW_NOT_FOUND
        BEGIN
          DECLARE msg_args MAP<STRING, STRING>;
          GET DIAGNOSTICS CONDITION 1 msg_args = MESSAGE_ARGUMENTS;
          VALUES('I\'m so sorry, but I cannot find: ' || msg_args['relationName']);
        END;
      SELECT * FROM does_not_exist;
    END;

    Result

    I'm so sorry, but I cannot find: `does_not_exist`

User raised exceptions​

Databricks provides the following functions to raise user defined errors:

  • raise_error

    Raises an exception with a custom error message.

  • assert_true

    Raises an error with an optional error message, if a condition is not met.

Both functions return the error condition ‘USER_RAISED_EXCEPTION’ and the SQLSTATE 'P0001' along with a user defined message.

Examples​

> SELECT raise_error('This is a custom error message');
 [USER_RAISED_EXCEPTION] This is a custom error message. SQLSTATE: P0001

> SELECT assert_true(1 = 2, 'One is not two!');
 [USER_RAISED_EXCEPTION] One is not two! SQLSTATE: P0001

> SELECT assert_true(1 = 2);
 [USER_RAISED_EXCEPTION] '(1 = 2)' is not true! SQLSTATE: P0001
Last updated on