很多公司流行使用数据仓库进行数据分析,一般从线上数据源备库(mirror,logshipping,slave等)抽取到ods 层
在从ods层到dw再到dm.特别在ods层到dw时,数据的清洗装载需要一定的时间和硬件资源.
但是当硬件成为瓶颈时,怎么能快速完成清洗转载,及时的提供数据分析?
下面提供一种方法使用Ssis 加载到 ods层后,直接通过分区表把数据加载到 dw
1 准备
- /*create filegroup*/
- ALTER DATABASE [testxwj] ADD FILEGROUP [account_1]
- go
- ALTER DATABASE [testxwj] ADD FILEGROUP [account_2]
- go
- ALTER DATABASE [testxwj] ADD FILEGROUP [account_3]
- /*create file to filegroup*/
- ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_1', FILENAME = N'E:\account_1.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_1]
- GO
- ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_2', FILENAME = N'E:\account_2.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_2]
- GO
- ALTER DATABASE [testxwj] ADD FILE ( NAME = N'account_3', FILENAME = N'E:\account_3.ndf' , SIZE = 409600KB , FILEGROWTH = 20480KB ) TO FILEGROUP [account_3]
- GO16
2 使用ssis copy table
- sp_spaceused accountdetail;
- /* delete EarnTime is not null*/
- /*23 sec*/
- delete from accountdetail where EarnTime is null
- /*26 sec*/
- delete from accountdetail where isnull(CommitStatus,0)<1
- /*12 sec*/
- delete from accountdetail where isnull(EarnStatus,0) =0
对传输过来的表进行分区
- /*create partition function*/
- declare @bdate char(8),@edate varchar(8),@sql varchar(500)
- select
- @bdate=convert(char(8),GETDATE()-1 ,112)
- ,@edate=convert(char(8),GETDATE() ,112)
- select @bdate,@edate;
- set @sql='
- CREATE PARTITION FUNCTION ac_EarnTime (datetime)
- AS11 RANGE RIGHT FOR VALUES ( '''+@bdate+''' ,'''+@edate+''')'
- execute(@sql)
- /*create partition schema*/
- CREATE PARTITION SCHEME ac_schema_ac_EarnTime
- AS PARTITION ac_EarnTime TO (account_1,account_2,account_3);
- /*create partition table */
- alter table accountdetail
- alter column EarnTime datetime not null;
- alter TABLE accountdetail
- add CONSTRAINT [PK_PARTITIONmis] PRIMARY KEY
- ( id,EarnTime
- )ON ac_schema_ac_EarnTime(EarnTime)
把分区partition 2指向给 dw 值得注意的是 accountdetail_dw 必须跟partition 2 分区所在同一个文件组
- /*switch accountdetail to accountdetail_dwl*/
- ALTER TABLE accountdetail SWITCH PARTITION 2 TO accountdetail_dw ;
- /**/
整个过程在 5分钟内.数据仓库最重要的还在当初的设计和选型.
原文标题：当硬件成为瓶颈时怎么提高数据仓库的加载？
链接：http://www.cnblogs.com/xwj1985/archive/2010/08/19/1803272.html
【编辑推荐】
