怎么用SQL吧數(shù)據(jù)表遷移到數(shù)據(jù)倉(cāng)庫(kù)中

本篇內(nèi)容介紹了“怎么用SQL吧數(shù)據(jù)表遷移到數(shù)據(jù)倉(cāng)庫(kù)中”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

 需求背景

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

需求分析

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

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

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

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

方案建議

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

方案思路

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

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

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

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

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

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

SELECT NAME FROM SYSOBJECTS WHERE TYPE='U'

上面的代碼就可以把當(dāng)前庫(kù)中的所有表的表名都給查詢出來(lái)，我這里在家里電腦測(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è)谕瓿缮厦鎯刹胶?，就可以開(kāi)始循環(huán)抽取各業(yè)務(wù)系統(tǒng)的數(shù)據(jù)了。這里我們需要寫一個(gè)游標(biāo)來(lái)循環(huán)執(zhí)行。具體代碼如下：

DECLARE @TableName varchar(50),@Sql varchar(500) --定義兩個(gè)變量，一個(gè)用來(lái)存儲(chǔ)表名，一個(gè)用來(lái)存儲(chǔ)插入語(yǔ)句 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 --打開(kāi)游標(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ù)庫(kù)的定時(shí)任務(wù)，這個(gè)我在以前的文章中有提到過(guò)?！稊?shù)據(jù)庫(kù)任務(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ù)庫(kù)的DBLINK

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

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

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

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

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

“怎么用SQL吧數(shù)據(jù)表遷移到數(shù)據(jù)倉(cāng)庫(kù)中”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注億速云網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！