By using AWS re:Post, you agree to the Terms of Use
/fail a glue job if the called stored procedure fails/

fail a glue job if the called stored procedure fails

0

Hi all,

I'm using a glue job that reads CSV files from S3 and injects data to MySQL RDS.

at the end of my pyspark glue script, I call a stored procedure. the issue is the glue job status end by succeeding but the stored procedure execution fails.

is there any way to fail the glue job if the execution of the called stored procedure fails?

when glue script calls myStoredProcedure() it's an async call?

appreciate any help

import pymysql

with conn.cursor() as cur:
   insertQry="CALL myStoredProcedure()"
   cur.execute(insertQry)
   conn.commit()
   cur.close()
   conn.close()
   
job.commit()

************* OR **********************
from py4j.java_gateway import java_import
java_import(sc._gateway.jvm,"java.sql.Connection")
java_import(sc._gateway.jvm,"java.sql.DatabaseMetaData")
java_import(sc._gateway.jvm,"java.sql.DriverManager")
java_import(sc._gateway.jvm,"java.sql.SQLException")

source_jdbc_conf = glueContext.extract_jdbc_conf(glue_connection_name)
conn = sc._gateway.jvm.DriverManager.getConnection(source_jdbc_conf.get('url') + '/' + database_name, source_jdbc_conf.get('user'), source_jdbc_conf.get('password'))

 stmt = conn.createStatement();
 rs = stmt.executeQuery('call ' + stored_proc);
 conn.close()
1 Answers
1

Hello,

Stored procedure is a part of Python procedure and the stored procedure is not executed on glue resources. So, I do not believe it waits for confirmation whether it is successful or not

But, you can try to enclose the code for stored procedure within try/except block to capture the exception if the job fails. Glue job will capture the exception. Otherwise, there is no way Glue will know what is going on since stored procedure is a part of Python procedure. You can also try to set up sys.exit(1) to make sure it will throw something as well. Please refer bellow link for more details

REFERENCES:

https://stackoverflow.com/questions/6720119/setting-exit-code-in-python-when-an-exception-is-raised

SUPPORT ENGINEER
answered 5 months ago
EXPERT
reviewed 3 months ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions