0

EDIT

I am working on a project to update a legacy ETL infrastructure that supports a number of clients, each with a slightly different setup.

Constraints that cannot be changed:

  1. Source data can come from flat files over sftp and then to S3, from MSSQL databases, or events published through webhooks.
  2. Source data ranges in size from ~60GB to ~1TB in size. Though we are grabbing diffs that are usually >5GB in total size. And the final databases for our clients are sized roughly the same.
  3. The current destination for all of our data is two-fold: a MySQL database (one for each client, may or may not be on a shared server) and second, a filtered set of data stored in a sql file on S3.

The existing system is designed to only load data from files so even when we have the data in a MSSQL RDS instance, we first extract the data to a series of files to be processed. Data is then loaded in through the MySQL LOAD DATA INFILE command, tranforms performed to get the raw data from the source system linked to the data in our system, and finally the data is moved from staging tables to the production tables. We are not doing any aggregations and so all of the data stays very close to its raw form.

One large problem we have with these existing systems is that as the data grows we are forced to increase the amount of RAM specifically because query performance degrades in MySQL as data is no longer able to be stored in memory.

In researching possible solutions, I've come to believe that parallelizing the ETL tasks using micro-batches would be a better approach as it should allow us to process increasingly large datasets without a linear increase in cost and/or processing time.

/EDIT

Our devops team is pretty invested in the AWS ecosystem so I've been concentrating on using AWS Lambda for parallel processing. I've come across some posts talking about using the MapReduce technique with AWS Lambda to perform the data processing.

What I'm wondering is whether Lambda with a MapReduce-like solution is really the correct solution when I'm really just moving, filtering, and tranforming data and not building any sort of aggregate indexes?

  • Can you show us one of your naive queries (or a reasonable facsimile thereof)? – Robert Harvey Feb 12 '18 at 17:28
  • 1
    I'm a little unclear on the problem you mention about "ull dataset is generally too large to fit in memory". Why would the dataset need to fit into memory? – JimmyJames Feb 12 '18 at 17:42
  • 1
    MapReduce only works if the datachunks for the processing clusters are independant of each other. Since we do not know anything of your huge data we cannot answer if MapReduce is the solution to your problem. I assume it is not. – k3b Feb 13 '18 at 13:05
  • What transformations do you need to apply to the data? MapReduce is a very general programming model and nothing about makes it suited especially well for ETL. – Marek Grzenkowicz Feb 15 '18 at 15:09
  • query performance degrades -> does it apply to the ETL process or working with the loaded data later? – Marek Grzenkowicz Feb 15 '18 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.