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 Class

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

    Some error classes include sublasses.

    For example: TABLE_OR_VIEW_NOT_FOUND, and INCOMPLETE_TYPE_DEFINITION.ARRAY.

    For a list of all error classes see Error Classes.

  • SQLSTATE

    A five character long string, grouping error classes 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 class 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 Class, 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.

Python

For Python use pySparkException

  • PySparkException.getErrorClass(): Returns the error class 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 class 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 class, message parameters and SQLSTATE. Also display the default error message

    import org.apache.spark.SparkThrowable

try {
  spark.sql("SELECT * FROM does_not_exist").show()
}
catch {
  case ex: SparkThrowable =>
    println("Error Class       : " + ex.getErrorClass)
    println("Message parameters: " + ex.getMessageParameters())
    println("SQLSTATE          : " + ex.getSqlState)
    println(ex)
}

    from pyspark.errors import PySparkException

try:
  spark.sql("SELECT * FROM does_not_exist").show()
except PySparkException as ex:
  print("Error Class       : " + ex.getErrorClass())
  print("Message parameters: " + str(ex.getMessageParameters()))
  print("SQLSTATE          : " + ex.getSqlState())
  print(ex)

    Result

      Error Class       : TABLE_OR_VIEW_NOT_FOUND
  Message parameters: {'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:

    import org.apache.spark.SparkThrowable

try {
  spark.sql("SELECT * FROM does_not_exist").show()
}
catch {
  case ex: SparkThrowable if (ex.getSqlState == "42P01") =>
    println("I'm so sorry, but I cannot find: " + ex.getMessageParameters().get("relationName"))
}

    from pyspark.errors import PySparkException

try:
  spark.sql("SELECT * FROM does_not_exist").show()
except PySparkException as ex:
  if (ex.getSqlState() == "42P01"):
    print("I'm so sorry, but I cannot find: " + ex.getMessageParameters()['relationName'])
  else:
    raise

    Result

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

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

    import org.apache.spark.SparkThrowable

try {
  spark.sql("SELECT * FROM does_not_exist").show()
}
catch {
  case ex: SparkThrowable if (ex.getErrorClass == "TABLE_OR_VIEW_NOT_FOUND") =>
    println("I'm so sorry, but I cannot find: " + ex.getMessageParameters().get("relationName"))
}

    from pyspark.errors import PySparkException

try:
  spark.sql("SELECT * FROM does_not_exist").show()
except PySparkException as ex:
  if (ex.getErrorClass() == "TABLE_OR_VIEW_NOT_FOUND"):
    print("I'm so sorry, but I cannot find: " + ex.getMessageParameters()['relationName'])
  else:
    raise

    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 class ‘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