三月 31, 2024
摘要:duckdb_fdw
是一个外部数据包装器(FDW),用于将 PostgreSQL 连接到 DuckDB 数据库文件。
+
DuckDB 是一个简单、可移植和高性能的嵌入式数据库引擎。它被设计用于支持分析查询工作负载,也称为在线分析处理(OLAP)。
目录
特性
- 事务
- 通过解析为不带
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
。