数据湖中的 Parquet 和 Postgres

原创 Ellison 2022-07-14

1840

静态数据不同

几周前，我看到了 Retool的一篇关于他们迁移 4TB 数据库的经验的博客。他们制定了一些好的程序并成功完成了迁移，但整个过程因数据库的大小而变得复杂。数据库的大小是几个非常大的“记录”表的结果：一个编辑日志和一个审计日志。

关于日志表的事情是，它们变化不大。它们是按设计仅附加的。他们也被相当不规则地查询，查询通常是时间范围的：“告诉我当时发生了什么”或“告诉我这些日期之间的活动”。

因此，Retool 迁移可能更容易的一种方法是，如果他们的日志表被构建为时间范围的分区。这样，分区集中将只有一个“活动”表（具有最近条目的表）和更大的历史表集合。迁移可以将活动分区作为关键路径的一部分移动，并在以后执行所有历史分区。

即使在将日志表分解为可管理的块之后，它们仍然总体上相当大！关于分区的 PostgreSQL 文档对存在于分区集合末尾的陈旧数据有一些严厉的意见：

“删除旧数据的最简单选择是删除不再需要的分区。”

那是有道理的！所有这些旧的历史记录只会使您的基本备份变得模糊，也许您几乎没有机会查询它。

有没有删除表的替代方法？

将您的数据转储到湖中

如果有一个存储选项仍然是持久的，允许通过多个查询工具进行访问，并且可以透明地集成到您的操作事务数据库中怎么办？

怎么样：以 Parquet 格式存储静态数据，但通过parquet_fdw保留对数据的数据库访问？

听起来有点疯狂，但是：

外部 parquet 表可以与本机 PostgreSQL 表一起参与分区。
R、 Python、 Go和许多云应用程序也可以使用 parquet 文件。
现代 PostgreSQL (14+) 可以并行访问外部表，因此可以有效地扫描 Parquet 文件的集合。
Parquet 存储压缩的数据，因此您可以将更多原始数据存储到更少的存储空间中。

等等，镶木地板？

Parquet 是一种独立于语言的存储格式，专为在线分析而设计，因此：

面向列
打字
二进制
压缩

PostgreSQL 中的标准表将在磁盘上面向行。

这种布局适用于 PostgreSQL 预期要做的事情，比如一次查询、插入、更新和删除“几条”记录的数据。（“少数”的价值可能会达到数十万或数百万，具体取决于操作。）

Parquet 文件将数据列存储在磁盘上，成批地称为“行组”。

您可以看到 Parquet 格式的得名：数据被分组到小方块中，就像镶木地板一样。将数据分组在一起的优点之一是压缩例程往往对相同类型的数据工作得更好，当数据元素具有相同的值时更是如此。

这甚至有效吗？

总之一句话“是的”，但有一些警告：Parquet 已经存在了好几年，但支持它的生态系统相对而言仍然在不断变化。底层 C++ 库的新版本仍然定期发布， parquet_fdw才几年，等等。

然而，我能够让我自己满意地证明，事情已经烤得足够有趣了。

加载数据中

我从一个方便的费城停车违规数据表开始，我在之前关于空间连接的博客文章中使用了该数据表，并按违规日期对文件进行了排序issue_datetime。

数据下载和排序

-- Create parking infractions table
CREATE TABLE phl_parking (
    anon_ticket_number integer,
    issue_datetime timestamptz,
    state text,
    anon_plate_id integer,
    division text,
    location text,
    violation_desc text,
    fine float8,
    issuing_agency text,
    lat float8,
    lon float8,
    gps boolean,
    zip_code text
    );

-- Read in the parking data
\copy phl_parking FROM 'phl_parking.csv' WITH (FORMAT csv, HEADER true);

好的，所以现在我有一个8M的记录数据表，适合一些批量数据实验。桌子有多大？

SELECT pg_size_pretty(pg_relation_size('phl_parking')) AS pg_table_size;

 pg_table_size
----------------
 1099 MB

刚刚超过1GB！

生成镶木地板

如何获取 Parquet 文件？

事实证明这比我预期的要困难得多。大多数互联网建议是使用 Python 或 Spark 将 CSV 文件转换为 Parquet。最后，我使用了 GDAL 库中对 Parquet 的非常新的（当前未发布，在 GDAL 3.5 中提供）支持，以及ogr2ogr进行转换的命令。

ogr2ogr -f Parquet \
  /tmp/phl_parking.parquet \
  PG:"dbname=phl host=localhost" \
  phl_parking

对于这些测试，Parquet 文件将驻留在我的本地磁盘中/tmp，尽管出于云目的，它可能驻留在云卷上，甚至（使用正确的软件）在对象存储中。

% ls -lh /tmp/phl_parking.parquet
-rw-r--r--  1 pramsey  wheel   216M 29 Apr 10:44 /tmp/phl_parking.parquet

由于数据压缩，Parquet 文件是数据库表大小的 20%！

查询 Parquet

在 PostgreSQL 中查询 Parquet 涉及许多部分，现在构建这些部分可能具有挑战性。

Apache libarrow，在启用 Parquet 支持的情况下构建。
parquet_fdw本身。

请注意，parquet_fdw需要libarrow版本 6，而不是最近发布的版本 7。

但是，一旦构建了 FDW 和支持库，一切都像其他 FDW 扩展一样工作。

CREATE EXTENSION parquet_fdw;

CREATE SERVER parquet_srv FOREIGN DATA WRAPPER parquet_fdw;

CREATE FOREIGN TABLE phl_parking_pq (
    anon_ticket_number integer,
    issue_datetime     timestamptz,
    state              text,
    anon_plate_id      integer,
    division           text,
    location           text,
    violation_desc     text,
    fine               float8,
    issuing_agency     text,
    lat                float8,
    lon                float8,
    gps                boolean,
    zip_code           text
    )
  SERVER parquet_srv
  OPTIONS (filename '/tmp/phl_parking.parquet',
           sorted 'issue_datetime',
           use_threads 'true');

与原始表相比，Parquet 文件的性能相似，通常要慢一些。只是爆破一个行数（当表预先缓存在内存中时）。

-- Give native table same indexing advantage
-- as the parquet file
CREATE INDEX ON phl_parking USING BRIN (issue_datetime);

SELECT Count(*) FROM phl_parking_pq;
-- Time: 1230 ms

SELECT Count(*) FROM phl_parking;
-- Time:  928 ms

同样，过滤器在 PostgreSQL 上也稍微快一些。

SELECT Sum(fine), Count(1)
FROM phl_parking_pq
WHERE issue_datetime BETWEEN '2014-01-01' AND '2015-01-01';
-- Time: 692 ms

SELECT Sum(fine), Count(1)
FROM phl_parking
WHERE issue_datetime BETWEEN '2014-01-01' AND '2015-01-01';
-- Time: 571 ms

实现得parquet_fdw非常好，甚至会告诉您将在文件上用于给定过滤器的执行计划。例如，前面的过滤器涉及打开 Parquet 文件中 132 个行组中的大约 20%。

EXPLAIN SELECT Sum(fine), Count(1)
FROM phl_parking_pq
WHERE issue_datetime BETWEEN '2014-01-01' AND '2015-01-01';

 Finalize Aggregate  (cost=6314.77..6314.78 rows=1 width=16)
   ->  Gather  (cost=6314.55..6314.76 rows=2 width=16)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=5314.55..5314.56 rows=1 width=16)
               ->  Parallel Foreign Scan on phl_parking_pq  (cost=0.00..5242.88 rows=14333 width=8)
                     Filter: ((issue_datetime >= '2014-01-01 00:00:00-08')
                          AND (issue_datetime <= '2015-01-01 00:00:00-08'))
                     Reader: Single File
                     Row groups: 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51,
                                 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63

对于遍历整个表并进行汇总，Parquet 查询的速度与 PostgreSQL 查询的速度大致相同。

SELECT issuing_agency, count(1)
FROM phl_parking_pq
GROUP BY issuing_agency;
-- Time: 3043 ms

SELECT issuing_agency, count(1)
FROM phl_parking
GROUP BY issuing_agency;
-- Time: 3103 ms

我对性能差异的内部模型是，虽然 Parquet 格式在避免不必要的读取、通过行块过滤和仅访问感兴趣的列方面具有一些优势，但这些优势被将原始数据从 parquet 转换为内部 PostgreSQL 格式。

结论

是这样吗？好吧，我们已经看到：

Parquet 是一种与软件无关的格式，在数据科学和数据中心中越来越普遍。
Parquet 访问可以通过parquet_fdw 扩展对 PostgreSQL 透明。
镶木地板存储可以节省大量空间。
Parquet 存储比本机存储慢一点，但可以将静态数据的管理从其余数据所需的备份和可靠性操作中分担。
静态数据的 Parquet 存储比将其丢弃要好得多。

更重要的是，我认为还有更多要讨论的内容：

parquet 文件可以参与分区吗？
可以在集合中并行访问镶木地板文件吗？
parquet 文件可以驻留在云对象存储中而不是文件系统存储中吗？
带有 parquet 存储的 PostgreSQL 能否像“中档大数据引擎”一样处理由 parquet 支持的大量静态数据集合上的数字？

到目前为止，Parquet 工具的生态系统一直由数据科学（R 和 Python）和少数云 OLAP 系统（Apache Spark）的需求所主导，但是 PostgreSQL 没有理由不能开始分享这种常见的云格式优势，并开始在数据湖中游泳。

原文作者：保罗拉姆齐

原文标题：Parquet and Postgres in the Data Lake

原文地址：https://www.crunchydata.com/blog/parquet-and-postgres-in-the-data-lake

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者