AWS Glue job cannot see Serverless Redshift DB as target

Question

I am trying to create a process that will ingest .cvs files dropped into an S3 bucket daily and add those records to an existing table in a serverless Redshift database.  To accomplish this, I'm trying to build a Glue job that will scan the S3 bucket for new files and update/add those records to the redshift table accordingly.  I have no Spark or Python skills so am using Glue Studio.  I have selected the 'Visual with a source and target' option with my source being Amazon S3 and Target as Amazon Redshift.  I click 'Create' and am taken to the canvas.  I can find my S3 Bucket, apply the transform mappings but when I click the Redshift Cluster node, I do not see my serverless Redshift database.  I do understand there is a difference between a Serverless Redshift database and a Redshift Cluster but there is no option to select the target as Serverless Redshift.    My question seems to be similar to that posted in this question but the answer provided did not really help me:  https://repost.aws/questions/QU33GP1YziR5OXIn-vehIxwA/aws-glue-studio-source-s-3-target-redshift-job-wants-to-select-gdc-not-redshift-table-in-target
 
I followed the steps outlined here https://docs.aws.amazon.com/glue/latest/dg/setup-vpc-for-glue-access.html (though this specifically applies to Redshift Clusters) to address the Inbound/outbound rules of the VPC security group in my Serverless Redshift workgroup and all look to be set up correctly.  I also reviewed the associated IAM roles and they also look to be set up correctly.

My question is how do I set up a Glue job to read from an S3 bucket and populate a table in a Serverless Redshift database?  What steps am I missing?  I can't tell if I'm running into permission issues (i.e. Redshift won't allow Glue access) or if there is something else I'm missing.  I cannot find any documentation that specifically addressed Glue and Serverless Redshift databases.  I am open to any suggestions.

Kayla Andersen · Answer

You actually can use a RedShift serverless table as your target in AWS Glue Studio jobs. The *catch* is that you need to add the target RedShift Serverless table to your Data Catalog first, either manually or using a Glue Crawler.

This is noted in here: https://docs.aws.amazon.com/glue/latest/ug/data-target-nodes.html.

"For all data sources except Amazon S3 and connectors, a table must exist in the AWS Glue Data Catalog for the target type that you choose. AWS Glue Studio does not create the Data Catalog table." There is also fine print in the Glue Studio job itself. Under "Amazon RedShift" is says "AWS Glue Data Catalog table with RedShift as the data target".

Once you the have added it, you can then use it as a source or target in the Glue Studio Job. Use the drop down to select "Amazon RedShift" as the target. Note that the database and table name will be based on the Glue Catalog table name, not whatever you named it in the actual RedShift Cluster.

To use it with a Crawlers, you need to first add a Connection in Glue -- RedShift serverless will be a JDBC Connection. Then when creating the crawler, select "JDBC" as your Data source and choose the RedShift connection that you created.

Hope that helps!

Divyam Chauhan · Answer

Unfortunately, Glue Studio does not currently support Serverless Redshift as a target. You'll need to use another approach to transfer data from your S3 bucket to your Redshift database. Some options include:

Use a traditional Redshift cluster as the target in Glue Studio and then use Amazon Redshift Spectrum to query your Serverless Redshift database from the Redshift cluster.

Use a different AWS service such as Amazon Data Pipeline, AWS Lambda, or AWS Step Functions to transfer the data from S3 to Serverless Redshift.

Use a Spark job with the Redshift JDBC driver to transfer the data.

Use the Redshift COPY command to transfer the data directly from S3 to Serverless Redshift.

AWS Glue job cannot see Serverless Redshift DB as target

Relevant content