mixport

mixport utilizes Mixpanel's data export API to download event data and make it far easier to consume and store.

Mixpanel exposes this data as a bunch of JSON blobs in the form {"event": "EventName", "properties": {...}}, where the properties map contains all of the arbitrary dimensions attached to the data point. There isn't a defined schema, property keys and event types can come and go, data types can change. Such is JSON.

This does however make storing the data in a SQL database somewhat of a chore, since you can really only try to guess the valid types (I wrote and maintained this application as the precursor to mixport, believe me, this isn't fun or very logical).

mixport is really only concerned with the export portion of the problem, but can do some streaming transforms on the data to make it more digestible.

Building

Requires Go >= 1.3 to compile.

Using go get:

$ go get github.com/erik/mixport

Usage

First, let's set up a configuration file:

$ cp mixport.conf{.example,}
$ $EDITOR mixport.conf

There are several comments in the example configuration which should give a decent explanation of what's required and available to configure.

Let's start off by just downloading yesterday's data for all the products specified in the configuration:

$ ./mixport

That's it! If your configuration isn't in . or is named something other than mixport.conf, you need to specify it with -c path/to/config.

If you want to just export a specific subset of the products defined in the configuration, the --products flags will be useful:

$ ./mixport --products=Product1,Product2,...,ProductN

If you want to download data for a single day (that isn't yesterday), use the -d flag:

$ ./mixport -d 2013/12/31

If you want to download multiple days of data, try this:

$ ./mixport -r 2013/12/31-2014/02/06

And that's about all you need to know to get started.

For a full listing of command arguments available, use ./mixport --help.

Export formats

There are currently 3 exportable formats:

Schemaless CSV

This format is meant to ultimately be COPY friendly for SQL databases and gets around the problems mentioned above about data types and column inconsistency by being schemaless. The output is simply: event_id,key,value

So for example, a data point input of {"event": "Foo", "properties": {"bar": "baz", ...}} becomes this:

event_id,key,value
some_UUID,event,Foo
some_UUID,bar,baz
...

If you can insert this data into a SQL table where the event_id, key, and value columns are all VARCHAR (or equivalent). You can then GROUP BY the event_id to get a row.

This subverts a bunch of advantages to SQL, so make sure you know this is the best way.

Note that this exploded format does get pretty big on disk, and hugely benefits from GZIP compression.

CSV with columns

Like schemaless CSV, this format is intended to be COPY friendly for SQL databases. Unlike the previous format, however, this export requires that the columns must be explicitly specified for each event.

Instead of writing to a single file for each product, this export function will create an output file for each event of each product.

The configuration for this is in the form of a single JSON file, specified by the columns variable under the columns configuration section.

The format for this file should be a single map in the following format:

{
  "product1": {
    "event1": ["col1", "col2", "col3", ... ],
    ...
  },
  ...
}

As an example, let's say we have this configuration:

{
  "productA": {
    "foo": ["a", "b", "c"]
    "bar": ["a", "b"]
  }
}

Now we see the following series of events instances for productA:

{"event": "foo", "properties": {"a": 1, "c": 3}}
{"event": "bar", "properties": {"a": 1, "b": 2, "c": 3}}
{"event": "baz", "properties": {"a": 1, "b": 2, "c": 3}}

For foo, the "b" property is missing from the event data, so the column is assigned a null value:

a,b,c
1,,3

For bar, the "c" property is not explicitly listed as a column in the configuration, so it is not included in the output:

a,b
1,2

And because we don't have a definition for the baz event type, the event is dropped completely.

Flattened JSON

So as I've just explained, the input is in the form {"event": "Foo", "properties": {"bar": "baz", ...}}.

This export basically folds the 'properties' map up into the top level:

{"event": "Foo", "bar": "baz", ... }

It's possible to preserve the original hierarchy by writing a new (or modifying this) export writer, but I don't really see why you would want to.

Like the CSV export, this is compressed by GZIP very efficiently. 85-90% compression ratio is typical for data I've looked at.

Mixpanel to X without hitting disk

mixport can write to named pipes instead of regular files, which can be incredibly useful to avoid temporary files.

The basic formula is to set fifo=true in the [json] or [csv] sections of the configuration file and then just pretend that the named pipe has all of the data already written to it, like so:

$ ./mixport &
$ for f in /mixport/output/dir/*; do psql -c "COPY TABLE ... FROM $f" & done

If you want to pipe things to S3 without hitting disk, I've had success with the s3stream gem:

$ ./mixport &
$ for f in /mixport/output/dir/*; do s3stream store mybucket $f < $f & done

Neat.