AWS Glue Job Call Redshift Stored Procedure
0
Anyone can help me? Need to run my Redshift Stored Procedure, always have and error to this query. Do I have input a wrong query? Please help
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)
# dw-poc-dev spark test
source_jdbc_conf = glueContext.extract_jdbc_conf('glue_connection')
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','username'), source_jdbc_conf.get('password','password'))
# call stored procedure
rs = conn.executeQuery('call ss_schema.sp_weekly_kpi_01_bbbklg()');
conn.close()
Topics
asked a month ago48 views
1 Answers
0
Hello,
You can use below code to execure Redshift Procedure:
Redshift Procedure name: post_script
post_query="begin; CALL post_script(); end;"
datasink = glueContext.write_dynamic_frame.from_jdbc_conf(frame = resolvechoice3, catalog_connection = "redshift_connection", connection_options = {"dbtable": "public.shoes", "database": "dev","postactions":post_query},redshift_tmp_dir = 's3://tempb/temp/' transformation_ctx = "datasink")
answered a month ago
Relevant questions
fail a glue job if the called stored procedure fails
asked 5 months agoCall a redshift stored procedure with dynamic parameter from boto3 lambda
Accepted Answerasked 3 months agooutput log for RDS stored procedure
asked 5 months agoCall a stored procedure in Redshift from Glue
Accepted Answerasked 3 years agoProgrammatically Running a Stored Procedure in Redshift
asked 3 years agoInvalid operation when creating Stored Procedure
asked 3 years agoAWS Glue Job Call Redshift Stored Procedure
asked a month agoTrigger stored procedure upon glue job succe
asked 5 months agocall a stored procedure from within glue job
Accepted Answerasked 5 months agoCan a Redshift stored procedure executing dynamic SQL use error exception handling to recover work & continue processing?
Accepted Answerasked 6 months ago
Thank you for this, but i got a new problem,
Question is this correct format of code in calling a stored procedure in redshift.
stmt = conn.createStatement();
try: rs = stmt.executeQuery('call dbo.sp_copy_csv_gz_staging('target_db', 's3_source')'); except: print("An exception occurred but proc has run")
conn.close()