Analyzing a GitHub Events Dump With DuckDB and SQL

An events dump is the best sort of messy JSON. There is a lot of it, every line has the same envelope, and the part that varies is the payload, whose structure depends on the type of event. GitHub's public event feed has that shape, and so do the audit logs, product analytics streams and activity feeds of most systems. The questions people ask of such a dump are consistent: how many of each type, which things are busiest, what happened over time, and what does one particular kind of event contain.

I generated a dump with the structure of a GitHub-style feed (ten event types, an actor, a repository, a payload that varies by type) and worked through the questions in DuckDB. The data is synthetic: 300,000 events over about a day, made by a script whose payloads follow the public shape of each event type. The numbers describe my data, not GitHub's.

Load it with a full sample

create table ev as
select * from read_ndjson('ghevents.ndjson', sample_size=-1);

300,000 lines, 183 MB, 2.48 s. The sample_size=-1 is the setting that matters for this kind of file. DuckDB infers a column's type from a sample of the lines, and by default that sample is small. An event type that makes up 1 percent of the file might have a field that never appears in the sample, and then the field is silently missing from the table (or read as a different type). Reading everything for inference costs a second or two here and removes the risk.

The result is one struct for the payload:

payload struct("action" varchar, issue struct(number bigint, state varchar,
  labels struct("name" varchar)[], title varchar), ... )

It is the union of every field in every type, and a field that belongs to only one type is NULL for the others. That is convenient for queries and a bit misleading: payload.forkee exists for every row, and it is only meaningful for a ForkEvent. Always filter on type before reading type-specific fields.

The first questions

select type, count(*) n, round(100.0*count(*)/sum(count(*)) over (), 1) pct
from ev group by 1 order by 2 desc;

PushEvent               119989   40.0
WatchEvent               53731   17.9
IssueCommentEvent        36164   12.1
PullRequestEvent         27151    9.1
CreateEvent              17936    6.0
IssuesEvent              15049    5.0
ForkEvent                11987    4.0
DeleteEvent               9086    3.0
PullRequestReviewEvent    5857    2.0
ReleaseEvent              3050    1.0

Pushes and watches are over half of everything. For a repository ranking:

select repo.name, count(*) n from ev group by 1 order by 2 desc limit 5;

The top five had 812, 812, 809, 806 and 805 events. The spread between first and fifth is seven events, because my generator picks repositories uniformly. Real feeds are heavy tailed, with a few repositories accounting for a large share, and a ranking like this one tells you whether the data is skewed. If the top is within a percent of the fifth, check whether the sample is uniform before you read anything into it.

Time buckets are date_trunc on the string timestamp cast to a TIMESTAMP:

select date_trunc('hour', created_at::timestamp) h, count(*) from ev group by 1 order by 1;

Each hour had 12,000 events, which is the generator's constant rate. Real data has a daily rhythm, and the same query is how you find it. Cast the timestamp once, in a view, rather than in every query (extracting fields from a JSON string column in DuckDB).

Type-specific questions

Push events carry size (the number of commits) and a commits list:

select avg(payload.size), avg(len(payload.commits)), max(payload.size), max(len(payload.commits))
from ev where type = 'PushEvent';
-- 8.12 | 5.61 | 40 | 20

The average push had 8.12 commits but listed 5.61. The maximum push had 40 commits and the longest list held 20. My generator caps the list at 20. Event feeds commonly carry a bounded sample of each push, so check the documentation of the feed you use for its cap. So the count of commits has to come from size, not from the list:

select count(*) filter (where payload.size > len(payload.commits)) as truncated, count(*) as pushes
from ev where type = 'PushEvent';
-- 15068 | 119989

12.6 percent of pushes had more commits than the list showed. Counting len(payload.commits) across the data would under-report commit volume by a significant factor. This is the general trap with embedded arrays in events: the array may be a preview of the data, not the data. Compare a count field with the length of the list before you rely on either.

Pull requests have a three-state flag:

select payload.pull_request.merged merged, count(*) from ev where type='PullRequestEvent' group by 1;
-- false 9116 | NULL 9058 | true 8977

merged was true on 8,977 events, false on 9,116 and null on 9,058. A where merged = false leaves out the null rows, which a reader would call "not merged" without thinking. In SQL, NULL is not equal to anything, including false. Decide what null means in the source (unknown, not applicable, not yet) and write the predicate to match: merged is not true includes both false and null, merged is distinct from true is the portable spelling.

Arrays of structs unnest into rows:

select l.name, count(*)
from (select unnest(payload.issue.labels) l from ev where type = 'IssueCommentEvent')
group by 1 order by 2 desc;

Bug, docs and good first issue each appeared about 18,000 times. An issue with no labels contributes no rows to this query, so the sum is a count of labels, not of issues. If you want issues with their labels, keep the issue and join the labels, or aggregate back with list.

Two ways to read the same file

If the file's structure is not yet known, or changes from week to week, read each line as a JSON value and extract with paths:

create table evj as select * from read_json_objects('ghevents.ndjson', format='newline_delimited');

select json_extract_string(json, '$.type') t, count(*) from evj group by 1 order by 2 desc;
select sum(json_extract(json, '$.payload.size')::int) from evj
where json_extract_string(json, '$.type') = 'PushEvent';
-- 974889

Loading as plain JSON took 0.37 s, because nothing is inferred, and the json_extract sum ran in 0.07 s. The struct version of the same sum, sum(payload.size), gave 974,889 in 0.11 s. The answers are identical and the speeds are the same order. Start with the schema-free read when you do not know the file, and move to the typed table once you do. The typed version has the advantage that the engine checks types and the queries are easier to read; the path version has the advantage that it cannot disagree with the file about what the schema is.

Practical notes for real dumps

Real event archives are compressed and large. DuckDB reads .json.gz files directly, and you can pass a glob of hourly files to one read_ndjson call; a folder of a hundred files can be queried as one table (reading a whole folder of JSON files at once). A line over the default maximum object size (16 MB) makes the reader fail, and a single corrupt line stops the whole read unless ignore_errors=true is set, which is then silent about what it skipped; count the rows afterwards against wc -l (finding one bad line in a huge JSON Lines file).

Ids deserve care. The events' id fields are strings of digits, and the actor and repository ids are numbers that may exceed 2^53 in the wild. DuckDB reads them as 64-bit integers, so they are exact, but anything that goes through JavaScript will not be (JSON security mistakes in apps and pipelines shows the rounding).

Write the cleaned data to Parquet once, with typed columns, and run the repeated queries against that (converting JSON to Parquet with DuckDB). The raw dump stays as the source. For the formats and options see the DuckDB JSON overview, and the shape of the real feed is described in the GitHub events documentation. To read one event comfortably, paste it into the JSON formatter.

When a schema on read is the wrong way to look at events

You need to find one event. A grep for an id on the file is faster than any load, and jq filters a stream without a database.

The payloads differ so much that the union is mostly nulls. If ten types share only the envelope, query each type into its own table with its own typed columns and union the envelope.

The file is too big for one machine's disk or memory. Partition it, or use a warehouse that reads files in place.

You need exact reproduction of the bytes. A struct drops key order and unknown whitespace. For audit, keep the raw file and query a copy.