2024-01-26

Multi-Datenbank-Unterstützung in DuckDB

Mark Raasveldt

DuckDB unterstützt Lesen und Schreiben nach MySQL, Postgres und SQLite

In der modernen Datenanalyse müssen Daten oft aus vielen verschiedenen Quellen kombiniert werden. Sie liegen vielleicht als CSV auf der eigenen Maschine, als Parquet in einem Data Lake oder in einer operativen Datenbank. DuckDB kann Daten zwischen vielen Quellen verschieben. Bisher war das aber auf das Lesen und das Schreiben in Dateien beschränkt.

DuckDB unterstützt fortgeschrittene Operationen auf seinem nativen Speicherformat – etwa Zeilen löschen, Werte aktualisieren oder das Schema einer Tabelle ändern. All das läuft mit ACID-Semantik. Dadurch bleibt die Datenbank immer in einem konsistenten Zustand: Operationen sind atomar und werden nicht nur teilweise ausgeführt.

DuckDB hat jetzt eine austauschbare Storage- und Transaktionsschicht. Darüber können DuckDB-Erweiterungen neue Storage-Backends anlegen. Diese Backends können alle Datenbankoperationen so unterstützen wie DuckDB selbst, einschließlich Einfügen von Daten und sogar Schemaänderungen.

Die Erweiterungen MySQL, Postgres und SQLite setzen diese neue Schicht um. DuckDB kann sich mit diesen Systemen verbinden und dort so arbeiten wie auf der eigenen nativen Storage-Engine.

Diese Erweiterungen ermöglichen eine Reihe nützlicher Dinge. Zum Beispiel können Sie:

Daten von SQLite nach JSON exportieren
Daten aus Parquet nach Postgres lesen
Daten von MySQL nach Postgres verschieben

… und vieles mehr.

Datenbanken anhängen

Das Statement ATTACH hängt eine neue Datenbank an das System. Standardmäßig wird eine native DuckDB-Datei angehängt. Über den Parameter TYPE lässt sich ein anderer Storage-Typ angeben. Alternativ geht das Präfix {type}:.

Mit der SQLite-Erweiterung können wir beispielsweise eine SQLite-Datenbankdatei öffnen und sie abfragen wie eine DuckDB-Datenbank.

ATTACH 'sakila.db' AS sakila (TYPE sqlite);
SELECT title, release_year, length FROM sakila.film LIMIT 5;

┌──────────────────┬──────────────┬────────┐
│      title       │ release_year │ length │
│     varchar      │   varchar    │ int64  │
├──────────────────┼──────────────┼────────┤
│ ACADEMY DINOSAUR │ 2006         │     86 │
│ ACE GOLDFINGER   │ 2006         │     48 │
│ ADAPTATION HOLES │ 2006         │     50 │
│ AFFAIR PREJUDICE │ 2006         │    117 │
│ AFRICAN EGG      │ 2006         │    130 │
└──────────────────┴──────────────┴────────┘

Der Befehl USE wechselt die Hauptdatenbank.

USE sakila;
SELECT first_name, last_name FROM actor LIMIT 5;

┌────────────┬──────────────┐
│ first_name │  last_name   │
│  varchar   │   varchar    │
├────────────┼──────────────┤
│ PENELOPE   │ GUINESS      │
│ NICK       │ WAHLBERG     │
│ ED         │ CHASE        │
│ JENNIFER   │ DAVIS        │
│ JOHNNY     │ LOLLOBRIGIDA │
└────────────┴──────────────┘

Die SQLite-Datenbank lässt sich behandeln, als wäre sie eine native DuckDB-Datenbank. Wir können zum Beispiel eine neue Tabelle anlegen, sie mit Werten aus einer Parquet-Datei füllen, ein paar Zeilen löschen und das Schema ändern.

CREATE TABLE lineitem AS FROM 'lineitem.parquet' LIMIT 1000;
DELETE FROM lineitem WHERE l_returnflag = 'N';
ALTER TABLE lineitem DROP COLUMN l_comment;

Die Tabelle duckdb_databases listet alle angehängten Datenbanken und ihre Typen.

SELECT database_name, path, type FROM duckdb_databases;

┌───────────────┬───────────┬─────────┐
│ database_name │   path    │  type   │
│    varchar    │  varchar  │ varchar │
├───────────────┼───────────┼─────────┤
│ sakila        │ sakila.db │ sqlite  │
│ memory        │ NULL      │ duckdb  │
└───────────────┴───────────┴─────────┘

Mix and Match

Das Anhängen verschiedener Datenbanktypen ist nützlich – noch mächtiger wird es in Kombination. Wir können etwa SQLite, MySQL und Postgres gleichzeitig anhängen.

ATTACH 'sqlite:sakila.db' AS sqlite;
ATTACH 'postgres:dbname=postgresscanner' AS postgres;
ATTACH 'mysql:user=root database=mysqlscanner' AS mysql;

Jetzt können wir Daten zwischen diesen angehängten Datenbanken verschieben und sie gemeinsam abfragen. Kopieren wir die Tabelle film nach MySQL und die Tabelle actor nach Postgres:

CREATE TABLE mysql.film AS FROM sqlite.film;
CREATE TABLE postgres.actor AS FROM sqlite.actor;

Nun können wir Tabellen aus diesen drei angehängten Datenbanken joinen. Suchen wir alle Schauspieler, die in Ace Goldfinger mitgespielt haben.

SELECT first_name, last_name
FROM mysql.film
JOIN sqlite.film_actor ON (film.film_id = film_actor.film_id)
JOIN postgres.actor ON (actor.actor_id = film_actor.actor_id)
WHERE title = 'ACE GOLDFINGER';

┌────────────┬───────────┐
│ first_name │ last_name │
│  varchar   │  varchar  │
├────────────┼───────────┤
│ BOB        │ FAWCETT   │
│ MINNIE     │ ZELLWEGER │
│ SEAN       │ GUINESS   │
│ CHRIS      │ DEPP      │
└────────────┴───────────┘

EXPLAIN auf der Abfrage zeigt, wie die Daten der verschiedenen Engines zum Endergebnis zusammengeführt werden.

┌───────────────────────────┐
│         PROJECTION        │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│         first_name        │
│         last_name         │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         HASH_JOIN         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           INNER           │
│     film_id = film_id     ├───────────────────────────────────────────┐
└─────────────┬─────────────┘                                           │
┌─────────────┴─────────────┐                             ┌─────────────┴─────────────┐
│         HASH_JOIN         │                             │           FILTER          │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │                             │   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│           INNER           │                             │ (title = 'ACE GOLDFINGER')│
│    actor_id = actor_id    ├──────────────┐              │                           │
└─────────────┬─────────────┘              │              └─────────────┬─────────────┘
┌─────────────┴─────────────┐┌─────────────┴─────────────┐┌─────────────┴─────────────┐
│        SQLITE_SCAN        ││       POSTGRES_SCAN       ││        MYSQL_SCAN         │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│    sakila.db:film_actor   ││           actor           ││            film           │
│   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   ││   ─ ─ ─ ─ ─ ─ ─ ─ ─ ─ ─   │
│          film_id          ││          actor_id         ││          film_id          │
│          actor_id         ││         first_name        ││           title           │
│                           ││         last_name         ││                           │
└───────────────────────────┘└───────────────────────────┘└───────────────────────────┘

Transaktionen

Alle in DuckDB ausgeführten Statements laufen in einer Transaktion. Wird kein explizites BEGIN TRANSACTION aufgerufen, läuft jedes Statement in einer eigenen Transaktion. Das gilt auch für Abfragen über andere Storage-Engines. Diese Engines unterstützen ebenfalls explizite BEGIN-, COMMIT- und ROLLBACK-Statements.

Wir können zum Beispiel in unserer angehängten SQLite-Datenbank eine Transaktion beginnen, eine Änderung vornehmen und sie zurückrollen. Die ursprünglichen Daten werden wiederhergestellt.

BEGIN;
TRUNCATE film;
SELECT title, release_year, length FROM film;

┌─────────┬──────────────┬────────┐
│  title  │ release_year │ length │
│ varchar │   varchar    │ int64  │
├─────────────────────────────────┤
│             0 rows              │
└─────────────────────────────────┘

ROLLBACK;
SELECT title, release_year, length FROM film LIMIT 5;

┌──────────────────┬──────────────┬────────┐
│      title       │ release_year │ length │
│     varchar      │   varchar    │ int64  │
├──────────────────┼──────────────┼────────┤
│ ACADEMY DINOSAUR │ 2006         │     86 │
│ ACE GOLDFINGER   │ 2006         │     48 │
│ ADAPTATION HOLES │ 2006         │     50 │
│ AFFAIR PREJUDICE │ 2006         │    117 │
│ AFRICAN EGG      │ 2006         │    130 │
└──────────────────┴──────────────┴────────┘

Transaktionen über mehrere Datenbanken

Jede Storage-Engine hat eigene Transaktionen, die eigenständig von der Engine verwaltet werden. Eine Transaktion in Postgres zum Beispiel ruft BEGIN TRANSACTION im Postgres-Client auf. Die Transaktion verwaltet Postgres selbst. Ebenso behandelt die Storage-Engine Commit und Rollback selbst.

Transaktionen dienen dem Lesen und dem Schreiben. Beim Lesen liefern sie einen konsistenten Snapshot der Datenbank. Beim Schreiben sorgen sie dafür, dass alle Daten einer Transaktion zusammengepackt und gleichzeitig geschrieben werden.

Führt eine Transaktion über mehrere angehängte Datenbanken, müssen wir mehrere Transaktionen öffnen: eine pro beteiligter Datenbank. Beim Lesen ist das kein Problem, beim Schreiben wird es kompliziert. Besonders beim COMMIT ist schwer sicherzustellen, dass entweder (a) jede Datenbank erfolgreich committed hat oder (b) jede zurückgerollt hat.

Deshalb ist es derzeit nicht unterstützt, in einer einzelnen Transaktion in mehrere angehängte Datenbanken zu schreiben. Stattdessen wird ein Fehler geworfen:

BEGIN;
CREATE TABLE postgres.new_table (i INTEGER);
CREATE TABLE mysql.new_table (i INTEGER);

Error: Attempting to write to database "mysql" in a transaction that has
already modified database "postgres" – a single transaction can only write
to a single attached database.

Daten zwischen Datenbanken kopieren

CREATE TABLE AS, INSERT INTO und COPY können Daten zwischen angehängten Datenbanken kopieren. Das eigene COPY FROM DATABASE ... TO kopiert alle Daten von einer Datenbank in eine andere. Das umfasst alle Tabellen und Views der Quelldatenbank.

-- attach a Postgres database
ATTACH 'postgres:dbname=postgresscanner' AS postgres;
-- attach a DuckDB file
ATTACH 'database.db' AS ddb;
-- export all tables and views from the Postgres database to the DuckDB file
COPY FROM DATABASE postgres TO ddb;

Eine Datenbank direkt öffnen

Das explizite Statement ATTACH ist nicht nötig, um einen anderen Datenbanktyp zu verbinden. Beim Instanziieren einer DuckDB-Instanz kann direkt ein anderer Typ über das Präfix {type}: verbunden werden. Für eine SQLite-Datei etwa sqlite:file.db. Für eine Postgres-Instanz postgres:dbname=postgresscanner. Das geht in jedem Client, einschließlich der CLI. Zum Beispiel:

CLI:

duckdb sqlite:file.db

Python:

import duckdb
con = duckdb.connect('sqlite:file.db')

Das entspricht dem Anhängen der Storage-Engine und einem anschließenden USE.

Fazit

DuckDBs austauschbare Storage-Engine-Architektur ermöglicht viele Einsatzszenarien. Durch das Anhängen mehrerer Datenbanken lassen sich Daten transaktionssicher für Bulk-ETL- oder ELT-Workloads extrahieren, ebenso für Data Virtualization on the fly. Die Techniken ergänzen sich gut: zum Beispiel regelmäßiger Bulk-Transfer und die letzten Datenpunkte on the fly.

Austauschbare Storage-Engines eröffnen auch neue Wege für gleichzeitige Schreiber in einer Datenplattform. Jeder Prozess könnte seine Ausgabe in eine transaktionale Datenbank schreiben, die Ergebnisse ließen sich in DuckDB zusammenführen – alles transaktionssicher. Analysen können dann auf der zentralen DuckDB-Datenbank mit besserer Leistung laufen.

Wir sind gespannt auf die kreativen Wege, auf denen Sie dieses Feature nutzen!

Ausblick

Wir wollen Leistung und Fähigkeiten der bestehenden Erweiterungen weiter verbessern. Außerdem kann die Community all diese Features nutzen, um weitere Datenbanken anzubinden.