2
$\begingroup$

I'm curious if anyone can point to some successful extract, transform, load (ETL) automation libraries, papers, or use cases for somewhat inhomogenious data?

I would be interested to see any existing libraries dealing with scalable ETL solutions. Ideally these would be capable of ingesting 1-5 petabytes of data containing 50 billion records from 100 inhomogenious data sets in tens or hundreds of hours running on 4196 cores (256 I2.8xlarge AWS machines). I really do mean ideally, as I would be interested to hear about a system with 10% of this functionality to help reduce our team's ETL load.

Otherwise, I would be interested to see any books or review articles on the subject or high quality research papers. I have done a literature review and have only found lower quality conference proceedings with dubious claims.

I've seen a few commercial products advertised, but again, these make dubious claims without much evidence of their efficacy.

The datasets are rectangular and can take the form of fixed width files, CSV, TSV, and PSV. Number of fields range from 6 to 150 and contain mostly text based information about entities. Cardinality is large for individual information (address), but smaller for specific details like car type (van, suv, sedan).

Mappings from abbreviated data to human readable formats is commonly needed, as is transformation of records to first-normal-form.

As is likely obvious to the cognoscenti, I am looking for techniques that move beyond deterministic methods, using some sort of semi-supervised or supervised learning model.

I know this is a tall order, but I'm curious to assess the state-of-the-art before embarking on some ETL automation tasks to help guide how far to set our sights.

Thanks for your help!

Improve this question
$\endgroup$

2 Answers 2

Reset to default
1
$\begingroup$

I don't think you'll find anything that checks all of your requirements, but here are some things to look at:

  • Automated ETL mapping: There is a tool called Karma started by a team at USC's Information Sciences Institute. It learns from your ETL mappings and helps automate future mappings. It's the only open source tool I'm aware of that helps automate the ETL process, but I would be very interested if there are others out there.
  • Large scale ETL: There are many many tools you could look at for the scalability you are looking for. I can personally recommend looking at Storm and Spark. Storm is excellent for stringing a connection of processing steps together that, given enough resources, can compute in near real time on streaming data. Not too dissimilar Spark has a streaming component with a similar use case, but standard Spark may fit your needs better if the data you are needing to ETL is a fixed set to be processed once.
  • Data storage: You may also need to consider where all this data will live during the ETL lifetime. You may need something like Kafka to deal with large streams of data. Or maybe HDFS to store a static collection of files.
Improve this answer
$\endgroup$
1
  • $\begingroup$ Thanks for your answer. Karma looks interesting in its ability to normalize/denormalize, perform simple decoding, and apply the Stanford NLP library for feature extraction. It has about the maturity that I imagined it would have i.e. it is not the Parcy Mcparsface equivalent of ETL, but does have some utility. $\endgroup$
    – AN6U5
    Oct 13, 2016 at 10:52
1
$\begingroup$

Just about any ETL tool can manage fixed width, CSV, TSV, or PSV input, and just about any tool should be able to manage 100B records. The limiting part of the question really has to do with what your destination format is, and what disk throughput you need.

Expected throughput on an i2.4xLarge is 250mb/s. If an 8xLarge is double that, times 32 machines, you are looking at the ability to write a petabyte in ~138 hours. Not to mention the time and bandwidth of bringing in the source data in the first place. Unless my math is completely off, that means 30 Petabytes can get written to disk in about 6 months.

It seems odd that you are looking to either normalize or turn into human readable format that much data (it's only going to get bigger), and even odder that you'd want to leverage machine learning as part of a transformation/load of that size.

Your solution will need to be on local hardware in order to keep costs reasonable.

I couldn't recommend a system (commercial or open source) that would scale to the degree necessary to perform this kind of ETL on 30 Petabytes in a matter of days. At that scale, I'd be looking into lots of memory, ram backed/fronted SSDs, and custom development on FPGAs for the actual transformations. Of course, if my math on the write timing is wrong this whole answer is invalid.

Improve this answer
$\endgroup$
2
  • $\begingroup$ Thanks for your answer. Yes, I adjusted the data size and node count as I wrote cores where I should have put nodes. We currently run a 250 TB database and I am exploring 10x scaling. Yes write times are significant, but manageable with large node counts. The desire for a learning algo comes from ETL automation e.g. inferred normalization, adequately handling schema breaking CSV records, easy mappings from codes into human readable formats, automated parsing like string to first name, last name, middle name, suffix, degree. We might not be able to solve the whole problem but parts of it? $\endgroup$
    – AN6U5
    Dec 14, 2016 at 5:06
  • $\begingroup$ Thinking about this a bit more... SSD solutions are going to be out-of-this-world expensive, and I get where ML would come into play - a certain amount of errant normalization will have to be acceptable given the size of the dataset and it's less of a strain than MRing the whole dataset repeatedly. If verification were necessary on a smaller subset of data, that can be done with a lower strain on the resources. I would still lean towards FPGA development and a custom ETL pipeline. Frankly, I wouldn't trust a vendor other than the NSA to manage a data set of that size. $\endgroup$
    – Steve Kallestad
    Dec 14, 2016 at 13:53

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.