Error while connecting AWS Glue to Redshift Serverless Workspace

Question

I am using AWS Glue to migrate data from Glue Catalog to Redshift Serverless and S3 after some transformations.

Since, I am learning Glue, I am using the Redshift Serverless to save my money.

So, I used two methods :-

Method 1:- Visual ETL Script

This method worked well. I was able to store the transformed file to S3, and to Redshift Serverless Database. The table is filled with data and all.

Method 2:- Using Spark script

When I use the above script (in method 1) to create a new ETL Job, the Job fails. I get an error

An error occurred while calling o86.getSink. The connection attempt failed. .

Which means the Connection to the Redshift Database is failing. However, the transformed CSV file is being stored to S3 correctly.

I am using the script as follows :-

ETL Script :-

target_Redshift = glueContext.write_dynamic_frame.from_options(
    frame=changeSchema_Transformation,
    connection_type="redshift",
    connection_options={
        "redshiftTmpDir": f"s3://{redshiftTmpDir_s3}/temporary/",
        "useConnectionProperties": "true",
        "dbtable": targer_redshift_dbtable,
        "connectionName": redshift_connection_name,
        "aws-iam-user" : target_iam_role_redshift
        #"preactions": "CREATE TABLE IF NOT EXISTS .......",
    },
    # transformation_ctx="target_Redshift",
)

I am assuming that the Redshift Serverless is not properly configured to work with ETL Jobs, but I have seen a lot of YouTube videos where the ETL job is succesful for Redshift Cluster.

Please help me with this issue !!

Answer 1

Same issue was happening to me! make sure you added the connection to the job (Visual ETL makes that automatically for you).

The thing is, when you create the Job with the Visual ETL the connections (JBDC connectors, etc) are added automatically for you. When you change your job from Visual to Script, no connections it's added, so you need to configure the connectors that are used in the job configuration