CODEWITHSUNDEEP
databricksazure-databricksdatabricks-sql
Vincy Wales
Vincy Wales

Reputation: 21

Exception handling on Azure Databricks SQL

I am migrating a few Oracle functions to Databricks SQL. Migrated functions are working fine but I am not sure how to code the EXCEPTION block.

I have the following on Oracle:

EXCEPTION WHEN OTHERS THEN RETURN -1;

How to code this logic on Databricks SQL?

Upvotes: 0

Views: 125

Answers (1)

Dileep Raj Narayan Thumula
Dileep Raj Narayan Thumula

Reputation: 3250

There are two ways to handle errors in SQL. One way is to use CASE expressions to handle errors within the SQL code itself. Another way is to use PySpark transformations and handle exceptions programmatically in the code.

I have tried the below approach I have registred a SQL temporary view:

In SQL, you can use CASE statements to handle invalid values and return a specific value like -1.

SELECT Name, 
       CASE 
           WHEN cast(Age as int) IS NULL THEN -1 
           WHEN Age RLIKE '^[0-9]+$' THEN cast(Age as int)
           ELSE -1
       END as Age
FROM people_mixed;

Results:

Name    Age
Jay 30
shankar -1
Rakesh  -1
Gopal   25

In PySpark, you can use conditional transformations (when-otherwise) to replace invalid data.

from pyspark.sql.functions import col, when
df_cleaned = df.withColumn(
    "Age_cleaned", 
    when(col("Age").rlike("^[0-9]+$"), col("Age").cast("int")).otherwise(-1)
)
df_cleaned.show()

Results:

+-------+----+-----------+
|   Name| Age|Age_cleaned|
+-------+----+-----------+
|    Jay|  30|         30|
|shankar|NULL|         -1|
| Rakesh| ABC|         -1|
|  Gopal|  25|         25|
+-------+----+-----------+

Upvotes: 0

Related Questions