Easy way to make local copy of entire SE database?

Question

Recently, I have been using the Data Explorer to try to find fishy users on SO. However, the Data Explorer is limited when it comes to cross-site and time-consuming queries, so I want to create a local copy of the entire SE database.

The data dump itself is easy to obtain from archive.org. However, I am not sure how to handle the resulting ~250 compressed XML files. Is there an easy semi-automated way to uncompress all these files, create the necessary SQL tables, transform all the data from files to database, and create the most obvious indexes?

I figured that someone must know of an automated way, since these dumps get updated every so often (and people therefore have to do it often), but I haven't been able to find anything here or elsewhere. The only thing close is this question, but its answers only work individual files. Did I miss something, or will I have to do it manually for every ~250 file?

Answer 1

Warning!!

A week after posting this answer, I realized that this method does not work as well as first thought. The program used to import the XML into the database is faulty, so for the tables with large amount of data for each row (Posts and PostHistory) it only imports a small fraction. The most extreme is Stack Overflow posts, where only 10% (2M of 20M) gets imported. It does however work perfectly for the smaller tables with less data per row (Comments, Users, Badges, Votes, and PostLinks).

Because this method still works to some intent, I am not removing this answer. Instead, I am making it community wiki, so anyone can update it if they find a solution.

---

Original post

It is possible to automate using a custom script. Here, I post a fairly simple python script that worked for me. However, before trying this, make sure that you fulfill the requirements:

Requirements

• Minimum 200 GB of free space. The uncompressed XML files temporarily take up 100 GB and the database files (on SQL Server) another 70 GB.
• A non-express version of the SQL Server (the express version is limited to a 10 GB database).
• Python installed, as the script won't run without.
• The latest version of the SODDI Data Dump Importer, available here (I used v1.1).

Known issues

• All large text fields (e.g. the Body field in Posts) are imported with the format NTEXT instead of NVARCHAR(MAX). This means, to use commands such as LEN() (which does not work on NTEXT, but does work on the Data Explorer) you will have to convert the field first using something like LEN(CAST(Body AS VARCHAR(MAX))).

Let's get started already

Uncompressing all the files

If you're sure, that you fulfill all the requirements, start by downloading the latest dump (this method has been tested on the May-2014 dump).

Firstly, you need to uncompress all the files and prepare them for the Soddi data dump importer. Soddi accepts folders named MMYYYY SomeName where MMYYYY is the month and year of the dump. Therefore, for each Stack Exchange site, the script creates a directory with that name (e.g. 052014 stackoverflowmeta), which holds all the XML files. Make sure you set the ARCHIVE, EXE7ZIP, and DUMP_VER constants properly:

import os, subprocess

ARCHIVE = 'D:\\Downloads\\stackexchange'
EXE7ZIP = 'C:\\Program Files\\7-Zip\\7z.exe'
DUMP_VER = '052014' #MMYYYY

for fname in sorted(os.listdir(ARCHIVE)):
    if fname[-3:] != '.7z':  
        continue  #skip non-7z files.
    print(fname[:-3])    
    # Create directory for XML files.
    sub, site = fname.split('.')[:2]
    dname = sub if site == 'stackexchange' or '-' in site else site + sub
    dpath = os.path.join(ARCHIVE, DUMP_VER + ' ' + dname)
    if not os.path.exists(dpath):
        os.mkdir(dpath)
    # Extract XML files.
    with open(os.devnull, 'w') as FNULL:
        args = [EXE7ZIP, 'x', '-bd', '-y', '-o' + dpath, os.path.join(ARCHIVE, fname)]
        subprocess.call(args, stdout=FNULL, stderr=FNULL) #run 7-zip

Estimated running time: 1 hour.

Loading the XML files into the database

Soddi Data Dump Importer is a small program that imports Stack Exchange XML dumps to an SQL Server. It was originally created by Sky Sanders, but taken over and updated by Jeremiah Peschka. Soddi has a graphical interface, which can be used as described in this blog-post. However, this did not work for me because of the massive amount of sites to import. Therefore, I wrote another script, which runs soddi individually for each uncompressed subfolder.

For each folder matching MMYYYY SomeName, the script will create database tables SomeName.Users, SomeName.Posts, etc. When doing this, the script will print Import complete. Approximately X rows in Y minutes for each folder. Don't be surprised if X is 0 for some folders, because the program rounds down from 5000 even though everything is imported. Furthermore, be ready to take a break when the script reaches StackOverflow, as it will give you something like 105,930,000 rows in 311.76 minutes:

import os, subprocess
    
ARCHIVE = 'D:\\Downloads\\stackexchange' #NO TRAILING SLASH
DUMP_VER = '052014' #MMYYYY
SODDI = 'C:\Path\\To\\soddi\\SODDI.exe'
DATABASE_NAME = 'SE' #Make sure this database already exists.

for dname in sorted(os.listdir(ARCHIVE)):
    if ' ' not in dname or dname.split(' ')[0] != DUMP_VER: 
        continue #skip folders without the right format.
    tname = dname.split(' ')[1]
    print(tname)
    # Import XML to database.
    args = [SODDI, 'source:"{}"'.format(ARCHIVE.replace('\\', '/')), 
            'target:"Data Source=(local);Initial Catalog=' + DATABASE_NAME +\
            ';Integrated Security=True;Provider=System.Data.SqlClient"', 
            tname]
    subprocess.call(args) #run soddi

Estimated running time: 6-7 hours.

Conclusion

In conclusion, after running the two scripts, everything should be imported into your database. You can then start querying and delete the whole downloaded database dump folder, including the extracted XML files. If you want to add indexes to all the tables at once, see the answers to this question. Happy querying!