PostgreSQL：有效地将数据加载到Star Schema中

想象一下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"

已邀请:

5 个回复

炉挤仙挟

只是问题： - 是否需要以1或2步转换数据？ - 我们可以在转换时修改表吗？运行更简单的查询可以提高性能（以及服务器负载）一种方法是：生成dimension_table（如果我理解正确，你没有性能问题）（可能有一个额外的临时布尔字段...） repeat：从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插入触发器，其中：在fact_table中搜索dimension_text 如果未找到，则在dimension_table中创建新记录将dimension_id更新为此id 在simle循环中，将raw_fact_table中的每条记录插入fact_table

弦砂牧扁

想象一下可怕的表现我通过比较所有字符串来获得其他字符串数次。当你这样做了一段时间后，你停止想象性能，然后开始测量它。 “过早优化是万恶之源。” “十亿”对你意味着什么？对我来说，在美国，这意味着1,000,000,000（或1e9）。如果你也是这样，你可能会看到1到7太字节的数据。我目前的方法基本上是运行以下查询以创建维度表：

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

你如何将100亿行放入一个使用整数作为主键的表中？我们甚至可以说一半的行是重复的。当你这样做时，算法如何工作？不要想象。先阅读。然后测试。使用PostgreSQL读取数据仓库。我怀疑这些演示幻灯片会给你一些想法。另请阅读填充数据库，并考虑要实施哪些建议。按照“分而治之”的过程，测试一百万（1e6）行。也就是说，不要一次尝试加载一百万;编写一个程序，将其分解为更小的块。跑

EXPLAIN <sql statement>

您已经说过估计至少有99％的重复行。从广义上讲，有两种方法可以摆脱欺骗在数据库内部，不一定是您用于生产的同一平台。在数据库外部，在文件系统中，不一定是用于生产的相同文件系统。如果你仍然有你加载的文本文件，我会考虑先在数据库外面尝试。这个awk单行将从每个文件输出唯一的行。它相对经济，因为它只对数据进行一次传递。

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

如果你真的有99％的傻瓜，那么在这个过程结束时你应该把你的1到7太比特减少到大约50演出。并且，完成此操作后，您还可以对每个唯一行进行编号，并在将其复制到数据仓库之前创建制表符分隔文件。这是另一个单线：

awk '{printf("%dt%sn", NR, $0);}' file_without_dupes > tab_delimited_file

如果你必须在Windows下执行此操作，我将使用Cygwin。如果必须在数据库中执行此操作，我将尽量避免使用生产数据库或生产服务器。但也许我太谨慎了。移动几TB会是一件昂贵的事情。但我会考试

SELECT DISTINCT ...

在使用GROUP BY之前。我或许可以为你做一些大数据集测试，但本周可能不会。（我通常不会使用太字节大小的文件。这很有趣。如果你可以等。）

青董据零

你最后在那里省略了一些细节，但我不认为肯定存在问题。没有证据表明所有字符串实际上与所有其他字符串进行了比较。如果你进行连接，PostgreSQL可以很好地选择一个更聪明的连接算法，例如散列连接，这可能会给你在MySQL解决方案中实现的相同散列。（再一次，你的细节很朦胧。）

献导外拘

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

尝试哈希（文本）;和btree（文本）看哪一个更快

玩翁文醚碱

我看到几种解决问题的方法 PostgreSql中有md5函数 md5（string）计算字符串的MD5哈希值，以十六进制形式返回结果插入维度_表格（从文本中选择null，md5（文本），raw_fact_table组中的文本）将md5字段添加到raw_fact_table中从维度内部联接raw_fact_table中选择id到fact_table（dimension.md5 = raw_fact_table.md5）; MD5上的索引也可能有所帮助或者，您可以在加载数据时动态计算MD5。例如，我们的ETL工具Advanced ETL处理器可以为您完成。此外，它可以同时将数据加载到多个表中。我们的网站上提供了许多在线教程例如，这个演示了加载缓慢变化的维度 http://www.dbsoftlab.com/online-tutorials/advanced-etl-processor/advanced-etl-processor-working-with-slow-changing-dimension-part-2.html

要回复问题请先登录或注册

PostgreSQL：有效地将数据加载到Star Schema中

5 个回复

发起人

fact_table

star_schema

问题状态

PostgreSQL：有效地将数据加载到Star Schema中

与内容相关的链接

5 个回复

发起人

fact_table

star_schema

问题状态