作者
digoal
日期
2020-10-22
标签
PostgreSQL , fdw , 列存储 , 向量计算 , DuckDB_fdw
背景
DuckDB 简介
DuckDB 是一款嵌入式数据库，主要用于嵌入其他程序执行快速的 SQL 分析查询，官方称其为“分析型数据库中的 SQLite”。DuckDB 易于安装和使用，没有外部依赖，同时提供了 C/C++、Python 以及 R 的语言绑定。
DuckDB 由荷兰 Centrum Wiskunde & Informatica 的数据库架构组开发，他们还开发了 MonetDB（一款开源的列式存储数据库）。DuckDB 采用 MIT 开源协议，并且在 GitHub 上开放了源代码。DuckDB 于 2019 年 6 月 27日首次发布了官方的 0.1 发布版。
使用
DuckDB 提供乐意一个基于 sqlite3 的命令行工具。对于发布版（默认），该工具位于 build/release/tools/shell/shell；对于调试版，位于 build/debug/tools/shell/shell。
嵌入应用
DuckDB 是一款嵌入式数据库，不需要启动数据库服务器，也不需要使用客户端连接服务器。不过，可以使用 C 或 C++ 绑定将数据库服务器直接嵌入应用程序。主构建程序将会创建共享链接库 build/release/src/libduckdb.[so|dylib|dll]，同时也会创建一个静态链接库。
DuckDB Foreign Data Wrapper for PostgreSQL
This PostgreSQL extension is a Foreign Data Wrapper for DuckDB.
The current version can work with PostgreSQL 9.6, 10, 11, 12 and 13.
Installation
1. Install DuckDB library
You can download DuckDB source code and build DuckDB.
2. Build and install duckdb_fdw
Add a directory of pg_config to PATH and build and install duckdb_fdw.
bash
make USE_PGXS=1
make install USE_PGXS=1
If you want to build duckdb_fdw in a source tree of PostgreSQL, use
bash
make
make install
Usage
Load extension
sql
CREATE EXTENSION duckdb_fdw;
Create server
Please specify DuckDB database path using database option:
sql
CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database '/tmp/test.db');
Create foreign table
Please specify table option if DuckDB table name is different from foreign table name.
sql
CREATE FOREIGN TABLE t1(a integer, b text) SERVER DuckDB_server OPTIONS (table 't1_DuckDB');
If you want to update tables, please add OPTIONS (key 'true') to a primary key or unique key like the following:
sql
CREATE FOREIGN TABLE t1(a integer OPTIONS (key 'true'), b text)
SERVER DuckDB_server OPTIONS (table 't1_DuckDB');
If you need to convert INT DuckDB column (epoch Unix Time) to be treated/visualized as TIMESTAMP in PostgreSQL, please add OPTIONS (column_type 'INT') when
defining FOREIGN table at PostgreSQL like the following:
sql
CREATE FOREIGN TABLE t1(a integer, b text, c timestamp without time zone OPTIONS (column_type 'INT'))
SERVER DuckDB_server OPTIONS (table 't1_DuckDB');
Import foreign schema
sql
IMPORT FOREIGN SCHEMA public FROM SERVER DuckDB_server INTO public;
Access foreign table
sql
SELECT * FROM t1;
Features (牛逼的功能)
- Update & Delete support
- Support CSV and parquet
- Columnar-vectorized query execution engine
- DuckDB is designed to support analytical query workloads, also known as Online analytical processing (OLAP)
- WHERE clauses are pushdowned
- Aggregate function are pushdowned
- Order By is pushdowned.
- Limit and Offset are pushdowned (*when all tables queried are fdw)
- Transactions
Limitations
COPYcommand for foreign tables is not supported- Insert into a partitioned table which has foreign partitions is not supported
Contributing
Opening issues and pull requests on GitHub are welcome.
Special thanks
https://github.com/pgspider/sqlite_fdw
License
MIT
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
