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

概述想象一下PostgreSQL 9.0上具有以下结构的表： create table raw_fact_table (text varchar(1000)); 为了简化起见,我只提到一个文本列,实际上它有十几个.该表有100亿行,每列有很多重复.该表是使用COPY FROM从平面文件(csv)创建的. 为了提高性能,我想转换为以下星型模式结构： create table dimension_tabl 想象一下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"

只是问题：
– 是否需要以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

总结

以上是内存溢出为你收集整理的PostgreSQL：有效地将数据加载到Star Schema中全部内容，希望文章能够帮你解决PostgreSQL：有效地将数据加载到Star Schema中所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。