Querying the GitHub archive with the ClickHouse playground#

ClickHouse#

ClickHouse is an open source column-oriented database, originally developed at Yandex but spun out into a separate, VC-funded company in 2021. It’s designed for big data analytical queries - in a similar space to HBase, BigQuery and DuckDB.

It turns out it can do that trick with HTTP range queries where you can point it at the URL to a Parquet or .native.zst file (ClickHouse native format, optionally compressed using Facebook Zstandard) and run queries without downloading the entire file first.

Exploring the Playground#

The ClickHouse Playground is a free hosted environment for trying out ClickHouse. You can access it here:

https://play.clickhouse.com/play?user=play

Try this query, taken from the ClickHouse Everything You Always Wanted To Know About GitHub (But Were Afraid To Ask) tutorial:

1
SELECT count() FROM github_events WHERE event_type = 'WatchEvent'

github_events#

The github_events table contains a copy of the GH Archive - a project that archives and makes available the public GitHub timeline, I think using data from the public events API. GH Archive then makes that data available as compressed newline-delimited JSON in this bucket. The archive stretches back to February 2011, and is constantly updated.

The ClickHouse demo table is continually updated with the latest archived data, by this script, which runs every 10 minutes.

You can do all sorts of fun stuff with it. Here’s my recent activity acrosss all of GitHub:

1
SELECT
2
  created_at,
3
  actor_login,
4
  repo_name,
5
  event_type,
6
  title
7
FROM
8
  github_events
9
WHERE
10
  actor_login = 'simonw'
11
  AND repo_name != 'simonw/disaster-data'
12
ORDER BY
13
  created_at DESC
14
LIMIT
15
  100

This link executes that query - note how it includes a base64 encoded copy of the SQL query following the # in the URL.

There are 77 tables total in the Playground instance - you can get a list of them like this:

1
SELECT database, name FROM system.tables

API access#

You can access the API via curl like this:

1
curl 'https://play.clickhouse.com/' \
2
  -X POST \
3
  -H 'Authorization: Basic cGxheTo=' \
4
  --data-raw $'
5
    SELECT created_at, actor_login, repo_name
6
    FROM github_events
7
    WHERE event_type = \'WatchEvent\'
8
    ORDER BY created_at DESC LIMIT 100'

This defaults to returning TSV without column headers, like this:

1
2023-01-01 03:59:59  Willmac16  nlohmann/json
2
2023-01-01 03:59:59  Samrose-Ahmed  Stebalien/stash-rs
3
2023-01-01 03:59:57  CodePromoter  aplus-framework/image

To get back data in JSON instead, add ?default_format=JSON to the URL. Here I’m piping that through jq to pretty print it:

1
curl 'https://play.clickhouse.com/?default_format=JSON' \
2
  -X POST \
3
  -H 'Authorization: Basic cGxheTo=' \
4
  --data-raw $'
5
    SELECT created_at, actor_login, repo_name
6
    FROM github_events
7
    WHERE event_type = \'WatchEvent\'
8
    ORDER BY created_at DESC LIMIT 1' | jq

Output:

1
{
2
  "meta": [
3
    {
4
      "name": "created_at",
5
      "type": "DateTime"
6
    },
7
    {
8
      "name": "actor_login",
9
      "type": "LowCardinality(String)"
10
    },
11
    {
12
      "name": "repo_name",
13
      "type": "LowCardinality(String)"
14
    }
15
  ],
16
  "data": [
17
    {
18
      "created_at": "2023-01-01 03:59:59",
19
      "actor_login": "Willmac16",
20
      "repo_name": "nlohmann/json"
21
    }
22
  ],
23
  "rows": 1,
24
  "rows_before_limit_at_least": 341429632,
25
  "statistics": {
26
    "elapsed": 0.925636889,
27
    "rows_read": 341540363,
28
    "bytes_read": 10567093585
29
  }
30
}

More format options are documented here.

CORS requests from JavaScript#

This pattern works for running queries from JavaScript. CORS is enabled - I pasted this into the Firefox DevTools console on https://www.example.com/ and it returned the results I expected:

1
r = await fetch("https://play.clickhouse.com/?user=play", {
2
  method: "POST",
3
  body: `SELECT
4
        created_at,
5
        event_type,
6
        actor_login,
7
        repo_name,
8
        number,
9
        title,
10
        body
11
      FROM
12
        github_events
13
      WHERE
14
         actor_login = 'simonw'
15
      ORDER BY
16
        created_at desc
17
      LIMIT
18
        100
19
      FORMAT JSON`,
20
});
21
d = await r.json();

Here I’m using FORMAT JSON at the end of the query itself, and passing the requested user as ?user=play rather than sending an Authorization header.