Fast Multi Platform Data Dump Import for SQL Server (2000/2005/2008), SQLite and MySQL

Question

UPDATE:

Both soapi.info and skysanders.net have been offline for a while.

---

---

The application readme file and hosting will be moved to http://soapi.info/tools/soddi.aspx.

---

Links are in the readme text below.

Stack Overflow Data Dump Import v.11
  ClickOnce Installer: http://skysanders.net/tools/se/soddi/publish.htm

  (c) 2010 Sky Sanders
  licensed under MIT/GPL - see license.txt

  msi :http://skysanders.net/files/soddi.11.msi
  info:http://skysanders.net/tools/se
  bin :http://skysanders.net/files/soddi.11.zip
  src :http://bitbucket.org/bitpusher/soddi/

SODDI is a .NET 3.5 SP1 executable written in C# that quickly and cleanly imports Stack Overflow 
Data Dump XML files into [SQL Server][3] 2000/05/08, [MySQL][4] 5.1 and [SQLite][5] 3 
(MySQL and SQLite drivers are included).

SODDI can be run as a command line utility or, when invoked with no arguments or GUI argument, 
will present a [Windows Forms][6] interface.

Quick Start:
The quickest route to your own copy of the Stack Overflow databases is to use the ClickOnce installer,
browse to the uncompressed data dump, accept the default SQLite provider selection and click 'Import'.

USAGE:

soddi.exe source:"" target:"" [batch:5000] [split] [indices] [fulltext] [[meta] [so] [su] [sf]] [gui]


SOURCE          The directory containing the individual site directories.
                NOTE: do not include trailing slash in quoted path as the arg
                parser will interpret it as an escaped quote and puke.

TARGET          A valid ADO.NET connection string, including the provider invariant
                name.

                Platform specific connection string details:

                SQL Server: the database must exist. Data will be loaded into tables segregated by
                schema named as the site data being imported. e.g. so.Users, meta.Users.
                The tables are dropped before import.

                MySQL: The connection string should include server, each site's data will be loaded
                into a database named as the site imported. The databases will be dropped and 
                recreated before import.

                SQLite: The connection string should specify a directory. The data will be imported
                into separate .db3 files, each named as the site imported. Existing data files
                will be overwritten.

                The target database/datafile/schema names can be modified by explicitly specifying
                sites to import and appending the desired schema as a parameter value or editing
                the Sites list item schema in the GUI.


-- OPTIONAL ARGUMENTS

SPLIT           Normalize post tags by splitting the concatenated Posts.Tags field into individual 
                rows in a separate PostTags table.

INDICES         Enables useful indexes on each table.

FULLTEXT        Enables a full text index on Posts.Body and Posts.Title - SQL Server only.

BATCH           Number of rows inserted in each transaction. Default 5000.

GUI             Presents a Windows Forms interface. If SODDI is invoked with arguments and GUI, the
                UI will be populated with the supplied arguments.

                The console window will remain open to receive all debug and error output.

META|SO|SU|SF   Specifies which sites to import. If none are specified, all site directories found
                in SOURCE will be imported.

                To specify a different target name simply treat the site name as a parameter.

                e.g. 

                SQL Server - SO:StackOverflowData will load the data from the XXXXX SO directory 
                into the database specified in the connection string and the schema 'StackOverflowData'

                MySQL - SO:StackOverflowData will load the data from the XXXXX SO directory 
                into a new database named StackOverflowData on the server specified in the connection
                string.

                SQLite - SO:StackOverflowData will load the data from the XXXXX SO directory into a 
                new db3 file named StackOverflowData.db3 in the directory specified in the connection
                string.

                In GUI mode you may edit the schema item in the Sites list.

Options are not case sensitive.

Example command lines.

GUI Mode:
    soddi 

SQLite - all sites:
    soddi source:"F:\Export-030110" target:"data source=c:\temp;version=3;new=True;Provider=System.Data.SQLite"

MySQL - all sites:
    soddi source:"F:\Export-030110" target:"server=localhost;user id=root;password=p@ssW0rd;Provider=MySql.Data.MySqlClient"

MySQL - Meta StackOverflow and StackOverflow data into specified databases:
    soddi source:"F:\Export-030110" target:"server=localhost;user id=root;password=p@ssW0rd;Provider=MySql.Data.MySqlClient" meta:MetaDb so:SoDb

SQL Server - all sites:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient"

SQL Server - Stack Overflow data only (SO):
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so

SQL Server - Stack Overflow data only into schema dbo:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so:dbo


SQL Server - Stack Overflow data only, split tags and add indices:
    soddi source:"F:\Export-030110" target:"data source=(local);initial catalog=SOData;integrated security=true;Provider=System.Data.SqlClient" so split indices

The latest data dump can be found at
http://blog.stackoverflow.com/category/cc-wiki-dump/


04/01/2010 - Sky Sanders

04/09/2010 - Explicitly set platform to x86 to allow same binaries to run on x64.

Answer 1

My experience with the October and November 2010 data dumps importing to MSSQL.

The latest data dump requires that you do a couple of things in order to use this.

1. Rename the folders.
   
   • The format of the folder needs to be MMYYYY Schema.
   • So, for the Web Apps data dump, I have 2 folders, 112010 WA and 112010 MWA.
   • The database tables will be <database>.WA.Users and <database>.MWA.Users etc.
2. Expand the About Me column.
   • The About Me column within the built project is 2100 characters. This is too short.
   • I didn't play around with what the length needs to be - I just went with NVARCHAR(MAX).

If you don't care about the Users table, step 2 isn't necessary. Chances are you'll want the users table though I suppose.

Answer 2

If there is enough interest, I will do the work to get this to compile and run on Mono for the LAMP folks.

update:

I see a little interest.

I am currently focusing my free time, free as in beer as I am currently without job (hint, big freakin hint), on polishing http://soapi.info and it's offerings.

A bit more interest and I will carve out a day to make this happen.

Answer 3

I have the XML files (for example, users.xml and votes.xml) for Stack Overflow data in a folder called 'c:\sodump'. I fire the command below (I am using Windows 7), but it does not import any records. I would appreciate any help or pointers to possible solutions. I would like to import Stack Overflow data into MySQL.

C:\Program Files\Salient Solutions\SODDI v.10>soddi source:"c:\sodump" target:"server=localhost;user id=root password=blah;Provider=MySql.Data.MySqlClient" so:SoDb

StackOverflow Data Dump Import v.10

Source  : c:\sodump
Target  : server=localhost;user id=root;password=blah;Provider=MySql.Data.MySql
Client
Options : None
Import complete. Approximately 0 rows in 0.00 minutes.

Answer 4

There is an issue with a case sensitivity of table names within SQL generated for MySQL. Table creation statements use lower case names of tables and insert statements use mixed case names so the importer cannot work with a MySQL database on Linux. Is there a way to make it work with MySQL on a Unix platform?

Answer 5

can anyone please explain HOW the source directories should be arranged, I have the stack-overflow xml dump (users.xml , posts.xml , votes.xml) inside a directory , I select it as the source directory , but can't get them imported , it tells me "Invalid source path." and also the sites list is empty!

Answer 6

A very useful tool and very well programmed. However, it doesn't work with the latest Aug-2012 data dump. Probably XML schema of dumped data changed from the previous version, and now for questions and answers without views, the value for the attribute ViewCount is an empty string. That cause the error mentioned by others in this post "Input string was not in a correct format" when the empty string is trying to be converted into a number (Int32).

The solution (that worked for me) was to make a simple modification to the source code to handle this case.

File SoBase.cs, line 83 show this:

if (attr == null)

It should be replaced by

if (attr == null || (string.IsNullOrWhiteSpace(attr.Value) && returnType.IsValueType))

Thanks Sky Sanders for this awesome tool, performance is very good too!

Answer 7

Additionally, if you need to import a data-dump to PostGreSQL (or MS-SQL), this will help:

https://github.com/ststeiger/StackExchangeDataDumpImporter

It's very efficient with memory consumption, it uses the original schema, it checks the data (serialization to type - exception if invalid), and it works on Linux/Mono, too.

You could also add Firebird easily (just create the schema and change the DataProviderFactory initialization).