How to handle an AnalysisException on Spark SQL?

Question

I am trying to execute a list of queries in Spark, but if the query does not run correctly, Spark throws me the following error: AnalysisException: "ALTER TABLE CHANGE COLUMN is not supported for changing ...

This is part of my code (i'm using python and Spark SQL on Databricks):

for index, row in df_tables.iterrows():
  query = row["query"]
  print ("Executing query: ")
  try:
      spark.sql(query)
      print ("Query executed")
  except (ValueError, RuntimeError, TypeError, NameError):
      print("Unable to process your query dude!!")
  else:
      #do another thing

Is there any way to catch that exception? ValueError, RuntimeError, TypeError, NameError seems not working. There's no so much information about that in the Spark webpage.

score 45 · Answer 1 · answered Oct 20 '20 at 06:51

45

I found AnalysisException defined in pyspark.sql.utils. https://spark.apache.org/docs/3.0.1/api/python/_modules/pyspark/sql/utils.html

import pyspark.sql.utils
try:
    spark.sql(query)
    print ("Query executed")
except pyspark.sql.utils.AnalysisException:
    print("Unable to process your query dude!!")

answered Oct 20 '20 at 06:51

Brett Koblinger

451
4
4

3

This should be the accepted answer because it catches the built-in exception. – Chris Koester Feb 05 '22 at 18:51
So if I stay in 2.4.0, I just use except Exception as e: at most ?? or except: at least ?? – Noppu Feb 18 '22 at 08:49

Manish · Accepted Answer · 2019-10-04T23:56:25.250

6

You can modify the try except statement as below :

try:
  spark.sql(query)
  print ("Query executed")
except Exception as x:
  print("Unable to process your query dude!!" + \
        "\n" + "ERROR : " + str(x))

edited Oct 04 '19 at 23:56

answered Oct 04 '19 at 18:50

Manish

1,144
8
12

1

Same problem, didn't work for me. Exception keep being ignored by the capture. DBR: 6.3, SPark 2.4.4 – ciurlaro Mar 16 '20 at 12:50
2

Never catch all exceptions, it's bad practice – Psychotechnopath Jan 11 '23 at 16:23

score 3 · Answer 3 · answered Jan 26 '23 at 13:56

I want to propose a way to pick specific Exceptions. I had the problem to find if some table already existed. The simplest way that i have found to do this is like this. Of course that this can break if Spark maintainers change the message of the exception, but i think that they do not have reason to do this, in this case.

    import pyspark.sql.utils

    try:
        spark.read.parquet(SOMEPATH)
    except pyspark.sql.utils.AnalysisException as e:
        if "Path does not exist:" in str(e):
            # Finding specific message of Exception.
            pass # run some code to address this specific case.
        else:
            # if this is not the AnalysisException that i was waiting,
            # i throw again the exception
            raise (e)
    except Exception as e:
        # if is another exception i can catch like this
        print(e)
        raise (e)

Piyush Patel · Answer 4 · 2019-10-04T20:20:08.070

0

I think it depends on your requirements. If you're running full workflow on this query and if you want them to pass through then your code will work fine. But let's say you want your workflow or datapipeline to fail, then you should exit from that except block.

The exact exception you may not get, but you can definitely get overview using

except Exception as x:
  print(str(x))

You can use logging module for putting more information in logs for further investigation.

edited Oct 04 '19 at 20:20

answered Oct 04 '19 at 20:15

Piyush Patel

1,646
1
14
26

Yes!! Thank you mate. – Kevin Gomez Oct 05 '19 at 23:09

How to handle an AnalysisException on Spark SQL?

4 Answers4

Linked