9

I got the latest data dump and wrote a PHP script to parse the XML, but the actual parsing is taking forever (the uncompressed Posts.xml is 10GB). In the interim, does anyone know of a MySQL-ready .sql file that can be imported directly into the database?

I googled but the only thing I found was an inactive torrent of a much older version of the data dump, when the data dump was only 250MB. Does anyone know of a new version?

P.S. If anyone can point me to an existing .sql, that would be great. If not, I'll probably upload one when parsing is done, if there's interest. But I'm only parsing the Posts table, as this is all I need.

Improve this question
8
  • 7
    Part of the reason that XML was chosen is because it is a universal format. Your particular request only works with MySQL; SE would have to make a specific file for each database system.
    – user102937
    Commented Jun 21, 2012 at 3:57
  • Are you using the correct type of parser here? You should be using an event-based parser, not some of the other parser that try to load the entire document into memory.
    – Zoredache
    Commented Jun 21, 2012 at 8:31
  • 2
    @Robert Harvey I agree, XML is the best here. I'm not expecting SE to prepare a database-specific file for each database under the sun. That's a lot of work. I was thinking of a community effort to arrange this, as needed, especially for the Posts table. It would be sweet to search questions on the go when no internet connection is available. When I was downloading from clearbits, it said 44 downloaded this file. It's not a lot of people, but maybe half of them use MySQL. Would be nice if we could all save some time.
    – Z V
    Commented Jun 21, 2012 at 13:07
  • @Zoredache Yep, I'm using XMLReader which is the stream-based reader in PHP. I initially tried the other XML readers, but quickly dropped them when they kept crashing the script. I think the reason the script takes a long time is that it's also inserting into the database.
    – Z V
    Commented Jun 21, 2012 at 13:07
  • Disable indexe, queue up a thousand or so posts and do mass inserts.
    – Zoredache
    Commented Jun 21, 2012 at 19:52
  • In case you are unaware of it, there is the Stack Exchange Data Explorer that you can run queries on. Not quite as flexible as having a local copy, but it's pretty cool.
    – Josh Darnell
    Commented Jul 26, 2012 at 20:30
  • All I can say is... don't ever try using Python to parse the Stack Overflow XML files. (Unless you have, say, 24 GB of RAM.)
    – uɐɯsO uɐɥʇɐN
    Commented Dec 23, 2012 at 23:54
  • Heh, I have extra server resources. I could possibly host and generate monthly a ready-to-download database in 1 or 2 formats depending on how well the data compresses
    – Earlz
    Commented Jan 23, 2013 at 19:24

2 Answers 2

Reset to default
3

Here's what I did. Create a script that reads the xml and outputs to a CSV file, and then use LOAD DATA INFILE to load the data into MySQL. Doing inserts one at time will take ages.

Also, I wish the data dump was in CSV to begin with. It's far more universal a format than XML especially when importing into databases is a concern. But I think we've been over that already here.

Improve this answer
0
3

I have used this java code to good effect

https://github.com/kashyaprpuranik/Stack-Overflow/blob/master/XMLDumpImporter/src/XMLDumpImporter/Main.java

.. it runs pretty fast once set up (and you need to add the jdbc driver)

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .