Insert Records To AWS RDS Aurora Database From Files In S3 Bucket

This article on inserting records into AWS Aurora database from files in S3 bucket helps you to set up the necessary role permission required to enable upload files from S3 bucket to Aurora My SQL database.

Follow the below steps to setup the S3 bucket and permissions

Step 1 : Create a S3 bucket named Test

Step 2 : Create a role named “s3_role”

Attach the following policy to s3_role

Click “Edit Trust Relationship Policy” in your role ( s3_role ) and paste below policy

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "",
            "Effect": "Allow",
            "Principal": {
                "Service": "rds.amazonaws.com"
            },
            "Action": "sts:AssumeRole"
        }
    ]
}

Add another policy in permission tab (Assume trust policy) to s3_role

{
    "Version": "2012-10-17",
    "Statement": {
        "Effect": "Allow",
        "Action": "sts:AssumeRole",
        "Resource": "arn:aws:iam::accountNumber:role/s3_role"
    }
}

Add another policy named S3-access-frm-rds and attach it to s3_role

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Effect": "Allow",
            "Action": [
                "s3:ListBucket"
            ],
            "Resource": [
                "arn:aws:s3:::test"
            ]
        },
        {
            "Effect": "Allow",
            "Action": [
                "s3:*"
            ],
            "Resource": [
                "arn:aws:s3:::test/*"
            ]
        }
    ]
}

Step 3: Go to RDS instance then select your cluster

In Action drop-down select “Manage IAM roles”

In Manage IAM role page we are able to see s3_role as drop down. Then select s3_role and click add role button, finally click done button.

Now you can log in to your RDS instance, using any DB client like SQL workbench

Run the below query in DB client.

LOAD DATA FROM S3 's3://test/a.csv' 
INTO TABLE databasename.tablename     
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'n'
(NO, EMPID)

Now you will see the number of rows inserted in your tables.