美化排版-PG白嫖DuckDB实现湖仓一体
digoal约 11 分钟文中参考文档点击阅读原文打开, 同时推荐2个学习环境:
1、懒人Docker镜像, 已打包200+插件:《最好的PostgreSQL学习镜像》
2、有web浏览器就能用的云起实验室: 《免费体验PolarDB开源数据库》
3、PolarDB开源数据库内核、最佳实践等学习图谱: https://www.aliyun.com/database/openpolardb/activity
美化排版-PG白嫖DuckDB实现湖仓一体
背景
用duckdb_fdw可以白嫖duckdb访问存储于对象存储中的parquet文件, 提升40倍分析性能.
非常感谢duckdb_fdw作者Steven的贡献, 这是github项目地址: https://github.com/alitrack/duckdb_fdw
alitrack是他的公众号:
参考如下文章实操一下找找感觉?
• 《PG被DuckDB碾压,该反省哪些方面? DuckDB v0.10.3 在Macmini 2023款上的tpch性能表现如何? PostgreSQL使用duckdb_fdw 的tpch加速性能表现如何?》
• 《PolarDB-PG | PostgreSQL + duckdb_fdw + 阿里云OSS 实现高效低价的海量数据冷热存储分离》
• 《PolarDB 开源版通过 duckdb_fdw 支持 parquet 列存数据文件以及高效OLAP》
• 《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》
• 《PostgreSQL 牛逼的分析型功能 - 列存储、向量计算 FDW - DuckDB_fdw - 无数据库服务式本地lib库+本地存储》
• 《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》
但是, duckdb_fdw使用体验还有一定的提升空间, 因为: 每次新增要访问的parquet文件都需要重新定义duckdb视图, 持久化数据文件, 然后定义pg外部表, 最后还需要在每次访问对象存储前都需要设置访问akid,key,region等.
一次操作:
• 创建duckdb_fdw插件
• 创建foreign server
• 定义持久化对象存储secret
• 创建duckdb 视图, 访问对象存储parquet文件
• 保存duckdb数据文件
• 创建foreign table
每当访问外部表(ft)时:
• 需要先配置一下duckdb fdw server连接对象存储的参数 (持久化也许不用每次配置)
• 读取foreign table
每当需要访问新的文件时都需要重新创建ft:
• 设置访问对象存储参数(akid,key,region)
• 创建duckdb 视图, 访问对象存储parquet文件
• 保存duckdb数据文件
• 创建foreign table
是不是挺繁琐? 后面有个例子可以感受一下, 不过昨天我发完本文后, DuckDB用户组的微信群炸了, steven表示很快会提高duckdb_fdw体验, 让user mapping支持DuckDB secret, 让ft的option支持对象存储parquet文件等选项. 有兴趣的朋友可以加我的微信拉到DuckDB微信群.
如何让PG像DuckDB一样无忧的访问对象存储parquet并利用duckdb的算力呢? 目前看到crunchydata的云服务有点这意思, 参考如下文章:
• 《什么? PostgreSQL大佬tom lane公司crunchydata“模仿”DuckDB创意?》
在duckdb中原生访问对象存储中的parquet就非常方便, 一个函数即可, 甚至直接指定文件路径即可.
例子
使用这个阿里云免费云起实验, 创建免费的oss.
• https://developer.aliyun.com/adc/scenario/exp/f55dbfac77c0467a9d3cd95ff6697a31
DuckDB访问对象存储parquet文件不需要任何提前定义, 自动发现parquet schema, 直接访问即可.
使用无敌pg docker image测试(请使用ARM版本, x86版本我还没时间更新):
root@591fb57b3bcd:~# su - postgres
postgres@591fb57b3bcd:~$ ./duckdb
v1.0.0 1f98600c2c
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database. D INSTALL httpfs;
100% ▕████████████████████████████████████████████████████████████▏
D LOAD httpfs;
D create table a(id int, info text);
D insert into a select range, md5(random()::text) from range(1,10000000);
100% ▕████████████████████████████████████████████████████████████▏
D set s3_access_key_id='xxx';
D set s3_secret_access_key='xxx';
D set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';
D copy a to 's3://otpawu20240715105432/a.parquet';
100% ▕████████████████████████████████████████████████████████████▏
D .timer on
D SELECT * FROM read_parquet('s3://otpawu20240715105432/a.parquet') where id<10;
┌───────┬──────────────────────────────────┐
│ id │ info │
│ int32 │ varchar │
├───────┼──────────────────────────────────┤
│ 1 │ 9185f0cf7a5ccd6bdc8a2314530b4874 │
│ 2 │ 58497506c65c04de46ffc9d01e04d472 │
│ 3 │ 7a85c95187b915e44d67a7fe4a180b84 │
│ 4 │ 65fcdc0e4ce9c4a91a33dbc36ce08c92 │
│ 5 │ 280c0adaae99569ae9520510ffbf645f │
│ 6 │ 4f9f28139f2a1e5ada45a92631d70dca │
│ 7 │ d5893b650320eb5d989b7ffa176e4310 │
│ 8 │ 98b01fa73dd9005db77b480ee30d6082 │
│ 9 │ ed1256892a1ab3aae2a2fd51771fbea5 │
└───────┴──────────────────────────────────┘
Run Time (s): real 1.532 user 1.429763 sys 0.018154
D SELECT * FROM read_parquet('s3://otpawu20240715105432/a.parquet') where id<10;
┌───────┬──────────────────────────────────┐
│ id │ info │
│ int32 │ varchar │
├───────┼──────────────────────────────────┤
│ 1 │ 9185f0cf7a5ccd6bdc8a2314530b4874 │
│ 2 │ 58497506c65c04de46ffc9d01e04d472 │
│ 3 │ 7a85c95187b915e44d67a7fe4a180b84 │
│ 4 │ 65fcdc0e4ce9c4a91a33dbc36ce08c92 │
│ 5 │ 280c0adaae99569ae9520510ffbf645f │
│ 6 │ 4f9f28139f2a1e5ada45a92631d70dca │
│ 7 │ d5893b650320eb5d989b7ffa176e4310 │
│ 8 │ 98b01fa73dd9005db77b480ee30d6082 │
│ 9 │ ed1256892a1ab3aae2a2fd51771fbea5 │
└───────┴──────────────────────────────────┘
Run Time (s): real 1.586 user 1.481081 sys 0.014454
D select count(*) from read_parquet('s3://otpawu20240715105432/a.parquet');
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 9999999 │
└──────────────┘
Run Time (s): real 0.155 user 0.029418 sys 0.004126
D select count(*),min(id),max(id),avg(id) from read_parquet('s3://otpawu20240715105432/a.parquet');
100% ▕████████████████████████████████████████████████████████████▏
┌──────────────┬─────────┬─────────┬───────────┐
│ count_star() │ min(id) │ max(id) │ avg(id) │
│ int64 │ int32 │ int32 │ double │
├──────────────┼─────────┼─────────┼───────────┤
│ 9999999 │ 1 │ 9999999 │ 5000000.0 │
└──────────────┴─────────┴─────────┴───────────┘
Run Time (s): real 10.665 user 1.998380 sys 0.765886
D select count(*),min(id),max(id),avg(id) from read_parquet('s3://otpawu20240715105432/a.parquet');
100% ▕████████████████████████████████████████████████████████████▏
┌──────────────┬─────────┬─────────┬───────────┐
│ count_star() │ min(id) │ max(id) │ avg(id) │
│ int64 │ int32 │ int32 │ double │
├──────────────┼─────────┼─────────┼───────────┤
│ 9999999 │ 1 │ 9999999 │ 5000000.0 │
└──────────────┴─────────┴─────────┴───────────┘
Run Time (s): real 9.397 user 1.978366 sys 0.689697
D select count(*),min(id),max(id) from read_parquet('s3://otpawu20240715105432/a.parquet');
100% ▕████████████████████████████████████████████████████████████▏
┌──────────────┬─────────┬─────────┐
│ count_star() │ min(id) │ max(id) │
│ int64 │ int32 │ int32 │
├──────────────┼─────────┼─────────┤
│ 9999999 │ 1 │ 9999999 │
└──────────────┴─────────┴─────────┘
Run Time (s): real 10.768 user 2.101236 sys 0.575175
PS, duckdb secret语法:
D CREATE SECRET my_secret (
TYPE S3,
KEY_ID 'xxx',
SECRET 'xxx',
endpoint 's3.oss-cn-shanghai.aliyuncs.com'
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true │
└─────────┘ D create table a(id int, info text);
D insert into a select range, md5(random()::text) from range(1,1000000);
D copy a to 's3://otpawu20240715105432/a.parquet';
pg_lakehouse 来了
pg_lakehouse, 看名字知道湖仓一体. 实际上就是结合了PG和duckdb, 一样使用fdw接口, 和duckdb_fdw不一样的地方是支持更多option, 在user mapping中定义对象存储的akid、key、region, 在ft的option中定义对象存储中的parquet文件位置. 如果你经常访问的数据都存储在对象存储里面, 使用pg_lakehouse就比较方便.
注意, pg_lakehouse目前下推支持还不好, 非常影响性能.
例子
postgres@591fb57b3bcd:~$ psql
psql (14.12 (Debian 14.12-1.pgdg110+1))
Type "help" for help. postgres=# create extension pg_lakehouse;
CREATE EXTENSION
postgres=# \dx
List of installed extensions
Name | Version | Schema | Description
--------------+---------+------------+-------------------------------------------------------
pg_lakehouse | 0.8.4 | public | pg_lakehouse: An analytical query engine for Postgres
plpgsql | 1.0 | pg_catalog | PL/pgSQL procedural language
(2 rows)
-- Parquet format is assumed
CREATE FOREIGN DATA WRAPPER parquet_wrapper
HANDLER parquet_fdw_handler
VALIDATOR parquet_fdw_validator;
CREATE SERVER parquet_server
FOREIGN DATA WRAPPER parquet_wrapper;
drop user MAPPING IF EXISTS FOR postgres SERVER parquet_server ;
CREATE USER MAPPING FOR postgres
SERVER parquet_server
OPTIONS (
type 'S3',
key_id 'xxx',
secret 'xxx',
endpoint 's3.oss-cn-shanghai.aliyuncs.com'
);
创建外部表, 条件下推还不好, 期待支持.
CREATE FOREIGN TABLE a ()
SERVER parquet_server
OPTIONS (files 's3://otpawu20240715105432/a.parquet'); postgres=# select * from a where id<10;
id | info
----+----------------------------------
1 | a1643bbc140edcf29801dc7dc035efbf
2 | ab1203864d433bdd52149e9518f556c4
3 | 405458dd89493cf502135ab22929bacb
4 | 1df2ffe1cea3505d07be0f5d6d355601
5 | 3b110bd4624394338be9696b4c2a2d23
6 | 3bea0628a283ee576d1afec88870ae73
7 | 406b6af0010736d5914b5267625092fc
8 | d69b192a096bce1892442631ba1aa04a
9 | 6c15142be51d784d8222bb2a2b63bbd0
(9 rows)
Time: 1572.423 ms (00:01.572)
postgres=# explain verbose select * from a where id<10;
QUERY PLAN
------------------------------------------------------------
Foreign Scan on public.a (cost=0.00..1.00 rows=1 width=0)
Output: id, info
Filter: (a.id < 10) -- 未下推, 性能不好
DuckDB Scan: SELECT id, info FROM public.a
(4 rows)
Time: 10.772 ms
疑问: 如果说完全没有下推, 好像也不对, 因为两次时间有明显的差别, 但是verbose打印的remote query确无差别.
postgres=# select * from a where id<1;
id | info
----+------
(0 rows)Time: 140.670 ms
postgres=# select * from a where id<=1;
id | info
----+----------------------------------
1 | a1643bbc140edcf29801dc7dc035efbf
(1 row)
Time: 1441.886 ms (00:01.442)
postgres=# select * from a where id<=10;
id | info
----+----------------------------------
1 | a1643bbc140edcf29801dc7dc035efbf
2 | ab1203864d433bdd52149e9518f556c4
3 | 405458dd89493cf502135ab22929bacb
4 | 1df2ffe1cea3505d07be0f5d6d355601
5 | 3b110bd4624394338be9696b4c2a2d23
6 | 3bea0628a283ee576d1afec88870ae73
7 | 406b6af0010736d5914b5267625092fc
8 | d69b192a096bce1892442631ba1aa04a
9 | 6c15142be51d784d8222bb2a2b63bbd0
10 | 91c1f7b6a498744676bf9b9e2e9f0d91
(10 rows)
Time: 1434.250 ms (00:01.434)
postgres=# explain verbose select * from a where id<1;
QUERY PLAN
------------------------------------------------------------
Foreign Scan on public.a (cost=0.00..1.00 rows=1 width=0)
Output: id, info
Filter: (a.id < 1)
DuckDB Scan: SELECT id, info FROM public.a
(4 rows)
postgres=# explain verbose select * from a where id<=1;
QUERY PLAN
------------------------------------------------------------
Foreign Scan on public.a (cost=0.00..1.00 rows=1 width=0)
Output: id, info
Filter: (a.id <= 1)
DuckDB Scan: SELECT id, info FROM public.a
(4 rows)
最后透露一个小道消息, 听说HaloDB也集成了DuckDB的能力, 不过他们是用table access method代替FDW来实现的, 坐等老章给最新的包更新到宇宙第一PG数据库Docker镜像中.
这是他们的公众号, 欢迎关注:
参考
https://developer.aliyun.com/adc/scenario/exp/f55dbfac77c0467a9d3cd95ff6697a31
https://help.aliyun.com/zh/oss/developer-reference/migrate-data-from-amazon-s3-to-alibaba-cloud-oss-1
https://docs.paradedb.com/analytics/object_stores/s3
https://github.com/paradedb/paradedb/blob/dev/docs/analytics/object_stores/s3.mdx
https://duckdb.org/docs/configuration/secrets_manager.html
https://duckdb.org/docs/extensions/httpfs/s3api_legacy_authentication
本期彩蛋-招商中,有需要的小伙伴可联系嵌入...
文章中的参考文档请点击阅读原文获得.
欢迎关注我的github (https://github.com/digoal/blog) , 学习数据库不迷路.
近期正在写公开课材料, 未来将通过视频号推出, 欢迎关注视频号: