原文：Multi-Database Support in DuckDB[1]
翻译：ChatGPT 校对：alitrack
摘要
DuckDB 除了存储在其自己格式中的数据库，还可以附加 MySQL、Postgres 和 SQLite 等数据库。这允许数据被读入 DuckDB 并在这些系统之间以方便的方式移动。
在现代数据分析中，数据经常需要从各种不同的来源进行合并。数据可能存储在您的计算机上的 CSV 文件中，也可能存储在数据湖中的 Parquet 文件中，或者存储在操作性数据库中。DuckDB 对于在许多不同的数据源之间移动数据有很强的支持。然而，这种支持以前仅限于读取数据和将数据写入文件。
DuckDB 支持对其自己的本地存储格式进行高级操作，比如删除行、更新值或更改表的模式。它支持所有这些操作都使用 ACID 语义。这保证了您的数据库始终处于一个合理的状态——操作是原子的，不会部分完成。
DuckDB 现在具有可插拔的存储和事务层。这种灵活的层允许 DuckDB 扩展创建新的存储后端。这些存储后端可以支持 DuckDB 支持的所有数据库操作，包括插入数据甚至修改模式。
MySQL、Postgres 和 SQLite 扩展实现了这个新的可插拔的存储和事务层，允许 DuckDB 连接到这些系统并以与其自己的本地存储引擎相同的方式对其进行操作。
这些扩展启用了许多有用的功能。例如，使用这些扩展，您可以：
• 从 SQLite 导出数据到 JSON
• 从 Parquet 读取数据到 Postgres
• 将数据从 MySQL 移动到 Postgres
…等等。
附加数据库
ATTACH 语句[2]可用于将新数据库附加到系统中。默认情况下，将附加一个本地 DuckDB 文件。TYPE
参数可用于指定不同的存储类型。或者，可以使用{type}:
前缀。
例如，使用 SQLite 扩展，我们可以打开一个 SQLite 数据库文件[3]并像查询 DuckDB 数据库一样查询它。
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 │
└──────────────────┴──────────────┴────────┘
使用USE
命令切换主数据库。
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 │
└────────────┴──────────────┘
SQLite 数据库可以被操作，就像它是一个本地 DuckDB 数据库一样。例如，我们可以创建一个新表，用来存放来自 Parquet 文件的值，从表中删除一些行，并修改表的模式。
CREATE TABLE lineitem AS FROM 'lineitem.parquet' LIMIT 1000;
DELETE FROM lineitem WHERE l_returnflag = 'N';
ALTER TABLE lineitem DROP COLUMN l_comment;
duckdb_databases
表包含所有附加数据库及其类型的列表。
SELECT database_name, path, type FROM duckdb_databases;
┌───────────────┬───────────┬─────────┐
│ database_name │ path │ type │
│ varchar │ varchar │ varchar │
├───────────────┼───────────┼─────────┤
│ sakila │ sakila.db │ sqlite │
│ memory │ NULL │ duckdb │
└───────────────┴───────────┴─────────┘
混合和匹配
附加到不同的数据库类型是有用的——当与组合使用时，它变得更加强大。例如，我们可以附加一个 SQLite、MySQL 和一个 Postgres 数据库。
ATTACH 'sqlite:sakila.db' AS sqlite;
ATTACH 'postgres:dbname=postgresscanner' AS postgres;
ATTACH 'mysql:user=root database=mysqlscanner' AS mysql;
现在我们可以在这些附加的数据库之间移动数据并一起查询它们。让我们将film
表复制到 MySQL，将actor
表复制到 Postgres：
CREATE TABLE mysql.film AS FROM sqlite.film;
CREATE TABLE postgres.actor AS FROM sqlite.actor;
现在我们可以从这三个附加的数据库中连接表。让我们找到所有出演Ace Goldfinger
的演员。
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
显示了来自不同引擎的数据是如何组合成最终的查询结果的。
⚠️: 自上次发布以来，Postgres 扩展已经进行了一些更改。使用FORCE INSTALL postgres
来安装扩展的最新版本。
事务
DuckDB 中执行的所有语句都在一个事务中执行。如果没有显式调用BEGIN TRANSACTION
，则每个语句将在自己的事务中执行。这也适用于在其他存储引擎上执行的查询。这些存储引擎也支持显式的BEGIN
、COMMIT
和ROLLBACK
语句。
例如，我们可以在我们附加的SQLite
数据库中开始一个事务，进行一些更改，然后回滚它。原始数据将被恢复。
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 │
└──────────────────┴──────────────┴────────┘
多数据库交易
每个存储引擎都有自己的事务，是独立的并由存储引擎本身管理。例如，打开一个 Postgres 中的事务会调用 Postgres 客户端中的BEGIN TRANSACTION
。事务由 Postgres 本身管理。同样，当事务提交或回滚时，存储引擎本身处理这个过程。
事务既用于读取数据，也用于写入数据。对于读取数据，它们用于提供数据库的一致快照。对于写入，它们用于确保事务中的所有数据被打包在一起并同时写入。
当执行涉及多个附加数据库的事务时，我们需要打开多个事务：每个用于事务中使用的附加数据库。当从数据库读取数据时，这并不是一个问题，但当写入时就变得复杂。特别是，当我们想要COMMIT
一个事务时，很难确保要么（a）每个数据库都已成功提交，要么（b）每个数据库都已回滚。
因此，目前不支持在单个事务中写入到多个附加数据库。相反，当尝试这样做时会抛出错误：
BEGIN;
CREATE TABLE postgres.new_table(i INT);
CREATE TABLE mysql.new_table(i INT);
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.
在数据库之间复制数据
CREATE TABLE AS
、INSERT INTO
和COPY
可以用于在不同的附加数据库之间复制数据。专用的COPY FROM DATABASE ... TO
[4]可以用于将一个数据库中的所有数据复制到另一个数据库。这包括存储在源数据库中的所有表和视图。
-- 附加一个Postgres数据库
ATTACH 'postgres:dbname=postgresscanner' AS postgres;
-- 附加一个DuckDB文件
ATTACH 'database.db' AS ddb;
-- 将所有表和视图从Postgres数据库导出到DuckDB文件
COPY FROM DATABASE postgres TO ddb;
请注意，此语句目前仅在开发版本中可用。它将在下一个 DuckDB 版本（v0.10）中可用。
直接打开数据库
不需要显式的ATTACH
语句来连接到不同的数据库类型。在实例化 DuckDB 实例时，可以直接使用{type}:
前缀连接到不同的数据库类型。例如，要连接到一个 SQLite 文件，使用sqlite:file.db
。要连接到一个 Postgres 实例，使用postgres:dbname=postgresscanner
。这可以在任何客户端中完成，包括 CLI。例如：
CLI:
duckdb sqlite:file.db
Python:
import duckdb
con = duckdb.connect('sqlite:file.db')
这等同于附加存储引擎并随后运行USE
。
结论
DuckDB 的可插拔存储引擎架构实现了许多用例。通过附加多个数据库，数据可以以事务安全的方式进行批量 ETL 或 ELT 工作负载的提取，以及即时数据虚拟化工作负载。这些技术在组合使用时也能很好地发挥作用，例如，定期进行大量数据的移动，同时即时填充最后几个数据点。
可插拔存储引擎还为处理数据平台中的并发写入提供了新的方式。每个单独的进程可以将其输出写入一个事务性数据库，结果可以在 DuckDB 中组合——所有这些都是在事务安全的情况下进行的。然后，数据分析任务可以在集中的 DuckDB 数据库上进行，以提高性能。
我们期待听到您能够使用这一功能的许多创造性方式！
未来工作
我们打算继续增强现有扩展的性能和功能。此外，社区可以利用所有这些功能来连接到其他数据库。
引用链接
[1]
Multi-Database Support in DuckDB: https://duckdb.org/2024/01/26/multi-database-support-in-duckdb.html[2]
ATTACH 语句: https://duckdb.org/docs/sql/statements/attach[3]
一个 SQLite 数据库文件: https://github.com/duckdb/sqlite_scanner/raw/main/data/db/sakila.db[4]
COPY FROM DATABASE ... TO
: https://duckdb.org/docs/sql/statements/copy.html#copy-from-database--to
