背景
数据库里的历史数据越来越多, 占用空间大, 备份慢, 恢复慢, 查询少但是很费钱, 迁移慢. 怎么办?
冷热分离方案:
使用PostgreSQL 或者 PolarDB-PG, 将历史数据存成parquet文件格式, 放到aliyun OSS存储里面. 使用duckdb_fdw对OSS内的parquet文件进行查询.
duckdb 存储元数据(parquet 映射)
方案特点:
- 内网oss不收取网络费用, 只收取存储费用, 非常便宜.
- oss分几个档, 可以根据性能需求选择.
- parquet为列存储, 一般历史数据的分析需求多, 性能不错.
- duckdb 支持 parquet下推过滤, 数据过滤性能也不错.
- 存储在oss内, 可以使用oss的函数计算功能, 仅计算时收费. 而且使用OSS存储打破数据孤岛, OSS与PG和PolarDB以及其他数据源打通, 形成数据联邦, 更容易发挥更大层面的数据价值.
架构如下:
PolarDB-PG 或 PostgreSQL
↑↓
↑↓
热数据: 高速本地存储
↑↓ ↓↓
↑↓ ↓↓
LibDuckDB ForeignServer 层: ↓↓
1、(通过 duckdb_fdw 读写OSS) 2、(通过 postgres_scanner 读高速本地存储)
↑↓
↑↓
归档数据: OSS 冷暖存储 (Parquet格式) demo
在以下debian 容器中部署
1、部署duckdb和依赖的parquet、httpfs插件
《Debian学习入门 - (作为服务器使用, Debian 操作系统可能是长期更好的选择?)》
确认编译了httpfs 和 parquet 插件
root@9b780f5ea2e8:~/duckdb/build/release/extension# pwd
/root/duckdb/build/release/extension
root@9b780f5ea2e8:~/duckdb/build/release/extension# ll
total 72K
-rw-r--r-- 1 root root 2.3K Mar 3 06:16 cmake_install.cmake
-rw-r--r-- 1 root root 6.2K Mar 3 06:16 Makefile
drwxr-xr-x 15 root root 4.0K Mar 3 06:16 .
drwxr-xr-x 2 root root 4.0K Mar 3 06:16 CMakeFiles
drwxr-xr-x 4 root root 4.0K Mar 3 06:40 jemalloc
drwxr-xr-x 10 root root 4.0K Mar 3 06:43 ..
drwxr-xr-x 4 root root 4.0K Mar 3 06:45 icu
drwxr-xr-x 3 root root 4.0K Mar 3 06:47 parquet
drwxr-xr-x 4 root root 4.0K Mar 3 06:47 tpch
drwxr-xr-x 4 root root 4.0K Mar 3 06:47 tpcds
drwxr-xr-x 3 root root 4.0K Mar 3 06:47 fts
drwxr-xr-x 3 root root 4.0K Mar 3 06:48 httpfs
drwxr-xr-x 3 root root 4.0K Mar 3 06:48 visualizer
drwxr-xr-x 5 root root 4.0K Mar 3 06:49 json
drwxr-xr-x 4 root root 4.0K Mar 3 06:49 excel
drwxr-xr-x 4 root root 4.0K Mar 3 06:50 sqlsmith
drwxr-xr-x 3 root root 4.0K Mar 3 06:50 inet 2、安装postgresql 或 PolarDB开源版本.
PolarDB开源版本部署请参考: 《如何用 PolarDB 证明巴菲特的投资理念 - 包括PolarDB on Docker简单部署》
以下是使用postgresql的例子:
apt install -y curl fastjar
mkdir /home/postgres
useradd postgres
chown postgres:postgres /home/postgres
su - postgres
curl https://ftp.postgresql.org/pub/source/v15.2/postgresql-15.2.tar.bz2 -o ./postgresql-15.2.tar.bz2
tar -jxvf postgresql-15.2.tar.bz2
cd postgresql-15.2
./configure --prefix=/home/postgres/pg15.2
make world -j 4
make install-world 3、部署duckdb_fdw
su - postgres
git clone --depth 1 https://github.com/alitrack/duckdb_fdw 将duckdb的lib包拷贝到postgresql的lib目录
root@9b780f5ea2e8:~/duckdb/build/release/src# pwd
/root/duckdb/build/release/src
root@9b780f5ea2e8:~/duckdb/build/release/src# ll libduckdb.so
-rwxr-xr-x 1 root root 58M Mar 3 06:42 libduckdb.so
cp libduckdb.so /home/postgres/pg15.2/lib/ 安装duckdb_fdw插件
su - postgres
export PATH=/home/postgres/pg15.2/bin:$PATH
cd duckdb_fdw
USE_PGXS=1 make
USE_PGXS=1 make install 4、初始化postgresql数据库集群
initdb -D /home/postgres/pgdata -E UTF8 --lc-collate=C -U postgres 5、简单配置一下pg配置文件
vi /home/postgres/pgdata/postgresql.conf
listen_addresses = '0.0.0.0'
port = 1921
max_connections = 100
unix_socket_directories = '/tmp,.'
shared_buffers = 128MB
dynamic_shared_memory_type = posix
max_wal_size = 1GB
min_wal_size = 80MB
log_destination = 'csvlog'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_file_mode = 0600
log_rotation_age = 1d
log_rotation_size = 10MB
log_truncate_on_rotation = on
log_timezone = 'Etc/UTC'
datestyle = 'iso, mdy'
timezone = 'Etc/UTC'
lc_messages = 'C'
lc_monetary = 'C'
lc_numeric = 'C'
lc_time = 'C'
default_text_search_config = 'pg_catalog.english' 6、启动数据库, 加载duckdb_fdw插件
pg_ctl start -D /home/postgres/pgdata
$ psql -h 127.0.0.1 -p 1921
psql (15.2)
Type "help" for help.
postgres=# create extension duckdb_fdw ;
CREATE EXTENSION 创建oss实验环境
可以使用阿里云云起实验免费创建oss实验环境, 参考如下:
《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》
1、初始化实验环境后, 得到一些需要的内容如下, 将被duckdb用于连接oss.
AK ID: xxxxxx
AK Secret: xxxxxx
Endpoint外网域名: oss-cn-shanghai.aliyuncs.com
Bucket名称: adc-oss-labs01969
Object路径: ECSOSS/u-bimcc3ei/ duckdb读写OSS的方法
COPY <table_name> TO 's3://<Bucket名称>/<Object路径>/filename';
SELECT * FROM read_parquet('s3://<Bucket名称>/<Object路径>/filename'); 在debian中, 测试duckdb是否能正常使用OSS, 并生成100万测试数据, 写入oss.
root@9b780f5ea2e8:~/duckdb/build/release# pwd
/root/duckdb/build/release
root@9b780f5ea2e8:~/duckdb/build/release# ./duckdb
v0.7.1 b00b93f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D load 'httpfs';
D set s3_access_key_id='xxxxxx'; // AK ID
D set s3_secret_access_key='xxxxxx'; // AK Secret
D set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com'; // Endpoint外网域名|内网域名
D COPY (select id, md5(random()::text) as info, now() as ts from range(0,1000000) as t(id)) TO 's3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet'; 测试创建视图是否正常使用
IT-C02YW2EFLVDL:release digoal$ ./duckdb
v0.7.1 b00b93f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D set s3_access_key_id='xxxxxx';
D set s3_secret_access_key='xxxxxx';
D set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';
将parquet文件映射为view
D create or replace view test_duckdb1 as
SELECT * FROM read_parquet('s3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet');
D select count(*) from test_duckdb1;
┌──────────────┐
│ count_star() │
│ int64 │
├──────────────┤
│ 1000000 │
└──────────────┘
D select * from main."test_duckdb1" limit 10;
100% ▕████████████████████████████████████████████████████████████▏
┌───────┬──────────────────────────────────┬────────────────────────────┐
│ id │ info │ ts │
│ int64 │ varchar │ timestamp with time zone │
├───────┼──────────────────────────────────┼────────────────────────────┤
│ 0 │ 87a144c45874838dbcd3255c215ababc │ 2023-03-08 17:28:12.902+08 │
│ 1 │ cce8d1f5d58e72e9f34a36ccd87188ed │ 2023-03-08 17:28:12.902+08 │
│ 2 │ 0ea50d2769b01c26537e09902dc5f732 │ 2023-03-08 17:28:12.902+08 │
│ 3 │ 70a6c5f594def5d1d1bbb993260a2fd7 │ 2023-03-08 17:28:12.902+08 │
│ 4 │ 5a7924f417b480210601508e2c144a2f │ 2023-03-08 17:28:12.902+08 │
│ 5 │ d1fde1c1dc8f268d9eb9fce477653bb0 │ 2023-03-08 17:28:12.902+08 │
│ 6 │ 1aac9556fd1b259c56ecef3ef4636a66 │ 2023-03-08 17:28:12.902+08 │
│ 7 │ 04181693f9b6c8576bb251612ffbe318 │ 2023-03-08 17:28:12.902+08 │
│ 8 │ 332b9bb9d00e8fa53a5661804bd1b41a │ 2023-03-08 17:28:12.902+08 │
│ 9 │ f0189d662187cc436662a458577a7ed2 │ 2023-03-08 17:28:12.902+08 │
├───────┴──────────────────────────────────┴────────────────────────────┤
│ 10 rows 3 columns │
└───────────────────────────────────────────────────────────────────────┘
Run Time (s): real 9.773 user 1.121633 sys 0.928902
D .timer on
D select max(id) from test_duckdb1;
┌─────────┐
│ max(id) │
│ int64 │
├─────────┤
│ 999999 │
└─────────┘
Run Time (s): real 0.482 user 0.087439 sys 0.065868 在postgresql中使用duckdb_fdw访问oss内的parquet文件
你可以创建duckdb内存数据库, 也可以指定为一个持久化文件, 使用持久化文件的话可以拥有一些元数据存储的能力, 不用每次都创建映射和配置.
下面用的是内存存储(非持久化)例子:
在psql内执行
postgres=# CREATE SERVER DuckDB_server FOREIGN DATA WRAPPER duckdb_fdw OPTIONS (database ':memory:');
CREATE SERVER
-- 设置为保持连接(会话内保持)
postgres=# alter server duckdb_server options ( keep_connections 'true');
ALTER SERVER 接下来创建一个duckdb视图, 用以查询parquet.
一定要分开执行:
SELECT duckdb_execute('duckdb_server',
$$
set s3_access_key_id='xxxxxx';
$$);
SELECT duckdb_execute('duckdb_server',
$$
set s3_secret_access_key='xxxxxx';
$$);
SELECT duckdb_execute('duckdb_server',
$$
set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';
$$);
SELECT duckdb_execute('duckdb_server',
$$
create or replace view test_duckdb1 as
SELECT * FROM read_parquet('s3://adc-oss-labs01969/ECSOSS/u-bimcc3ei/test_duckdb1.parquet');
$$); 检查是否保持连接
postgres=# select * from duckdb_fdw_get_connections();
server_name | valid
---------------+-------
duckdb_server | t
(1 row) 创建duckdb_fdw外部表, 指向刚才创建的duckdb视图:
create foreign TABLE ft_test_duckdb1(
id int,
info text,
ts timestamp)
SERVER duckdb_server OPTIONS (table 'test_duckdb1'); 我们查看一下duckdb_fdw的下推能力, 非常帮, 过滤、limit、排序、distinct等都进行了下推, 详细参考duckdb_fdw开源项目:
postgres=# explain verbose select id from ft_test_duckdb1 limit 1;
QUERY PLAN
--------------------------------------------------------------------------
Foreign Scan on public.ft_test_duckdb1 (cost=1.00..1.00 rows=1 width=4)
Output: id
SQLite query: SELECT "id" FROM main."test_duckdb1" LIMIT 1
(3 rows)
postgres=# explain verbose select * from ft_test_duckdb1 where id<100;
QUERY PLAN
-----------------------------------------------------------------------------------------
Foreign Scan on public.ft_test_duckdb1 (cost=10.00..401.00 rows=401 width=44)
Output: id, info, ts
SQLite query: SELECT "id", "info", "ts" FROM main."test_duckdb1" WHERE (("id" < 100))
(3 rows)
postgres=# explain verbose select * from ft_test_duckdb1 where id<100 order by ts limit 100;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Foreign Scan on public.ft_test_duckdb1 (cost=1.00..1.00 rows=1 width=44)
Output: id, info, ts
SQLite query: SELECT "id", "info", "ts" FROM main."test_duckdb1" WHERE (("id" < 100)) ORDER BY "ts" ASC NULLS LAST LIMIT 100
(3 rows)
postgres=# explain verbose select count(distinct id) from ft_test_duckdb1;
QUERY PLAN
----------------------------------------------------------------------
Foreign Scan (cost=1.00..1.00 rows=1 width=8)
Output: (count(DISTINCT id))
SQLite query: SELECT count(DISTINCT "id") FROM main."test_duckdb1"
(3 rows) postgres=# select * from ft_test_duckdb1 limit 1;
id | info | ts
----+----------------------------------+-------------------------
0 | 87a144c45874838dbcd3255c215ababc | 2023-03-08 09:28:12.902
(1 row) 执行查询, 看看性能如何. 以下对比pg本地表、parquet(实验环境在公网, 如果是内网还不好说谁快谁慢.)
postgres=# create table t as select * from ft_test_duckdb1 ;
SELECT 1000000
Time: 21196.441 ms (00:21.196) postgres=# \timing
Timing is on.
postgres=# select count(distinct id) from ft_test_duckdb1;
count
---------
1000000
(1 row)
Time: 1281.537 ms (00:01.282)
postgres=# select count(distinct id) from t;
count
---------
1000000
(1 row)
Time: 260.007 ms
postgres=# select count(*) from ft_test_duckdb1 where id<100;
count
-------
100
(1 row)
Time: 806.976 ms
postgres=# select count(*) from t where id<100;
count
-------
100
(1 row)
Time: 60.254 ms 多个会话同时访问相同server, 相同foreign table
使用同一个server, 每次建立连接都会新建一个duckdb inmemory进程. 每次都需要设置oss配置, 创建duckdb view. 然后就能通过ft读取数据.
session a:
- 访问正常
postgres=# select * from ft_test_duckdb1 limit 1;
id | info | ts
----+----------------------------------+-------------------------
0 | 77e736e2033e489f3134607dcfd63d05 | 2023-03-09 05:45:25.506
(1 row) session b:
- 访问正常
postgres=# select * from ft_test_duckdb1 limit 1;
id | info | ts
----+----------------------------------+-------------------------
0 | 77e736e2033e489f3134607dcfd63d05 | 2023-03-09 05:45:25.506
(1 row) 通过duckdb_fdw将历史数据写入oss, 实现历史数据归档操作
准备工作, 配置需要密码连接postgresql.
vi pg_hba.conf
# IPv4 local connections:
host all all 127.0.0.1/32 md5
pg_ctl reload -D $PGDATA
psql
alter role postgres encrypted password '123456'; 1、建立pg本地表
postgres=# create table t1 (id int, info text, ts timestamp);
CREATE TABLE
postgres=# insert into t1 select generate_series(1,1000000), md5(random()::text), clock_timestamp();
INSERT 0 1000000 2、在duckdb中使用postgres插件可以读取pg本地表的数据
root@9b780f5ea2e8:~# cd duckdb/build/release/
root@9b780f5ea2e8:~/duckdb/build/release# ./duckdb
v0.7.1 b00b93f
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
D load 'postgres';
D select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1') limit 1;
┌───────┬──────────────────────────────────┬────────────────────────────┐
│ id │ info │ ts │
│ int32 │ varchar │ timestamp │
├───────┼──────────────────────────────────┼────────────────────────────┤
│ 1 │ c8ecbcc36395bfa4d39b414e306c1b81 │ 2023-03-09 05:49:30.184854 │
└───────┴──────────────────────────────────┴────────────────────────────┘
D 3、在duckdb中可以打通pg和oss, 也就是将pg的数据写入oss
set s3_access_key_id='xxxxxx';
set s3_secret_access_key='xxxxxx';
set s3_endpoint='s3.oss-cn-shanghai.aliyuncs.com';
COPY (
select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
)
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/abc.parquet';
100% ▕████████████████████████████████████████████████████████████▏ 4、紧接着, 直接在pg里面使用duckdb_fdw插件, 让duckdb来读取pg的数据写入oss.
SELECT duckdb_execute('duckdb_server',
$$
install 'postgres';
$$);
SELECT duckdb_execute('duckdb_server',
$$
load 'postgres';
$$);
SELECT duckdb_execute('duckdb_server',
$$
COPY (
select * from POSTGRES_SCAN_PUSHDOWN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
)
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';
$$); 使用如上方法install postgres时, 会自动从duckdb官方下载对应版本编译好的插件, 例如:
详见:
方法没问题, 目前bug可能和gpdb postgres_fdw遇到的问题一样, 感兴趣的朋友可以参与一起解决:
https://github.com/alitrack/duckdb_fdw/issues/15
postgres=#
SELECT duckdb_execute('duckdb_server',
$$
COPY (
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
)
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';
$$);
ERROR: HV00L: SQL error during prepare: IO Error: Unable to connect to Postgres at dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456: libpq is incorrectly linked to backend functions
COPY (
select * from POSTGRES_SCAN('dbname=postgres user=postgres hostaddr=127.0.0.1 port=1921 password=123456', 'public', 't1')
)
TO 's3://adc-oss-1872hd2/ECSOSS/u-ijr7vhba/test_import_from_pg1.parquet';
LOCATION: sqlite_prepare_wrapper, duckdb_fdw.c:504 相关代码sqlite3_prepare_v2:
/* Wrapper for sqlite3_prepare */
static void
sqlite_prepare_wrapper(ForeignServer *server, sqlite3 * db, char *query, sqlite3_stmt * *stmt,
const char **pzTail, bool is_cache)
{
int rc;
// db = sqlite_get_connection(server, false);
// elog(DEBUG1, "duckdb_fdw : %s %s %p %p %p %p\n", __func__, query, server,db,&stmt,stmt);
rc = sqlite3_prepare_v2(db, query, -1, stmt, pzTail);
// elog(DEBUG1, "duckdb_fdw : %s %s %d \n", __func__, query, rc);
if (rc != SQLITE_OK)
{
ereport(ERROR,
(errcode(ERRCODE_FDW_UNABLE_TO_CREATE_EXECUTION),
errmsg("SQL error during prepare: %s %s", sqlite3_errmsg(db), query)
));
}
/* cache stmt to finalize at last */
if (is_cache)
sqlite_cache_stmt(server, stmt);
} gpdb类似的一个issue.
https://github.com/greenplum-db/gpdb/issues/11400
https://github.com/greenplum-db/gpdb/commit/667f0c37bc6d7bce7be8b758652ef95ddb823e19
Fix postgres_fdw's libpq issue (#10617)
* Fix postgres_fdw's libpq issue
When using posgres_fdw, it reports the following error:
unsupported frontend protocol 28675.0: server supports 2.0 to 3.0
root cause: Even if postgres_fdw.so is dynamic linked to libpq.so
which is compiled with the option -DFRONTEND, but when it's loaded
in gpdb and run, it will use the backend libpq which is compiled together
with postgres program and reports the error.
We statically link libpq into postgres_fdw and hide all the symbols
of libpq.a with --exclude-libs=libpq.a to make it uses the frontend
libpq.
As postgres_fdw is compiled as a backend without -DFRONTEND, and linked
to libpq which is a frontend, but _PQconninfoOption's length is
different between backend and frontend as there is a macro in it.
The backend's _PQconninfoOption has field connofs, but the frontend
doesn't. This leads to the crash of postgres_fdw. So we delete the
frontend macro in _PQconninfoOption.
* Add FRONTEND macro on including libpq header files
postgres_fdw is compiled as a backend, it needs the server's
header files such as executor/tuptable.h. It also needs libpq
to connect to a remote postgres database, so it's staticly linked
to libpq.a which is compiled as a frontend using -DFRONTEND.
But the struct PQconninfoOption's length is different between
backend and frontend, there is no "connofs" field in frontend.
When postgres_fdw calls the function "PQconndefaults" implemented
in libpq.a and uses the returned PQconninfoOption variable, it crashes,
because the PQconninfoOption variable returned by libpq.a doesn't contain
the "connofs" value, but the postgres_fdw thinks it has, so it crashes.
In last commit, we remove the FRONTEND macro in struct PQconninfoOption
to make PQconninfoOption is same in backend and frontend, but that brings
an ABI change. To avoid that, we revert that, and instead, we add
the FRONTEND macro on including libpq header files, so that
postgres_fdw can process the libpq's variables returned by libpq.a's
functions as frontend.
* Report error if the libpq-fe.h is included before postgres_fdw.h
postgres_fdw needs to include frontend mode libpq-fe.h, so if
the libpq-fe.h is included before the postgres_fdw.h, and we
don't know if it is frontend mode, so we just report the error here. 感谢steven贡献duckdb_fdw
未来duckdb_fdw的优化期待:
1、在server中加入更多的option, 例如
- 设置s3的参数, 连接时就默认配置好, 这样的话就可以直接查询foreign table, 不需要每次都需要通过execute接口来配置.
启动时设置allow_unsigned_extensions, 允许使用未签名的外部extension.
参考
https://github.com/alitrack/duckdb_fdw
《Debian学习入门 - (作为服务器使用, Debian 操作系统可能是长期更好的选择?)》
《DuckDB DataLake 场景使用举例 - aliyun OSS对象存储parquet》
《用duckdb_fdw加速PostgreSQL分析计算, 提速40倍, 真香.》
