| 導購 | 订阅 | 在线投稿
分享
 
 
 

用一個實例講解數據增量抽取的模擬實現

來源:互聯網  2008-06-12 07:24:07  評論

數據增量抽取的模擬實現──腳本實現:

實現的環境:

業務數據庫: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

數據增量抽取的模擬實現──腳本實現: 實現的環境: 業務數據庫:Oracle數據庫9i 數據倉庫數據庫:SQL Server 2000數據庫 1、前提條件:SQL Server服務器必須已經安裝Oracle驅動 2、創建鏈接數據庫 打開企業管理器->安全性-鏈接服務器-右鍵新建 [url=/bbs/detail_1790410.html][img]http://image.wangchao.net.cn/it/1323411716569.jpg[/img][/url] 通常情況當鏈接數據庫創建好,進行打開的時候都會彈出一下錯誤窗口: [url=/bbs/detail_1790410.html][img]http://image.wangchao.net.cn/it/1323411732293.jpg[/img][/url] 一般情況下運行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
󰈣󰈤
王朝萬家燈火計劃
期待原創作者加盟
 
 
 
>>返回首頁<<
 
 
 
 
 熱帖排行
 
 
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有