2023-08-23

Noch freundlicheres SQL mit DuckDB

Alex Monahan

Wer sagt, dass SQL in der Zeit eingefroren bleiben und an eine Spezifikation von 1999 gekettet sein muss? Zur Erinnerung: Wie hat sich JavaScript vor Promises angefühlt? Die kamen erst 2012! Innovation auf der Syntaxebene kann ein ganzes Sprachökosystem spürbar verbessern.

Wir sehen viele gute Gründe, SQL weiterzuentwickeln: einfachere Basisabfragen und dynamischere Analysen. Viele dieser Features kamen aus Community-Vorschlägen! Ihre SQL-Schmerzpunkte gern auf Discord oder GitHub – machen Sie mit, wenn wir ändern, wie sich SQL-Schreiben anfühlt!

Wer den ersten Teil der Serie noch nicht gelesen hat: „Friendlier SQL with DuckDB“.

Die Zukunft ist jetzt

Die ersten Verbesserungen auf dieser Liste standen im vorigen Beitrag noch unter „Ideen für die Zukunft“.

Wiederverwendbare Spaltenaliase

Bei schrittweise berechneten Ausdrücken in einem Select zwingen klassische SQL-Dialekte dazu, den vollen Ausdruck je Spalte zu wiederholen oder um jeden Berechnungsschritt eine Common Table Expression (CTE) zu legen. Jetzt kann jedes Spaltenalias von nachfolgenden Spalten im selben Select wiederverwendet werden. Und die Aliase gehen auch in Where und Order by.

Alter Weg 1: Sich wiederholen

SELECT
    'These are the voyages of the starship Enterprise...' AS intro,
    instr('These are the voyages of the starship Enterprise...', 'starship')
        AS starship_loc
    substr('These are the voyages of the starship Enterprise...',
    instr('These are the voyages of the starship Enterprise...', 'starship')
        + len('starship') + 1) AS trimmed_intro;

Alter Weg 2: CTEs ohne Ende

WITH intro_cte AS (
    SELECT
        'These are the voyages of the starship Enterprise...' AS intro
), starship_loc_cte AS (
    SELECT
        intro,
        instr(intro, 'starship') AS starship_loc
    FROM intro_cte
)
SELECT
    intro,
    starship_loc,
    substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro
FROM starship_loc_cte;

Neuer Weg

SELECT
     'These are the voyages of the starship Enterprise...' AS intro,
     instr(intro, 'starship') AS starship_loc,
     substr(intro, starship_loc + len('starship') + 1) AS trimmed_intro;

Dynamische Spaltenauswahl

Datenbanken mögen strenge Spaltendefinitionen und flexible Zeilenzahlen. Das hilft bei Datentypen und Spaltenmetadaten. In Data-Science-Workflows und anderswo werden Spalten aber oft dynamisch erzeugt (etwa beim Feature Engineering).

Die Spaltennamen müssen Sie nicht mehr alle im Voraus kennen! DuckDB kann Spalten per regulärem Ausdruck auswählen und sogar ändern, über die Modifier EXCLUDE oder REPLACE und über Lambda-Funktionen (siehe den Abschnitt zu Lambda-Funktionen!).

Schauen wir uns Fakten zur ersten Star-Trek-Staffel an. Mit DuckDBs httpfs-Erweiterung können wir einen CSV-Datensatz direkt von GitHub abfragen. Es gibt mehrere Spalten, also DESCRIBE:

INSTALL httpfs;
LOAD httpfs;

CREATE TABLE trek_facts AS
    SELECT *
    FROM 'https://blobs.duckdb.org/data/Star_Trek-Season_1.csv';

DESCRIBE trek_facts;

COLUMNS() mit regulären Ausdrücken

Der Ausdruck COLUMNS nimmt einen String entgegen, der ein regulärer Ausdruck ist, und liefert alle passenden Spaltennamen. Wie hat sich Warp über die erste Staffel verändert? Alle Spaltennamen mit dem Wort warp:

SELECT
    episode_num,
    COLUMNS('.*warp.*')
FROM trek_facts;

COLUMNS lässt sich von anderen Funktionen umhüllen, die dann auf jede gewählte Spalte wirken. Vereinfachen wir die Abfrage auf die Maxima über alle Folgen:

SELECT
    max(COLUMNS('.*warp.*'))
FROM trek_facts;

Wir können auch eine WHERE-Klausel über mehrere Spalten legen. Alle Spalten müssen das Filterkriterium erfüllen, das entspricht einer Kombination mit AND. Welche Folgen hatten mindestens 2 Warp-Speed-Befehle und mindestens Warp-Stufe 2?

SELECT
    episode_num,
    COLUMNS('.*warp.*')
FROM trek_facts
WHERE
    COLUMNS('.*warp.*') >= 2;
    -- cnt_warp_speed_orders >= 2
    -- AND
    -- highest_warp_speed_issued >= 2

COLUMNS() mit EXCLUDE und REPLACE

Einzelne Spalten können vor Berechnungen ausgeschlossen oder ersetzt werden. Unser Datensatz enthält nur Staffel 1, das max dieser Spalte brauchen wir nicht. Das wäre höchst unlogisch.

SELECT
    max(COLUMNS(* EXCLUDE season_num))
FROM trek_facts;

Die Syntax REPLACE ist auch bei einem dynamischen Spaltensatz nützlich. Hier wandeln wir die Daten vor dem Maximum je Spalte in Zeitstempel. Früher hätte das eine ganze Subquery oder CTE nur für diese eine Spalte gebraucht!

SELECT
    max(COLUMNS(* REPLACE aired_date::timestamp AS aired_date))
FROM trek_facts;

COLUMNS() mit Lambda-Funktionen

Am flexibelsten lässt sich ein dynamischer Spaltensatz über eine Lambda-Funktion abfragen. Beliebige Kriterien auf Spaltennamen, nicht nur reguläre Ausdrücke. Mehr zu Lambdas weiter unten.

Ist LIKE vertrauter, können wir Spalten nach einem LIKE-Muster statt per Regex wählen.

SELECT
    episode_num,
    COLUMNS(lambda col: col LIKE '%warp%')
FROM trek_facts
WHERE
    COLUMNS(lambda col: LIKE '%warp%') >= 2;

Automatische Umwandlung von JSON in verschachtelte Typen

Im ersten Teil der Serie stand JSON-Punktnotation noch als Zukunftsarbeit. Das Team ist weiter gegangen! Statt JSON-typisierte Spalten per Punktnotation anzusprechen, kann JSON jetzt automatisch in DuckDBs native Typen geparst werden – deutlich schneller, bessere Kompression und dazu die freundliche Punktnotation!

Zuerst die Erweiterungen httpfs und json installieren und laden, falls sie nicht im Client mitkommen. Dann eine entfernte JSON-Datei direkt wie eine Tabelle abfragen!

INSTALL httpfs;
LOAD httpfs;
INSTALL json;
LOAD json;

SELECT
     starfleet[10].model AS starship
FROM 'https://raw.githubusercontent.com/vlad-saling/star-trek-ipsum/master/src/content/content.json';

Jetzt neue SQL-Fähigkeiten über die Ideen aus dem vorigen Beitrag hinaus!

FROM zuerst in SELECT-Statements

Beim Bauen einer Abfrage muss man zuerst wissen, woher die Daten kommen – FROM. Warum ist das dann die zweite Klausel in einem SELECT?? Nicht mehr! DuckDB baut SQL, wie es immer hätte sein sollen – die Klausel FROM zuerst! Das adressiert eine der ältesten Beschwerden über SQL, und das DuckDB-Team hat es in 2 Tagen umgesetzt.

FROM my_table SELECT my_column;

Nicht nur das: Das SELECT-Statement kann ganz wegfallen, DuckDB nimmt dann alle Spalten. Eine Tabelle anschauen ist jetzt so einfach:

FROM my_table;
-- SELECT * FROM my_table

Andere Statements wie COPY werden ebenfalls einfacher.

COPY (FROM trek_facts) TO 'phaser_filled_facts.parquet';

Neben weniger Tastenanschlägen und besserem Flow hat das einen weiteren Vorteil: Autocomplete hat mehr Kontext, wenn Sie Spalten wählen. Der KI eine Hilfestellung!

Die Syntax ist völlig optional, Ihre Tastenkürzel für SELECT * FROM bleiben sicher – auch wenn sie überholt sind … 🙂

Function Chaining

Viele SQL-Blogs raten zu CTEs statt Subqueries. Unter anderem sind sie lesbarer. Operationen stecken in diskreten Blöcken und lassen sich von oben nach unten lesen, statt sich von innen nach außen vorzuarbeiten.

Denselben Lesbarkeitsgewinn gibt DuckDB jeder skalaren Funktion! Mit dem Punktoperator Funktionen verketten, wie in Python. Der vorherige Ausdruck in der Kette ist das erste Argument der nächsten Funktion.

SELECT
     ('Make it so')
          .upper()
          .string_split(' ')
          .list_aggr('string_agg','.')
          .concat('.') AS im_not_messing_around_number_one;

Vergleichen Sie das mit dem alten Weg …

SELECT
     concat(
          list_aggr(
               string_split(
                    upper('Make it stop'),
               ' '),
          'string_agg','.'),
     '.') AS oof;

Union by Name

DuckDB will das Beste aus Datenbanken und DataFrames verbinden. Die neue Syntax ist von der concat-Funktion in Pandas inspiriert. Statt Tabellen nach Spaltenposition vertikal zu stapeln, werden Spalten nach Namen gematcht und entsprechend gestapelt. Einfach UNION durch UNION BY NAME ersetzen oder UNION ALL durch UNION ALL BY NAME.

Zum Beispiel mussten wir in The Next Generation ein paar neue Sprichwörter außerirdischer Spezies ergänzen:

CREATE TABLE proverbs AS
     SELECT
          'Revenge is a dish best served cold' AS klingon_proverb
     UNION ALL BY NAME
     SELECT
          'You will be assimilated' AS borg_proverb,
          'If winning is not important, why keep score?' AS klingon_proverb;

FROM proverbs;

Der Ansatz hat weitere Vorteile. Wie oben zu sehen: Nicht nur Tabellen mit anderer Spaltenreihenfolge lassen sich kombinieren, sondern auch mit völlig unterschiedlicher Spaltenzahl. Hilfreich bei Schema-Migrationen und besonders für DuckDBs Multi-Datei-Lesen.

Insert by Name

Eine weitere Stelle, an der SQL die Spaltenreihenfolge streng nimmt: das Einfügen in eine Tabelle. Entweder müssen die Spalten exakt passen, oder alle Spaltennamen stehen zweimal in der Abfrage.

Stattdessen nach dem Tabellennamen die Schlüsselwörter BY NAME. Beliebige Teilmengen der Tabellenspalten in beliebiger Reihenfolge lassen sich einfügen.

INSERT INTO proverbs BY NAME
     SELECT 'Resistance is futile' AS borg_proverb;

SELECT * FROM proverbs;

Dynamisches PIVOT und UNPIVOT

Historisch sind Datenbanken für Pivot-Operationen schlecht geeignet. DuckDBs Klauseln PIVOT und UNPIVOT können dynamische Spaltennamen erzeugen oder stapeln – wirklich flexibles Pivotieren! Dazu gibt es die SQL-Standard-Syntax und eine freundlichere Kurzform.

Ein Blick auf Beschaffungsprognosen, gerade als der Erd-Romulaner-Krieg begann:

CREATE TABLE purchases (item VARCHAR, year INTEGER, count INTEGER);

INSERT INTO purchases
    VALUES ('phasers', 2155, 1035),
           ('phasers', 2156, 25039),
           ('phasers', 2157, 95000),
           ('photon torpedoes', 2155, 255),
           ('photon torpedoes', 2156, 17899),
           ('photon torpedoes', 2157, 87492);

FROM purchases;

Phaser- und Photonentorpedo-Bedarf lassen sich leichter vergleichen, wenn die Jahreszahlen visuell nah beieinanderstehen. Pivotieren wir in ein freundlicheres Format! Jedes Jahr eine eigene Spalte (ohne jedes Jahr in der Abfrage anzugeben!), die Summe von count, und weiter eine eigene Gruppe (Zeile) je item.

CREATE TABLE pivoted_purchases AS
     PIVOT purchases
          ON year
          USING sum(count)
          GROUP BY item;

FROM pivoted_purchases;

Sieht aus, als wären Photonentorpedos im Angebot gewesen …

Stellen Sie sich die umgekehrte Lage vor. Scotty in Engineering hat die Beschaffungsprognose visuell analysiert und von Hand aufgebaut. Er mag alles pivotiert, weil es lesbarer ist. Jetzt muss es zurück in die Datenbank! Der Krieg kann noch eine Weile dauern, nächstes Jahr vielleicht wieder. Eine UNPIVOT-Abfrage zurück ins Originalformat, die jedes Jahr verträgt.

COLUMNS nimmt alle Spalten außer item. Nach dem Stapeln wird die Spalte mit den Namen aus pivoted_purchases in year umbenannt, die Werte darin sind count. Das Ergebnis ist derselbe Datensatz wie das Original.

UNPIVOT pivoted_purchases
     ON COLUMNS(* EXCLUDE item)
     INTO
          NAME year
          VALUE count;

Weitere Beispiele stehen in unserem Ankündigungsbeitrag zu DuckDB 0.8.0; die Dokumentationsseiten zu PIVOT und UNPIVOT zeigen komplexere Abfragen.

Ein späterer Beitrag erklärt, was hinter den Kulissen passiert!

Listen-Lambda-Funktionen

Listen-Lambdas wenden Operationen auf jedes Listenelement an. Sie müssen nicht vorab definiert sein – sie entstehen on the fly in der Abfrage.

Hier verkürzt eine Lambda-Funktion zusammen mit list_transform jeden offiziellen Schiffsnamen.

SELECT
     (['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
          .list_transform(lambda x: x.string_split(' ')[1]) AS short_name;

Lambdas können Listen auch filtern. Das Lambda liefert eine Liste von Booleans; list_filter wählt damit bestimmte Elemente. Die Funktion contains nutzt das oben beschriebene Function Chaining.

SELECT
     (['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031'])
          .list_filter(lambda x: x.contains('1701')) AS the_original;

List Comprehensions

Was, wenn es eine einfache Syntax gäbe, um eine Liste zu ändern und zu filtern? DuckDB orientiert sich an Pythons List Comprehensions und vereinfacht die obigen Beispiele stark. List Comprehensions sind syntaktischer Zucker – hinter den Kulissen werden die Abfragen in Lambda-Ausdrücke umgeschrieben!

In Klammern zuerst die gewünschte Transformation, dann die zu iterierende Liste, zuletzt das Filterkriterium.

SELECT
     [x.string_split(' ')[1]
     FOR x IN ['Enterprise NCC-1701', 'Voyager NCC-74656', 'Discovery NCC-1031']
     IF x.contains('1701')] AS ready_to_boldly_go;

Struct.* explodieren

Ein Struct in DuckDB ist eine Menge von Schlüssel/Wert-Paaren. Hinter den Kulissen liegt je Schlüssel eine eigene Spalte. Ein Struct in getrennte Spalten zu explodieren ist daher rechnerisch einfach – und jetzt auch syntaktisch! Wieder ein Beispiel dafür, dass SQL dynamische Spaltennamen verträgt.

WITH damage_report AS (
     SELECT {'gold_casualties':5, 'blue_casualties':15, 'red_casualties': 10000} AS casualties
)
FROM damage_report
SELECT
     casualties.*;

Automatische Struct-Erzeugung

DuckDB macht es leicht, jede Tabelle in einen einspaltigen Struct zu wandeln. Statt Spaltennamen zu SELECTen, SELECTen Sie den Tabellennamen selbst.

WITH officers AS (
     SELECT 'Captain' AS rank, 'Jean-Luc Picard' AS name
     UNION ALL
     SELECT 'Lieutenant Commander', 'Data'
)
FROM officers
SELECT officers;

Der Datentyp Union

DuckDB nutzt starke Typisierung für Leistung und Datenqualität. Gleichzeitig ist DuckDB so nachsichtig wie möglich, etwa durch implizites Casting, damit nicht ständig gecastet werden muss.

Eine weitere Flexibilität ist der neue Datentyp UNION. Eine Spalte kann mehrere Wertetypen enthalten. Das lässt sich als „Opt-in“ in SQLites flexible Typregeln verstehen (die Gegenrichtung zu SQLites kürzlich angekündigten strict tables).

Standardmäßig sucht DuckDB beim Kombinieren von Tabellen den gemeinsamen Nenner der Datentypen. Die folgende Abfrage ergibt eine VARCHAR-Spalte:

SELECT 'The Motion Picture' AS movie UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 'First Contact';

Mit einem Typ UNION behält jede Zeile ihren ursprünglichen Datentyp. Ein UNION wird über Schlüssel-Wert-Paare definiert: Schlüssel als Name, Wert als Datentyp. Die einzelnen Typen lassen sich auch als eigene Spalten herausziehen:

CREATE TABLE movies (
     movie UNION(num INTEGER, name VARCHAR)
);
INSERT INTO movies VALUES
     ('The Motion Picture'), (2), (3), (4), (5), (6), ('First Contact');

FROM movies
SELECT
     movie,
     union_tag(movie) AS type,
     movie.name,
     movie.num;

Weitere freundliche Features

Einige weitere freundliche Features verdienen Erwähnung, manche ein eigenes Blog.

DuckDB nimmt sich die describe-Funktion in Pandas zum Vorbild und implementiert das Schlüsselwort SUMMARIZE: diverse Statistiken je Spalte für einen schnellen Überblick. Einfach SUMMARIZE vor jede Tabelle oder jedes SELECT.

Im Beitrag zu korrelierten Subqueries steht, wie Subqueries aufeinander Bezug nehmen. DuckDBs Optimizer verbessert die Leistung korrelierter Subqueries um Größenordnungen; Abfragen lassen sich so natürlich wie möglich formulieren. Was aus Leistungsgründen ein Anti-Pattern war, kann jetzt frei genutzt werden!

DuckDB hat weitere JOIN-Arten ergänzt, die gängige Berechnungen leichter ausdrücken. Manche wie LATERAL, ASOF, SEMI und ANTI gibt es in anderen Systemen, in DuckDB mit hoher Leistung. Neu ist ein POSITIONAL-Join, der über Zeilennummern kombiniert – analog zu Pandas und Joins über Zeilenindizes. Details in der JOIN-Dokumentation; ein Beitrag zu DuckDBs State-of-the-Art-ASOF-Joins folgt!

Zusammenfassung und Ausblick

DuckDB soll die einfachste Datenbank sein. In-Process, keine Abhängigkeiten und starke Typisierung tragen dazu bei, der freundliche SQL-Dialekt ebenso. DuckDB erweitert den Branchenstandard PostgreSQL-Dialekt, damit sich die nötigen Transformationen so einfach wie möglich ausdrücken lassen. Die Änderungen reichen von der uralten Klauselreihenfolge des SELECT (jetzt mit FROM zuerst) über Function Chaining bis zu Berechnungen auf verschachtelten Typen wie List Comprehensions. All das ist im Release 0.8.1 verfügbar.

Geplante Arbeit an freundlicherem SQL:

• Lambda-Funktionen mit mehr als einem Argument, etwa list_zip
• Unterstriche als Zifferntrenner (z. B. 1_000_000 statt 1000000)
• Nutzererlebnis bei Erweiterungen, einschließlich Autoload
• Verbesserungen beim Datei-Globbing
• Ihre Vorschläge!

Sagen Sie uns, wo SQL besser werden kann! Feedback gern auf Discord oder GitHub.

Leben Sie lang und in Frieden! 🖖