Error handling in Azure Databricks

Applies to: Databricks SQL Databricks Runtime 12.2 and above

When Azure 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 Azure 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
  

Applies to: Databricks SQL Databricks Runtime 14.2 and above

Azure Databricks provides language specific APIs to handle error conditions.

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.

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.

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

  Scala

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

  Python

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

  Result

    Error Condition       : 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:

  Scala

  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"))
  }
  

  Python

  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 condition TABLE_OR_VIEW_NOT_FOUND only and display a custom message:

  Scala

  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"))
  }
  

  Python

  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`
  

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

> 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

• assert_true function
• raise_error function