call a stored procedure from within glue job

Question

Hi team,

I want to call my stored procedure at the end of my glue job (inside the glue job itself),

can I connect to MySQL RDS DB and call this stored procedure from my pyspark glue job without using py4j?

I don't find examples on the net (this one speak about using py4j: https://stackoverflow.com/questions/64775753/how-to-run-arbitrary-ddl-sql-statements-or-stored-procedures-using-aws-glue)

any code example would be helpful.
Thanks a lot

Jess · Accepted Answer

I used pymysql and worked for me.

this is an example of the code I used :

```
import sys
import pymysql

........

rds_host  = "database-id.rds.amazonaws.com"
name = "userName"
password = "secretPassword"
db_name = "myDb"
conn = pymysql.connect(host=rds_host, user=name, passwd=password, db=db_name, connect_timeout=5)

with conn.cursor() as cur:
   query="CALL mystoredProcedure()"
   cur.execute(query)
   conn.commit()
   cur.close()
```

rePost-User-8843498 · Answer

Create a function to call the procedure

CREATE OR REPLACE FUNCTION public.call_sp_export()
 RETURNS TABLE(result text)
 LANGUAGE plpgsql
AS $function$
BEGIN
    CALL t3.sp_exports();
    RETURN QUERY SELECT 'Procedure executed successfully' AS result;
END;
$function$
;

prepare glue job

spark = SparkSession.builder \
    .appName("sample") \
    .config("spark.jars.packages", "org.apache.hadoop:hadoop-aws:2.7.4") \
    .getOrCreate()

# Extract JDBC configuration details from Glue connection
jdbc_conf = glueContext.extract_jdbc_conf(connection_name="test")

# Construct JDBC URL
jdbc_url = jdbc_conf["fullUrl"]
jdbc_properties = {
    "user": jdbc_conf["user"],
    "password": jdbc_conf["password"],
    "driver": "org.postgresql.Driver"
}

# Define the SQL query to call the helper function
sql_query = "(SELECT * FROM public.call_sp_export()) AS result_alias"

# Execute the SQL query to call the stored procedure
try:
    # Using a dummy table call to trigger the function
    df = spark.read.jdbc(url=jdbc_url, table=sql_query, properties=jdbc_properties)
    print("Stored procedure called successfully.")
    df.show()
except Exception as e:
    print(f"Error while calling the stored procedure: {e}")

call a stored procedure from within glue job

Extract JDBC configuration details from Glue connection

Construct JDBC URL

Define the SQL query to call the helper function

Execute the SQL query to call the stored procedure

Add your answer

Relevant content