Read data from AWS Redshift using AWS Glue job

Question

I set up a connection between AWS Glue and AWS Redshift, created an AWS Glue job, in the job when trying to execute a valid SQL query:

select distinct user_id from user_api.payment_made

source code of Glue job is presented below:

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
from awsglue import DynamicFrame

args = getResolvedOptions(sys.argv, ["JOB_NAME"])
sc = SparkContext()
glueContext = GlueContext(sc)
spark = glueContext.spark_session
job = Job(glueContext)
job.init(args["JOB_NAME"], args)

AmazonRedshift_node1688056230525 = glueContext.create_dynamic_frame.from_options(
    connection_type="redshift",
    connection_options={
        "sampleQuery": "select distinct user_id from user_api.payment_made",
        "redshiftTmpDir": "s3://aws-glue-assets-840773940442-us-east-1/temporary/",
        "useConnectionProperties": "true",
        "aws_iam_role": "arn:aws:iam::840773940442:role/service-role/AWSGlueServiceRole-RDSExportS3",
        "connectionName": "aws_glue_to_aws_redshift_connection",
    },
    transformation_ctx="AmazonRedshift_node1688056230525",
)

AmazonRedshift_node1688056290381 = glueContext.write_dynamic_frame.from_options(
    frame=AmazonRedshift_node1688056230525,
    connection_type="redshift",
    connection_options={
        "redshiftTmpDir": "s3://aws-glue-assets-840773940442-us-east-1/temporary/",
        "useConnectionProperties": "true",
        "aws_iam_role": "arn:aws:iam::840773940442:role/service-role/AWSGlueServiceRole-RDSExportS3",
        "dbtable": "business_intellegence_data.user_path_info",
        "connectionName": "aws_glue_to_aws_redshift_connection",
        "preactions": "CREATE TABLE IF NOT EXISTS business_intellegence_data.test_t1 (user_id VARCHAR); TRUNCATE TABLE business_intellegence_data.test_t1;",
    },
    transformation_ctx="AmazonRedshift_node1688056290381",
)

job.commit()

I get an error:

Invalid operation: exception name : UnauthorizedException, error type : 135, message: Not authorized to get credentials of role arn:aws:iam::840773940442:role/service-role/AWSGlueServiceRole-RDSExportS3

role AWSGlueServiceRole-RDSExportS3 was assigned to Redshift cluster.

Tell me, please, how can I solve this problem?

Answer

The role you mention `AWSGlueServiceRole-RDSExportS3` is not AWS managed role but customer managed role instead. Double check if you are giving permission to Redshift or RDS (as the name suggests).

Refer to [Configuring Redshift connections in AWS Glue](https://docs.aws.amazon.com/glue/latest/dg/aws-glue-programming-etl-connect-redshift-home.html) which provides the necessary details in the `Configuring IAM roles` section.

Read data from AWS Redshift using AWS Glue job

Relevant content