寻求亿级wap数据解决方案

Question

该公司wap数据情况：
1.服务器上的数据每天定时更新，更新的时候把昨天的删除；
2.数据存储在一个表中，每天更新的数据巨大，有五千万条以上记录
3.数据字段主要有：日期(某一天） 手机号 上线时间 下线时间 访问的url，请求状态现在我用的方案：
用PDI(pentaho)工具来实现 第一步：从服务器上采集数据放在原始表(A)中，该表按一个月中的天数分区的，建立了分区局部索引，表结构与服务器表基本一致，但加入了时间戳字段，
该过程采集数据五千万条记录在两小时以内，基本可以接受。该步骤与服务器更新数据同步。
这一步采用的是PDI(kettle)的Oracle Bulk Loader实现 第二步：该步要实现对表A的操作，操作后的记录插入的预处理表B（分区表，分区与A表一致）中，包含下面几个步骤。
1）过滤： 过滤掉不符合要求的记录，如url为空的，请求状态没成功的
2）计算： 计算上线时间与下线时间差
3）转换：维度的转换，根据上线时间获得它是属于哪个时刻上线的
4）拆分：要将URL差分成一级，二级，三级域名和一级文件夹，二级文件夹，三级文件夹。某url  http://info3.3g.qq.com/g/s?sid=7B3bGletbE...
 一级域名 qq.com 二级：3g.qq.com 三级：info3.3g.qq.com
一级文件夹： http://info3.3g.qq.com/ 二级： http://info3.3g.qq.com/g/ 三级 http://info3.3g.qq.com/g/s
5）除了要将拆分的url插入到B中，还有根据拆分的url，从网址库维度表中（有6个对应的网址库)获取相应的维度ID插入B中 这一步我用存储过程实现，然后再在PDI中调用存储过程，存储过程中采用下面语句插入B中
insert into B
select ... 现在这步没法实现，别说插入几千万条，就是插入几百万条oracle就会死掉 第三步：该步实现就是对B表做一些相应因子的简单分析和统计，第二步不实现该步没法进行刚接触数据仓库，望在这方面有经验的能指点迷津，应该怎么实现或采用别的方案，不胜感激...

Answer 1

第二步，我可以提点建议，为什么insert into B select ... 500条oracle就会死掉呢，会不会是这个时候产生了大量归档日志或者脏块导致空间满了，然后oracle就瘫痪了呢，正常情况下500条记录insert就算业务量非常繁忙，也不会影响整个数据库的性能的。如果你insert into B select ...放在晚上业务量少的情况下执行，oracle数据库还会瘫痪吗？

Answer 2

、insert into B select ...已经是批量插入、批量提交了吧？

Answer 3

insert /*+append*/ into 
定时提交。
我的本本，2g内存，插入200w条记录，40分钟。
服务器咋着也比本本配置好吧。

Answer 4

不要指望一次把这几千万数据处理完
可以每次处理一万这样，处理完及时commit，再处理下一部分。
程序写的好，可以多个同时处理，如果你的服务器有N个CPU，还可以提高点速度。

Answer 5

谢谢你的回复
 1.数据有5千万以上
 2.该表不产生归档日志，我已经将它设成了nologging

Answer 6

1.insert into b
 select ... 这个select语句本身比较复杂，里面已经有好几个子层，
2. 几百万条没关系，我白500万条的话好像也不要多久，但几千万就死了

Answer 7

看起来楼主是做的一个etl过程，
我觉得问题在第5步:
"5）除了要将拆分的url插入到B中，还有根据拆分的url，从网址库维度表中（有6个对应的网址库)获取相应的维度ID插入B中
这一步我用存储过程实现"楼主这一步应该没有落地处理，何不用些中间表或是临时的平面文件做过渡，再做插入
这样一长步可以拆成两短步，性能应该会有提高

Answer 8

我觉得第五步有问题...
1.采集的数据先放到一个中间表做间接处理,保证先采集到数据;
2.在业务处理比较空闲时,再做拆分的url插入到B中;
3.你的6个维度表应该分类好,每做一次拆分就插入一类维度表,不能一次性插入6个不同的维度表

Answer 9

谢谢lpc19598188回复，我有做中间表处理，为一次表，每次数据分析时truncate后再插入数据。
为w1, w2, w3, w4, w5, w6
使用PDI将原始数据表A(存服务器采集的数据)并行处理，同一时间往w1..w6中插入
数据，w1-w6表中存储的是A表中分组后的url和对应拆分的url.
拆分网址采用的是函数regexp_substr,
如 regexp_substr(regexp_substr(t.url,'http://([[:alnum:]]+\.?)+(:[[:digit:]]{1,5})?/?'), '([[:alnum:]]+)\.([[:alnum:]]+)\.(com|org|net|edu|gov|mil|arts|firm|info|nom|rec|store|web|cc|cn|au|jp|us)(\.cn|jp|us)?(:[[:digit:]]{1,5})?') as url2因网址(该网址为用户访问的某个具体网址，如http://kong.net/kona/wap/cmsimg/img.jsp?id=15967673)
千千万，所有6个表中的记录都有千万级，将具体网址作为主键。假如往B(预处理表)中插入数据则应该是
insert into B
select ... from A(近亿级), W1(千万级),W2(千万级),W3(千万级),...
where ...这样也肯定很慢。即使A表中不按具体url分组，而是按到某个层次如http://kong.net/kona/wap/分w1-w6中的记录也会不少，而这样可能会导致插入B表中数据不准确

Answer 10

谢谢hdhai9451，“总技术分：63042 ”，两颗星，羡慕...
"6个维度表应该分类好,每做一次拆分就插入一类维度表,不能一次性插入6个不同的维度表"
这样应该怎么实现实现呢？
插入了一类，另一类更新吗？那应该怎么更新，update可不行。

Answer 11

我一直认为应该有种更好的方案来解决这个后台数据库的设计，可是肚里实在没墨，很郁闷...
通讯公司应该都有这么大的数据量，ftp方式从交换机等处每个时间段获取相当数据后利用proc等进行数据检查后生成文件在利用sqlldr等方式并行插入数据库原始表中，然后对原始
表中的数据进行ETL等操作，不知人家是怎么实现的。

Answer 12

感觉好象没有特别复杂的业务逻辑，
处理的时候不要想一口吞掉一头大象，尽可能把一天的数据分批去处理(比如按时段分)，处理数据的procedure最好直接放在所在的机器上去运行，不要放在你那个PDI工具里去调用。

Answer 13

业务逻辑不复杂，就是拿到数据后利用算法分析数据。工具应要求还得用PDI.现在基本解决了,还是借助了中间表，采用完全刷新的方式。
就是将第二步中的4)每步拆分都单独出来放入相应的中间表中，这几个拆分可以在PDI中同时进行。
谢谢lpc19598188和hdhai9451