I am trying to call SQL Server stored procedure from Glue job. Below is the script that I'm using which creates a jdbc connection to the RDS database instance and executes the stored procedure. 'AWSC' is the connection I have created for connecting to the database. I have tested this connection and I can read data as well as insert data into same database tables using this connection. However, upon job execution I get the below error:
-
import sys
-
from awsglue.transforms import *
-
from awsglue.utils import getResolvedOptions
-
from pyspark.context import SparkContext
-
from awsglue.context import GlueContext
-
from awsglue.job import Job
-
args = getResolvedOptions(sys.argv, ['JOB_NAME'])
-
sc = SparkContext()
-
glueContext = GlueContext(sc)
-
spark = glueContext.spark_session
-
logger = glueContext.get_logger()
-
job = Job(glueContext)
-
job.init(args['JOB_NAME'], args)
-
source_jdbc_conf = glueContext.extract_jdbc_conf('AWSC')
-
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")
-
conn = sc._gateway.jvm.DriverManager.getConnection(source_jdbc_conf.get('url'), source_jdbc_conf.get('user'), source_jdbc_conf.get('password'))
-
print(conn.getMetaData().getDatabaseProductName())
-
cstmt = conn.prepareCall("{exec AWSC.DDS.SP_INSERT}");
-
cstmt.setString("job_name", "testjob");
-
results = cstmt.execute();
-
conn.close()
I tried to replicate the above code with pymssql module as my data base is SQL Server. Now I receive the below error which I believe is again linked to RDS instance accessibility
OperationalError: (20009, b'DB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (gluetest.cgj1ict7xclx.ap-southeast-1.rds.amazonaws.com)\nNet-Lib error during Connection timed out (110)\nDB-Lib error message 20009, severity 9:\nUnable to connect: Adaptive Server is unavailable or does not exist (gluetest.cgj1ict7xclx.ap-southeast-1.rds.amazonaws.com)\nNet-Lib error during Connection timed out (110)\n')