DuckDBs Friendly SQL ist ein Game Changer für die Developer Experience

Ich denke nicht, dass irgendjemand überrascht sein wird, wenn ich sage, dass SQL nicht die angenehmste Sprache zum Arbeiten ist. Manche würden sogar so weit gehen zu sagen, dass die Ergonomie von SQL furchtbar ist. Insbesondere bei großen und komplexen Queries. Allerdings ist SQL nicht ohne Grund die Wahl für die Abfrage und Verarbeitung von Daten. Glücklicherweise behebt der Friendly SQL Dialekt von DuckDB viele dieser Probleme und schafft es gleichzeitig, den vertrauten Stil von klassischem SQL zu erhalten.

Die Probleme bei der Wartung großer und komplexer SQL Queries laufen im Großen und Ganzen darauf hinaus, dass sich die syntaktische Reihenfolge der Anweisungen stark von der semantischen Reihenfolge unterscheidet. Dies muss bei der Arbeit mit SQL immer berücksichtigt werden, was das Schreiben unintuitiv macht und das Verständnis erschwert: Zum Beispiel ist der logische Startpunkt einer irgendwo in der Mitte und zusätzliche Subqueries werden gebraucht^PDF, um Zwischenergebnisse zu verwenden. Aber auch die Tool-Unterstützung kann davon beeinträchtigt werden, etwa wenn die Autovervollständigung von Spaltennamen nicht möglich ist, da die FROM Klausel noch nicht existiert.

Dass das besser geht, zeige ich an einem halbwegs realistischen Beispiel im PostgreSQL Dialekt als Stellvertreter für klassisches SQL. Dieses wird im Anschluss mit einer Auswahl von DuckDBs Friendly SQL Features schrittweise verbessert. Aber bevor es in die Details geht, noch ein paar Anmerkungen, um das Ganze in Kontext zu setzen:

---

• Du bevorzugst einen praktischen Ansatz?
  • Schau dir mein GitHub Repository an, um die Beispiele lokal auszuführen.
  • Oder nutze die Web-IDE von MotherDuck (kostenloser Account erforderlich). Mit diesem Share kannst du auf die Beispieldaten zugreifen: ATTACH 'md:_share/duckdb-friendly-sql-example-data/0c374195-c236-4b6f-a8ce-02595d90cba6'
• DuckDBs Dialekt ist nicht der einzige, der die Probleme von klassischem SQL angeht. Es ist nur einer, der mir zufälligerweise sehr gut gefällt. Was ich zeigen werde, gibt es also vielleicht auch anderswo.
• Die gezeigten Features sind eine persönliche Auswahl von mir, von subtil bis ausgefallen, und keineswegs eine vollständige Liste der Möglichkeiten. Gut möglich, dass dir nicht alles (oder meine Formatierung) gefallen wird, aber da DuckDB weitgehend mit PostgreSQL kompatibel ist, kannst du behalten was dir gefällt und den Rest verwerfen.

---

Ein halbwegs realistisches Beispiel: Rubber Duck KPIs

Minimale Beispiele sind hervorragend geeignet, um zu zeigen wie Dinge funktionieren. Aber sie sind meiner Meinung nach nicht gut darin zu demonstrieren, wie sich Feature auf die Developer Experience auswirken. Deswegen habe ich mich entschieden, ein halbwegs realistisches Beispiel zu erstellen: Ausreichend groß, aber nicht so groß, dass es unhandlich werden würde. Damit kann ich besser zeigen, welchen Effekt die einzelnen Anpassungen haben.

Die Beispiel-Query selektiert eine bestimmte Produktkategorie, führt ein paar zusätzliche Eigenschaften ein und aggregiert einige KPIs, gruppiert nach Produkt und Land des Kunden.

1WITH
2  enriched AS (
3    SELECT
4      p.product_id                   AS duck_id,
5      p.product_name                 AS duck_name,
6      COALESCE(p.color, 'Unknown')   AS duck_color,
7      COALESCE(p.material, 'Rubber') AS duck_material,
8      c.country                      AS customer_country,
9
10      oi.quantity,
11      oi.unit_price,
12      COALESCE(oi.discount_pct, 0)   AS discount_pct,
13      p.base_cost,
14
15      (
16        COALESCE(oi.discount_pct, 0) >= 0.20
17        OR COALESCE(p.color, '') IN ('Gold', 'Platinum')
18        OR COALESCE(p.material, '') ILIKE '%silicone%'
19      )                              AS is_premium_duck
20    FROM orders      AS o
21    JOIN order_items AS oi USING (order_id)
22    JOIN products    AS p  USING (product_id)
23    JOIN customers   AS c  USING (customer_id)
24    WHERE p.category = 'Rubber Duck'
25  )
26
27SELECT
28  duck_id,
29  format('%s (%s, %s)', duck_name, duck_color, duck_material) AS duck_description,
30  customer_country,
31  SUM(quantity * unit_price * (1 - discount_pct))                            AS net_revenue,
32  SUM(quantity * base_cost)                                                  AS cogs,
33  SUM((quantity * unit_price * (1 - discount_pct)) - (quantity * base_cost)) AS gross_margin
34FROM enriched
35WHERE is_premium_duck
36GROUP BY duck_id, duck_description, customer_country
37ORDER BY duck_id, customer_country;

Ein paar Punkte, die meiner Meinung nach die Developer Experience negativ beeinflussen:

• Viele wiederholte, ähnliche Ausdrücke. Dadurch ist es mühsam Änderungen vorzunehmen und es erhöht das Risiko von Inkonsistenzen.
• Schlecht lesbar von oben nach unten. Für mich wäre der logische Startpunkt die FROM Klausel der enriched CTE.
• Ich fände es besser, wenn die Berechnung der einzelnen KPIs nicht Teil der Aggregation in der finalen Projektion wäre. Das lässt sich leicht mit weiteren CTEs oder Subqueries erreichen, wobei allerdings weitere Wiederholungen entstehen und die Query noch größer wird (ausführlicheres Beispiel).
• Der letzte Punkt ist meine persönliche Präferenz, aber mir gefällt die klassische Alias Syntax nicht. Der Spaltenname kann wesentlichen Kontext für den dazugehörigen Ausdruck geben. expression AS alias stört meinen Lesefluss, da man viel von hin und her springen muss.

In den folgenden Abschnitten stelle ich meine ausgewählten Features des Friendly SQL mit minimalen Beispielen vor und zeige anschließend, wie sie sich auf das halbwegs realistische Beispiel auswirken.

Nettere Syntax

Die ersten Features für ein angenehmeres SQL sind rein syntaktisch. Manche sind sehr subtil, andere können gewöhnungsbedürftig sein. Insbesondere, wenn man schon länger mit SQL arbeitet und sich bestimmte Lesemuster angewöhnt hat.

FROM-First Syntax

Hiermit kann man einfach die FROM und JOIN Klauseln vor das SELECT Statement stellen.

1FROM orders o
2JOIN customers c USING (customer_id)
3SELECT o.order_id, o.order_date, c.country;

Dadurch ist es wesentlich einfacher, Queries von oben nach unten zu lesen. Mehr Details finden sich in der offiziellen Dokumentation.

Nachgestellte Kommas

Viele moderne Programmiersprachen erlauben nachgestellte Kommas an unterschiedlichsten Stellen, und das aus gutem Grund. Die Reihenfolge von Projektionen zu ändern oder Zeilen auszukommentieren, ohne nachdenken zu müssen, macht das Arbeiten wesentlich flüssiger.

1FROM products
2SELECT
3  product_id,
4  product_name,
5  base_cost,
6;

Anfangs fällt das kaum auf, aber es wird schmerzlich vermisst, sobald es nicht mehr geht. Leider werden nachgestellte Kommas nicht überall unterstützt. Das fällt vor allem bei CTEs auf, aber auch ORDER BY unterstützt sie nicht.

Prefix Aliase

Den Spaltennamen vor deren Ausdruck stellen zu können, ist definitiv keine subtile Änderung und absolut eine Frage der persönlichen Vorlieben.

1FROM products
2SELECT
3     duck_id:  product_id,
4   duck_name:  product_name,
5  duck_color:  color,
6WHERE
7  category = 'Rubber Duck';

Ich finde Queries werden dadurch verständlicher, aber ich kann nachvollziehen, dass dadurch die Formatierung erschwert wird oder es sich komisch anfühlt, wenn man expression AS alias gewohnt ist. Letztendlich ist es entscheidend, einen Stil konsistent anzuwenden und nicht für welchen man sich entscheidet. Unglücklicherweise werden auch Prefix Aliase nicht überall unterstützt: zum Beispiel bei Star und COLUMNS Ausdrücken (welche ich später zeige).

Ein netteres halbwegs realistisches Beispiel

Diese drei Features bilden ein solides Fundament für eine verbesserte Developer Experience.

1WITH
2  enriched AS (
3    FROM  o: orders
4    JOIN oi: order_items USING (order_id)
5    JOIN  p: products    USING (product_id)
6    JOIN  c: customers   USING (customer_id)
7    SELECT
8               duck_id:  p.product_id,
9             duck_name:  p.product_name,
10            duck_color:  COALESCE(p.color, 'Unknown'),
11         duck_material:  COALESCE(p.material, 'Rubber'),
12      customer_country:  c.country,
13  
14           oi.quantity,
15         oi.unit_price,
16          discount_pct:  COALESCE(oi.discount_pct, 0),
17           p.base_cost,
18  
19       is_premium_duck:  (
20         COALESCE(oi.discount_pct, 0) >= 0.20
21         OR COALESCE(p.color, '') IN ('Gold', 'Platinum')
22         OR COALESCE(p.material, '') ILIKE '%silicone%'
23       ),
24    WHERE p.category = 'Rubber Duck'
25  )
26
27FROM enriched
28SELECT
29           duck_id,
30  duck_description:  format('{0} ({1}, {2})', duck_name, duck_color, duck_material),
31  customer_country,
32       net_revenue:  SUM(quantity * unit_price * (1 - discount_pct)),
33              cogs:  SUM(quantity * base_cost),
34      gross_margin:  SUM((quantity * unit_price * (1 - discount_pct)) - (quantity * base_cost)),
35WHERE is_premium_duck
36GROUP BY duck_id, duck_description, customer_country
37ORDER BY duck_id, customer_country;

Direkt verwendbare Aliase

In klassischem SQL kann man Aliase von Ausdrücken nicht im selben Scope wiederverwenden. Das führt zu vielen Wiederholungen oder zusätzlichen Unterabfragen und CTEs. Komplexe Abfragen werden so schnell schwer schwer zu warten, und ein gutes Gleichgewicht zwischen Länge und Lesbarkeit zu finden, ist keine leichte Sache.

Dass die direkte Verwendung von Aliasen in DuckDB möglich ist, ist einer der Gründe, warum ich es als Game Changer sehe. Dadurch kann man beim Schreiben von Queries viel direkter vorgehen. Und Berechnungen in kleinen Schritten aneinanderzureihen ist einfacher zu schreiben und leichter zu verstehen.

1FROM order_items
2SELECT
3  discount_price:  unit_price * (1 - discount_pct),
4     total_price:  discount_price * quantity,
5     taxed_price:  total_price * (1 + tax_rate),
6WHERE
7  taxed_price - total_price > 5;

Solche minimalen Beispiele können die Wirkung nicht wirklich zum Ausdruck bringen, und auch das halbwegs realistische Beispiel ist meiner Meinung nach nicht ausreichend dafür. Daher empfehle ich jedem, ein eigenes SQL Monster der Woche zu nehmen und zu schauen, was passiert, wenn Aliase direkt verwendet werden können.

1WITH
2  enriched AS (
3    FROM  o: orders
4    JOIN oi: order_items USING (order_id)
5    JOIN  p: products    USING (product_id)
6    JOIN  c: customers   USING (customer_id)
7    SELECT
8                duck_id:  p.product_id,
9              duck_name:  p.product_name,
10             duck_color:  COALESCE(p.color, 'Unknown'),
11          duck_material:  COALESCE(p.material, 'Rubber'),
12       customer_country:  c.country,
13
14          duck_discount:  COALESCE(oi.discount_pct, 0),
15       net_revenue_line:  oi.quantity * oi.unit_price * (1 - duck_discount),
16              cogs_line:  oi.quantity * p.base_cost,
17      gross_margin_line:  net_revenue_line - cogs_line,
18
19        is_premium_duck:  duck_discount >= 0.20 OR duck_color IN ('Gold', 'Platinum') OR duck_material ILIKE '%silicone%',
20    WHERE p.category = 'Rubber Duck'
21  )
22
23FROM enriched
24SELECT
25           duck_id,
26  duck_description:  format('{0} ({1}, {2})', duck_name, duck_color, duck_material),
27  customer_country,
28       net_revenue:  SUM(net_revenue_line),
29              cogs:  SUM(cogs_line),
30      gross_margin:  SUM(gross_margin_line),
31WHERE is_premium_duck
32GROUP BY duck_id, duck_description, customer_country
33ORDER BY duck_id, customer_country;

Ein paar Unterschiede, die ich hervorheben möchte:

• Die Wiederverwendung der Aliase im selben SELECT Statement macht das enriched CTE wesentlich kompakter.
  • Spalten wie duck_color und duck_material können wiederverwendet werden, womit is_premium_duck in eine Zeile passt.
  • Die KPIs können Schritt für Schritt berechnet werden, ohne dass eine Zwischenabfrage nötig wird.
• Die KPIs in das enriched CTE zu verschieben, verbessert die Separation of Concerns.
  • enriched wird zu einer Sammlung von vorverarbeiteten Datenpunkten.
  • Die finale Projektion baut die Zielstruktur und berechnet die gewünschten Aggregationen.

Star & COLUMNS Ausdruck

Die letzten beiden Abschnitte waren eher in der Kategorie subtil. Daher wollte ich als letztes Feature noch etwas Ausgefalleneres zeigen. DuckDBs Star- und COLUMNS Ausdrücke eröffnen einem ganz neue Möglichkeiten. Aber wie bei jedem mächtigen Werkzeug kann eine übermäßige Anwendung mehr schaden als nützen. Und wie zuvor erwähnt: Prefix Aliase werden nicht unterstützt, weswegen die klassische Alias Syntax angewendet werden muss.

Mächtigerer Star Ausdruck

Eine Kurzform zur Selektion aller Spalten einer Tabelle ist praktisch. Was aber, wenn man alle Spalten außer ein oder zwei braucht? In klassischem SQL müssten alle gewünschten Spalten einzeln aufgelistet werden. DuckDB macht das einfacher: Mit EXCLUDE, REPLACE und RENAME kann man diese Fälle abbilden.

1-- EXCLUDE
2FROM products
3SELECT * EXCLUDE(category)
4WHERE category = 'Rubber Duck';
5
6-- REPLACE
7FROM order_items
8SELECT * REPLACE(
9  lpad(order_item_id::VARCHAR, 4, '0') AS order_item_id,
10  lpad(product_id::VARCHAR, 4, '0') AS product_id,
11);
12
13-- RENAME
14FROM customers
15SELECT * RENAME(customer_name AS full_name);
16
17-- Combination of all
18FROM customers
19SELECT 
20  * EXCLUDE(country)
21    REPLACE('000' || customer_id AS customer_id)
22    RENAME(customer_name AS full_name),
23;

DuckDBs Star Ausdruck kann noch mehr, alle Features stehen in der Dokumentation.

COLUMNS Ausdruck

Der erweiterte Star Ausdruck ist bereits sehr mächtig, aber der COLUMNS Ausdruck kann noch mehr. Zum Beispiel lässt sich derselbe Ausdruck auf alle passenden Spalten anwenden oder man kann Spalten mit regulären Ausdrücken umbenennen. Letzteres fällt für mich definitiv in die Kategorie „Gefahrenzone“.

1FROM products
2SELECT lower(
3  COLUMNS(* EXCLUDE (product_id, base_cost))
4);
5
6FROM order_items
7SELECT COLUMNS('(.+)_id') AS '\1';

Auch hier kann ich nur einen kleinen Einblick geben. Was damit sonst noch möglich ist, kann in der Dokumentation nachgeschaut werden.

Ein ausgefalleneres halbwegs realistisches Beispiel

Diese Features auf das halbwegs realistische Beispiel anzuwenden, war nicht ganz einfach. Eine weitere CTE war nötig, weswegen es etwas künstlicher wird. Aber ich hoffe, dass es leicht vorstellbar ist, wieso sich Queries im Laufe der Zeit so verändern.

1WITH
2  enriched AS (
3    FROM  o: orders
4    JOIN oi: order_items USING (order_id)
5    JOIN  p: products    USING (product_id)
6    JOIN  c: customers   USING (customer_id)
7    SELECT
8                duck_id:  p.product_id,
9              duck_name:  p.product_name,
10             duck_color:  COALESCE(p.color, 'Unknown'),
11          duck_material:  COALESCE(p.material, 'Rubber'),
12       customer_country:  c.country,
13
14          duck_discount:  COALESCE(oi.discount_pct, 0),
15       net_revenue_line:  oi.quantity * oi.unit_price * (1 - duck_discount),
16    --  tax_collected_line:  net_revenue_line * COALESCE(oi.tax_rate, 0),
17              cogs_line:  oi.quantity * p.base_cost,
18      gross_margin_line:  net_revenue_line - cogs_line,
19
20        is_premium_duck:  duck_discount >= 0.20 OR duck_color IN ('Gold', 'Platinum') OR duck_material ILIKE '%silicone%',
21    WHERE p.category = 'Rubber Duck'
22  ),
23  premium_with_description AS (
24    FROM enriched
25    SELECT
26      * EXCLUDE (duck_name, duck_color, duck_material),
27      duck_description: format('{0} ({1}, {2})', duck_name, duck_color, duck_material),
28    WHERE is_premium_duck
29  )
30
31FROM premium_with_description
32SELECT
33  duck_id,
34  duck_description,
35  customer_country,
36  SUM(COLUMNS('(.*)_line')) AS '\1',
37GROUP BY duck_id, duck_description, customer_country
38ORDER BY duck_id, customer_country;

Spalten flexibel und weniger langatmig selektieren zu können, ist praktisch. Aber neue KPIs hinzuzufügen, indem man einfach eine Spalte <KPI Name>_line zu enriched hinzufügt, ist schon ziemlich beeindruckend. Aber auch etwas unheimlich, da die Gefahr von Seiteneffekten wesentlich erhöht wird. Zum Vergleich, eine ähnliche Abfrage mit klassischem SQL ist ungefähr 15 Zeilen (35%) länger und jede Query muss angepasst werden, um neue KPIs hinzuzufügen.

Fazit

Ich hoffe, ich konnte zeigen, warum ich DuckDBs Friendly SQL für einen echten Game Changer für Software und Data Engineers halte, sobald der Code über ein paar Zeilen hinauswächst. Falls du DuckDB für dein nächstes Projekt in Betracht ziehst, solltest du bedenken, dass es explizit als einfaches und schnelles DBMS konzipiert ist. Für Orchestrierung, verteilte Workloads, Skalierbarkeit und andere Anforderungen größerer Projekte braucht es noch etwas weiteres. Eine Option ist MotherDuck, das einzige mir bekannte Tool, das DuckDB als Kern verwendet. Und vielleicht das richtige für dich.

Das hier war auch nur ein kleiner Einblick in die Features, die DuckDB zu bieten hat. Von netten Verbesserungen von SQL bis hin zu völlig neuen Möglichkeiten. Hier eine Liste in willkürlicher Reihenfolge von Dingen, die nicht mehr reingepasst haben:

• QUALIFY-Klausel zum direkten Filtern von Window-Function Ergebnissen.
• GROUP BY ALL und ORDER BY ALL, damit man die Spalten ganz weglassen kann.
• First-Class Unterstützung komplexer Datentypen wie Listen, Structs & Maps inklusive List Comprehension und Slicing.
• CREATE OR REPLACE als Kurzform zum Überschreiben von Objekten.
• Lambda-Funktionen, um SQL auf den Kopf zu stellen.
• Zusätzliche Join-Typen wie ASOF und POSITIONAL.
• Rekursive CTEs mit USING KEY, womit Zugriff auf Ergebnisse jenseits der vorherigen Iteration möglich ist.
• Dot-Operator für Function Chaining, damit Statements nicht in fünf schließenden Klammern enden.
• Und alles andere in der Friendly SQL Dokumentation.

Gibt es etwas von dieser Liste, worüber du mehr erfahren möchtest? Habe ich ein offensichtliches DuckDB Feature übersehen, was das halbwegs realistische Beispiel verbessern würde? Findest du meine SQL Formatierung schrecklich und bist der Meinung Prefix Aliase sollten verboten werden? Sag Bescheid und ich werde schauen was sich einrichten lässt.