2022-05-04

Freundlicheres SQL mit DuckDB

Alex Monahan

Eine elegante Nutzererfahrung ist ein zentrales Designziel von DuckDB. Dieses Ziel leitet einen Großteil der Architektur: DuckDB ist einfach zu installieren, fügt sich nahtlos in andere Datenstrukturen wie Pandas, Arrow und R-DataFrames ein und hat keine Abhängigkeiten. Parallelisierung läuft automatisch, und wenn eine Berechnung den verfügbaren Speicher übersteigt, werden Daten elegant auf die Platte gepuffert. Und natürlich macht DuckDBs Verarbeitungsgeschwindigkeit es leichter, mehr Arbeit zu erledigen.

SQL ist jedoch nicht berühmt dafür, nutzerfreundlich zu sein. DuckDB will das ändern! DuckDB enthält sowohl eine Relational API für DataFrame-artige Berechnungen als auch eine stark Postgres-kompatible SQL-Variante. Wenn Sie DataFrame-artige Berechnungen bevorzugen, freuen wir uns über Feedback zu unserer Roadmap. Wenn Sie SQL-Fan sind, lesen Sie weiter, wie DuckDB Innovation und Pragmatismus zusammenbringt, um SQL in DuckDB einfacher zu schreiben als anderswo. Melden Sie sich auf GitHub oder Discord und sagen Sie uns, welche weiteren Features Ihre SQL-Workflows vereinfachen würden. Machen Sie mit, wenn wir einem alten Hund neue Tricks beibringen!

SELECT * EXCLUDE

Eine traditionelle SQL-SELECT-Abfrage verlangt, dass angeforderte Spalten explizit angegeben werden, mit einer wichtigen Ausnahme: dem Platzhalter *. SELECT * erlaubt SQL, alle relevanten Spalten zurückzugeben. Das gibt enorme Flexibilität, besonders wenn Abfragen aufeinander aufbauen. Oft interessieren uns aber fast alle Spalten. In DuckDB geben Sie einfach an, welche Spalten Sie EXCLUDE möchten:

SELECT * EXCLUDE (jar_jar_binks, midichlorians) FROM star_wars;

So sparen wir Zeit beim wiederholten Tippen aller Spalten, verbessern die Lesbarkeit und behalten Flexibilität, wenn der zugrunde liegenden Tabelle weitere Spalten hinzugefügt werden.

DuckDBs Umsetzung dieses Konzepts kann sogar Ausschlüsse aus mehreren Tabellen in einem Statement handhaben:

SELECT
    sw.* EXCLUDE (jar_jar_binks, midichlorians),
    ff.* EXCLUDE cancellation
FROM star_wars sw, firefly ff;

SELECT * REPLACE

Ähnlich wollen wir oft alle Spalten einer Tabelle nutzen, abgesehen von ein paar kleinen Anpassungen. Das würde ebenfalls * verhindern und eine Liste aller Spalten erfordern, einschließlich der unbearbeiteten. In DuckDB wenden Sie Änderungen an wenigen Spalten einfach mit REPLACE an:

SELECT
    * REPLACE (movie_count+3 AS movie_count, show_count*1000 AS show_count)
FROM star_wars_owned_by_disney;

So können Views, CTEs oder Subqueries sehr knapp aufeinander aufbauen und bleiben anpassbar, wenn neue zugrunde liegende Spalten dazukommen.

GROUP BY ALL

Eine häufige Ursache für repetitiven, wortreichen SQL-Code ist die Notwendigkeit, Spalten sowohl in der SELECT- als auch in der GROUP BY-Klausel anzugeben. Theoretisch gibt das SQL Flexibilität, in der Praxis selten einen Mehrwert. DuckDB bietet jetzt das GROUP BY, das wir alle erwartet haben, als wir SQL lernten – einfach GROUP BY ALL Spalten in der SELECT-Klausel, die nicht in einer Aggregatfunktion stehen!

SELECT
    systems,
    planets,
    cities,
    cantinas,
    sum(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;
-- GROUP BY systems, planets, cities, cantinas

Änderungen an einer Abfrage müssen jetzt nur noch an einer Stelle statt an zwei gemacht werden! Außerdem verhindert das viele Fehler, bei denen Spalten aus einer SELECT-Liste entfernt, aber nicht aus dem GROUP BY, was zu Duplikaten führt.

Das vereinfacht nicht nur viele Abfragen drastisch, es macht die Klauseln EXCLUDE und REPLACE oben auch in viel mehr Situationen nützlich. Stellen Sie sich vor, wir wollten die Abfrage oben anpassen, indem wir das Ausmaß von Schurken und Niedertracht in jeder einzelnen Cantina nicht mehr berücksichtigen:

SELECT
    * EXCLUDE (cantinas, booths, scum, villainy),
    sum(scum + villainy) AS total_scum_and_villainy
FROM star_wars_locations
GROUP BY ALL;
-- GROUP BY systems, planets, cities

Das ist knappes und flexibles SQL! Wie viele Ihrer GROUP BY-Klauseln könnten so umgeschrieben werden?

ORDER BY ALL

Eine weitere häufige Ursache für Wiederholung in SQL ist die Klausel ORDER BY. DuckDB und andere RDBMS haben das zuvor angegangen, indem Abfragen die Nummern der zu sortierenden Spalten angeben durften (z. B. ORDER BY 1, 2, 3). Häufig ist das Ziel aber, nach allen Spalten der Abfrage von links nach rechts zu sortieren, und das Pflegen dieser Zahlenliste beim Hinzufügen oder Entfernen von Spalten ist fehleranfällig. In DuckDB einfach ORDER BY ALL:

SELECT
    age,
    sum(civility) AS total_civility
FROM star_wars_universe
GROUP BY ALL
ORDER BY ALL;
-- ORDER BY age, total_civility

Das ist besonders nützlich beim Bauen von Zusammenfassungen, weil viele andere Client-Werkzeuge Ergebnisse automatisch so sortieren. DuckDB unterstützt auch ORDER BY ALL DESC, um jede Spalte umgekehrt zu sortieren, sowie Optionen für NULLS FIRST oder NULLS LAST.

Spaltenaliase in WHERE / GROUP BY / HAVING

In vielen SQL-Dialekten kann ein in einer SELECT-Klausel definiertes Alias nirgendwo außer in der ORDER BY-Klausel desselben Statements genutzt werden. Das führt oft zu wortreichen CTEs oder Subqueries, nur um diese Aliase zu nutzen. In DuckDB kann ein Nicht-Aggregat-Alias in der SELECT-Klausel sofort in den Klauseln WHERE und GROUP BY genutzt werden, und Aggregat-Aliase in der HAVING-Klausel, sogar auf derselben Query-Tiefe. Keine Subquery nötig!

SELECT
    only_imperial_storm_troopers_are_so_precise AS nope,
    turns_out_a_parsec_is_a_distance AS very_speedy,
    sum(mistakes) AS total_oops
FROM oops
WHERE
    nope = 1
GROUP BY
    nope,
    very_speedy
HAVING
    total_oops > 0;

Groß-/Kleinschreibung ignorieren, dabei die Schreibweise behalten

DuckDB erlaubt, dass Abfragen unabhängig von Groß- und Kleinschreibung sind, behält aber die angegebene Schreibweise beim Fluss der Daten in und aus dem System bei. Das vereinfacht Abfragen in DuckDB und stellt die Kompatibilität mit externen Bibliotheken sicher.

CREATE TABLE mandalorian AS SELECT 1 AS "THIS_IS_THE_WAY";
SELECT this_is_the_way FROM mandalorian;

Freundliche Fehlermeldungen

Unabhängig von der Erfahrung und trotz DuckDBs Bemühungen, unsere Absichten zu verstehen, machen wir alle Fehler in unseren SQL-Abfragen. Viele RDBMS lassen Sie die Macht nutzen, um einen Fehler zu erspüren. In DuckDB erhalten Sie bei einem Tippfehler in einem Spalten- oder Tabellennamen einen hilfreichen Vorschlag zum ähnlichsten Namen. Nicht nur das: Ein Pfeil zeigt direkt auf die problematische Stelle in Ihrer Abfrage.

SELECT * FROM star_trek;

Error: Catalog Error: Table with name star_trek does not exist!
Did you mean "star_wars"?
LINE 1: SELECT * FROM star_trek;
                      ^

(Keine Sorge, Enten und ententhematische Datenbanken mögen auch ein bisschen Trek).

DuckDBs Vorschläge sind sogar kontextspezifisch. Hier erhalten wir einen Vorschlag, die ähnlichste Spalte aus der Tabelle zu nutzen, die wir abfragen.

SELECT long_ago FROM star_wars;

Error: Binder Error: Referenced column "long_ago" not found in FROM clause!
Candidate bindings: "star_wars.long_long_ago"
LINE 1: SELECT long_ago FROM star_wars;
               ^

String-Slicing

Auch als SQL-Fans wissen wir, dass SQL von neueren Sprachen einiges lernen kann. Statt wuchtiger SUBSTRING-Funktionen können Sie Strings in DuckDB mit Klammersyntax slicen. Hinweis: SQL muss 1-indiziert sein, das ist ein kleiner Unterschied zu anderen Sprachen (hält DuckDB intern aber konsistent und ähnlich zu anderen DBs).

SELECT 'I love you! I know'[:-3] AS nearly_soloed;

Einfache List- und Struct-Erzeugung

DuckDB bietet verschachtelte Typen, um flexiblere Datenstrukturen als das rein relationale Modell zu erlauben, bei hoher Leistung. Damit sie so einfach wie möglich zu nutzen sind, verwendet das Anlegen einer LIST (Array) oder eines STRUCT (Objekt) eine einfachere Syntax als andere SQL-Systeme. Datentypen werden automatisch erkannt.

SELECT
    ['A-Wing', 'B-Wing', 'X-Wing', 'Y-Wing'] AS starfighter_list,
    {name: 'Star Destroyer', common_misconceptions: 'Can''t in fact destroy a star'} AS star_destroyer_facts;

List-Slicing

Klammersyntax kann auch zum Slicen einer LIST genutzt werden. Wieder 1-indiziert für SQL-Kompatibilität.

SELECT
    starfighter_list[2:2] AS dont_forget_the_b_wing
FROM (SELECT ['A-Wing', 'B-Wing', 'X-Wing', 'Y-Wing'] AS starfighter_list);

Struct-Punktnotation

Nutzen Sie bequeme Punktnotation, um den Wert eines bestimmten Schlüssels in einer DuckDB-STRUCT-Spalte zu lesen. Enthalten Schlüssel Leerzeichen, können doppelte Anführungszeichen genutzt werden.

SELECT
    planet.name,
    planet."Amount of sand"
FROM (SELECT {name: 'Tatooine', 'Amount of sand': 'High'} AS planet);

Nachgestellte Kommas

Haben Sie jemals Ihre letzte Spalte aus einem SQL-SELECT entfernt und einen Fehler bekommen, nur um festzustellen, dass Sie auch das nachgestellte Komma entfernen mussten!? Nie? Ok, Jedi… Im Ernst: Dieses Feature ist ein Beispiel für DuckDBs Reaktionsfähigkeit auf die Community. In unter 2 Tagen nach diesem Issue in einem Tweet (nicht einmal über DuckDB!) war das Feature gebaut, getestet und in den Hauptzweig gemerged. Sie können nachgestellte Kommas an vielen Stellen in Ihrer Abfrage setzen, und wir hoffen, das erspart Ihnen die langweiligsten, aber frustrierendsten Fehler!

SELECT
    x_wing,
    proton_torpedoes,
    --targeting_computer
FROM luke_whats_wrong
GROUP BY
    x_wing,
    proton_torpedoes,
;

Funktionsaliase aus anderen Datenbanken

Für viele Funktionen unterstützt DuckDB mehrere Namen, um sich an andere Datenbanksysteme anzulehnen. Enten sind schließlich vielseitig – sie können fliegen, schwimmen und laufen! Am häufigsten unterstützt DuckDB PostgreSQL-Funktionsnamen, aber auch viele SQLite-Namen und einige aus anderen Systemen. Wenn Sie Workloads nach DuckDB migrieren und ein anderer Funktionsname hilfreich wäre, schreiben Sie uns – sie sind sehr leicht zu ergänzen, solange das Verhalten dasselbe ist! Details in unserer Funktionsdokumentation.

SELECT
    'Use the Force, Luke'[:13] AS sliced_quote_1,
    substr('I am your father', 1, 4) AS sliced_quote_2,
    substring('Obi-Wan Kenobi, you''re my only hope', 17, 100) AS sliced_quote_3;

Automatisches Hochzählen doppelter Spaltennamen

Beim Bauen einer Abfrage, die ähnliche Tabellen joint, stoßen Sie oft auf doppelte Spaltennamen. Ist die Abfrage das Endergebnis, gibt DuckDB die doppelten Spaltennamen einfach unverändert zurück. Wird die Abfrage jedoch zum Anlegen einer Tabelle genutzt oder in einer Subquery oder einem Common Table Expression verschachtelt (wo doppelte Spalten von anderen Datenbanken verboten sind!), weist DuckDB den wiederholten Spalten automatisch neue Namen zu, um das Prototyping von Abfragen zu erleichtern.

SELECT
    *
FROM (
    SELECT
        s1.tie_fighter,
        s2.tie_fighter
    FROM squadron_one s1
    CROSS JOIN squadron_two s2
    ) theyre_coming_in_too_fast;

Implizite Typcasts

DuckDB setzt auf spezifische Datentypen für die Leistung, versucht aber, bei Bedarf automatisch zwischen Typen zu casten. Beim Join zwischen Integer und Varchar castet DuckDB sie zum Beispiel automatisch auf denselben Typ und führt den Join erfolgreich aus. Ein List- oder IN-Ausdruck kann auch mit einer Mischung von Typen angelegt werden, sie werden ebenfalls automatisch gecastet. Außerdem sind INTEGER und BIGINT austauschbar, und dank DuckDBs neuer Speicherkompression braucht ein BIGINT meist nicht einmal extra Platz! So können Sie Ihre Daten als optimalen Datentyp speichern und trotzdem leicht nutzen – das Beste aus beiden Welten!

CREATE TABLE sith_count_int AS SELECT 2::INTEGER AS sith_count;
CREATE TABLE sith_count_varchar AS SELECT 2::VARCHAR AS sith_count;

SELECT
    *
FROM sith_count_int s_int
JOIN sith_count_varchar s_char
  ON s_int.sith_count = s_char.sith_count;

Weitere freundliche Features

Es gibt viele weitere Features von DuckDB, die die Analyse mit SQL erleichtern!

DuckDB macht die Arbeit mit Zeit auf viele Arten leichter, unter anderem durch mehrere verschiedene Syntaxen (aus anderen Datenbanken) für den INTERVAL-Datentyp, mit dem eine Zeitdauer angegeben wird.

DuckDB setzt auch mehrere SQL-Klauseln außerhalb der klassischen Kernklauseln um, darunter die SAMPLE-Klausel zum schnellen Auswählen einer zufälligen Teilmenge Ihrer Daten und die QUALIFY-Klausel, mit der die Ergebnisse von Window-Funktionen gefiltert werden können (ähnlich wie eine HAVING-Klausel für Aggregationen).

Die Klausel DISTINCT ON erlaubt DuckDB, eindeutige Kombinationen einer Teilmenge der Spalten in einer SELECT-Klausel zu wählen und für Spalten, die nicht auf Eindeutigkeit geprüft werden, die erste Datenzeile zurückzugeben.

Ideen für die Zukunft

Neben dem bereits Umgesetzten wurden mehrere weitere Verbesserungen vorgeschlagen. Sagen Sie uns, wenn eine besonders nützlich wäre – wir sind flexibel mit unserer Roadmap! Wenn Sie beitragen möchten, sind wir sehr offen für PRs, und Sie können sich vorab auf GitHub oder Discord melden, um das Design eines neuen Features zu besprechen.

• Spalten über Regex wählen
  • Entscheiden, welche Spalten mit einem Muster gewählt werden, statt Spalten explizit anzugeben
  • ClickHouse unterstützt das mit dem COLUMNS-Ausdruck
• Inkrementelle Spaltenaliase
  • Auf zuvor definierte Aliase in nachfolgenden berechneten Spalten verweisen, statt die Berechnungen erneut anzugeben
• Punktoperatoren für JSON-Typen
  • Die JSON-Erweiterung ist brandneu (siehe unsere Dokumentation!) und setzt bereits freundliche ->- und ->>-Syntax um

Danke, dass Sie DuckDB ausprobieren! Möge die Macht mit Ihnen sein…