PostgreSQL到数据仓库：最佳方法进行近实时ETL / 数据提取

Question

PostgreSQL到数据仓库：最佳方法进行近实时ETL / 数据提取

postgresqldata-warehouseetlnear-real-timedata-extraction

14

背景：

我有一个为OLTP（联机事务处理）进行了大量优化的PostgreSQL（v8.3）数据库。

我需要以半实时的方式从中提取数据（肯定会有人问半实时是什么意思，答案是尽可能频繁地，但我会采取务实的方式，作为基准，假设我们希望每15分钟提取一次），并将其馈送到数据仓库。

有多少数据？在高峰期，我们谈论的是每分钟约80-100k行命中OLTP端，低峰期这将显著下降至15-20k。最常更新的行每个约64字节，但有各种表等，因此数据相当多样化，每行可达4000字节。OLTP运行时间为24x5.5。

最佳解决方案？

根据我所了解的情况，最实用的解决方案如下：

创建触发器将所有DML活动写入旋转的CSV日志文件
执行所需的任何转换
使用本地DW数据泵工具有效地将转换后的CSV泵入DW

为什么采用这种方法？

触发器允许选择性地针对特定表，而不是全系统覆盖+输出可配置（即到CSV中）且编写和部署相对容易。SLONY采用类似的方法，开销可接受
CSV易于快速转换
将CSV泵入DW很容易

考虑的替代方案....

使用本地日志记录 (http://www.postgresql.org/docs/8.3/static/runtime-config-logging.html)。问题在于相对于我所需的内容，它看起来非常冗长并且有点棘手解析和转换。然而，由于我认为与触发器相比，它的开销更小，因此它可能会更快。当然，由于它是系统范围的，因此它将使管理员更轻松，但是再次强调，我不需要某些表格（其中一些用于持久存储JMS消息，我不想记录）
通过ETL工具（如Talend）直接查询数据并将其推送到DW中...问题是需要调整OLTP架构以支持此操作，这会产生许多负面影响
使用调整/修改过的SLONY - SLONY很好地记录和迁移更改到从节点，因此概念框架已经存在，但是建议的解决方案似乎更简单、更干净
使用WAL

有人做过这个吗？想分享一下你的想法吗？

- belvoir

你最终使用了什么？ - Gajus

4个回答

3

如果您能想到一个“校验和表”，其中只包含ID和“校验和”，那么您不仅可以快速选择新记录，还可以选择更改和删除的记录。

校验和可以是您喜欢的crc32校验和函数。

- Mercure

1

我不知道为什么没有更多关于这个解决方案的讨论，这是许多平台常见的解决方案。 - JHixson

0

PostgreSQL 中的新 ON CONFLICT 子句改变了我进行许多更新的方式。我将新数据（基于 row_update_timestamp）提取到临时表中，然后在一个 SQL 语句中使用 ON CONFLICT UPDATE 插入到目标表中。如果您的目标表是分区的，则需要跳过一些步骤（即直接访问分区表）。ETL 可以在加载 Temp 表时发生（最有可能），也可以在 ON CONFLICT SQL 中发生（如果是微不足道的）。与其他“UPSERT”系统（更新、插入零行等）相比，这显示出了巨大的速度提升。在我们特定的 DW 环境中，我们不需要/希望容纳 DELETEs。查看 ON CONFLICT 文档-它使 Oracle 的 MERGE 失色！

- Spike Hodge

0

我对这个话题的看法是针对现今的2023年...

选项1（批处理方法）：

使用增量提取进行分段，每次迭代保留每个表中传输的最大行数的整数或时间戳。我们可以始终使用ONCONFLICT语句来避免由于意外的迭代崩溃而导致的任何意外键冲突。这种方式无法跟踪行删除，但我们可以使用删除标志作为列来过滤数据仓库。
使用存储过程创建计算表，进行复杂的连接/计算，并将结果插入新的预计算表中，从而实现数据仓库。

-选项2（管道方法）

使用逻辑复制进行实时提取的分段。逻辑复制可以捕获并复制更改，使其与发生的顺序相同，因此目标数据库始终保持一致。这种方式也可以跟踪删除操作。
使用增量物化视图进行实时预计算轻量级连接/计算的混合，以及使用存储过程进行更重的连接/计算的计算表，因为IVM目前不支持外部连接和所有类型的聚合。

- Stavros Koureas

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- vladr · Accepted Answer

假设您感兴趣的表格已经具有（或可以增加）唯一的、索引的、顺序键，那么你就可以通过简单地发出 SELECT ... FROM table ... WHERE key > :last_max_key 并将结果输出至文件来获得更好的价值。其中，last_max_key 是上一次提取的最后一个键值（如果是第一次提取，则为0）。这种增量、解耦的方法避免了在插入数据路径中引入触发器延迟（无论是自定义触发器还是修改过的 Slony），并且根据您的设置，可能会随着 CPU 数量的增加而扩展得更好。（然而，如果您也需要跟踪 UPDATE，而且顺序键是由您添加的，则您的 UPDATE 语句应该将键列设置为 NULL，以便它获取一个新值并被下一次提取选中。如果没有触发器，您将无法跟踪 DELETE。）这符合您提到 Talend 时想要的吗？

我不建议使用日志记录功能，除非您无法实现上述解决方案；日志记录很可能涉及锁定开销，以确保日志行按顺序写入，并且当多个后端同时写入日志时不会重叠/覆盖（请检查 Postgres 源代码）。锁定开销可能不会灾难性，但如果您可以使用增量的 SELECT 替代方案，则可以避免此开销。此外，语句记录将淹没任何有用的 WARNING 或 ERROR 消息，而且解析本身也不是瞬间完成的。

除非你愿意解析WAL（包括事务状态跟踪，并且准备好每次升级Postgres都需要重写代码），否则我不会建议使用WAL。除非你有额外的硬件可用，在这种情况下，你可以将WAL 传输到另一台机器进行提取（在第二台机器上，你可以毫不羞耻地使用触发器，甚至是语句日志记录，因为在那里发生的任何事情都不会影响主机上 INSERT / UPDATE / DELETE 的性能。）请注意，就性能而言（在主机上），除非你能将日志写入SAN，否则将WALs发送到另一台机器和运行增量 SELECT 相比，你会得到类似的性能损失（主要是文件系统缓存被扰乱）。