chdb Postgres扩展：高性能导入云存储数据

我们很高兴宣布一个新的 Postgres 扩展：chdb。此扩展通过chDB 库，一个进程内ClickHouse引擎，扩展了 Postgres 的导入导出功能，在您喜欢的云存储系统上实现与多种数据格式之间高效、灵活的转换。数据格式。

基准测试

而且 哎呀我们确实指 高效！我们比较了 chdb 导入 NYC 出租车数据集（100万行，宽表）在多种数据格式下的性能，与另外三个 Postgres 扩展对比，均从同一区域的 AWS S3 存储桶读取。查看图表！

为了最小化差异并优化对扩展性能的测量，而不是基础设施，chdb、pg_lake 和 pg_duckdb 基准测试在r8id.xlarge ClickHouse 托管 Postgres服务上运行，配备4个 vCPU 和32 GB RAM；aws_s3基准测试在 db.r8g.xlarge AWS RDS主机上运行，同样配备4个 vCPU 和32 GB RAM。结果取每次导入三次运行的平均值。详见 基准测试源代码。

在这四个扩展中，chdb 表现出最一致的性能。pg_duckdb 和 pg_lake，均基于 DuckDB，从 CSV、JSON 和 Parquet 导入数据所需时间约为2-3倍。只有 aws_s3 接近 chdb 的性能，但它支持的数据格式范围要有限得多。

数据格式

我们提到数据格式了吗？chdb 扩展可以读写多种数据格式——所有这些 ClickHouse 本身支持的格式。下表总结了所比较扩展支持的数据格式和压缩算法；注意此chdb 格式列表只是 其支持的格式 的一个子集：

随着 ClickHouse 和 chDB 库 添加更多，chdb 扩展将免费获得它们！

我们对 chdb 加载 NYC 出租车数据集 的多种格式进行了基准测试，展示了相当一致的性能：

我们使用了 JSONCompact 格式以与其他扩展兼容。其他 JSON 格式，如 JSONCompactEachRow，将更接近其他格式的性能。

用法

该 chdb 包附带两个扩展：一个 CREATE EXTENSION 扩展名称为 chdb，以及一个 钩子模块 名称为 chdb_hook。

chdb 扩展

chdb 扩展（文档）提供 chdb_query() 函数，执行单个 chDB 查询。例如，此查询：

1SELECT*FROM chdb_query($$2SELECT*FROM s3('s3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv');3$$) AS (id int, months int, days int);

输出：

1id | months | days2----+--------+------3  1 |      2 |    34  3 |      2 |    15  4 |      5 |    66(3 rows)

chdb_hook 模块

该 chdb_hook 模块（文档）挂接到 COPY 命令，以向/从 AWS S3、Google Cloud Storage、Azure Blob Storage、文件或 HTTP URL 复制数据。此示例从 S3 上的 CSV 文件加载记录：

1CREATE TABLE times (2    id     INTNOT NULL,3    months INTNOT NULL,4    days   INTNOT NULL5);67LOAD 'chdb_hook';8COPY times FROM's3://datasets-documentation/my-test-bucket-768/some_prefix/some_file_1.csv';

之后，times 表包含文件中的记录：

1# SELECT * FROM times;2id | months | days3----+--------+------4  1 |      2 |    35  3 |      2 |    16  4 |      5 |    67(3 rows)

一个 CREATE TABLE 命令还可以从这样的 URL 派生其列并加载其行。尝试这个（本文中的所有 URL 都指向真实数据文件）：

1CREATE TABLE reviews () WITH (2    copy_from ='s3://datasets-documentation/amazon_reviews/amazon_reviews_2015.snappy.parquet'3);

生成的、完全加载的表具有此结构：

数据类型

像 pg_clickhouse一样，chdb 依赖 pg-clickhouse-c 头文件库将值从 ClickHouse 转换为 Postgres，包括其 类型映射，如上面的 CREATE TABLE 示例。当前版本几乎将 ClickHouse 类型映射到 Postgres 类型，反之亦然。这些映射在大多数情况下有效；当无效时，使用 structure 选项告诉 chdb 要使用的类型。

例如，pg-clickhouse-c 将 Postgres JSON 值映射到 ClickHouse String，因为 ClickHouse JSON 目前仅识别 JSON 对象，而 Postgres JSON 支持对象、数组和 JSON 标量。但也许您确信您的 JSON 列只包含对象，这要感谢检查约束：

1CREATE TABLE projects (2    name   TEXT PRIMARY KEY,3    meta   JSON NOT NULLCHECK (json_typeof(meta) ='object')4);56INSERT INTO projects7VALUES ( 'chdb',   '{"status": "release"}' ),8       ( 'walrus', '{"status": "revise"}'  );

为了从对象感知存储格式（如 Parquet JSON）的灵活性和存储中获益，使用 structure 选项将其映射到 ClickHouse JSON：

1COPY projects to'file:///tmp/projects.parquet' (2    structure 'name String, meta JSON'3);

云存储 URL

该 chdb_hook 扩展读写所有您喜欢的存储平台。它根据 URL 方案确定适当的协议。

URL 还可以使用多种 通配符 并发获取多个文件。重新审视上面的 CREATE TABLE 示例，此命令从 S3 查找并导入六个文件：

1CREATE TABLE times () WITH (2    copy_from ='s3://datasets-documentation/my-test-bucket-768/{some,another}_prefix/some_file_{1..3}.csv'3);

之后，times 表包含加载的每个文件中的记录：

1SELECT * FROM times;2 c1 | c2 | c3 3----+----+----4  1 |  2 |  35  3 |  2 |  16  4 |  5 |  67  1 |  2 |  38  3 |  2 |  19  4 |  5 |  610  1 |  2 |  311  3 |  2 |  112  4 |  5 |  613  1 |  2 |  314  3 |  2 |  115  4 |  5 |  616  1 |  2 |  317  3 |  2 |  118  4 |  5 |  619  1 |  2 |  320  3 |  2 |  121  4 |  5 |  622 (18 rows)

架构

支持如此多的数据格式和云平台需要大量依赖。我们通过将问题委托给 chDB 库 来避免管理这些依赖。但是将该库加载到 Postgres 后端会显得过度，尤其是对于通常偶尔或定期执行的任务，例如每天从数据源加载一次。

因此，数据加载扩展采取多种方法来管理此类库的大小和复杂性，通过多种方式：

• aws_s3 简单地将文件下载到本地文件系统，并将控制权交给 COPY；因此其限制为 AWS S3 源以及 Postgres COPY 支持的格式
• pg_duckdb 将 DuckDB 引擎嵌入到 Postgres 后端，这对于偶尔的 COPY 需求来说是大材小用
• pg_lake 运行一个单独的 DuckDB-驱动的服务，并通过 libpq 协议 与之通信，这永久消耗 Postgres 主机上的资源

该 chdb 扩展 采用了自己独特的架构：它将 chDB 库 嵌入到一个单独的辅助应用程序中。扩展和 chdb_hook 链接 chDB。相反，它们按需启动辅助应用，并通过高效的内存通道（文件描述符）与之通信：STDIN、STDOUT和STDERR，外加另一个用于配置信息的文件描述符）。

这种设计防止了chDB库在执行单个命令时占用超出必要的资源。它还使PostgreSQL集群本身免受使用共享内存的内存不足问题的影响，而这些问题在使用后台工作进程时会出现。

当辅助应用执行完命令并将所有结果以ClickHouse Native格式（直接来自源）传递给后端后，它会清理并退出，将服务器资源留给最重要的服务：PostgreSQL。

1+-------------+2                  |   helper    |3+----------+      |    app      |      +------+4| Postgres |      | +---------+ |      | chDB |5| Backend  |<---->| |  chDB   | |<---->| Data |6+----------+      | | Library | |      +------+7                  | +---------+ |8                  +-------------+

下一步是什么？

我们计划继续改进chdb。潜在路线图项目包括：

• 完成类型映射。我们正在逐步填补Postgres和ClickHouse数据类型之间的差距，这对chdb和pg_clickhouse都有好处。
• 通过凭证链访问对象存储。目前，读写对象存储所需的凭证必须在每次chdb调用中显式传递。我们希望允许透明的服务器配置凭证也能工作。
• 支持COPY (query) TO
• 支持WHERE条件在COPY
• 中COPY选项
• 支持Iceberg格式
• 直接从存储查询文件

尝试一下

在chdb扩展中查找所有常见位置，包括GitHub和PGXN。我们还将其作为更广泛的pg_clickhouse包的一部分，在ClickHouse托管Postgres上提供；请咨询您的支持联系人，将chdb_hook添加到您的默认配置，或通过psql或您喜欢的客户端连接超级用户账户，运行CREATE EXTENSION chdb;或LOAD 'chdb_hook';并开始使用！