pg_duckdb: 集成 DuckDB 以释放 PostgreSQL 数据分析的潜力

九月 19, 2024

摘要：pg_duckdb是一个 PostgreSQL 扩展，可将 DuckDB 的列式向量化分析引擎和能力嵌入到 PostgreSQL 中。

目录

介绍

pg_duckdb扩展将完全能够查询存储在云上 DuckDB 中的数据，就像它是本地数据一样。DuckDB 的“双执行”能力让我们能够将本地 PostgreSQL 数据与 DuckDB 数据无缝连接，并且我们会找出运行查询的最佳位置。作为用户，您真的不需要关心计算在哪里运行，我们只需要弄清楚如何让它快速运行。

此外，在数据分析场景中，希望将数据从事务型数据库卸载到分析型存储中是很常见的。pg_duckdb扩展和 DuckDB 一起能够实现这一点；您只需在 PostgreSQL 中运行一个查询，从 PostgreSQL 数据库中提取最新数据，并将其写入 DuckDB。您无需导出和重新导入数据，也无需设置 CDC。

最后，在运行应用程序的同一数据库上进行数据分析也有一些缺点。在让数据分析能正常运行时，所需的内存量和 CPU 运算量方面，可能会非常耗费资源。超出一定的资源消耗量，人们可能不想在他们的生产环境事务型数据库上来进行分析。DuckDB 将可帮助将计算卸载到云上，人们甚至不必更改他们正在运行的查询；查询也能变得更快。

安装

要构建 pg_duckdb，您需要：

要构建和安装，请运行：

make install

接下来，加载 pg_duckdb 扩展：

CREATE EXTENSION pg_duckdb;

重要：加载后，您可以通过运行SET duckdb.execution TO true，来使用 DuckDB 执行。这是一个避免中断现有查询的选项。为避免对每个会话都执行此操作，您可以通过执行ALTER USER my_analytics_user SET duckdb.execution TO true，以对特定用户进行配置。

特性

  • DuckDB 引擎执行的SELECT查询，可以直接读取 PostgreSQL 表。

    • 能够读取 PostgreSQL 和 DuckDB 中都存在的数据类型。支持的数据类型有：数值、字符、二进制、日期/时间、布尔值、uuid、json 和数组。
    • 如果 DuckDB 由于任何原因无法支持查询，则执行会回退到 PostgreSQL。

  • 从对象存储（AWS S3、Cloudflare R2 或 Google GCS）读取 Parquet 和 CSV 文件。

    • SELECT n FROM read_parquet('s3://bucket/file.parquet') AS (n int)
    • SELECT n FROM read_csv('s3://bucket/file.csv') AS (n int)
    • 您可以将通配符和数组传递给这些函数，就像在 DuckDB 中一样。

  • 使用SELECT duckdb.enable_extension('iceberg')启用 DuckDB 的 Iceberg 扩展，并使用iceberg_scan读取 Iceberg 文件。

  • 将一个查询或整个表的数据，写入到对象存储中的 parquet 文件。

    • COPY (SELECT foo, bar FROM baz) TO 's3://...'
    • COPY table TO 's3://...'

  • 在单个查询中，以 Parquet 格式读取和写入

    COPY (
  SELECT count(*), name
  FROM read_parquet('s3://bucket/file.parquet') AS (name text)
  GROUP BY name
  ORDER BY count DESC
) TO 's3://bucket/results.parquet';

  • 使用 PostgreSQL 表、视图和物化视图，查询和JOIN在对象存储中的数据。

  • 在 PostgreSQL 表上创建索引，以加速 DuckDB 查询。

  • 使用SELECT duckdb.install_extension('extension_name');安装 DuckDB 扩展

  • 使用设置，来打开/关闭 DuckDB 的执行：

    • SET duckdb.execution = true|false

开始上手

最好的入门方法是，使用 pg_duckdb 将 PostgreSQL 连接到新的或现有的对象存储桶（AWS S3、Cloudflare R2 或 Google GCS）。您可以分别使用read_parquet、read_csv和iceberg_scan，查询 Parquet、CSV 和 Iceberg 格式的数据。

  1. 添加一个凭证，以启用 DuckDB 的 httpfs 支持。

    -- Session Token is Optional
INSERT INTO duckdb.secrets
(type, id, secret, session_token, region)
VALUES ('S3', 'access_key_id', 'secret_access_key', 'session_token', 'us-east-1');

  2. 将数据直接复制到您的存储桶 - 无需 ETL 管道！

    COPY (SELECT user_id, item_id, price, purchased_at FROM purchases)
TO 's3://your-bucket/purchases.parquet;

  3. 在您的数据上执行分析。

    SELECT SUM(price) AS total, item_id
FROM read_parquet('s3://your-bucket/purchases.parquet')
  AS (price float, item_id int)
GROUP BY item_id
ORDER BY total DESC
LIMIT 100;

了解更多

pg_duckdb 项目

搜索

分类

标签