pg_parquet: 访问外部存储中的 Parquet 格式文件

在 PostgreSQL 中，使用 pg_parquet 扩展，可以从 S3、Azure Blob 存储、Google 云存储、HTTP(S) 存储、本地文件或标准输入流，访问 Parquet 格式数据。

介绍

pg_parquet是一个 PostgreSQL 扩展，支持通过COPY TO/FROM命令，读取或写入存储在 S3、Azure Blob Storage、Google Cloud Storage、HTTP(S) 端点 或 文件系统 中的 Parquet 文件。该扩展依赖 Apache Arrow 项目处理 Parquet 文件的读写逻辑，依赖 pgrx 项目扩展 PostgreSQL 的COPY命令功能。

-- 将查询结果导出为 S3 中的 Parquet 文件
COPY (SELECT * FROM table)
  TO 's3://mybucket/data.parquet'
  WITH (format 'parquet');

-- 从 S3 中的 Parquet 文件导入数据
COPY table
  FROM 's3://mybucket/data.parquet'
  WITH (format 'parquet');

用法

pg_parquet主要支持三大核心功能：

1. 将 PostgreSQL 表/查询结果导出至 Parquet 文件、标准输入/输出或程序流；
2. 从 Parquet 文件导入数据至 PostgreSQL 表；
3. 查看 Parquet 文件的模式和元数据。

Parquet 文件与 PostgreSQL 表之间的导入/导出

可以通过 PostgreSQL 的COPY命令，实现 Parquet 文件与表的读写操作。以下示例展示如何将包含复杂类型的 PostgreSQL 表写入 Parquet 文件，再将 Parquet 文件内容导回原表：

-- 创建复合类型
CREATE TYPE product_item AS (id INT, name TEXT, price float4);
CREATE TYPE product AS (id INT, name TEXT, items product_item[]);

-- 创建包含复杂类型的表
CREATE TABLE product_example (
    id int,
    product product,
    products product[],
    created_at TIMESTAMP,
    updated_at TIMESTAMPTZ
);

-- 插入测试数据
insert into product_example values (
    1,
    ROW(1, 'product 1', ARRAY[ROW(1, 'item 1', 1.0), ROW(2, 'item 2', 2.0), NULL]::product_item[])::product,
    ARRAY[ROW(1, NULL, NULL)::product, NULL],
    now(),
    '2022-05-01 12:00:00-04'
);

-- 将表数据导出为 Parquet 文件（使用 gzip 压缩）
COPY product_example
  TO '/tmp/product_example.parquet'
  (format 'parquet', compression 'gzip');

-- 查看表数据
SELECT * FROM product_example;

-- 将 Parquet 文件数据导入表
COPY product_example FROM '/tmp/product_example.parquet';

-- 再次查看表数据（验证导入结果）
SELECT * FROM product_example;

通过 URI 模式列出并读取 Parquet 文件

调用SELECT * FROM parquet.list(<uri_pattern>)，可列出所有匹配指定 URI 模式的 Parquet 文件。URI 模式支持**匹配多级目录，*匹配文件名中的字符：

-- 导出 100 万行数据至多个 Parquet 文件（每个文件大小限制为 1MB）
COPY (SELECT i FROM generate_series(1, 1000000) i)
  TO '/tmp/some/test.parquet'
  with (file_size_bytes '1MB');

-- 列出匹配 URI 模式的所有 Parquet 文件
SELECT * FROM parquet.list('/tmp/some/**/*.parquet');
                  uri                  |  size
---------------------------------------+---------
 /tmp/some/test.parquet/data_4.parquet |  100162
 /tmp/some/test.parquet/data_3.parquet | 1486916
 /tmp/some/test.parquet/data_2.parquet | 1486916
 /tmp/some/test.parquet/data_0.parquet | 1486920
 /tmp/some/test.parquet/data_1.parquet | 1486916
(5 rows)

COPY FROM命令支持 URI 模式（HTTP(S) 端点除外），可批量导入匹配的 Parquet 文件：

-- 导出 100 万行数据至 S3 的多个 Parquet 文件（每个文件大小限制为 1MB）
COPY (SELECT i FROM generate_series(1, 1000000) i)
  TO 's3://testbucket/some/test.parquet'
  with (file_size_bytes '1MB');

-- 创建目标表
CREATE TABLE test(a int);

-- 批量导入 S3 中匹配 URI 模式的 Parquet 文件
COPY test FROM 's3://testbucket/some/**/*.parquet';

对象存储支持

pg_parquet支持读写存储在 S3、Azure Blob Storage、HTTP(S) 和 Google Cloud Storage 中的 Parquet 文件。

注意：

• 若需写入对象存储，需为当前 PostgreSQL 用户授予parquet_object_store_write角色；
• 若需从对象存储读取，需为当前 PostgreSQL 用户授予parquet_object_store_read角色。

复制选项

COPY TO 命令支持的选项

• format parquet：当 Parquet 文件扩展名不为.parquet[.<compression>]时，需指定该选项；
• file_size_bytes <string>：单个 Parquet 文件的目标大小。设置后，会在指定路径下创建同名目录，并生成多个目标大小的 Parquet 文件。默认不指定时，生成单个文件且不创建目录。支持不带单位（如file_size_bytes 2000000）或带单位（KB/MB/GB，如file_size_bytes '1MB'）；
• field_ids <string>：为 Parquet 文件模式中的字段分配 ID。默认不分配，传入auto可自动生成，也可传入 JSON 字符串显式指定；
• row_group_size <<int64>：写入 Parquet 文件时每个行组的行数，默认值为122880；
• row_group_size_bytes <<int64>：写入 Parquet 文件时每个行组的字节大小，默认值为row_group_size * 1024；
• compression <string>：写入 Parquet 文件时使用的压缩格式，支持uncompressed、snappy、gzip、brotli、lz4、lz4raw和zstd，默认值为snappy。未指定时，将根据文件扩展名自动识别；
• compression_level <<int>：压缩级别（仅支持gzip、zstd和brotli）：
  • gzip：默认 6（范围 0-10）
  • zstd：默认 1（范围 1-22）
  • brotli：默认 1（范围 0-11）
• parquet_version <string>：Parquet 文件的写入器版本，默认值为v1（兼容多数查询引擎），可设置为v2以启用部分新编码功能。

COPY FROM 命令支持的选项

• format parquet：当 Parquet 文件扩展名不为 .parquet[.<compression>] 时，需指定该选项；
• match_by <string>：Parquet 文件字段与 PostgreSQL 表列的匹配方式，支持position（按位置匹配，默认）和name（按名称匹配）。当 Parquet 文件与表的字段顺序不同但名称一致时，推荐使用 name 匹配。

支持的数据类型

pg_parquet支持丰富的数据类型，包括 PostgreSQL 的基本类型、数组类型和复合类型。以下是 PostgreSQL 类型与 Parquet 类型的映射关系：

警告：

1. numeric类型会按最小内存宽度写入 Parquet 文件：

• numeric(P <= 9, S) → INT32（DECIMAL 逻辑类型）
• numeric(9 < P <= 18, S) → INT64（DECIMAL 逻辑类型）
• numeric(18 < P <= 38, S) → FIXED_LEN_BYTE_ARRAY(9-16)（DECIMAL 逻辑类型）
• numeric(38 < P, S) → BYTE_ARRAY（STRING 逻辑类型）

未指定精度和刻度的 numeric 类型默认按精度 38、刻度 9 处理（整数部分最多 29 位，小数部分最多 9 位），若超出该范围会触发运行时错误。

2. date 类型写入Parquet文件时按 Unix 时间戳存储，读取时转换为 PostgreSQL 时间戳。

3. timestamptz 和 timetz 类型写入Parquet文件时转换为 UTC 时区，读取时保持 UTC 时区。

4. 若已创建 postgis 扩展，geometry 类型按 geoparquet 规范编码为 WKB 格式的 BYTE_ARRAY；否则按 BYTE_ARRAY（STRING逻辑类型）存储。

5. 无对应 Parquet 类型的 PostgreSQL 类型（如 enum），默认按BYTE_ARRAY（STRING 逻辑类型）存储。

参考

pg_parquet：https://github.com/CrunchyData/pg_parquet