How to connect AWS RDS to AWS Glue ? (VPC)

user_10825800 0
Viewed 506

I have created a process to export data from my MySQL database to AWS Glue DataBrew and then to AWS Glue. So, I'm able to create a crawler and and modify the data via AWS Glue Studio.

Now I want to write the data back to my MySQL database. The MySQL database is in a VPC network. But I'm having trouble connecting the MySQL database to AWS Glue since over 10 hours, after watching tutorials and reading the documentation.

What I did :

Following https://docs.aws.amazon.com/glue/latest/dg/setup-vpc-for-glue-access.html

Following https://www.youtube.com/watch?v=jX-lvUFJ2jI

  1. Create a security group, assigned to the VPC ID with which my database is associated Type : All TCP, Destination : self-security-group

  2. Assigned the new security group to my RDS instance

  3. Added a VPC Endpoint to S3

  4. Created a new IAM Role with following permission :

    AmazonRDSFullAccess AmazonS3FullAccess AWSGlueServiceRole

Trusted entities :

glue.amazonaws.com
apigateway.amazonaws.com
rds.amazonaws.com
  1. Added the connection at AWS Glue to RDS

When I click on

  1. Test connection

  2. Select IAM role

  3. Result : The connection cannot be established

Does anyone have a solutions ?

Thanks in advance !

amazon-vpc aws-glue amazon-rds amazon-web-services amazon-s3
1 Answers

It appears that you might not be getting the use-case right. You are trying to get the data out of MySQL into DataBrew and then into Glue (may be via S3?). One thing to note is that DataBrew does not store your data anywhere, it relies either on S3 or such other data stores in your account.

If you are using DataBrew for getting your data out of MySql, you can equally use it to put the data back into MySQL right from DataBrew, but running a DataBrew job.

Here is what I suggest you try:

  1. Create a DataBrew dataset for MySql (and create a MySQL connection from DataBrew console. May be you already did this)
  2. Create a DataBrew project and wrangle with your data (apply transforms, delete/rename columns, fix schema etc)
  3. Create a DataBrew job, while doing this, select MySql JDBC connection and specify details/roles etc.
  4. Run this job and expect data to be put back into your MySQL table. Note that DataBrew only supports creating new table for each job run, you may want to create a process in MySQL to put data back from new table into original table (this is for safety purposes).
user_48445570
Related