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

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

實現的環境:

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

 
實例講解 實現互聯網數據庫的安全-服務器
設置Proxy Server和SQL Server實現互聯網上的數據庫安全: 首先,我們需要了解一下SQL Server在WinSock上定義協議的步驟: 1. 在”啓動”菜單上,指向”程序/Microsoft Proxy Server”,然後點擊”Microsoft...查看完整版>>實例講解 實現互聯網數據庫的安全-服務器
 
實例講解如何實現互聯網上數據庫的安全
設置Proxy Server和SQL Server實現互聯網上的數據庫安全: ◆首先,我們需要了解一下SQL Server在WinSock上定義協議的步驟: 1. 在”啓動”菜單上,指向”程序/Microsoft Proxy Server”,然後點擊”Microsoft Manag...查看完整版>>實例講解如何實現互聯網上數據庫的安全
 
實例講解Access數據庫在線壓縮的實現方法
如果在 Access 數據庫中刪除數據或對象,或者在 Access 項目中刪除對象,Access 數據庫或 Access 項目可能會産生碎片並會降低磁盤空間的使用效率。壓縮 Access 數據庫或Access項目實際上是複制該文件,並重新組織文件...查看完整版>>實例講解Access數據庫在線壓縮的實現方法
 
用J2ME在移動設備上實現動畫的實例講解
使用MIDP(Mobile Information Device Profile)的開發人員經常會抱怨用些什麽辦法才可以在一個MIDlet上顯示動畫。MIDP 1.0 沒有直接提供對動畫的支持(正在開發中的MIDP 2.0支持),但真要是自己去實現,其實也並非是一...查看完整版>>用J2ME在移動設備上實現動畫的實例講解
 
實例講解ASP實現抓取網上房産信息
     <%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%><!-- #include file="conn.asp" --><!-- #include file="inc/function.asp" --><!DOCTYPE HTML PU...查看完整版>>實例講解ASP實現抓取網上房産信息