How can I flter data from a stream of very large text files?

Question

I have a stream of data being delivered to my Amazon S3 bucket in the form of tab-delimited text. I would like to scan as it comes in to keep a very small subset of the rows from that match certain criteria. Given the following constraints:

1. Each file is 5-50GB in size.
2. I'm receiving just over 1 billion rows of data per day.
3. I am to use Amazon AWS services.
4. I need to do it as cheaply as possible.

All of the data only needs to be read once then can be deleted. How do I most efficiently perform both a range filter on one field, and a keyword filter on another, keeping the entire row that matches a given query for ingest into another system?

I can think of a number of solutions and none of them are cheap. Originally I was thinking of loading into a Redshift cluster but this seems like it may be overkill on the basis of both complexity. Lambda seems like an obvious choice except for the fact that it doesn't support loading that much data into memory. I suppose I could see how well an auto-scaling group of workers performs, but that doesn't seem like the most efficient way. Ideally I'd like to perform spatial queries on locations represented as x,y coordinates but I can simplify that to a series of bounding box queries if necessary. All suggestions appreciated.

Answer 1

Have you looked at AWS Athena yet? It doesn't require any additional storage (besides the existing S3 bucket) and you can query the data (all of it) whenever you want - for example if your query changes or you get new business requirements. It probably won't give you anything like the "streaming" or real-time characteristics that you seem to be leaning toward in your other solutions, but it's fairly cheap if your interactions are efficient.

Alternatively, I would probably look at something like a Lambda function triggered on the S3 event. Each file would then be processed via a file stream reader (to keep the memory in check - see this post or this one for some possibly helpful discussions) - from there you could perform a function on individual rows (or batches of rows) to either do your filtering and forward the relevant data or you could spend a bit more and send everything through a Kinesis stream (or Firehose) and then probably through Kinesis Analytics to perform the filtering. But you did specify you were looking for "cheap", so this may be beyond the budget.

Depending on your appetite for managed services vs. writing, deploying, and maintaining your own servers and applications, you might be able to squeeze out some cost savings using SQS and some workers, but personally I lean toward managed services as much as I can.

Final thought is to possibly think a bit more about what else you might want out of that data and whether throwing away the stuff that fails the filter is really a good idea. Moreover it might be worthwhile to determine what data structure you need for future use cases of this data. Some possible options would be Redshift (like you mentioned) and something more akin to a data lake (like in the S3 + Athena use case).