Introducing data dumps

We've seen many requests for data dumps, and this is something we've wanted to support from the beginning. A data dump allows exploration or analysis that's not possible on-site. It is also a way to externally preserve the content you provide for our communities.

There's now a link in the footer to the data dumps page, where you'll find a downloadable MySQL export of public data -- posts, comments, users, and more -- for our entire network. Sensitive information is excluded.

What's included?

The dump includes a subset of the database. For context, you can find the full list of table definitions in the database schema, which you can always find in our open-source repository. That's everything that a production database uses; the subset that is included in dumps is specified in a separate file.

Let's look at an example. The users table contains everything related to a user -- name, profile text, email address, password (encrypted), creation time, 2FA configuration, and lots of other sensitive stuff. The dump specification for this table is:

users:
  columns:
    - id
    - is_global_moderator
    - is_global_admin
    - username
    - profile
    - profile_markdown
    - staff
  query: >
    WHERE deleted = FALSE

All those other columns are exported as NULL (if permitted by the schema) or default values (otherwise). If you look in the dump, you'll see that every user was created on 1970-01-01. Ok not really, but the exported value needs to match the data type in the schema.

Votes are similarly protected; you can see what the votes were but not who cast them:

votes:
  columns:
    - id
    - vote_type
    - post_id
    - recv_user_id
    - community_id

A sensitive table like warnings is not included in dump_permitted_columns.yml at all.

But wait, you might observe -- the actual dump does include a definition for that table! It does, because some other included table refers to it, but there is no actual data in the dump, just the table definition. Actual data is always inside a statement that begins with INSERT INTO; if you don't see that, there's no data.

How can I load it?

The dumps are generated using mysqldump. You can load them into a MySQL database the same way you load any other .sql file:

$ mysql [parameters] < dump.sql

You can also browse the file in an editor if you're just curious, but heads-up: the dump is larger than some editors will handle gracefully. We hope it will keep getting bigger.

How am I allowed to use it?

We offer the data dumps as part of our contribution to the commons. You must still abide by the terms of the license set by the author of each post. As a reminder, for almost all licenses in use on our network, those terms include proper attribution.

Why do I have to be logged in to get it?

Supporting the information commons is part of our mission. Not having our servers pounded into oblivion by bots is part of our mission of serving human users, too. We don't think having an account, and having to log into it, is too much to ask. We're not tracking downloads aside from whatever's buried in logs.

How often is it updated?

We intend for the dump to be updated automatically once a week. We're currently dealing with an issue in a third-party library that sometimes prevents this job from running; we delayed this announcement hoping to fix that first, but we can run the job manually if this happens. We'll try to keep an eye on it; feel free to poke us if the posted dump is getting stale.

Credits

Thanks to ArtOfCode and Oleg Valter for implementing data dumps, trichoplax for reviewing and helping to test, and all the people who've been part of conversations that helped shape this feature.