Interview Question: Stack Overflow THAT

We are doing perf testing right now, and we are looking into real world datasets to play around with. Luckily for us, Stack Overflow has regular data dumps of size significant enough to be useful for our experiments.

The file that I’m currently looking it is the Posts.xml file, which is about 45GB in size, and looks roughly like this (lot of stuff removed to make the point).

Since Stack Overflow is using relational databases, their output is also relational. You can see that each element is a single row, and you have the ParentId in row #7 that points back to row #4.

Basically, row #4 is the question, and row #7 is one of the answers.

What I want is to take all of this data and move it into a more document-like format. In other words, what I want is to have all the answers for a question contained within the question, something like this:

The fun part here is that this is a pretty big file, and we are writing the output into a GzipStream, so we don’t really have the option of saving/modifying midway through. Once we have written something out to the GzipStream, it cannot be changed.

So we need to find a way in which we can group all the answers under their questions, but at the same time, the file size is big (much bigger than the memory I have available) so we can’t just keep it all in memory and write it out in the end.

How would you solve this issue? My attempt is currently sitting at roughly 10GB of RAM used after processing about 30GB of XML, but I have to admit that I have thrown it together rather quickly, since I just needed the data and a quick and dirty solution is just fine here.