需求背景

最近公司打算集中梳理幾大業務系統的數據，希望將各個業務系統中的數據集中到數據倉庫中?？偣灿?000多張數據表，但是好在業務數據量沒有像電商那么龐大，也就幾十個G。

[[355322]]

需求分析

其實這個需求很簡單，就是把這5000多張不同數據庫中的表放到一個地方。需要注意的有以下幾點：

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

2、表的數據量較多，一個一個寫查詢代碼肯定不現實

3、后續數據倉庫的維護

方案建議

由于數據量不是很大，我打算用DBLINK來實現從不同的庫中抽取數據到數據倉庫中。

方案思路

1、創建不同的DBLINK

數據倉庫我們目前使用的是SQL Server的服務器，整體性能還可以。但是業務系統的數據庫類型不一，在新建DBLINK時有不同的要求：

a、針對SQL Server的業務數據庫可以直接在服務器上新建

b、針對MySQL和Oracle的業務數據庫需要先使用ODBC作用中間組件來配置。

2、查詢數據庫中的所有表表名

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

SELECT NAME FROM SYSOBJECTS WHERE TYPE='U' 

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

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

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

SELECT * FROM USER_TABLES; 

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

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

3、循環抽取數據

我們在完成上面兩步后，就可以開始循環抽取各業務系統的數據了。這里我們需要寫一個游標來循環執行。具體代碼如下：

DECLARE @TableName varchar(50),@Sql varchar(500) 
--定義兩個變量，一個用來存儲表名，一個用來存儲插入語句 
DECLARE cursor_variable CURSOR FOR 
--定義一個游標，并且將目標表的所有表名插入游標中 
select name from [192.168.0.39].[test].[dbo].sysobjects where xtype='u' order by name; 
OPEN cursor_variable 
--打開游標 
FETCH NEXT FROM cursor_variable 
INTO @TableName 
--獲取游標中的數據插入到變量中 
WHILE @@FETCH_STATUS=0 
--循環執行，當游標中的數據被讀完為止 
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 
--關閉游標 
DEALLOCATE cursor_variable; 
--釋放游標 

目前只是測試代碼，后續在性能上還可以繼續優化。

4、設置定時任務

代碼寫好了，肯定不可能每天手動去執行，這時候我們可以使用數據庫的定時任務，這個我在以前的文章中有提到過。《數據庫任務自動化其實很簡單，JOB的簡單介紹》

我們把代碼放到定時任務里面，讓它每天凌晨1點執行即可。

總結

這個辦法在處理數據量不多的情況下是可行的，如果數據量較大，性能上會存在較大風險。下面我們回顧一下做了哪些內容：

1、創建不同數據庫的DBLINK

2、查詢到每個數據庫的所有表名

3、使用游標循環插入到數據倉庫

4、設置定時任務執行上面的游標

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

覺得不錯，記得轉發分享給更多人~