DuckDB与PostgreSQL结合一下，PostgreSQL会如虎添翼

PostgreSQL考试认证中心 2023-05-04

3758

背景

DuckDB简介

DuckDB 是一款嵌入式数据库，主要用于嵌入其他程序执行快速的 SQL 分析查询，官方称其为“分析型数据库中的 SQLite”。DuckDB 易于安装和使用，没有外部依赖，同时提供了 C/C++、Python 以及 R 的语言绑定。 DuckDB 由荷兰 Centrum Wiskunde & Informatica 的数据库架构组开发，他们还开发了 MonetDB （一款开源的列式存储数据库）。DuckDB 采用 MIT 开源协议，并且在 GitHub 上开放了源代码。DuckDB 于 2019 年 6 月 27日首次发布了官方的 0.1 发布版。

关于它的更多的介绍，可以见论文：https://duckdb.org/pdf/SIGMOD2019-demo-duckdb.pdf

DuckDB就是位于问号这个区域的。

目录已经到了：0.70版了。看看他们官宣的BLOG/News: (https://duckdb.org/news/)

使用

我之所以写这个短文，是因为发现DuckDB里头有一些非常有用的东西，可以直接拿来用。它实在是太简易了。只有一个二进制可执行文件。然后命令也简单。把它用到PostgreSQL当中，做辅助工具，简直不要太方便。

详细的用法可以见官方文档：https://duckdb.org/docs/ 对它的文档编排，我还是非常赞的。实例非常丰富也明了，没有废话。

这里仅举两个例子来佐证它的方便：

1、直接访问PostgreSQL中的表

早在去年的时候，有duckdb_fdw插件提供给PG, 于是PG可以访问duckdb中的数据，这意味着啥？这意味着PG可以充分利用DUCKDB强大的分析功能。duckdb_fdw的编译安装使用，不缀述。大家可以直接在网上找到相应的使用方法。

我要说的是它现在支持直接访问PG中的表，虽然是只读方式，但也足够爽一爽了。

只需要简单的运行：

INSTALL postgres;
LOAD postgres;

复制

然后配置一下连接信息即可：

CALL postgres_attach('dbname=mydb user=postgres host=127.0.0.1 password=**** port=5555', source_schema='public', sink_schema='abc');

复制

我们在PG端准备点表和数据：

mydb=# \d
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+----------
 public | t123 | table | postgres
(1 row)

mydb=# create table t(id int, col2 varchar(32));
CREATE TABLE
mydb=# insert into t select n, 'test' || n from generate_series(1, 10) as n;
INSERT 0 10

复制

回到duckdb命令行：

[21:45:49-postgres@sean-rh3:/pgccc/source/duckdb]$ ./duckdb abc.db
v0.7.1 b00b93f0b1
Enter ".help" for usage hints.

D INSTALL postgres;
D LOAD postgres;

D  postgres_attach('dbname=mydb user=postgres host=127.0.0.1 password=****** port=5555', source_schema='public', sink_schema='abc');
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ 0 rows  │
└─────────┘
D .tables
t     t123

D select * from t123;
┌───────┬─────────┐
│  id   │  col2   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ wang    │
└───────┴─────────┘
D select * from t;
┌───────┬─────────┐
│  id   │  col2   │
│ int32 │ varchar │
├───────┼─────────┤
│     1 │ test1   │
│     2 │ test2   │
│     3 │ test3   │
│     4 │ test4   │
│     5 │ test5   │
│     6 │ test6   │
│     7 │ test7   │
│     8 │ test8   │
│     9 │ test9   │
│    10 │ test10  │
├───────┴─────────┤
│     10 rows     │
└─────────────────┘
-- 实际上创建的是视图，不能删除数据
D delete from t;
Error: Binder Error: Can only delete from base table!

复制

这意味着，我们可以直接对PG中的表数据进行强大的分析操作。臆想空间很大。

2、直接查询访问csv文件

调整PG配置，让它生成csv log文件:

vi postgresql.conf
log_destination = 'csvlog'
logging_collector = on
log_min_messages = debug1

复制

重启PG，执行几条简单的SQL。

mydb=# insert into t select 1, 'test100'; \watch 0.5;
INSERT 0 1
Wed 19 Apr 2023 10:04:55 PM UTC (every 0.5s)
INSERT 0 1

Wed 19 Apr 2023 10:04:55 PM UTC (every 0.5s)
INSERT 0 1

Wed 19 Apr 2023 10:04:56 PM UTC (every 0.5s)
INSERT 0 1

Wed 19 Apr 2023 10:04:56 PM UTC (every 0.5s)
INSERT 0 1

Wed 19 Apr 2023 10:04:57 PM UTC (every 0.5s)
INSERT 0 1

复制

这时我们看看文件：postgresql-2023-04-19.csv，已经有不少内容了。

看看duckdb如何直接访问这个文件：

D SELECT * FROM read_csv_auto('/opt/pg/14/data/log/postgresql-2023-04-19.csv') limit 2;
┌──────────────────────┬──────────┬──────────┬────────┬──────────┬───┬──────────┬──────────┬────────────┬──────────┬───────┐
│ 2023-04-19 22:03:4…  │ column01 │ column02 │ 386704 │ column04 │ … │ column21 │ column22 │ postmaster │ column24 │  0_1  │
│      timestamp       │ varchar  │ varchar  │ int64  │ varchar  │   │ varchar  │ varchar  │  varchar   │ varchar  │ int64 │
├──────────────────────┼──────────┼──────────┼────────┼──────────┼───┼──────────┼──────────┼────────────┼──────────┼───────┤
│ 2023-04-19 22:03:4…  │          │          │ 386704 │          │ … │          │          │ postmaster │          │     0 │
│ 2023-04-19 22:03:4…  │          │          │ 386704 │          │ … │          │          │ postmaster │          │     0 │
├──────────────────────┴──────────┴──────────┴────────┴──────────┴───┴──────────┴──────────┴────────────┴──────────┴───────┤
│ 2 rows                                                                                             26 columns (10 shown) │
└──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

复制