PostgreSQL:高效地将数据加载到星型模式中

Question

PostgreSQL:高效地将数据加载到星型模式中

postgresqlnormalizationetlstar-schemafact-table

7

假设在PostgreSQL 9.0上有一张如下结构的表：

create table raw_fact_table (text varchar(1000));

为了简化起见，我只提到一个文本列，实际上有十几个。这张表有100亿行，每一列都有很多重复的数据。该表是使用COPY FROM从平面文件（csv）创建的。

为了提高性能，我想将其转换为以下星型模式结构：

create table dimension_table (id int, text varchar(1000));

事实表将被替换为以下事实表：

create table fact_table (dimension_table_id int);

我的当前方法基本上是运行以下查询来创建维度表：

Create table dimension_table (id int, text varchar(1000), primary key(id));

然后为了创建填充维度表，我使用以下代码：

insert into dimension_table (select null, text from raw_fact_table group by text);

之后我需要运行以下查询：

select id into fact_table from dimension inner join raw_fact_table on (dimension.text = raw_fact_table.text);

想象一下，如果我将所有的字符串都与其他字符串进行多次比较，那么性能将会多么糟糕。

在MySQL中，我可以在COPY FROM期间运行存储过程。这可以创建一个字符串的哈希值，随后所有的字符串比较都是在哈希值上进行而不是在长字符串上进行。但是在PostgreSQL中似乎不可能做到这一点，那我该怎么办呢？

示例数据可能是一个包含以下内容的CSV文件（我还使用引号限定整数和双精度浮点数）：

"lots and lots of text";"3";"1";"2.4";"lots of text";"blabla"
"sometext";"30";"10";"1.0";"lots of text";"blabla"
"somemoretext";"30";"10";"1.0";"lots of text";"fooooooo"

- David

这似乎需要多长时间？你原本期望它需要多长时间？ - Mike Sherrill 'Cat Recall'

我从未使用提到的数据量完成它。但是在1500万行上，它需要几个小时。我已经研究了所有标准服务器优化内容（work_mem等），因此我正在寻找一种不同的方法来实现相同的结果。 - David

我已经编辑了问题并包含了样本数据。 - David

5个回答

2

-- add unique index
CREATE UNIQUE INDEX uidx ON dimension_table USING hash(text);
-- for non case-sensitive hash(upper(text))

尝试使用hash(text)和btree(text)来看哪个更快。

- Bonshington

2

你在最后省略了一些细节，但我并不认为这 necessarily 是一个问题。没有证据表明所有字符串都与其他字符串进行了比较。如果你进行连接操作，PostgreSQL 可能会选择更智能的连接算法，例如哈希连接，这可能会给你提供与你在 MySQL 解决方案中自己实现的哈希相同的结果。(再次说明，你的细节模糊不清。)

- Peter Eisentraut

谢谢你的回答。我已经更新了问题，补充了缺失的细节。 - David

2

只有两个问题： - 将数据转换为1或2步骤是否必要？ - 在转换时可以修改表格吗？

运行更简单的查询可能会提高性能（并且在执行时减轻服务器负载）

一种方法是：

生成dimension_table（如果我理解正确，您不会遇到性能问题）（可能还带有一个临时布尔字段...）
重复：从dimension_table中选择一个以前未选择的条目，选择包含它的每一行raw_fact_table，并将它们插入fact_table。标记dimension_table记录为完成，然后进行下一个...您可以将其编写为存储过程，并在后台转换数据，占用最少的资源...

或者另一种（可能更好的）方法：

将fact_table创建为raw_fact_table中的每个记录和一个dimension_id。（因此包括dimension_text和dimension_id行）
创建dimension_table
为fact_table创建一个after insert触发器，该触发器：
- 在fact_table中搜索dimension_text
- 如果没有找到，就在dimension_table中创建一个新记录
- 更新dimension_id为此id
在一个简单的循环中，将raw_fact_table中的每条记录插入fact_table

- Dutow

谢谢您的建议。我没有具体说明，但我的唯一关注点是尽可能快地处理所有数据，因此在我的设置中运行后台程序没有意义（我知道在其他情况下非常明智）。您第二种方法的问题是触发器不会在COPY FROM时触发。因此，我认为没有触发器的原因。使用游标仍然非常有效，但我对性能不确定：http://stackoverflow.com/questions/4776127/postgres-surprising-performance-on-updates-using-cursor - David

关于你的第一种方法，我还没有计算生成维度表的性能（我应该这样做）。我喜欢你反其道而行的方法。我会创建一个基于你的方法的新评论。 - David

创建维度表 dimension_table（id serial，text varchar（1000），raw_fact_table_id bigint []，primary key（id））;插入到 dimension_table（text，raw_fact_table_id）（select text，array_agg（raw_fact_table.id）from raw_fact_table group by text）;之后需要找到一种方法根据 raw_fact_table_id 中的 id 更新 raw_fact_table。你觉得呢？ - David

看起来不错。在此之后，您可以轻松地更改原始表格（添加列，并在此之后更新），或者您可以创建一个联接表（无需更改原始表格），从而解决问题。（使用unnest） - Dutow

我现在尝试了这种方法，它比原来的方法慢了大约7倍。我的更新方式是从维度表中获取包含数组的一行，然后运行update raw_fact_table set text = '3' where id in(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17); 数组被解析为数字。特别是如果一列中只有少量重复项（例如仅70%），那么需要运行很多更新查询。 - David

为什么不使用(dimension_id，raw_fact_table_id)对创建一个联接表呢？使用这种方式进行联接速度会更快且只需要从当前维度表中执行一个SQL语句即可生成该表。 - Dutow

1

我可以看到解决您的问题有几种方法。 PostgreSql 中有 md5 函数。 md5(string) 计算字符串的 MD5 哈希值，并以十六进制返回结果。

将 md5 字段插入维度表中（使用 select null, md5(text), text from raw_fact_table group by text）

同时在原始事实表中添加 md5 字段使用 inner join 将维度表和原始事实表连接起来，选择 id 列插入到事实表中（on (dimension.md5 = raw_fact_table.md5)）

在 MD5 字段上建立索引也可能会有所帮助。

或者您可以在加载数据时动态计算 MD5。例如，我们的 ETL 工具 Advanced ETL processor 可以为您完成此操作。此外，它还可以同时将数据加载到多个表中。

我们的网站上提供了许多在线教程。例如，这个教程演示了如何加载慢变化维度。

http://www.dbsoftlab.com/online-tutorials/advanced-etl-processor/advanced-etl-processor-working-with-slow-changing-dimension-part-2.html

- ETL Man

我不相信在运行COPY FROM（这是推荐的数据加载方式）时可以同时计算MD5。如果这意味着您的工具不使用COPY FROM，则我认为它是无用的，因为没有此功能将需要很长时间进行加载。我必须说，我对无代码ETL解决方案非常怀疑。只要我只需要做标准操作，那么这很好，但是如果我遇到特殊问题，我就没有代码可供回退。 - David

完全同意你的观点，COPY FROM 是将数据加载到 PostgreSQL 中最快的方法。这也是我们在 Advanced ETL Processor 内部使用它的原因。来自 PostgreSQL 文档： COPY TABLE_NAME FROM STDIN （STDIN 指定输入来自客户端应用程序。） - ETL Man

我们尽力使其尽可能快速。对于每个数据库，我们使用最快的数据加载方式。（例如Oracle的直接/传统路径，SQL Server的bcp，PostgreSQL的copy from等）我们实际上打印了关键代码，并标记和消除了所有无效部分。然后我们使用分析器进一步优化性能。我们不断地进行改进。（请查看我们的支持论坛，并注意解决问题或引入新功能所需的时间，然后将其与大型参与者进行比较）。 - ETL Man

我们的软件不臃肿（企业版仅28M），可以直接与16个数据源和17个数据目标进行交互。它不会占用大量内存，内存使用量也与文件/表大小无关（除了OLE DB Source）。与SSIS/DTS/ODBC/OLEDB不同的是，我们的软件可以始终正确地处理Excel。它可以将文件系统作为数据源，并将文件加载到Blob字段中。它可以将Blob字段保存到单独的文件中。它可以处理电子邮件，并使用POP3服务器作为数据源。它还可以发送电子邮件，并使用SMTP服务器作为数据目标。我可以继续列举更多功能，但我想你已经有了一个大致的了解。 - ETL Man

ETL软件的编写非常困难。我的意思是，你认识谁对MS Sql Server、Oracle、MySql、PostgreSQL、Firebird、Interbase、SQLite、POP3、SMTP、XML、Excel、Access、DBF、Foxpro、ODBC、OLE DB、FTP、SFTP、FTPS、HTTP、SSL等底层API有深入了解吗？我认识。 - ETL Man

显示剩余2条评论

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

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

- Mike Sherrill 'Cat Recall' · Accepted Answer

想象一下，通过将所有字符串与所有其他字符串进行多次比较，我得到的性能有多糟糕。

当你做了一段时间后，你会停止想象性能，开始测量它。"过早优化是万恶之源。"

"十亿"对你意味着什么？对我来说，在美国，它表示10亿（或1e9）。如果对你也是这样，那么你可能正在查看1到7TB的数据。

我的当前方法基本上是运行以下查询来创建维度表：

Create table dimension_table (id int, text varchar(1000), primary key(id));

你如何将100亿行数据装入用整数作为主键的表中？我们甚至可以假设一半的行是重复的。这个算术运算怎么处理呢？

不要想象，先阅读。然后进行测试。

请阅读使用PostgreSQL进行数据仓库建模。我猜这些演示文稿会给你一些思路。

还要阅读填充数据库, 并考虑实施哪些建议。

通过“分而治之”的过程，对100万（1e6）行数据进行测试。也就是说，不要试图一次性加载100万行数据；编写一个将其拆分成较小块的过程。运行

EXPLAIN <sql statement>

您说过至少有99%的重复行。总体而言，有两种方法可以摆脱这些重复项：

在数据库内（不一定是您用于生产的同一平台）
在文件系统中（不一定是您用于生产的同一文件系统）

如果您仍然拥有已加载的文本文件，我建议首先尝试在数据库外进行。此awk单行程序将从每个文件输出唯一行。它相对经济，因为它只对数据进行一次遍历。

awk '!arr[$0]++' file_with_dupes > file_without_dupes

如果您真的有99%的重复数据，那么在此过程结束时，您应该将1到7TB的数据减少到约50GB。完成这个步骤后，您还可以对每个唯一的行进行编号，并创建一个制表符分隔的文件，然后将其复制到数据仓库中。这是另一个一行代码：

awk '{printf("%d\t%s\n", NR, $0);}' file_without_dupes > tab_delimited_file

如果你必须在Windows下执行此操作，我建议使用Cygwin。

如果你必须在数据库中执行此操作，我建议避免使用生产数据库或生产服务器。但也许我太谨慎了。移动几个TB的数据是一件昂贵的事情。

但我会进行测试。

SELECT DISTINCT ...

在使用GROUP BY之前。我可能可以为您对大型数据集进行一些测试，但可能不是本周。(我通常不使用千兆字节大小的文件。这有点有趣。如果您能等待。)