Create AWS lambda to copy csv file from S3 to RDS MySQL

Question

I am trying to load at least 4 csv files from my S3 bucket into my RDS Mysql database. Everytime the files are put in the bucket they will have a different name. The filenames have the date added at the end. I would like for them to automatically be uploaded to database when they are put in the S3 bucket. So far all I have is the load function to connect to the database. At this point I'm just trying to load one file. What would I do to have the file automatically loaded once its put in the S3 bucket? Thanks for the help!

lambdafunctionhandler file

public class LambdaFunctionHandler implements RequestHandler<Service, ResponseClass> {
public void loadService(){
    Statement stmt = null;
    try{
         Connection conn = DriverManager.getConnection("jdbc:mysql://connection/db", "user", "password");
        log.info("Connected to database.");


         //load date sql
         String query="LOAD DATA FROM S3 '"+ S3_BUCKET_NAME + "' INTO TABLE " + sTablename 
            + " FIELDS TERMINATED BY ',' ENCLOSED BY '\"' " 
            + "lines terminated by '\r\n' "+"IGNORE " + ignoreLines+" LINES";

         stmt.executeUpdate(query);
         System.out.println("loaded table.");

         conn.close();
    }catch(SQLException e){
        e.printStackTrace();
    }
}
@Override
public ResponseClass handleRequest(Service arg0, Context arg1) {
    String path="";

    return null;
}

NateH06 · Accepted Answer · 2018-08-06 21:13:18Z

1

If you have the full key of whatever file you're trying to load into S3 is going to be, then the standard AmazonS3 client object has this method: boolean doesObjectExist(String bucketName, String objectName) . By the "rules" of S3, uploading a file to S3 is atomic. The specified S3 key will not return true for this call unless the file is completely uploaded.

So you can trigger your upload of your file, and test for completeness with the doesObjectExist call. Once done, then perform your lambda function.

Alternatively, S3 also has another service (if you want to keep feeding the AWS beast) where you can turn on Bucket notifications, or trigger a Lambda function to execute with one of these notifications. I can't remember the name off the top of my head.

answered Aug 6, 2018 at 21:13

NateH06

3,6549 gold badges42 silver badges70 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Roro Over a year ago

OK I tried running the lambda function but it says invalid s3 uri. I have the full s3 bucket name. The bucket name is:private static final String S3_BUCKET_NAME="sftpgateway-i-XXXXXX/VolvoSA/uploads/DVD37411_20180530_0213_SV_APPT.csv";

NateH06 Over a year ago

@Roro try prepending s3:// to your bucket name. Are you only using the S3_BUCKET_NAME variable in your String query object?

NateH06 Over a year ago

@Roro - you also want to write your code in a way that your conn.close will get guarantee to execute, like in a finally{ conn.close } block. Alternatively you could also open the connection in the try with resources construct Java has.

Roro Over a year ago

OK thanks. I added finally block and added the s3:.// to bucket name and it works! I'm able to upload it to my lambda function and I created a rule in cloudwatch for the function to be ran on the schedule I want. How do I specify that I want to run this function on every file in this bucket? How do I pass in the name of multiple files into the lambda function??@NateH06

NateH06 Over a year ago

Quick note on terminology for your reference and clarity: a bucket is the top-level directory of the "file structure", what you call S3_BUCKET_NAME is actually what S3 refers to as a key - all the files don't actually sit all in the file system in a folder structure, but as mapped keys. Just an FYI to help your understanding later on

|

Collectives™ on Stack Overflow

Create AWS lambda to copy csv file from S3 to RDS MySQL

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related