duckdb_fdw: PostgreSQL 访问 DuckDB 的外部数据包装器

三月 31, 2024

摘要：duckdb_fdw是一个外部数据包装器（FDW），用于将 PostgreSQL 连接到 DuckDB 数据库文件。

PostgreSQL + DuckDB

DuckDB 是一个简单、可移植和高性能的嵌入式数据库引擎。它被设计用于支持分析查询工作负载，也称为在线分析处理（OLAP）。

本文包含以下部分：

  1. 特性
  2. 要求
  3. 用法
  4. 示例
  5. 限制

特性

  • 事务
  • 通过解析为不带WHERE子句的DELETE语句来支持TRUNCATE
  • 允许控制外部服务器在事务完成后是否保持连接打开。由keep_connections控制，默认为 on
  • 支持使用函数duckdb_fdw_get_connections()，列出缓存的外部服务器连接。
  • 支持使用函数duckdb_fdw_disconnect()、duckdb_fdw_disconnect_all()，丢弃缓存的外部服务器连接。
  • 使用batch_size选项支持批量INSERT
  • 支持带生成列的INSERT / UPDATE

要求

duckdb_fdw是在 MacOS 上开发的，并在 Linux 上进行了测试，因此它应该可以在任何 POSIX 兼容的系统上运行。

该扩展适用于 PostgreSQL 9.6 和更新版本，并在某些版本的 DuckDB 中完成过验证。

用法

CREATE SERVER 选项

duckdb_fdw通过CREATE SERVER命令可以接受以下选项：

  • database 类型 string，必需

    DuckDB 数据库路径。

  • truncatable 类型 boolean，可选，默认值为 false

    允许使用TRUNCATE命令截断外部表。

  • keep_connections 类型 boolean，可选，默认值为 false

    在 PostgreSQL 和 DuckDB 之间没有 SQL 操作时，允许保持与 DuckDB 的连接。

  • batch_size 类型 integer，可选，默认值为 1

    指定在单个INSERT操作中应插入的行数。可以针对各个表修改此设置。

  • temp_directory 类型 string，可选，默认值为 NULL

    指定写入临时文件的目录。

CREATE USER MAPPING 选项

在 DuckDB 中没有用户或密码的概念，因此duckdb_fdw不需要任何CREATE USER MAPPING命令。

在操作系统中，duckdb_fdw以 PostgreSQL 服务器用户的权限，执行工作。通常是postgres操作系统用户。为了与 DuckDB 数据库进行交互而不出现访问错误，请确保此用户对 DuckDB 文件具有访问权限，有时还需要有文件目录的权限。

  • 通过 DuckDB 数据库文件的路径，读取所有目录的权限；
  • 读取 DuckDB 数据库文件的权限；

CREATE FOREIGN TABLE 选项

duckdb_fdw通过CREATE FOREIGN TABLE命令可以接受以下表级选项：

  • table 类型 string，可选，无默认值

    DuckDB 表名。如果和 PostgreSQL 中外部表的名称不同，则使用。另请参阅有关标识符大小写处理的内容。

  • truncatable 类型 boolean，可选，默认值同CREATE SERVER的相应选项

    有关详细信息，请参见CREATE SERVER选项部分。

  • batch_size 类型 integer，可选，默认值同CREATE SERVER的相应选项

    有关详细信息，请参见CREATE SERVER选项部分。

duckdb_fdw通过CREATE FOREIGN TABLE命令可以接受以下列级选项：

  • column_name 类型 string，可选，无默认值

    此选项用于提供远程服务器上的列的名称。另请参阅有关标识符大小写处理的内容。

  • column_type 类型 string，可选，无默认值

    选项用于转换 DuckDB 中的 INT 列（Unix 纪元时间），在 PostgreSQL 中被视为/显示为 TIMESTAMP。

  • key 类型 boolean，可选，默认值为 false

    指示该列为 DuckDB 表的主键或唯一键的一部分。

IMPORT FOREIGN SCHEMA 选项

duckdb_fdw支持 IMPORT FOREIGN SCHEMA（PostgreSQL 9.5+），但不接受此命令的自定义选项。

TRUNCATE 支持

duckdb_fdw实现了外部数据包装器的TRUNCATE API，在 PostgreSQL 14 及以上版本中可用。

由于 SQlite 不提供TRUNCATE命令，所以它是用一个简单的无条件限定的DELETE操作来模拟的。

TRUNCATE ... CASCADE的支持未描述。

函数

除了标准的duckdb_fdw_handler()和duckdb_fdw_validator()函数外，duckdb_fdw还提供了以下实用函数，供用户调用：

  • SETOF record duckdb_fdw_get_connections(server_name text, valid bool)

  • bool duckdb_fdw_disconnect(text)

    关闭当前会话中从 PostgreSQL 到 DuckDB 的连接。

  • bool duckdb_fdw_disconnect_all()

  • duckdb_fdw_version()

    返回标准的“整数版本号”，公式为major version * 10000 + minor version * 100 + bugfix。

 duckdb_fdw_version
--------------------
              20300

duckdb_execute

FUNCTION duckdb_execute(server name, stmt text) RETURNS void

此函数可用于在远程 DuckDB 服务器上执行任意 SQL 语句。这只适用于不返回结果的语句（通常是 DDL 语句）。

使用此函数时要小心，因为它可能会干扰 duckdb_fdw 的事务管理。请记住，在 DuckDB 中运行一个 DDL 语句会发出一个隐式的 COMMIT。建议您在多语句事务块之外使用此函数。

使用 duckdb_fdw 不支持的命令非常有用，例如，

  • 直接添加更多的表或视图到 DuckDB。
SELECT duckdb_execute('duckdb_server',
'create or replace view iris_parquet as select * from parquet_scan(''temp/iris.parquet'');');

create foreign TABLE duckdb.iris_parquet(
  "Sepal.Length" float,
  "Sepal.Width" float,
  "Petal.Length" float,
  "Petal.Width" float,
  "Species" text)
SERVER duckdb_server OPTIONS (table 'iris_parquet');

-- or an easy way

IMPORT FOREIGN SCHEMA public limit to (iris_parquet) FROM SERVER
duckdb_server INTO duckdb;
  • 在外部表上运行 COPY 命令
SELECT duckdb_execute('duckdb_server'
,'CREATE TABLE test (a INTEGER, b INTEGER, c VARCHAR(10));
');
SELECT duckdb_execute('duckdb_server'  
,'COPY test FROM ''/tmp/test.csv'';');

标识符大小写处理

PostgreSQL 默认情况下将标识符转换为小写。DuckDB 的行为未描述。注意表名和列名可能的潜在问题非常重要。

生成列

DuckDB 提供了对生成列的支持。duckdb_fdw在这种列上的行为尚未描述。

请注意，虽然duckdb_fdw会INSERT或UPDATE生成列的值到 DuckDB，但没有任何办法可以阻止 DuckDB 中的值被修改，因此不能保证在后续的SELECT操作中，该列仍然包含预期的生成值。这个限制也适用于postgres_fdw。

有关生成列的更多详细信息，请参见：

示例

安装扩展

在您需要的数据库上，以 PostgreSQL 超级用户执行一次。

CREATE EXTENSION duckdb_fdw;

使用适当的配置创建外部服务器：

对于您需要的外部数据源，以 PostgreSQL 超级用户执行一次。请使用database选项指定 DuckDB 数据库路径。

CREATE SERVER duckdb_server
FOREIGN DATA WRAPPER duckdb_fdw
OPTIONS (
  database '/path/to/database'
);

将外部服务器上的使用权授予 PostgreSQL 中的普通用户：

对于一个 PostgreSQL 中的普通用户（非超级用户），以 PostgreSQL 超级用户执行一次。只在真正需要的地方，使用超级用户才是个好做法，所以让我们允许一个普通用户使用外部服务器（这不是示例中所必需的，但在安全上是推荐的）。

GRANT USAGE ON FOREIGN SERVER duckdb_server TO pguser;

其中pguser是一个要使用外部服务器（和外部表）的示例用户。

用户映射

在 DuckDB 中没有用户或密码的概念，因此duckdb_fdw不需要任何CREATE USER MAPPING命令。关于访问问题，请参见 CREATE USER MAPPING 选项。

创建外部表

如果执行了正确的GRANT USAGE ON FOREIGN SERVER，所有CREATE FOREIGN TABLE的 SQL 命令都可以作为普通 PostgreSQL 用户执行。出于安全原因，不需要 PostgreSQL 超级用户，但也可以使用 PostgreSQL 超级用户。

如果 DuckDB 表名与外部表名不同，请指定table选项。

CREATE FOREIGN TABLE t1 (
  a integer,
  b text
)
SERVER duckdb_server
OPTIONS (
  table 't1_duckdb'
);

如果您想要更新表，请将OPTIONS (key 'true')添加到主键或唯一键中，如下所示：

CREATE FOREIGN TABLE t1(
  a integer OPTIONS (key 'true'),
  b text
)
SERVER duckdb_server 
OPTIONS (
  table 't1_duckdb'
);

如果你需要将 DuckDB 中的 INT 列（Unix 纪元时间）转换为 PostgreSQL 中的TIMESTAMP，请在 PostgreSQL 中定义外部表时添加 OPTIONS (column_type 'INT')，如下所示：

CREATE FOREIGN TABLE t1(
  a integer,
  b text,
  c timestamp without time zone OPTIONS (column_type 'INT')
)
SERVER duckdb_server
OPTIONS (
  table 't1_duckdb'
);

和上面一样，但带有列名的别名：

CREATE FOREIGN TABLE t1(
  a integer,
  b text OPTIONS (column_name 'test_id'),
  c timestamp without time zone OPTIONS (column_type 'INT', column_name 'unixtime')
)
SERVER duckdb_server
OPTIONS (
  table 't1_duckdb'
);

将 DuckDB 数据库作为模式导入 PostgreSQL：

IMPORT FOREIGN SCHEMA someschema
FROM SERVER duckdb_server
INTO public;

注意：someschema没有特殊意义，可以设置为任意值。

访问外部表

对于前面示例中的表，

SELECT * FROM t1;

限制

  • 不支持INSERT到带有外部分区的分区表中。这会显示错误Not support partition insert。
  • 如果存在带ON DELETE CASCADE子句的外键引用，则duckdb_fdw中的TRUNCATE始终会删除父表和子表的数据（无论用户输入TRUNCATE table CASCADE还是TRUNCATE table RESTRICT）。
  • 不支持RETURNING。

搜索

分类

标签