需求背景

最近公司打算集中梳理幾大業(yè)務(wù)系統(tǒng)的數(shù)據(jù)，希望將各個(gè)業(yè)務(wù)系統(tǒng)中的數(shù)據(jù)集中到數(shù)據(jù)倉(cāng)庫中?？偣灿?000多張數(shù)據(jù)表，但是好在業(yè)務(wù)數(shù)據(jù)量沒有像電商那么龐大，也就幾十個(gè)G。

[[355322]]

需求分析

其實(shí)這個(gè)需求很簡(jiǎn)單，就是把這5000多張不同數(shù)據(jù)庫中的表放到一個(gè)地方。需要注意的有以下幾點(diǎn)：

1、數(shù)據(jù)來自各種不同類型的數(shù)據(jù)庫，有SQL Server，MySQL和Oracle

2、表的數(shù)據(jù)量較多，一個(gè)一個(gè)寫查詢代碼肯定不現(xiàn)實(shí)

3、后續(xù)數(shù)據(jù)倉(cāng)庫的維護(hù)

方案建議

由于數(shù)據(jù)量不是很大，我打算用DBLINK來實(shí)現(xiàn)從不同的庫中抽取數(shù)據(jù)到數(shù)據(jù)倉(cāng)庫中。

方案思路

1、創(chuàng)建不同的DBLINK

數(shù)據(jù)倉(cāng)庫我們目前使用的是SQL Server的服務(wù)器，整體性能還可以。但是業(yè)務(wù)系統(tǒng)的數(shù)據(jù)庫類型不一，在新建DBLINK時(shí)有不同的要求：

a、針對(duì)SQL Server的業(yè)務(wù)數(shù)據(jù)庫可以直接在服務(wù)器上新建

b、針對(duì)MySQL和Oracle的業(yè)務(wù)數(shù)據(jù)庫需要先使用ODBC作用中間組件來配置。

2、查詢數(shù)據(jù)庫中的所有表表名

每個(gè)業(yè)務(wù)數(shù)據(jù)庫都是全庫抽取，那么首先需要找到這些數(shù)據(jù)庫中的所有表。這里我們以SQL Server為例來查找數(shù)據(jù)庫中的所有表。

SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' 

上面的代碼就可以把當(dāng)前庫中的所有表的表名都給查詢出來，我這里在家里電腦測(cè)試了一下，給大家看下截圖：

大家也可以在自己的電腦上試一試就知道了。

Oracle獲取用戶表表名的代碼如下：

SELECT * FROM USER_TABLES; 

MySQL獲取用戶表表名的代碼如下：

select table_name 
from information_schema.tables 
where table_schema='db_name'; 

3、循環(huán)抽取數(shù)據(jù)

我們?cè)谕瓿缮厦鎯刹胶?，就可以開始循環(huán)抽取各業(yè)務(wù)系統(tǒng)的數(shù)據(jù)了。這里我們需要寫一個(gè)游標(biāo)來循環(huán)執(zhí)行。具體代碼如下：

DECLARE @TableName varchar(50),@Sql varchar(500) 
--定義兩個(gè)變量，一個(gè)用來存儲(chǔ)表名，一個(gè)用來存儲(chǔ)插入語句 
DECLARE cursor_variable CURSOR FOR 
--定義一個(gè)游標(biāo)，并且將目標(biāo)表的所有表名插入游標(biāo)中 
select name from [192.168.0.39].[test].[dbo].sysobjects where xtype='u' order by name; 
OPEN cursor_variable 
--打開游標(biāo) 
FETCH NEXT FROM cursor_variable 
INTO @TableName 
--獲取游標(biāo)中的數(shù)據(jù)插入到變量中 
WHILE @@FETCH_STATUS=0 
--循環(huán)執(zhí)行，當(dāng)游標(biāo)中的數(shù)據(jù)被讀完為止 
BEGIN  
   SET @Sql='select * into dbo.'+@TableName +' from [192.168.0.39].[test].[dbo].'+@TableName 
   Exec @Sql 
FETCH NEXT FROM cursor_variable 
INTO @TableName 
END 
CLOSE cursor_variable 
--關(guān)閉游標(biāo) 
DEALLOCATE cursor_variable; 
--釋放游標(biāo) 

目前只是測(cè)試代碼，后續(xù)在性能上還可以繼續(xù)優(yōu)化。

4、設(shè)置定時(shí)任務(wù)

代碼寫好了，肯定不可能每天手動(dòng)去執(zhí)行，這時(shí)候我們可以使用數(shù)據(jù)庫的定時(shí)任務(wù)，這個(gè)我在以前的文章中有提到過。《數(shù)據(jù)庫任務(wù)自動(dòng)化其實(shí)很簡(jiǎn)單，JOB的簡(jiǎn)單介紹》

我們把代碼放到定時(shí)任務(wù)里面，讓它每天凌晨1點(diǎn)執(zhí)行即可。

總結(jié)

這個(gè)辦法在處理數(shù)據(jù)量不多的情況下是可行的，如果數(shù)據(jù)量較大，性能上會(huì)存在較大風(fēng)險(xiǎn)。下面我們回顧一下做了哪些內(nèi)容：

1、創(chuàng)建不同數(shù)據(jù)庫的DBLINK

2、查詢到每個(gè)數(shù)據(jù)庫的所有表名

3、使用游標(biāo)循環(huán)插入到數(shù)據(jù)倉(cāng)庫

4、設(shè)置定時(shí)任務(wù)執(zhí)行上面的游標(biāo)

每個(gè)步驟都可能會(huì)存在問題，但是只要把這些問題都解決了，這件事就解決了。

覺得不錯(cuò)，記得轉(zhuǎn)發(fā)分享給更多人~