My story of SQL as a data analysis equalizer

So, the title of this post is a little bit extreme, but I chose it specifically because I think it will help you (gentle reader) in thinking in the direction that I'd like you to think.

Back when I was a young & bright eyed computer scientist who was slowly realizing that social dynamics were a fascinating and complex environment within which to practice technology development, I was invited to intern at the Wikimedia Foundation with a group of 7 other researchers.  It turns out that we all had very different backgrounds going in.  Fabian, Yusuke, and I had a background in computer science.  Jonathan had expertise in technology design methods (but not really programming).  Melanie's expertise was in rhetoric and language.  Stuart was trained in sociology and philosophy of science (but he'd done a bit of casual programming to build some bots for Wikipedia).  I think this diverse set of backgrounds enabled us to think very well about the problem that we had to face, but that's a subject for another blog entry.  Today I want to talk to you about the technology that we ended up rallying around and taking massive advantage of: the Structured Query Language (SQL) and a Relational Database Management System (RDBMS).

Up until my time at the Wikimedia Foundation, I had to do my research of Wikipedia the hard way.  1st, I downloaded Wikipedia's 10 terrabyte XML dump (compressed to ~100 gigabytes).  Then I write Python script that used a streaming p7zip decompressor and a unix pipe to read the XML with a streaming processor.  This workflow was complex.  It tapped many of the skills I had learned in my training as a computer scientist.  And yet, it was still incredibly slow to perform basic analyses.

It didn't take me long to start using this XML processing strategy to produce intermediate datasets that could be loaded into a postgres RDBMS for high-speed querying.  This was invaluable to making quick progress.  Still I learned some lessons about including *all the metadata I reasonably could* in this dataset since going back to the XML was another headache and week-long processing job.  As a side-note, I've since learned that many other computer scientists working with this dataset went through a similar process and have since polished and published their code that implements these workflows.  TL;DR: This was a really difficult analysis workflow even for people with a solid background in technology.  I don't think it's unreasonable to say that social scientist or rhetoric scholar would have found it intractable to do alone.

When I was helping organize the work we'd be doing at the Wikimedia Foundation, I'd heard that there was a proposal in the works to get us researchers a replica of Wikipedia's databases to query directly.  Honestly, I was amazed that people weren't doing this already.  I put my full support behind it and thanks to others who saw the value, it was made reality.  Suddenly I didn't need to worry about processing new XML dumps to update my analyses, I could just run a query against a database that was already indexes and up to date at all times.  This was a breakthrough for me and I found myself doing explorations of the dataset that I had never considered before because the speed of querying the relevancy of the data made them possible.  Stuart and I had a great time writing SQL queries for both our own curiosity and to explore what we ought to be exploring.

For my coworkers who had no substantial background in programming, they saw yet another language that the techies were playing around with.  So, they took advantage of us to help them answer their questions by asking us to produce excel-sized that they could explore.  But as these things go when people gets busy, these requests would often remain unanswered for days at a time.  I've got to hand it to Jonathan.  Rather than twiddling his thumbs while he waited for a query request to be resolved, he decided to pick up SQL as a tool.  I think he set an example.  It's one thing to have a techy say to you, "Try SQL!  It's easy and powerful." and a totally different thing for someone without such a background to agree.  By the end of the summer internship, I don't think we had anyone (our managers included) who were not writing a bit of SQL here and there.  All would agree that they were substantially empowered by it.

Since then, Jonathan has made it part of his agenda to bring SQL and basic data analysis techniques to larger audiences.  He's been a primary advocate (and volunteer product manager) of Quarry our new open querying to for Wikimedia data.  That service has taken off like wildfire -- threatening to take down our MariaDB servers.  Check it out https://quarry.wmflabs.org/  Specifically, I'd like to point you to the list of recent queries: https://quarry.wmflabs.org/query/runs/all  Here, you can learn SQL techniques by watching others use them!

ORES: Hacking social structures by building infrastructure

So, I just crossed a major milestone on a system I'm building with my shoe-string team of mostly volunteers and I wanted to tell you about it.  We call it ORES.

The ORES logo

The Objective Revision Evaluation Service is one part response to a feminist critique of power structures and one part really cool machine learning and distributed systems project.   It's a machine learning service that is designed to take a very complex design space (advanced quality control tools for Wikipedia) and allow for a more diverse set of standpoints to be expressed.  I hypothesize that systems like these will make Wikipedia more fair and welcoming while also making it more efficient and productive.

Wikipedia's power structures

So...  I'm not going to be able to go into depth here but there's some bits I think I can say plainly.  If you want a bit more, see my recent talk about it.  TL;DR: The technological infrastructure of Wikipedia was build through the lens of a limited standpoint and it was not adapted to reflect a more complete account of the world once additional standpoints entered the popular discussion.  Basically, Wikipedia's quality control tools were designed for what Wikipedia editors needed in 2007 and they haven't changed in a meaningful way since.  

Hacking the tools

I had some ideas on what kind of changes to the available tools would be important.  In 2013, I started work in earnest on Snuggle, a successor system.  Snuggle implements a socialization support system that helps experienced Wikipedia editors find promising newcomers who need some mentorship.  Regretfully, the project wasn't terribly successful.  The system works great and I have a few users, but not as many the system would need to do its job at scale.  In reflecting on this, I can see many reasons why, but I think the most critical one was that I couldn't sufficiently innovate a design that fit into the social dynamics of Wikipedia  It was too big of a job.  It requires the application of many different perspectives and a conversation of iterations.  I was a PhD student -- one of the good ones because Snuggle gets regular maintenance -- but this work required a community.

When I was considering where I went wrong and what I should do next, I was inspired by was the sudden reach that Snuggle gained when the HostBot developer wanted to use my "promising newcomer" prediction model to invite fewer vandals to a new Q&A space.  My system just went from 2-3 users interacting with ~10 newcomers per week to 1 bot interacting with ~2000 newcomers per week.  Maybe I got the infrastructure bit right.  Wikipedia editors do need the means to find promising newcomers to support after all!

Hacking the infrastructure

So, lately I've been thinking about infrastructure rather than direct applications of experimental technology.  Snuggle and HostBot helped to know to ask the question, "What would happen if Wikipedia editors could find good new editors that needed help?" without imagining any one application.  The question requires a much more system-theoretic way of reasoning about Wikipedia, technology and social structures.  Snuggle seemed to be interesting as an infrastructural support for Wikipedia.  What other infrastructural support would be important and what changes might that enable across the system itself?

OK.  Back to quality control tools -- the ones that haven't changed in the past 7 years despite the well known problems.  Why didn't they change?  Wikipedia's always had a large crowd of volunteer tool developers who are looking for ways to make Wikipedia work better.  I haven't measured it directly, but I'd expect that this tech community is as big and functional as it ever was.  There were loads of non-technological responses to the harsh environment for newcomers (including the Teahouse and various WMF initiatives).  AFAICT, the tool I built in 2013 was the *only* substantial technological response.

Why is there not a conversation of innovation happening around quality control tools?  If you want to build a quality control tool for Wikipedia that works efficiently, you need a machine learning model that calls your attention to edits that are likely to be vandalism.  Such an algorithm can reduce the workload of reviewing new edits in Wikipedia by 93%, but standing one up is excessively difficult.  To do it well, you'll need an advanced understand of computer science and some substantial engineering experience in order to get the thing to work in real time.  

The "activation energy" threshold to building a new quality
control tool is primarily due to the difficulty of building a
machine learning model.

So, What would happen if Wikipedia editors could quickly find the good, the bad, and the newcomers in need of support.  I'm a computer scientist.  I can build up infrastructure for that and cut the peak off of that mountain -- or maybe cut it down entirely.  That's what ORES is. 

What ORES is

ORES is a web service that's provides access to a scale-able computing cluster full of state-of-the-art machine learning algorithms for detecting damage, differentiating good-faith edits from bad and measuring article quality.  All that is necessary to use this service is to request a url containing the revision you want scored and the models you would like to apply to it.  For example, if you wanted to know if my first registered edit on Wikipedia was damaging, you could request the following URL. 

    https://ores.wmflabs.org/scores/enwiki/damaging/190057686/

Luckily, ORES does not think this is damaging in the slightest.

{
"190057686": {
"prediction": false,
"probability": {
"false": 0.9999998999999902,
"true": 1.0000000994736041e-07
}
}
}

We use a distributed architecture to make scaling up the system to meet demand easy.  The system is built in python.  It uses celery to distribute processing load and redis for a cache.  It is based on revscoring library I wrote to generalize machine learning models of edits in Wikipedia.   This same library will allow you to download one of our model files and use it on your own machine -- or just use our API.

Our latest models are substantially more fit than the state-of-the-art (.84 AUC to our .90-95 AUC) our system has been substantially battle tested.  Last month, Huggle, one of the dominant yet unchanged quality control tools started making use of our service.  We've seen tool devs and other projects leap at the ability to use our service.

• Ra•un (for projects that support ORES)
• Real-Time Recent Changes 
• en:Wikipedia:WikiProject X via en:User:Reports bot
• en:Wiki Education Foundation – Student quality/productivity measurements
• en:User:SuggestBot
• crosswatch – cross-wiki watchlist
• (in development) ORES extension to MediaWiki
• WikiEd recent student activity dashboard
• WikiEd article finder

MediaWiki Utilities -- Unix style

Today, I want to talk about a specific type of research output that, I feel, adds a substantial amount of value beyond a single research project.  I'm talking about open sourced research code, but not of the type that you normally see -- the type that's actually intended to be used by other people to serve new purposes.

Before I dig into software libraries I want to talk to you about, I must make a distinction:

• CRAPL quality code -- This is the code that a researcher builds ad-hoc in order to get something done.  There's little thought spent on generalizability or portability.  With this code, it's usually better and faster to fix a problem by adding a step in the workflow rather than fixing the original problem.  So, you end up with quite a mess usually.  While good for documenting the process of data science work, this is not useful to others.  
• Library quality code -- This code has been designed to generalize to new problems.  It's intended to be used as a utility by others who are doing similar work -- but not the exact same work.  It's usually well documented and well tested.  With this code, it is sacreligious to add more code on top of broken code to fix a problem.  This is just one of the many disciplines that must be applied to the practice of writing software to have good, library quality code. 

I've been analyzing Wikipedia data for nearly a decade (!!!) -- and I can tell you that it was never easy.  The English Wikipedia XML dumps that I have done most of my work with are on the order of 10 terrabytes uncompressed.  The database, web API and XML dumps all use different field names to refer to the same thing.  In each one, the absence of a field -- or NULLing of the field can mean different things.  Worse, the MediaWiki software has been changing over time, so in order to do historical analyses, you need to take that into account.  In the process of working out these details and getting my work done, I've produced reams of CRAPL quality code.  See https://github.com/halfak/Activity-sessions-research for an example.  In this case, I have a Makefile that, if executed, would replicate my research project.  But if you look inside that Makefile, you'll see things like this:

# datasets/originals/enwiki_edit_action.tsv: sql/edit_action.sql
#     cat sql/edit_action.sql | \
#     mysql $(dbstore) enwiki > \
#     datasets/originals/enwiki_edit_action.tsv

That's a commented out Makefile rule that calls my local database with my local configuration hardcoded and runs some SQL against it.  This is great if you want to know what SQL produced which datafile, but not very useful if you want to replicate the work.  And why is it commented out!?  Well, the database query takes a long time to run and I didn't want to accidentally overwrite the data file as I was finishing off the research paper.  Gross, right?  This isn't all that useful if you wanted to perform a similar analysis.

But in producing this CRAPL code, there are some nice, generalizable parts that occur to me so I write them up for others' benefit.  I've gone through a few iterations of this and learned from my mistakes.

Back in 2011, I released the first version of wikimedia-utilities, a set of utilities that made the work I was doing at the Wikimedia Foundation easier.  The killer feature of this library was the XML processing strategy.  It changed the work of processing Wikipedia's terrabyte scale XML dumps from a ~2000 line script to a ~100 line script.   But the code wasn't very pythonic, it lacked proper tests and did not integrate well into the python packaging environment.

In 2013, I decided to make a clean break and start working on mediawiki-utilities, a super-set of utilities from wikimedia-utilities that were intentionally generalized to run on any MediaWiki instance.  I had learned some lessons about being pythonic, implementing proper tests and integrating with python's packaging environment.

But as I had been working on new projects and realizing how they could generalize, I ended up expanding mediawiki-utilities to a monolith of loosely related parts.  And it gets worse.  Since I focused on those parts as I needed them, there were certain modules that were ignored.  Since I did most of my work with the databases directly, it was rare that I spent time on the 'database' module of mediawiki-utilities.  I ended up with a monolith that was inconsistently developed!

So, in thinking about monoliths and how to solve problems that they impose, I was inspired by the Unix philosophy of combining "small, sharp tools" to solve larger problems.  I realized that the primary modules of mediawiki-utilities could be split off into their own projects and combined in interesting ways -- and that this would enable a more distributed strategy to management.  So I've been hard at work to bring this vision into the light.

First, the core utilities:

• pip install mwxml • docs • source
• This library contains a collection of utilities for efficiently processing MediaWiki’s XML database dumps. There are two important concerns that this module intends to address: complexity and performance of streaming XML parsing.
• pip install mwapi • docs • source
• This library provides a set of basic utilities for interacting with MediaWiki’s “action” API – usually available at /w/api.php. The most salient feature of this library is the mwapi.Session class that provides a connection session that sustains a logged-in user status and provides convenience functions for calling the MediaWiki API. See get() and post().
• pip install mwdb • source
• This library provides a set of utilities for connecting to and querying a MediaWiki database.
• pip install mwparserfromhell • docs • source
• This library provides an easy-to-use and outrageously powerful parser for MediaWiki wikicode.
• Note that I am primarily a user of this library -- not a major contributor -- but it obviously belongs in this list.

Now the peripheral libraries that make use of these core utilities:

• pip install mwoauth • docs • source
• This library provide a simple means to performing an OAuth handshake with a MediaWiki installation with the OAuth Extension installed.
• pip install mwreverts • docs • source
• This library provides a set of utilities for detecting reverts (see mwreverts.Detector and mwreverts.detect()) and identifying the reverted status of edits to a MediaWiki wiki.
• pip install mwsessions • docs • source
• This library provides a set of utilities for group MediaWiki user actions into sessions. mwsessions.Sessionizer and mwsessions.sessionize() can be used by python scripts to group activities into sessions or the command line utilities can be used to operate directly on data files. Such methods have been used to measure editor labor hours.
• pip install mwpersistence • source
• This library provides a set of utilities for measuring content persistence and tracking authorship in MediaWiki revisions.

And I have a bunch more that are just on the horizon.  They represent a sampling of my active research projects.

• mwmetrics -- User behavioral statistics extraction for MediaWiki editors
• mwrefs -- Extract citations, references and scholarly identifiers from MediaWiki
• mwevents -- Generalized event extraction and processing framework for MediaWiki
• mwtalkpage -- A talk page discussion parser for MediaWiki

It's my goal that researchers who haven't been working with wiki datasets will have a much easier time building off of my work to do their own.  I think that a good set of libraries can make a huge difference in this regard.  That's my goal.

I'll be making a more substantial announcement soon.  In the meantime, I'm cleaning up and extending documentation and working together some examples that demonstrate how a researcher can compose these small, sharp libraries together to perform powerful analyses of Wikipedia and users in other MediaWiki wikis.  Until then, please use these utilities, let me know about bugs and send my your pull requests!

Pre-blog: The life of a traveling technologist

So, I didn't have much time this week and I'm doing this Iron Blogging thing.  If you got here looking for a cool discussion of the life of a traveling technologist, I regret to inform you that this will only be a meta-discussion.  Once I've completed the proper discussion, I'll put the link right below this paragraph.

What?

So my life is pretty weird in a lot of ways.  I travel a lot.  I generally don't see my teammates at the Wikimedia Foundation for months at a time.  Worse, I have a group of friends who are extremely geographically distributed that my geographically local friends don't know about.  We only see each other during conferences and other academic events.  

Another sort of interesting aspect of my professional life is that I straddle the line between industry and academia.  When it comes to the meat of knowledge & knowledge production, there's no conflict.  But the timescales are amazingly different.  

But through dealing with this, I've worked out some hacks.  Some have to do with communication channels and making it feel like you are present even when you are not in the office.  Others are my folding bike and the amazing experience I get visiting European cities.  

So, I conclude with a promise of future bloggings with photos and insights.  I just don't have the time right now!

Some of my geographically distributed social network who are

unknown to my local friends. 

My cardboard cutout at the WMF office.

My folding bike -- waiting for my flight.  

VisualEditor and barriers to entry in Wikipedia

In this blog entry, I'm going to briefly cover a presentation that I gave recently as part of the Wikimedia Research Showcase.

The Newcomer Gauntlet. A theoretical diagram of barriers to entry in 

Wikipedia are depicted with the hypothesized effect of VisualEditor's

 reduction in technical literacy barrier highlighted in green. 

TL;DR: We ran an experiment where we gave a WYSIWYG editor to newly registered editors in Wikipedia and monitored the effect it had on their productivity.  We found that it didn't affect productivity either way.  I think that this is because the barriers to entry in Wikipedia primarily consist of social/motivational issues, so reducing the technical literacy barriers that VE targets did not have a meaningful effect.

The talk is embedded below.  My talk is first.  There's a second talk by some students looking at building a knowledge graph with Wikipedia and some google tech too.

Fitting hadoop streaming into my python workflow

I got something working in Hadoop (by reading the documentation[1]) that is going to change the way I work with Wikipedia text data.  In order to give you a sense for how excited I am, I'm going to need to talk to you about the way that I approach my work with large datasets. 

My python workflow

So, I try to do as much work as I can with input/output streams.  I'm a bit of a unix geek, so that means I'm thinking about standard-in and standard-out and writing my analytics code as though I were supplementing UNIX core utils.  This has nice computational practicalities and it allows you to stream operations together.

Computational practicalities

The worst bottleneck you're likely to encounter in the course of doing data analysis on large files is memory.  Most statistical environments specifically support in-memory analysis of datasets to the exclusion of streaming.  This means that, in order to get any work done, you need to first copy all of your data from disk to memory.  If your dataset is larger than the available memory, you'll likely end up crashing the machine you are working with -- or at least render it unusable until your process finally crashes.

On the other hand, python, perl and unix utilities (like cut, shuf, sort, sed, etc.) afford some powerful capability for working with datasets as streams, and therefore, dramatically reducing the memory footprint of a dataset.  As the diagram above makes apparent, if you can fit your data operation into a streaming job, then it doesn't matter how big your dataset is and you'll end up needing very little memory. 

Streaming operations together

I like Doug McIlroy's summary of the unix philosophy:

Write programs that do one thing and do it well. Write programs to work together. Write programs to handle text streams, because that is a universal interface.

By following these principles, you can build "operators" rather than stand-alone programs.  This allows you to write less code (which is a Good Thing™) and get more done.  Recently, I wrote a paper that required me to wrangle 12 different datasets from 7 different websites -- and being able to integrate my code with unix core utilities was a godsend.  Here's a line from my projects Makefile that I think captures the amazingness. 

(echo -e "user_id\tintertime\ttype";
bzcat datasets/originals/aol_search.tsv.bz2 |
tail -n+2 |

sort -k1,2 |
./intertimes |

sed -r "s/(.*)/\1\tapp view/" | things
shuf -n 100000) >
datasets/aol_search_intertime.sample.tsv

These lines achieve the following steps of a streaming job:

1. Print out new headers: user_id, intertime, type
2. Decompress the app_view.tsv.bz2 dataset
3. Trim off the original header line
4. Sort the dataset by the first two columns (user_id, timestamp)
5. Compute the time between events per user (this is my python)
6. Add a column of "app view" corresponding to the "type" header
7. Randomly sample 100000 observations
8. Write it all out to a new data file.  

In order to perform this complex operation over 28 million rows, I wrote ~30 lines of python code and put it in the middle of a few unix utilities that do most of the heavy lifting for me.  The processes took about 2 minutes and used a couple hundred MB of memory.  The random sample that we finally arrive at contains only 100k rows and will happily load into memory in about a second.  

Bringing it together with Hadoop streaming

If you've been living under a rock for the last 5 years or so, Hadoop is a framework for performing map/reduce operations that has become the industry standard.  Through Hadoop's streaming interface, I can make use of UNIXy streaming utilities to get my python work done faster.  Since I'm already thinking about my work as processing data one-row-at-a-time, map/reduce is not that much of a leap. '

However, there's one part of my streaming scripts that is very important to me, but I hadn't told you about it yet.  When I do behavioral analysis in Wikipedia, I often need to choose a dimension and process user actions in order over time.  A good example of actions in Wikipedia is an "edit".  There are two really interesting dimensions to process edits: page and user. 

The diagram above visualizes these two dimensions.  If we look at my edits over time, we are looking at the "user" dimension.  If we look at the article Anarchism's edits of time, we are looking at the "page" dimension.  In order to processed data based on these dimensions, I'll usually sort and partition the data.  Notabily, the designers of Wikipedia's XML dumps had this in mind when they put together their format.  They partition based on page and sort based on timestamp.  Yet, hadoop's native map/reduce strategy doesn't afford the ability to insist that I'll see a page's revisions or a user's edits together and in order.

Enter secondary sort.  Using  org.apache.hadoop.mapred.lib.KeyFieldBasedComparator and org.apache.hadoop.mapred.lib.KeyFieldBasedPartitioner, I can tell hadoop to sort and partition data before the "reduce" phase of the process.  The best part is, these guys are parameterized in the exact same way as UNIX sort! 

So, let's say you want to process a users activities over time.  Set the partitioner divide the data based on the user_id column and tell hadoop to sort based on the revision timestamp.  Here's what the call looks like for a dataset I'm working with right now.  (first column == user_id, second column == timestamp)

hadoop jar /usr/lib/hadoop-mapreduce/hadoop-streaming.jar \
    -D  mapreduce.output.fileoutputformat.compress=false \
    -D  mapreduce.input.fileinputformat.split.minsize=300000000 \
    -D  mapreduce.task.timeout=6000000 \
    -D  stream.num.map.output.key.fields=2 \
    -D  mapreduce.partition.keypartitioner.options='-k1,1n' \
    -D  mapred.output.key.comparator.class="org.apache.hadoop.mapred.lib.KeyFieldBasedComparator" \
    -D  mapreduce.partition.keycomparator.options='-k1,1n -k2,2n' \
    -files       intertime.py \
    -archives    'hdfs:///user/halfak/hadoopin/virtualenv.zip#virtualenv' \
    -input       /user/halfak/enwiki_20141025.*.tsv \
    -output      "/user/halfak/hadoopin/enwiki_intertime/$(date +%Y%m%d%H%M%S)" \
    -mapper      cat \
    -reducer     intertime.py \

    -partitioner org.apache.hadoop.mapred.lib.KeyFieldBasedPartitioner

So, I'm stoked.  This will change the way I do my work.

1. Which leaves a bit to be desired[2]

2. Finding which error code to google is worse

Wikipedia's socio-technology as compared to biology

I recently gave a presentation at the Wikimedia Foundation's Research Showcase where I compared Wikipedia to a paramecium.  I'll be turning that presentation into a blog post later, but in the meantime, I wanted to share a slide from that presentation that I thought was fun.

From left to right: a small village with about 150 people, the MediaWiki software and extensions, the crowd from Wikimania'14 and Wikipedia.

Here, I equate organelles to specialized sub-systems of the greater paramecium.  In the case of Wikipedia, specialized sub-systems tend to take the form of software.

See also:

• Video: https://www.youtube.com/watch?v=-We4GZbH3Iw#t=34m05s
• Slides: https://commons.wikimedia.org/wiki/File:Wikipedia_is_socio-technical_--_Research_Showcase_(October,_2014).pdf