數據增量抽取的模擬實現──腳本實現:
實現的環境：
業務數據庫：Oracle數據庫9i
數據倉庫數據庫：SQL Server 2000數據庫
1、前提條件：SQL Server服務器必須已經安裝Oracle驅動
2、創建鏈接數據庫
打開企業管理器->安全性-鏈接服務器-右鍵新建
通常情況當鏈接數據庫創建好，進行打開的時候都會彈出一下錯誤窗口：
一般情況下運行C:PRogram FilesCommon FilesSystemOle DBmtxoci81_win2k.reg該文件後，重啓SQLServer數據庫，再重新連接；
如果仍有問題，重啓操作系統，即可OK。
3、創建Oracle環境腳本
--創建Oracle業務系統表結構
CREATE TABLE SourceTable
(
ID1 VARCHAR2(50),
ID2 VARCHAR2(50),
Measure1 INTEGER,
Measure2 INTEGER,
CloseDate DATE
)
--創建測試數據
DECLARE
-- Local variables here
i INTEGER;
BEGIN
-- Test statements here
FOR i IN 1..365 LOOP
INSERT INTO SourceTable
VALUES(i,i,i,i,TO_DATE('2006-01-01','yyyy-mm-dd')+i);
INSERT INTO SourceTable
VALUES(i,i,i,i,TO_DATE('2006-01-01 12:00:00','yyyy-mm-dd hh24:mi:ss')+i);
END LOOP
COMMIT;
END;
4、創建SQLServer數據倉庫環境腳本
--創建系統參數表內
CREATE TABLE ExtractTaskList (
TaskName VARCHAR(32) ,
TargetTable VARCHAR(32) ,
TargetFieldList VARCHAR(500) ,
SourceTable VARCHAR(32) ,
SourceFieldList VARCHAR(500) ,
WhereFieldName VARCHAR(32) ,
IncType INT ,
TransType INT ,
TargetDate DATETIME ,
SourceDate DATETIME ,
Flag INT ,
Note VARCHAR (500)
)
GO
--創建數據倉庫目標表
CREATE TABLE TargetTable (
ID1 VARCHAR(50) ,
ID2 VARCHAR(50) ,
Measure1 DECIMAL(18, 0) ,
Measure2 DECIMAL(18, 0) ,
CloseDate DATETIME
)
GO
5、創建SQLServer數據倉庫ETL腳本
腳本考慮到現實的問題，已經做了許多取舍，不再追求全部動態實現，旨在給定一個模板，在有限的範圍內可以更改每次抽取的周期，每次時間的跨度，抽取的字段，表等等；數據字典表僅僅利用了其中的四個字段：任務名稱，當前抽取時間、結束時間、抽取狀態。 CREATE PROCEDURE p_org_Extract
AS
DECLARE @sql VARCHAR(3000)
BEGIN
DECLARE @BeginDate DATETIME,
@EndDate DATETIME,
@TaskName VARCHAR(32),
@Flag INTEGER,
@Num INTEGER,
@CurrDate DATETIME
SELECT @Num = COUNT(TaskName) FROM ExtractTaskList
WHERE UPPER(TaskName) = UPPER('test')
IF @Num != 1
INSERT INTO ExtractTaskList(TaskName,IncType,TransType) VALUES('test',2,2)
--獲取列表中的當前任務的時間戳和狀態
SELECT @BeginDate = SourceDate,@Flag = Flag FROM ExtractTaskList WHERE TaskName='TEST'
--如果上次執行未成功，這樣取值效率會高一些，則從數據倉庫表中直接讀取
--TargetDate和SourceDate可能會不一致
IF @Flag = 2 OR @Flag IS NULL
SELECT @BeginDate = DATEADD(ss,1,MAX(closedate)) FROM TargetTable
--如果數據倉庫無數據，則從業務系統中直接讀取，也可以設置一個默認的初始化時間
IF @BeginDate IS NULL
SELECT @BeginDate = MinLogDate FROM OPENQUERY(SOURCE,'SELECT MIN(CloseDate) AS MinLogDate FROM SourceTable')
--如果仍無數據，則表示無數據可抽取，退出執行
IF @BeginDate IS NULL
RETURN
--抽取結束時間爲當前時間前一天,每次循環抽取1天數據,可以更改dd爲hh，變成按小時抽取
--通常業務系統是連續的，如果有疑問也可以從業務系統中獲取最大時間
SELECT @EndDate = CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,GETDATE(),120),10)+' 00:00:00')
--更新當前開始時間和結束時間
UPDATE ExtractTaskList
SET TargetDate = @BeginDate,
SourceDate = @EndDate
WHERE UPPER(TaskName) = UPPER('test')
WHILE @BeginDate < @EndDate
BEGIN
SELECT @sql = ' INSERT INTO TargetTable
(
ID1,
ID2,
Measure1,
Measure2,
CloseDate
)SELECT * FROM OPENQUERY(SOURCE,''select
ID1,
ID2,
Measure1,
Measure2,
CloseDate
FROM SourceTable
WHERE CloseDate >= TO_DATE(''''' + CONVERT(varchar,@BeginDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'
+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,DATEADD(day,1,@BeginDate),120) + ''''', ''''yyyy-mm-dd HH24:MI:SS'
+ ''''') AND CloseDate < TO_DATE(''''' + CONVERT(varchar,@EndDate,120) + ''''', ''''YYYY-MM-DD HH24:MI:SS'
+ ''''')'')'
--PRINT @sql
EXEC (@sql)
--獲取本次任務運行抽取的最大時間
IF DATEADD(day,1,@BeginDate)>@EndDate
SELECT @CurrDate = @EndDate
ELSE
SELECT @CurrDate = DATEADD(day,1,@BeginDate)
--如果@sql執行失敗，同樣記錄狀態和時間
IF @@ERROR <> 0
GOTO FAIL
--記錄每次運行的時間運行情況，可提供相應參考
UPDATE ExtractTaskList
SET TargetDate = @CurrDate,
Flag = 1
WHERE UPPER(TaskName) = UPPER('test')
SELECT @BeginDate = DATEADD(DD,1,@BeginDate)
END
RETURN
FAIL:
--記錄錯誤
UPDATE ExtractTaskList
SET TargetDate = @CurrDate,
Flag = 2
WHERE UPPER(TaskName) = UPPER('test')
RETURN 0
END