怎么用SQL吧數據表遷移到數據倉庫中

本篇內容介紹了“怎么用SQL吧數據表遷移到數據倉庫中”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

 需求背景

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

需求分析

其實這個需求很簡單，就是把這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、設置定時任務執行上面的游標

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

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

“怎么用SQL吧數據表遷移到數據倉庫中”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！