2025-01-10

Vertikales Stapeln, wie das relationale Modell es meint: UNION ALL BY NAME

Alex Monahan

Überblick

Schon mal von SQLs Schlüsselwort CORRESPONDING gehört? Ich auch nicht! Es steht mindestens seit 1992 im SQL-Standard, und fast niemand hat es implementiert! CORRESPONDING war ein Versuch, einen Fehler in SQL zu beheben – und ist gescheitert. Es ist Zeit, dass SQL beim Stapeln von Daten zu den Wurzeln des relationalen Modells zurückkehrt. Drehen wir die Uhren auf 1969 zurück…

Sie haben gerade Ihren eigenen Ford Mustang Boss 302 abgeholt und driften an jeder Ecke, um zur Bibliothek zu kommen und den neuesten Forschungsbericht von IBM von Edgar Codd zu lesen. (Brauchen wir eine Netflix-Serie über Datenbanken?) Beim Lesen dieses Berichts, in reichlich Karo, gewinnen Sie eine entscheidende Einsicht: Daten sollten als ungeordnete Mengen behandelt werden! (Technisch Multimengen – Duplikate sind überall…) Zeilen sollten ungeordnet behandelt werden, und Spalten auch. Das relationale Modell ist der Weg. Jede Sprache auf dem relationalen Modell sollte diese Kernprinzipien unbedingt befolgen.

Ein paar Jahre später lernen Sie SQL kennen, und es wirkt wie eine ziemlich coole Idee. Deklarativ, relational – nichts von diesem Ordnung-erhalten-Geschäft. Sie wollen sich schließlich nicht an eine Ordnung binden. Was, wenn Sie Ihre Meinung ändern, wie Sie Ihre Daten abfragen? Mengen sind der beste Weg, über diese Dinge nachzudenken.

Mehr Zeit vergeht, und dann müssen Sie Daten in SQL stapeln. Sollte einfach genug sein – ich nehme einfach zwei Tabellen und staple sie, und die entsprechenden Attribute werden einander zugeordnet. Keine Sorge um Ordnung, und schon gar nicht darum, dass die Relationen genau gleich breit sind.

Warte. Das kann nicht stimmen.

Ich muss die Reihenfolge meiner Spalten genau richtig haben? Und ich muss in beiden Relationen genau dieselbe Zahl von Spalten haben? Haben diese SQL-Leute Codd vergessen??

Ein paar Jahrzehnte später macht DuckDB das Stapeln in SQL wieder richtig groovy.

Vertikales Stapeln wieder groovy machen

Zusätzlich zu den klassischen Operatoren UNION und UNION ALL ergänzt DuckDB sowohl UNION BY NAME als auch UNION ALL BY NAME. Diese stapeln mehrere Relationen (z. B. SELECT-Statements) vertikal, indem sie die Namen der Spalten unabhängig von ihrer Reihenfolge zuordnen. Als Beispiel liefern wir die Spalten a und b in falscher Reihenfolge und führen sogar die völlig neue Spalte c ein – das Stapeln gelingt trotzdem:

SELECT
    42 AS a,
    'woot' AS b

UNION ALL BY NAME

SELECT
    'woot2' AS b,
    9001 AS a,
    'more wooting' AS c;

Jede Spalte, die nicht in allen Relationen vorkommt, wird an den fehlenden Stellen mit NULL gefüllt.

Diese Fähigkeit öffnet eine Reihe nützlicher Muster, die Flexibilität bringen und Zeit sparen. Einige Beispiele:

• Datensätze mit unterschiedlicher Spaltenreihenfolge stapeln
• Neue Spalten zu einer Analyse hinzufügen, aber nur für einen Teil der Zeilen
• Völlig unabhängige Datensätze in ein einziges Resultset kombinieren
  • Das ist nützlich, wenn Ihre IDE, Ihr BI-Tool oder Ihre API nur ein einziges Resultset auf einmal zurückgeben kann, Sie aber mehrere Datensätze ansehen müssen

DuckDB hat diese Fähigkeit seit August 2022, aber Performance und Skalierbarkeit dieses Features wurden kürzlich stark verbessert! Siehe das Ende des Beitrags für einige Mikro-Benchmarks.

UNION vs. UNION ALL

Nutzt man nur das Schlüsselwort UNION, werden Duplikate beim Stapeln entfernt. Mit UNION ALL sind Duplikate erlaubt, und das Stapeln erfolgt ohne zusätzliche Verarbeitung.

Leider haben wir Codd diesen verwirrenden Teil zu verdanken! Wäre UNION ALL doch der Standard… Typischerweise ist UNION ALL (und sein neues Gegenstück UNION ALL BY NAME!) das gewünschte Verhalten, weil es die Eingaberelationen treu wiedergibt, nur gestapelt. Das ist auch performanter, weil die Deduplizierung bei UNION bei großen Datensätzen ziemlich zeitintensiv sein kann. Und schließlich erhält UNION ALL die ursprüngliche Zeilenreihenfolge.

Mehrere Dateien lesen

Diese Spaltenzuordnung wird besonders nützlich, wenn man Daten aus mehreren Dateien mit unterschiedlichen Schemas abfragt. DuckDB bietet einen booleschen Parameter union_by_name in den Tabellenfunktionen zum Einlesen externer Flachdateien:

• read_csv
• read_json
• read_parquet

Um mehrere Dateien zu lesen, kann DuckDB Glob-Muster im Dateipfad-Parameter nutzen (oder eine Dateiliste oder eine Liste von Glob-Mustern!). Könnten diese Dateien unterschiedliche Schemas haben, erlaubt union_by_name=True das Lesen und Stapeln! Spalten, die in einer bestimmten Datei nicht vorkommen, werden mit NULL-Werten gefüllt. Zum Beispiel:

COPY (SELECT 'Star' AS col1) TO 'star.parquet';
COPY (SELECT 'Wars' AS col2) TO 'wars.parquet';

FROM read_parquet(
    ['star.parquet', 'wars.parquet'],
    union_by_name = true);

Wenn Ihre Dateien unterschiedliche Schemas haben und Sie das nicht erwartet haben, schlagen DuckDBs freundliche Fehlermeldungen den Parameter union_by_name vor! Auswendiglernen ist nicht nötig:

If you are trying to read files with different schemas, try setting union_by_name=True

Data Lakes

Schemaänderungen über die Zeit sind in Data Lakes sehr üblich, deshalb öffnet das viele zusätzliche Nutzungen für DuckDB in diesen Umgebungen. Der Nebeneffekt dieses Features: Sie dürfen Ihre Data-Lake-Schemas jetzt frei ändern! Mehr Attribute über die Zeit zum Data Lake hinzuzufügen ist jetzt schmerzlos – DuckDB ist bereit für die Analyse!

DuckDBs Erweiterungen zum Lesen von Lakehouse-Tabellenformaten wie Delta und Iceberg behandeln Schema Evolution in den eigenen Metadaten der Formate, deshalb ist union_by_name nicht nötig.

Daten nach Name einfügen

Ein weiterer Anwendungsfall für das vertikale Stapeln von Daten ist das Einfügen in eine bestehende Tabelle. Die DuckDB-Syntax INSERT INTO ⟨my_table⟩ BY NAME{:.language-sql .highlight} bietet dieselbe Flexibilität, Spalten nach Name statt nach Position anzusprechen. Damit können Sie die einzufügenden Daten in beliebiger Spaltenreihenfolge und sogar nur mit einer Teilmenge der Spalten liefern. Zum Beispiel:

CREATE TABLE year_info (year INTEGER, status VARCHAR);

INSERT INTO year_info BY NAME
    SELECT
        'The planet made it through' AS status,
        2024 AS year;

INSERT INTO year_info BY NAME
    SELECT
        2025 AS year;

FROM year_info;

Der vorherige Ansatz war, eine zusätzliche Klausel anzugeben, die die Liste der hinzuzufügenden Spalten in derselben Reihenfolge wie der Datensatz spezifiziert. Das verlangt aber, dass Reihenfolge und Zahl der Spalten von vornherein bekannt sind, statt dynamisch bestimmt zu werden. In vielen Fällen müssen Spalten auch an zwei Stellen angegeben werden: im INSERT-Statement und im SELECT-Statement, das die Daten erzeugt. Den weisen Rat „Don’t Repeat Yourself“ zu ignorieren, hat in meinem eigenen Code zu mehr als ein paar unbeabsichtigten Folgen geführt… Es ist immer schöner, eine einzige Stelle zum Editieren zu haben, statt Dinge synchron halten zu müssen!

Die Inspirationen für UNION ALL BY NAME

Andere Systeme und Communities haben die Herausforderungen des Stapelns unordentlicher Daten seit vielen Jahren angegangen. DuckDB nimmt Inspiration von ihnen und bringt ihre Verbesserungen zurück nach SQL!

Die direkteste Inspiration ist die Funktion Pandas concat. Sie wurde im Januar 2012 ergänzt und unterstützte von Anfang an das Hinzufügen neuer Spalten. Pandas ist unglaublich weit verbreitet und ein wesentlicher Beitrag zur heutigen Popularität von Python. Diese Fähigkeit nach SQL zu bringen, kann ihre Wirkung über Python hinaus in die anderen Sprachen erweitern, die DuckDB unterstützt (Java, Node.js, Go, Rust usw.). Datenbanken sollten von Dataframes lernen!

PySpark hat die Funktion unionByName 2018 ergänzt und die Fähigkeit, das Hinzufügen neuer Spalten zu behandeln, in Version 3.1 im März 2021. Das ist eine weitere Option für Pythonistas, trägt aber die Anforderung eines Spark-Clusters und dessen Overhead.

SQLs Klausel UNION hatte das Schlüsselwort CORRESPONDING mindestens seit 1992 (!), ihr fehlt aber entscheidend die Fähigkeit, neue oder fehlende Spalten zu behandeln. Deshalb ist sie für Schema Evolution nutzlos.

Wir hoffen, andere SQL-Engines zu inspirieren, „freundlicher“ zu werden und diese Flexibilität zu erlauben!

Verbesserte Performance in DuckDB 1.1

DuckDB unterstützt UNION ALL BY NAME seit 2022, aber Version 1.1 brachte signifikante Skalierungs- und Performance-Verbesserungen. Dieses Feature war früher ein „wenn man muss“-Ansatz, kann jetzt aber breiter genutzt werden!

Die erste Änderung senkte den Speicherverbrauch beim Lesen mehrerer Dateien über das Netzwerk mit union_by_name. Das bringt Skalierungsvorteile beim Abfragen aus Cloud-Object-Storage wie S3, besonders wenn die Dateien im Verhältnis zum verfügbaren Speicher groß sind.

Die zweite Änderung war, Reads über Dateien zu parallelisieren, wenn union_by_name genutzt wird. Das liefert erwartungsgemäß eine dramatische Performance-Verbesserung (~6× im Mikrobenchmark im PR).

Mikro-Benchmark

Dieser Mikro-Benchmark ist eine Reproduktion der Arbeit von Daniel Beach (@DataEngDude) in diesem Beitrag. Dank an Daniel für die Erlaubnis, seinen Benchmark für diesen Beitrag zu nutzen!

Der Benchmark verlangt das Lesen von 16 GB CSV-Dateien auf S3 mit wechselnden Schemas auf einer Cloud-Instanz mit 4 GB Speicher. Die Absicht: große Datensätze auf kleiner Commodity-Hardware zu verarbeiten (ein Anwendungsfall, in dem wir DuckDB hilfreich sehen wollen!). Der Originalbeitrag nutzt Linode; für diesen Beitrag haben wir die ähnlichste AWS-Instanz mit derselben Speichermenge gewählt (c5d.large).

Wir nutzen zwei Quartale CSV-Dateien aus dem Backblaze-Datensatz (2023 Q2 und 2023 Q3), die in einem S3-Bucket liegen.

Ich habe die Query von hier leicht geändert, um die Option ignore_errors = true zu entfernen. Der Benchmark nutzte weiter Python; ich zeige hier nur das SQL für bessere Syntaxhervorhebung:

CREATE OR REPLACE VIEW metrics AS
    SELECT
        date,
        sum(failure) AS failures
    FROM read_csv_auto('⟨s3_path⟩/*.csv', union_by_name = true)
    GROUP BY date;

COPY metrics TO '⟨s3_path⟩/results/results.csv';

Mit einer 4-GB-Instanz und einer älteren DuckDB-Version (1.0.0) kann ich die Out-of-Memory-Fehler reproduzieren, die Daniel hatte. Upgrade ich auf DuckDB 1.1.3, laufen die Queries erfolgreich! Sie brauchten aber etwa 5,8 Minuten.

Als ich tiefer in den Datensatz eintauchte, entdeckte ich, dass die in der Benchmark-Query gewählten Spalten in jeder Datei vorkommen. In früheren DuckDB-Versionen verlangte schon das Vorhandensein von Dateien mit unterschiedlichen Spaltenmengen das Flag union_by_name = True, auch wenn die inkonsistenten oder neuen Spalten in der Query nicht genutzt wurden. Zwischen dem Originalbeitrag und Version 1.1.3 hat DuckDB aber die Fähigkeit ergänzt, Projection Pushdown in CSV-Dateien zu machen! Das heißt, nur die in der Query genutzten Spalten werden tatsächlich aus der CSV gelesen, nicht alle Spalten. Deshalb können wir das Flag union_by_name = true für die Benchmark-Query tatsächlich entfernen und erfolgreich laufen. Das braucht weniger Overhead (weil wir keine Zeit investieren müssen, zu prüfen, ob alle Schemas passen – wir können uns auf das zuerst gelesene Schema verlassen). Die vereinfachte Query läuft in nur 4 Minuten, übt aber nicht die Fähigkeit aus, über die wir gesprochen haben – Schema Evolution!

Um die Fähigkeit BY NAME zu üben, fügen wir der SQL-Query eine Spalte hinzu, die nur in manchen Dateien vorkommt.

CREATE OR REPLACE VIEW metrics AS
    SELECT
        date,
        count(DISTINCT datacenter) AS datacenters,
        sum(failure) AS failures
    FROM read_csv_auto('⟨s3_path⟩/*.csv', union_by_name = true)
    GROUP BY date;

COPY metrics TO '⟨s3_path⟩/results/results.csv';

Diese Query läuft in etwa derselben Zeit wie das Original (5,6 Minuten), ist also ein guter Proxy für das Original und zeigt, wie DuckDB Schema Evolution behandelt!

Dann habe ich ein paar Tweaks gemacht, um die Performance zu verbessern. Die erste Änderung: die Erzeugung einer Sicht überspringen und die Operationen in einem Schritt abschließen. Das verbessert die Performance, weil DuckDB versucht, sicherzustellen, dass eine Sicht korrekt definiert ist, indem sie sie beim Anlegen bindet. Normalerweise hat das vernachlässigbaren Overhead (Sichten sind eine tolle Abstraktion!); beim Lesen aus Cloud-Object-Storage und mit UNION ALL BY NAME löst das aber eine Prüfung des Schemas jeder Datei aus, die Zeit kosten kann. In diesem Fall rund 2 Minuten! Das aktualisierte SQL-Statement sieht so aus:

COPY (
    SELECT
        date,
        count(DISTINCT datacenter) AS datacenters,
        sum(failure) AS failures
    FROM read_csv_auto('⟨s3_path⟩/*.csv', union_by_name = true)
    GROUP BY date
) TO '⟨s3_path⟩/results/results.csv';

Die Performance verbessert sich mit dieser Änderung auf etwa 4,1 Minuten und reduziert den Test außerdem auf eine einzige Query.

Wir können den Overhead der Flexibilität, die UNION ALL BY NAME bietet, quantifizieren, wenn wir die verbesserte Subquery-Syntax behalten, aber wieder die Spalte datacenter und das Flag union_by_name entfernen.

COPY (
    SELECT
        date,
        sum(failure) AS failures
    FROM read_csv_auto('⟨s3_path⟩/*.csv')
    GROUP BY date
) TO '⟨s3_path⟩/results/results.csv';

Diese Query läuft in 3,7 Minuten, der Overhead für Schema Evolution beträgt also nur etwa 10 %! Das ist ein kleiner Preis für Flexibilität und Einfachheit.

Wir können die Performance aber noch weiter verbessern. Die nächste Änderung war, die Zahl der Threads zu erhöhen, die DuckDB nutzt. Standardmäßig nutzt DuckDB einen Thread pro Kern. Diese Query ist aber sehr I/O-intensiv (wegen der Netzwerk-Hops beim Lesen von und Schreiben nach S3) und weniger CPU-intensiv. DuckDB nutzt synchrones I/O, deshalb liegt mit der Standard-Threadzahl ein CPU-Kern brach, wenn ein Thread I/O macht. Mehr Threads könnten deshalb die Netzwerkressourcen voller auslasten, die in diesem Test der Engpass sind. Hier habe ich einfach geraten, dass das helfen würde; die CPU-Auslastung zu überwachen ist der bessere Ansatz.

Mit 4 Threads statt der Standard-2 verbessert sich die Performance auf 3 Minuten!

Mehr Threads haben die Performance nicht sinnvoll weiter verbessert. Zusätzliche Threads brauchen mehr Speicher, aber mit den Verbesserungen in 1.1 ist das kein signifikantes Problem mehr (ich habe bis 16 Threads mit nur 2,2 GB Speicher getestet).

Die Tabelle unten fasst die Ergebnisse auf einer Instanz c5d.large zusammen, die 2 vCPUs und 4 GB RAM hat. Wir berichten die Gesamtlaufzeit und die maximale Speichernutzung für jede Query.

Schlussgedanken

Beim Stapeln von Daten bringt DuckDB den Geist des relationalen Modells zurück nach SQL! Schließlich sollte das Stapeln von Daten nicht verlangen, dass die Spaltenreihenfolgen passen… Die Schlüsselwörter BY NAME können gängige Operationen vereinfachen: Relationen mit unterschiedlicher Reihenfolge oder Spaltenmengen kombinieren, die Ergebnisse einer Query in eine Tabelle einfügen oder einen Data Lake mit wechselndem Schema abfragen. Ab DuckDB Version 1.1 ist das jetzt ein performanter und skalierbarer Ansatz!

Viel Spaß beim Analysieren!