2023-03-03

Tief verschachteltes JSON zerlegen, ein Vektor nach dem anderen

Laurens Kuiper

Wir haben diesen Blogbeitrag im Dezember 2024 an die geänderte JSON-Syntax von DuckDB angepasst.

DuckDB hat eine JSON-Erweiterung, die sich über SQL installieren und laden lässt:

INSTALL 'json';
LOAD 'json';

Die JSON-Erweiterung bietet verschiedene Funktionen zum Erzeugen, Lesen und Bearbeiten von JSON-Strings. Sie ähneln der JSON-Funktionalität anderer Datenbanken wie PostgreSQL und MySQL. Intern parst DuckDB JSON mit yyjson, einer hochperformanten JSON-Bibliothek in ANSI C. Vielen Dank an die yyjson-Autoren und Beitragenden!

Neben diesen Funktionen kann DuckDB JSON jetzt direkt lesen! Typen und Spaltennamen werden automatisch erkannt, die Werte im JSON in DuckDB-Vektoren gewandelt. Die automatische Schemaerkennung vereinfacht die Arbeit mit JSON stark, und nachfolgende Abfragen auf DuckDB-Vektoren sind deutlich schneller.

JSON automatisch mit DuckDB lesen

Seit Version 0.7.0 unterstützt DuckDB JSON-Tabellenfunktionen. Zur Demonstration lesen wir todos.json, eine falsche TODO-Liste mit 200 Einträgen (nur die ersten zwei sind gezeigt):

[
  {
    "userId": 1,
    "id": 1,
    "title": "delectus aut autem",
    "completed": false
  },
  {
    "userId": 1,
    "id": 2,
    "title": "quis ut nam facilis et officia qui",
    "completed": false
  },
  ...
]

Jeder TODO-Eintrag ist ein Element im JSON-Array; in DuckDB wollen wir eine Tabelle, in der jeder Eintrag eine Zeile ist. Das geht so einfach:

SELECT * FROM 'todos.json' LIMIT 5;

Welcher Nutzer die meisten TODOs erledigt hat, ist ebenso einfach:

SELECT userId, sum(completed::INTEGER) total_completed
FROM 'todos.json'
GROUP BY userId
ORDER BY total_completed DESC
LIMIT 1;

Unter der Haube erkennt DuckDB die Dateiendung .json in 'todos.json' und ruft stattdessen read_json('todos.json') auf. Die Funktion ähnelt unserem read_csv, das Spaltennamen und Typen für CSV-Dateien automatisch ableitet.

Wie unsere anderen Tabellenfunktionen kann read_json mehrere Dateien über eine Liste lesen, z. B. read_json(['file1.json', 'file2.json']), oder per Globbing, z. B. read_json('file*.json'). DuckDB liest mehrere Dateien parallel.

Newline-delimited JSON

Nicht jedes JSON entspricht dem Format von todos.json, einem Array von „Records“. Newline-delimited JSON, oder NDJSON, legt jede Zeile in eine neue Zeile. DuckDB kann dieses Format auch lesen (und schreiben!). Zuerst schreiben wir unsere TODO-Liste als NDJSON:

COPY (SELECT * FROM 'todos.json') to 'todos2.json';

Wieder erkennt DuckDB das Suffix .json in der Ausgabedatei und schließt automatisch auf (FORMAT json). Die erzeugte Datei sieht so aus (nur die ersten zwei Records):

{"userId":1,"id":1,"title":"delectus aut autem","completed":false}
{"userId":1,"id":2,"title":"quis ut nam facilis et officia qui","completed":false}
...

DuckDB kann diese Datei genau wie die ursprüngliche lesen:

SELECT * FROM 'todos2.json';

Ist die JSON-Datei newline-delimited, kann DuckDB das Lesen parallelisieren. Das geht über read_ndjson oder den Parameter records = true an read_json:

SELECT * FROM read_ndjson('todos2.json');
SELECT * FROM read_json('todos2.json', records = true);

Sie können auch records = auto setzen, damit DuckDB selbst erkennt, ob die Datei newline-delimited ist.

Andere JSON-Formate

Wird read_json direkt genutzt, lässt sich das JSON-Format über den Parameter format angeben. Standard ist 'auto': DuckDB leitet ab, um welche Art JSON es geht. Das erste format ist 'array', das zweite 'nd'. So angegeben:

SELECT * FROM read_json('todos.json', format = 'array');
SELECT * FROM read_json('todos2.json', format = 'nd');

Ein weiteres unterstütztes Format ist unstructured. Records müssen dann kein JSON-Objekt sein, sondern können auch ein JSON-Array, ein String oder alles in JSON Erlaubte sein.

Manuelle Schemas

Vielleicht ist Ihnen auch der Parameter auto_detect aufgefallen. Er sagt DuckDB, das Schema abzuleiten, also Namen und Typen der zurückgegebenen Spalten zu bestimmen. Die lassen sich auch manuell angeben:

SELECT *
FROM read_json(
    'todos.json',
    columns = {userId: 'INTEGER', id: 'INTEGER', title: 'VARCHAR', completed: 'BOOLEAN'},
    format = 'array'
);

Sie müssen nicht alle Felder angeben, nur die, die Sie interessieren:

SELECT *
FROM read_json(
    'todos.json',
    columns = {userId: 'INTEGER', completed: 'BOOLEAN'},
    format = 'array'
);

Jetzt, da wir die neuen DuckDB-JSON-Tabellenfunktionen kennen, tauchen wir in etwas Analyse ein!

Beispiele mit dem GitHub Archive

GH Archive zeichnet die öffentliche GitHub-Timeline auf, archiviert sie und macht sie für weitere Analyse zugänglich. Jede Stunde wird eine gzip-komprimierte, newline-delimited JSON-Datei mit allen öffentlichen GitHub-Events hochgeladen. Ich habe einen ganzen Tag (2023-02-08) per wget heruntergeladen und die 24 Dateien (beginnend mit 2023-02-08-0.json.gz) im Verzeichnis gharchive_gz abgelegt. Das Archiv des ganzen Tages gibt es als gharchive-2023-02-08.zip:

Die Daten sind komprimiert:

du -sh gharchive_gz

2.3G  gharchive_gz

Dekomprimiert sind das mehr als 18 GB JSON für einen Tag GitHub-Aktivität.

gunzip -dc gharchive_gz/* | wc -c

18396198934

Um ein Gefühl für die Daten zu bekommen, führen wir folgende Abfrage aus:

SELECT json_group_structure(json)
FROM (
    SELECT *
    FROM read_ndjson_objects('gharchive_gz/*.json.gz')
    LIMIT 2048
);

Hier nutzen wir unsere Funktion read_ndjson_objects, die die JSON-Objekte in der Datei als rohes JSON liest, also als Strings. Die Abfrage liest die ersten 2048 JSON-Records aus den Dateien im Verzeichnis gharchive_gz und beschreibt die Struktur. Sie können die JSON-Dateien von GH Archive auch direkt über DuckDBs httpfs-Erweiterung abfragen; wir fragen die Dateien aber mehrfach ab, daher ist Herunterladen hier besser.

Tipp Im CLI-Client macht .mode line die Ausgabe lesbarer.

Das Ergebnis habe ich mit einem Online-JSON-Formatter und -Validator formatiert:

{
   "id":"VARCHAR",
   "type":"VARCHAR",
   "actor":{
      "id":"UBIGINT",
      "login":"VARCHAR",
      "display_login":"VARCHAR",
      "gravatar_id":"VARCHAR",
      "url":"VARCHAR",
      "avatar_url":"VARCHAR"
   },
   "repo":{
      "id":"UBIGINT",
      "name":"VARCHAR",
      "url":"VARCHAR"
   },
   "payload":{"..."},
   "public":"BOOLEAN",
   "created_at":"VARCHAR",
   "org":{
      "id":"UBIGINT",
      "login":"VARCHAR",
      "gravatar_id":"VARCHAR",
      "url":"VARCHAR",
      "avatar_url":"VARCHAR"
   }
}

"payload" habe ich weggelassen, weil es tief verschachteltes JSON ist und die formatierte Struktur über 1000 Zeilen braucht!

Wie viele Records genau? Zählen wir mit DuckDB:

SELECT count(*) AS count
FROM 'gharchive_gz/*.json.gz';

Das sind rund 4,4 Mio. tägliche Events, fast 200.000 pro Stunde. Die Abfrage braucht auf meinem Laptop, einem MacBook Pro 2020 mit M1 und 16 GB Speicher, etwa 7,3 Sekunden. Das ist die Zeit für das Entpacken von GZIP und das Parsen jedes JSON-Records.

Um zu sehen, wie viel Zeit das Entpacken von GZIP in der Abfrage kostet, habe ich ein Verzeichnis gharchive mit denselben Daten unkomprimiert angelegt. Dieselbe Abfrage auf den unkomprimierten Daten braucht etwa 5,4 Sekunden, fast 2 Sekunden schneller. Wir sind schneller, lesen aber über 18 GB vom Speicher statt 2,3 GB komprimiert. Der Vergleich hängt also stark von der Speichergeschwindigkeit ab. Ich behalte die Daten lieber komprimiert.

Nebenbei: Die Geschwindigkeit dieser Abfrage zeigt, wie schnell yyjson ist!

Welche Event-Typen stecken in den GitHub-Daten?

SELECT type, count(*) AS count
FROM 'gharchive_gz/*.json.gz'
GROUP BY type
ORDER BY count DESC;

Diese Abfrage braucht etwa 7,4 Sekunden, kaum mehr als count(*). Sobald alles entpackt und geparst ist, ist die Analyse also sehr schnell.

Der häufigste Event-Typ ist das PushEvent – über die Hälfte aller Events, wenig überraschend: Leute pushen committeten Code nach GitHub. Am seltensten ist das GollumEvent, unter 1 %: Anlegen oder Aktualisieren einer Wiki-Seite.

Wollen wir dieselben Daten mehrfach analysieren, ist jedes Mal Entpacken und Parsen überflüssig und bremst. Stattdessen können wir eine DuckDB-Tabelle anlegen:

CREATE TABLE events AS
    SELECT * EXCLUDE (payload)
    FROM 'gharchive_gz/*.json.gz';

Das dauert etwa 9 Sekunden bei einer In-Memory-Datenbank. Bei einer On-Disk-Datenbank etwa 13 Sekunden, Ergebnisgröße 444 MB. On-Disk stellt DuckDB sicher, dass die Tabelle persistent ist, und wendet allerlei Kompression an. Das Feld payload haben wir vorerst mit der praktischen Klausel EXCLUDE ignoriert.

Was wir gelesen haben, zeigt DuckDB mit DESCRIBE:

DESCRIBE SELECT * FROM events;

Das ergibt:

Die Felder "actor", "repo" und "org" – JSON-Objekte – wurden zu DuckDB-Structs. Die Spalte "id" war im Original-JSON ein String, DuckDBs automatische Typerkennung hat sie zu BIGINT gemacht. DuckDB erkennt in JSON-Strings außerdem einige DATE/TIMESTAMP-Formate sowie TIME und UUID.

Die Tabelle können wir jetzt wie jede andere DuckDB-Tabelle analysieren! Wie viel Aktivität gab es im GitHub-Repository duckdb/duckdb an diesem Tag:

SELECT type, count(*) AS count
FROM events
WHERE repo.name = 'duckdb/duckdb'
GROUP BY type
ORDER BY count DESC;

Das ist viel Pull-Request-Aktivität! Das heißt nicht, dass an diesem Tag 35 Pull Requests eröffnet wurden; Aktivität innerhalb eines Pull Requests zählt mit. Suchen wir die Pull Requests dieses Tages, sind es nur 15. Mehr als sonst, weil die meisten DuckDB-Entwickler Bugs für das Release 0.7.0 behoben.

Wer war am aktivsten:

SELECT actor.login, count(*) AS count
FROM events
WHERE repo.name = 'duckdb/duckdb'
  AND type = 'PullRequestEvent'
GROUP BY actor.login
ORDER BY count desc
LIMIT 5;

Wie erwartet war Mark Raasveldt (Mytherin, Mitgründer von DuckLabs) am aktivsten! Meine Aktivität (lnkuiper, Software Engineer bei DuckLabs) taucht ebenfalls auf.

Inkonsistente JSON-Schemas behandeln

Bisher haben wir das "payload" der Events ignoriert. Der Inhalt dieses Feldes hängt vom Event-Typ ab. Wie sie sich unterscheiden, zeigt:

SELECT json_group_structure(payload) AS structure
FROM (
    SELECT *
    FROM read_json(
        'gharchive_gz/*.json.gz',
        columns = {
            id: 'BIGINT',
            type: 'VARCHAR',
            actor: 'STRUCT(id UBIGINT,
                          login VARCHAR,
                          display_login VARCHAR,
                          gravatar_id VARCHAR,
                          url VARCHAR,
                          avatar_url VARCHAR)',
            repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
            payload: 'JSON',
            public: 'BOOLEAN',
            created_at: 'TIMESTAMP',
            org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
        },
        records = true
    )
    WHERE type = 'WatchEvent'
    LIMIT 2048
);

Das Feld "payload" ist bei Events vom Typ WatchEvent einfach. Wechseln wir den Typ auf PullRequestEvent, kommt eine JSON-Struktur von über 500 Zeilen, formatiert mit einem JSON-Formatter. Wir wollen nicht alle Felder durchsehen, die automatische Schemaerkennung würde sie aber alle holen. Stattdessen können wir die Struktur der Felder, die uns interessieren, manuell angeben. DuckDB überspringt die anderen Felder. Ein anderer Ansatz: "payload" als DuckDB-JSON-Typ speichern und zur Query-Zeit parsen (siehe das Beispiel weiter unten!).

Die JSON-Struktur des "payload" von PullRequestEvents habe ich auf das reduziert, was mich wirklich interessiert:

{
   "action":"VARCHAR",
   "number":"UBIGINT",
   "pull_request":{
      "url":"VARCHAR",
      "id":"UBIGINT",
      "title":"VARCHAR",
      "user":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "body":"VARCHAR",
      "created_at":"TIMESTAMP",
      "updated_at":"TIMESTAMP",
      "assignee":{
         "login":"VARCHAR",
         "id":"UBIGINT",
      },
      "assignees":[
         {
            "login":"VARCHAR",
            "id":"UBIGINT",
         }
      ],
  }
}

Technisch ist das kein gültiges JSON, weil nachgestellte Kommas drin sind. In DuckDB versuchen wir aber, nachgestellte Kommas wo möglich zu erlauben, auch in JSON!

Das können wir in den Parameter columns von read_json stecken, müssen es aber zuerst in einen DuckDB-Typ wandeln. Ich bin faul und lasse das lieber DuckDB machen:

SELECT typeof(
    json_transform('{}', '{
        "action":"VARCHAR",
        "number":"UBIGINT",
        "pull_request":{
          "url":"VARCHAR",
          "id":"UBIGINT",
          "title":"VARCHAR",
          "user":{
              "login":"VARCHAR",
              "id":"UBIGINT",
          },
          "body":"VARCHAR",
          "created_at":"TIMESTAMP",
          "updated_at":"TIMESTAMP",
          "assignee":{
              "login":"VARCHAR",
              "id":"UBIGINT",
          },
          "assignees":[
              {
                "login":"VARCHAR",
                "id":"UBIGINT",
              }
          ],
        }
    }')
);

Zurück kommt ein DuckDB-Typ, den wir in unsere Funktion stecken können! Weil wir das Schema nicht automatisch erkennen, müssen wir timestampformat angeben, damit die Zeitstempel stimmen. Der Schlüssel "user" muss in Anführungszeichen, weil er in SQL reserviert ist:

CREATE OR REPLACE TABLE pr_events AS
    SELECT *
    FROM read_json(
        'gharchive_gz/*.json.gz',
        columns = {
            id: 'BIGINT',
            type: 'VARCHAR',
            actor: 'STRUCT(id UBIGINT,
                          login VARCHAR,
                          display_login VARCHAR,
                          gravatar_id VARCHAR,
                          url VARCHAR,
                          avatar_url VARCHAR)',
            repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
            payload: 'STRUCT(
                        action VARCHAR,
                        number UBIGINT,
                        pull_request STRUCT(
                          url VARCHAR,
                          id UBIGINT,
                          title VARCHAR,
                          "user" STRUCT(
                            login VARCHAR,
                            id UBIGINT
                          ),
                          body VARCHAR,
                          created_at TIMESTAMP,
                          updated_at TIMESTAMP,
                          assignee STRUCT(login VARCHAR, id UBIGINT),
                          assignees STRUCT(login VARCHAR, id UBIGINT)[]
                        )
                      )',
            public: 'BOOLEAN',
            created_at: 'TIMESTAMP',
            org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
        },
        format = 'newline_delimited',
        records = true,
        timestampformat = '%Y-%m-%dT%H:%M:%SZ'
    )
    WHERE type = 'PullRequestEvent';

Die Abfrage braucht etwa 36 Sekunden mit einer On-Disk-Datenbank (Größe 478 MB) und 9 Sekunden In-Memory. Wenn die Einfügereihenfolge egal ist, beschleunigt diese Einstellung:

SET preserve_insertion_order = false;

Mit dieser Einstellung braucht die Abfrage etwa 27 Sekunden On-Disk und 8,5 Sekunden In-Memory. Der Unterschied On-Disk/In-Memory ist hier groß, weil DuckDB deutlich mehr Daten komprimieren und persistieren muss.

Jetzt können wir Pull-Request-Events analysieren! Die maximale Zahl von Assignees:

SELECT max(length(payload.pull_request.assignees)) AS max_assignees
FROM pr_events;

Das sind viele Leute an einem einzelnen Pull Request!

Wer am häufigsten zugewiesen wurde:

WITH assignees AS (
    SELECT payload.pull_request.assignee.login AS assignee
    FROM pr_events
    UNION ALL
    SELECT unnest(payload.pull_request.assignees).login AS assignee
    FROM pr_events
)
SELECT assignee, count(*) AS count
FROM assignees
WHERE assignee NOT NULL
GROUP BY assignee
ORDER BY count DESC
LIMIT 5;

Das sind viele Zuweisungen – ich vermute allerdings Duplikate.

Als JSON speichern und zur Query-Zeit parsen

Das JSON-Schema von "payload" anzugeben war hilfreich: Wir konnten direkt analysieren, was da ist, und Folgeabfragen sind viel schneller. Bei komplexem Schema kann das aber umständlich sein. Wollen Sie das Schema eines Feldes nicht angeben, setzen Sie den Typ auf 'JSON':

CREATE OR REPLACE TABLE pr_events AS
    SELECT *
    FROM read_json(
        'gharchive_gz/*.json.gz',
        columns = {
            id: 'BIGINT',
            type: 'VARCHAR',
            actor: 'STRUCT(id UBIGINT,
                          login VARCHAR,
                          display_login VARCHAR,
                          gravatar_id VARCHAR,
                          url VARCHAR,
                          avatar_url VARCHAR)',
            repo: 'STRUCT(id UBIGINT, name VARCHAR, url VARCHAR)',
            payload: 'JSON',
            public: 'BOOLEAN',
            created_at: 'TIMESTAMP',
            org: 'STRUCT(id UBIGINT, login VARCHAR, gravatar_id VARCHAR, url VARCHAR, avatar_url VARCHAR)'
        },
        format = 'newline_delimited',
        records = true,
        timestampformat = '%Y-%m-%dT%H:%M:%SZ'
    )
    WHERE type = 'PullRequestEvent';

Damit wird "payload" als JSON-String geladen, und wir können DuckDBs JSON-Funktionen bei der Abfrage nutzen. Zum Beispiel:

SELECT DISTINCT payload->>'action' AS action, count(*) AS count
FROM pr_events
GROUP BY action
ORDER BY count DESC;

Der Pfeil ->> ist Kurzform für unsere Funktion json_extract_string. Das ganze Feld "payload" als Spalte vom Typ JSON anzulegen ist nicht der effizienteste Weg nur zum Feld "action" – das Beispiel zeigt vor allem die Flexibilität von read_json. Die Abfrage ergibt:

Nur wenige Pull Requests wurden also wieder geöffnet.

Fazit

DuckDB soll ein leicht nutzbares Werkzeug sein, das allerlei Datenformate liest. Im Release 0.7.0 haben wir das Lesen von JSON ergänzt. JSON kommt in vielen Formaten und Schemas. DuckDBs reiche Unterstützung für verschachtelte Typen (LIST, STRUCT) erlaubt es, JSON vollständig ins spaltenorientierte Format zu „zerlegen“ – für effizientere Analyse.

Wir sind gespannt, was Sie von der neuen JSON-Funktionalität halten. Fragen oder Vorschläge gern auf Discord oder GitHub!