glue job for redshift connection: "Unable to find suitable security group"

python amazon-web-services jdbc amazon-redshift aws-glue
Growler picture Growler · Oct 2, 2017 · Viewed 7k times · Source

I'm trying to set up a AWS Glue job and make a connection to Redshift.

I'm getting error when I set the connection type to Redshift:

"Unable to find a suitable security group. Change connection type to JDBC and retry adding your connection."

Following what was said here in these forums, I added permissions to my IAM account for role AWSGlueServiceRoleDefault:

enter image description here

I then set up the job with the matching IAM role AWSGlueServiceRoleDefault:

enter image description here

I need to store the Glue data in Redshift DB, so I chose JDBC then added a connection:

enter image description here

As soon as I choose Redshift, it complains that it cannot find a suitable security group. Why is this?

enter image description here

enter image description here

Answer

fikipollo picture fikipollo · Feb 28, 2018

I found the same issue when trying to connect Glue with Amazon RDS (MySQL) and solved it following the AWS Glue guidelines -> Setting Up a VPC to Connect to JDBC Data Stores.

In a nutshell you should check that the security group associated to your RedShift cluster allows self-referencing traffic.

  1. Go to RedShift console and choose Clusters
  2. Look at the Cluster Properties section for the ID of the security group associated to the cluster (e.g. sg-957be3ef).
  3. Click at the security group name to jump to the EC2 console -> Security groups section. Choose the group and modify the Inbound and Outbound rules adding self-referencing rule to allow AWS Glue components to communicate.
    • Inbound rules: chose Type=All TCP, leave the default values and type the security group in the Source field (i.e. sg-957be3ef for this example).
    • Outbound rules: same as Inbound rules.

Hope that works!

Related questions

How to write a file or data to an S3 object using boto3

In boto 2, you can write to an S3 object using these methods: Key.set_contents_from_string() Key.set_contents_from_file() Key.set_contents_from_filename() Key.set_contents_from_stream() Is there a boto 3 equivalent? What is the …

Read More
How to upload a file to directory in S3 bucket using boto

I want to copy a file in s3 bucket using python. Ex : I have bucket name = test. And in the bucket, I have 2 folders name "dump" & "input". Now I want to copy a file from local directory to S3 "…

Read More
How to handle errors with boto3?

I am trying to figure how to do proper error handling with boto3. I am trying to create an IAM user: def create_user(username, iam_conn): try: user = iam_conn.create_user(UserName=username) return user except Exception as …

Read More