2

I am having a requirement where I need to use AWS lambda function to update a database export . You can imagine that I have a .sql export file which I want to import into a database, run number of sql queries on it and then export back into sql.

This is a daily activity and hence I want to have AWS lambda function doing it using cloudwatch cron scheduling.

I have following ideas to try :

  1. Compile MySQL just like we compile additional python extensions using docket mount /opt/lambda directory and run this operation on lambda amazon instance
  2. Spinning up an RDS and running the operations on it, destroying it when done
  3. Spinning up a t2-micro and running the operations on it, destroying it when done

I tried option 1 but it was not successful. Please suggest if you find any better idea please. Thank you in advance.

Improve this question
2
  • Do you require a database to perform the transformations, or can you simply do it on the flat files? (Option #3 seems to suggest you don't need a database?) How long does the transformation require, given that an AWS Lambda function runs for a maximum of 15 minutes? What do you mean by "export back into sql"? Do you mean export as a MySQL export file? This would, presumably, require MySQL to be involved in the process. Feel free to edit your question to add more details. Commented Apr 17, 2019 at 22:10
  • Yes you are right, I have an already dumped export file lets say production.sql. I need to import it into mysql, run some set of queries on it which changes the data and then export back to lets say sandbox.sql. So you are right this would require mysql involvement. in option 3 I was thinking to install mysql on ec2 server instead of doing that on RDS just to save cost. Commented Apr 18, 2019 at 6:31

1 Answer 1

Reset to default
3

I would recommend:

  • Use an Amazon CloudWatch Events scheduled rule to trigger an AWS Lambda function
  • Have the Lambda function:
    • Launch an Amazon RDS database
    • Launch an Amazon EC2 instance with User Data that downloads a script from Amazon S3 and executes it

The script would:

  • Download the data from Amazon S3
  • Wait until the RDS database is ready
  • Load the data into RDS
  • Run the transformation queries
  • Run the export and save it to Amazon S3
  • Terminate the RDS instance
  • Terminate the EC2 instance it is running on

A t3.micro Amazon EC2 instance is only 1c/hour, and it does not suffer from the 15-minute time limit of an AWS Lambda function. The RDS instance is charged per-hour. So, the whole process is rather low-cost.

All the logic would be stored in the script that is stored in S3. So, if you want to change the process, simply modify the script and store it in S3. You can then test it by manually running the Lambda process.

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

1 Comment

That sounds perfect, Thanks @John :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.