解锁Postgres与Parquet连接

Apache Parquet 是一种开源、标准化的列式文件格式，源自大数据时代的 Hadoop。通过优化 SQL 查询的方式来存储数据，在数据湖环境中广泛应用。作为一种高效的列式格式，Parquet 提供了优秀的数据压缩能力。在系统之间共享数据时，选择 Parquet 格式进行处理尤为合适，特别是对于需要归档的旧数据或适合分析的格式，而非事务性工作负载。

有许多工具支持 Parquet，但 Postgres 用户通常需要自行解决相关问题。现在， pg_parquet 扩展支持 Postgres 本地读取和写入 Parquet 文件到本地磁盘或 S3。借助 pg_parquet 扩展，Postgres 和 Parquet 可以无缝协同工作，用户无需再维护额外的数据通道。

使用 pg_parquet 您可以：

• **Postgres －> Parquet：**将表或查询从 Postgres 导出到 Parquet 文件
• **Parquet －> Postgres：**将数据从 Parquet 文件获取到 Postgres
• **Browse Parquet：**浏览 Parquet 文件的架构和元数据

使用 pg_parquet

扩展 Postgres copy命令，我们能够有效地将数据复制到 Parquet 或从本地服务器或 s3 中复制数据。

# 将查询结果复制到 postgres 服务器上的 Parquet 文件中
COPY (SELECT * FROM table) TO '/tmp/data.parquet' WITH (format 'parquet');

#  将查询结果复制到 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');

我们看下 products 示例表，但不仅仅是一个基本版本，它具有复合 Postgres 类型和数组：

# 创建复合类型
CREATE TYPE product_item AS (i​​d INT, name TEXT, price float4);CREATE TYPE product AS (i​​d 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 文件
COPY product_example TO '/tmp/product_example.parquet' (格式 'parquet', 压缩 'gzip');

# 将 parquet 文件复制到表
COPY product_example FROM '/tmp/product_example.parquet';

# 显示表
SELECT * FROM product_example;

检查 Parquet 文件

除了将数据复制到 Parquet 中或从 Parquet 中复制数据之外，您还可以浏览现有的 Parquet 文件以了解其结构。

# 描述 parquet 模式
SELECT name, type_name, logical_type, field_id FROM parquet.schema('s3://mybucket/data.parquet');
│     name     │ type_name  │ logical_type │ field_id ││ arrow_schema │
│ a            │ BYTE_ARRAY │ STRING       │        0 │
│ s            │ INT32      │              │        1 │
(2 rows)

-- 检索 parquet 详细元数据，包括列统计信息
SELECT row_group_id, column_id, row_group_num_rows, row_group_bytes FROM parquet.metadata('s3://mybucket/data.parquet');
│ row_group_id │ column_id │ row_group_num_rows │ row_group_bytes │
│            0 │         0 │                100 │             622 │
│            0 │         1 │                100 │             622 │
(2 rows)

# 检索 parquet 文件元数据，例如总行数
SELECT created_by, num_rows, format_versionFROM parquet.file_metadata('s3://mybucket/data.parquet');
│ created_by │ num_rows │ format_version │
│ pg_parquet │      100 │ 1              │
(1 row)

Parquet 和云平台

如果您正在使用对象存储来管理 Parquet 文件，可能是S3或S3兼容的内容。如果您配置了 ~/.aws/credentials 和 ~/.aws/config 文件，pg_parquet将自动使用这些凭证，以便您在云对象存储之间进行复制。

$ cat ~/.aws/credentials[default]aws_access_key_id = AKIAIOSFODNN7EXAMPLEaws_secret_access_key = wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
$ cat ~/.aws/config[default]region = eu-central-1

能够通过 COPY 命令直接访问对象存储对于存档、分析、导入其他应用程序写入的数据以及在服务器之间移动数据非常有用。

结论

Michael Stonebraker 认为：“现代数据架构中，传统关系数据库系统与数据分析平台可以协同工作，传统数据库在查询优化和事务处理方面有优势，但在处理大规模、低结构化的数据时，新的技术和数据库架构，如专门的数据湖查询引擎（如 Apache Parquet 和 Apache Hive）会更加高效。”从前它们之间更多的互相隔离，有了pg_parquet 扩展之后， Postgres对于数据湖的数据访问将为数据分析提供更加便捷的通道。

代码可在以下网址获得：https://github.com/CrunchyData/pg_parquet/