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

Question

想象一下在 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 文件，其中包含如下内容(我也在整数和 double 周围使用引号):

"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"

Answer 1

Just imagine the horrible performance I get by comparing all strings to all other strings several times.

当您这样做一段时间后，您就会停止想象性能，并开始衡量它。 “过早的优化是万恶之源。”

“十亿”对您来说意味着什么？对我来说，在美国，这意味着 1,000,000,000(或 1e9)。如果您也是这样，那么您可能正在查看 1 到 7 TB 的数据。

My current method is to essentially run the following query to create the dimension table:

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

如何将 100 亿行放入一个使用整数作为主键的表中？甚至可以说一半的行是重复的。当您这样做时，该算法是如何工作的？

别想了。先阅读。然后测试。

阅读Data Warehousing with PostgreSQL .我想这些演示幻灯片会给您一些想法。

另请阅读 Populating a Database ，并考虑实现哪些建议。

按照“分而治之”的过程，用一百万 (1e6) 行进行测试。也就是说，不要试图一次加载一百万；编写一个将其分解成更小块的过程。运行

EXPLAIN <sql statement>

您说过您估计至少有 99% 的重复行。从广义上讲，有两种方法可以摆脱欺骗

1. 在数据库中，不一定是您用于生产的同一平台。
2. 在数据库之外，在文件系统中，不一定与您用于生产的文件系统相同。

如果您仍然有加载的文本文件，我会考虑先在数据库外尝试。这个 awk 单行代码将从每个文件中输出唯一的行。它相对经济，因为它只传递一次数据。

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

如果您真的有 99% 的被骗，到这个过程结束时，您应该已经将 1 到 7 TB 的数据减少到大约 50 GB。而且，这样做之后，您还可以对每个唯一的行进行编号并创建一个制表符分隔的文件，然后再将其复制到数据仓库中。这是另一个单行:

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

如果您必须在 Windows 下执行此操作，我会使用 Cygwin .

如果您必须在数据库中执行此操作，我会尽量避免使用您的生产数据库或生产服务器。但也许我太谨慎了。移动数 TB 的数据是一件代价高昂的事情。

但我会测试

SELECT DISTINCT ...

在使用 GROUP BY 之前。我也许可以为您在大型数据集上做一些测试，但可能不会在本周进行。 (我通常不处理 TB 大小的文件。这有点有趣。如果你可以等待。)