Convert pre-2015 archives to new event format

[igrigorik]

Pre-2015 we flattened the JSON events into a pre-defined schema and appended them to a global "timeline" table. This worked, but it has a few issues:

• The GH event format changed overtime, whereas our schema stayed the same
  • New fields were omitted to keep backwards-compat with older data
• One big table was convenient to query but became expensive due to large dataset

Post 2015 we moved to a different format that keeps the minimum number of well-defined fields as separate columns and stashes the rest into a JSON payload - see https://www.githubarchive.org/#bigquery for more details.

Ideally, we should reprocess the pre 2015 events to follow the same pattern. The ~rough work involved:

1. Downloading the raw pre-2015 hourly gzip's
2. Reformat each archive to use the new event format with fixed fields and JSON payload
3. Import above data into separate daily tables
4. Create appropriate rollups to simplify querying

In other words, an ETL.. with bulk of the work in (2) where all the data massaging needs to happen -- the caveat is that the format changed over time, so this needs to be done carefully to avoid losing data.

Update: we should also scrub email's from the new tables and files: user@site.com -> redacted@site.com

[ronaknnathani]

Thanks for the description Ilya! It makes things clearer.

I was thinking to start in reverse chronological order from 2014 to 2011 and act on schema based on the changes encountered. If you can share some details regarding how the data is imported in BigQuery tables, it would help in choosing the right tools and formats. I had this idea on the top of my head to use a schema serializer like Avro (https://avro.apache.org/) but I am not sure if that would be the best approach in regards to importing data into the BigQuery tables finally. Also, I assume I will need certain access rights to be able to interface with BigQuery.

Please let me know what you think and I would be glad to know if you have a specific approach to move forward with.

[igrigorik]

The schema we want is here: https://github.com/igrigorik/githubarchive.org/blob/master/bigquery/schema.js - the bulk of the data is in payload field. This corresponds to the GitHub format: https://developer.github.com/v3/activity/events/

You can see my current transform logic here: https://github.com/igrigorik/githubarchive.org/blob/master/bigquery/upload.rb#L52

The final format needs to be newline delimited JSON.. same format as above script generates.

If you're willing to generate the archives and you can upload them to some GCS bucket.. I can kick off the import into BQ.

[ronaknnathani]

Thanks for the references! I can generate the archives with newline delimited JSON and the required schema. I will let you know once they are ready to be uploaded.

[igrigorik]

@ronaknnathani that would be awesome! FWIW, incremental progress is good too -- we don't need to block on all the archives.

[ronaknnathani]

@igrigorik sure, I will move incrementally and keep you posted.

[harishvc]

👍

[hsborges]

👍

[arfon]

@ronaknnathani - how are you getting on with this? I'd love to help out with this too - I have a copy of the event data pre-2015 and was planning on looking at writing the logic to do this transformation next.

In other words, an ETL.. with bulk of the work in (2) where all the data massaging needs to happen -- the caveat is that the format changed over time, so this needs to be done carefully to avoid losing data.

@igrigorik I realize it's somewhat embarrassing to be asking this given my role/current employer 😊 but do you know how often this happened?

[arfon]

@igrigorik I realize it's somewhat embarrassing to be asking this given my role/current employer 😊 but do you know how often this happened?

Further to my last post, here are some of the changes I've stumbled upon during preliminary processing:

The PushEvent payload used to have a shas array containing the commit information (including the committer). This key later changed to commits.

Event display_login (https://github.com/igrigorik/githubarchive.org/blob/master/bigquery/upload.rb#L54) looks to be a relatively new thing. Older events only seem to have a login field.

@igrigorik - if you have a chance, would appreciated your feedback on the following:

• Sometimes events don't parse and this looks to be a GitHub thing: invalid bytes in UTF8 string brianmario/yajl-ruby#64 (comment) - did you end up just dropping those from the archive?
• What do we do when the actor is nil, for example when there are anonymous Gists: https://help.github.com/articles/creating-gists/#creating-an-anonymous-gist - do we just record these events with a blank actor field?

[igrigorik]

@arfon thanks for working on this! Before I get into the specifics, some high-level points:

1. My first attempt at this whole problem was to auto-generate a "flattened" schema for BigQuery. (Note: at the time, BigQuery did not support nested records, hence the flatten logic).
   • Pros: all fields are mapped to individual columns, which means you can do targeted (fast and cheap) selects, filters, etc.
   • Cons: we're locked into the schema.. If anything changes, we either have start a new table with updated schema, reimport all old data with new field as "null", or drop the new field and stick with old schema.
   • We ran with "drop new field" for a while.. with obvious downsides.
2. Second attempt, which is what we're doing now, takes a hybrid approach:
   • Extract "stable fields" into a predefined schema
   • Serialize payloads that are likely to change into JSON string, that can be parsed via JSON_EXTRACT..
   • Pro: you can still do fast and cheap selects on most popular fields, new fields can be added and are available immediately (no changes needed in the pipeline). Assuming "stable" fields are, actually stable :)
   • Cons: some fields are inside of the JSON blob, which requires an expensive parse.

We ran with 2 for ~19 months before display_login showed up. So, on balance it seems to have worked pretty well.. However, now that I think about it, I think we can improve things a bit:

1. We can freeze the top-level schema (same as now)
2. As part of the upload pipeline, we can validate the top-level fields and find/detect any that are not in the schema --> instead of dropping them, we can serialize them into a JSON object.. e.g "other": "json_string here". This way we never drop data.

(2) is a relatively simple fix on top of what we have currently.

Back to import pre 2015 data...

• Ideally, massage it into same top-level schema
  • Extract actor / repo / org / created_at / type / id
  • Push everything else into payload (or, we could even push the full record into it, including above fields)

Sometimes events don't parse and this looks to be a GitHub thing: brianmario/yajl-ruby#64 (comment) - did you end up just dropping those from the archive?

Hmm, I was under the impression that we resolved this (by fixing Yajl). Not the case?

What do we do when the actor is nil, for example when there are anonymous Gists: https://help.github.com/articles/creating-gists/#creating-an-anonymous-gist - do we just record these events with a blank actor field?

Yep. And that also reminds me... #123

[arfon]

@igrigorik - thanks for the additional context ⚡

We can freeze the top-level schema (same as now)

👍

As part of the upload pipeline, we can validate the top-level fields and find/detect any that are not in the schema --> instead of dropping them, we can serialize them into a JSON object.. e.g "other": "json_string here". This way we never drop data.

Sounds good. I'm thinking we could do with a small Ruby module that can be included in https://github.com/igrigorik/githubarchive.org/blob/master/bigquery/upload.rb that transforms the events into the schema (and adds any extra fields to the other field). This module should be able to handle all events (past and current) incase there's a reason to process all the events again sometime.

I've half-written this already while testing the parsing of these archival events so if it sounds like a good approach I'll keep pushing on that.

We ran with 2 for ~19 months before display_login showed up. So, on balance it seems to have worked pretty well.. However, now that I think about it, I think we can improve things a bit:

😄 I hadn't realized that was such a recent addition. BTW I've dug into this a little in the GitHub codebase and it looks like we are safe to drop from the actor key for now (but perhaps put in an other field as you suggest).

Hmm, I was under the impression that we resolved this (by fixing Yajl). Not the case?

I think it was fixed. It's just there seem to be some events in the tar files that aren't parseable by Yajl (it's pretty small numbers).

[igrigorik]

This module should be able to handle all events (past and current) incase there's a reason to process all the events again sometime... I've half-written this already while testing the parsing of these archival events so if it sounds like a good approach I'll keep pushing on that.

Awesome!

BTW I've dug into this a little in the GitHub codebase and it looks like we are safe to drop from the actor key for now (but perhaps put in an other field as you suggest).

Two options to explore here:

• (a) We can craft the the top-level ("frozen") fields and whatever is the remainder we serialize and stick into top-level "other" JSON-String field -- e.g. say tomorrow you guys add a new key under 'repo' and 'actor'. Our top level would stay the same, but then in 'other' we'd have --> "{repo: {'newthing': ...}, 'actor': {'othernewthing': ...}}".
• (b) or, we could put "other" under each object - e.g. actor record has 'other', record does too...

Personally, I think I'm leaning towards (a) -- simpler to grok and implement, I think.

Actually, nm, .. I think we want (a) because then we only need to update our top-level schema once with one extra field, instead of defining "other" on each record object.

Does this make sense?

[arfon]

Does this make sense?

Yep, that makes complete sense. BTW, here's the direction I've been heading with the event parser: https://gist.github.com/arfon/53c570d157e5b1ce166d68a87912e0ab .

(a) We can craft the the top-level ("frozen") fields and whatever is the remainder we serialize and stick into top-level "other" JSON-String field -- e.g. say tomorrow you guys add a new key under 'repo' and 'actor'. Our top level would stay the same, but then in 'other' we'd have --> "{repo: {'newthing': ...}, 'actor': {'othernewthing': ...}}".

👍 This is basically what the parse_field method does here.

BTW, the only major structural difference in events between 2012-2016 I'm handling is in the structure of the payload in the PushEvent (when scrubbing emails) which happens here. Does that sound about right?

If this seems like a good direction I'll write some tests based using some real events as fixtures.

[igrigorik]

https://gist.github.com/arfon/53c570d157e5b1ce166d68a87912e0ab .

That's looking great! Have a feeling it'll get a bit more complex once we start looking at older records though.. which had different format and such?

This is basically what the parse_field method does here.

Other should probably preserve the nested structure? Right now I think it'll just hoist any and all missing subfields into same top-level namespace (which can conflict too). Instead, I think we want to preserve the nested structure?

BTW, the only major structural difference in events between 2012-2016 I'm handling is in the structure of the payload in the PushEvent (when scrubbing emails) which happens here. Does that sound about right?

Ah, interesting. Are you finding that all the other fields match?