使用BCP + Polybase 實作本地資料遷移到Azure DB

一、背景

最近因為要做一些實驗的緣故，需要在Azure DB上準備一些帶資料的資料庫， AdventureWorks2019 和AdventureWorksDW2019就挺合適的，官網上能提供這兩個資料庫的備份檔案，在我將其成功還原到了本地SQL 實體中，但是怎么把資料遷移到Azure DB上有點犯難了，

雖然辦法有很多，比如可用采用資料庫遷移工具，但我的目標是能盡量的自動化，因此更希望以腳本的方式來實作，在這個目標前提下，成功實作了資料的上云遷移，我的這個方式不一定是最好的，但效率上還是挺不錯的，而且在資料遷移的程序中也趟了一些坑，因此在此記錄程序中踩過的一些問題，有需要的可以借鑒，

二、程序

以下為具體實施程序的記錄，

Step 1: BCP 匯出資料為檔案，

-- ===============

-- bcp 批量匯出資料

-- ===============

select concat('bcp AdventureWorksDW2019.'

, a.name,'.' , b.name

, ' out ', a.name, '.', b.name, '.txt -c -t"|" -T -S. -C65001') as c

from sys.schemas a

inner join sys.objects b on a.schema_id = b.schema_id

where (b.name like 'dim%' or b.name like 'fact%') and b.type='u'

order by a.name, b.name

-- 示例

bcp AdventureWorksDW2019.dbo.DimAccount out dbo.DimAccount.txt -c -t"|" -T -S. -C65001

1) 上面SQL的結果是形成多個BCP的命令，將AdventureWorksDW2019的DIM表和FACT表的資料匯出到獨立txt檔案的命令，

2) 沒有檔案頭行，也就是沒有表頭；如果需要表頭，只能用Query的方式Union All一行出來，不過在我的實驗中，可不用要求匯出表頭; 而是直接使用raw表的schema，題外一句，這么用，可能不太適合使用ADF的場景，

3) 沒有Quated；因此如果使用逗號,分隔，一定要注意現有的內容是否也會包含逗號；如果包含了，則需要改成其他分隔符，在此我使用的是豎線”|”作為分隔符；

4) Code page = 65001 （UTF8 Formated），大部分情況都不需要指定Code Page, 但是我在匯出DimProduct表的是時候出現了亂碼問題，指定Code Page = 65001解決，

5) 型別問題，比如binary， image， geometry ，這些事實上都不會造成困擾，

Step 2: 上傳資料檔案到Azure Blob

1) 可用使用azcopy 工具將檔案上傳，可用查閱官網示例 azcopy | Microsoft Learn

2) 也可以手動，本例中我是手動在Portal上上傳到路徑src_data/adventureworks2019dw/bcp 下的

Step3: 資料庫上新建 External Data Source

1) 參考檔案 CREATE EXTERNAL DATA SOURCE (Transact-SQL) - SQL Server | Microsoft Learn

2) 已手動建立【azblobadvtwksblob】

Step 4: 在資料庫上建立raw 表

1) 建raw表目的是為了能讓資料更好的從blob中通過polybase技術復制到SQL DB中來，

2) 創建raw schema

CREATE SCHEMA raw

3) 1:1 創建raw表

select concat('SELECT top 0 * INTO raw.', a.name,'_', b.name

, ' FROM ', a.name , '.', b.name

, '; TRUNCATE table raw.', a.name,'_', b.name )

from sys.schemas a

inner join sys.objects b on a.schema_id = b.schema_id

where (b.name like 'dim%' or b.name like 'fact%')

and b.type='u' and a.name='dbo'

order by a.name, b.name

-- 示例

SELECT top 0 * INTO raw.dbo_DimAccount FROM dbo.DimAccount; TRUNCATE table raw.dbo_DimAccount

SELECT top 0 * INTO raw.dbo_DimCurrency FROM dbo.DimCurrency; TRUNCATE table raw.dbo_DimCurrency

NOTES: 這樣做，可用減少手動一個個建表的作業量，確保raw表是空表，無資料，

為什么要建raw層的表？目的就是為了減少因為DB本身的約束導致的資料匯入失敗，比如外鍵、視圖系結、型別問題等，因為raw表沒有外鍵、Identity、參考等約束條件，所以復制資料的時候不容易產生問題，資料都進入DB了，再查詢起來就比較方便，也就更容易找到問題所在，資料在Blob上，理論上也有工具可以查詢，但是從效率上還是SQL更高，

在本例中，由于是同構的資料庫，所以不會出現型別兼容的問題，但是如果有必要，可用將raw表的所有的欄位型別都改為nvarchar(N)的，N取決于業務欄位具體的長度，這樣在上數的程序中更不容易出現型別轉換不兼容導致的問題，具體的型別轉換，將在” 從raw表查詢資料插入到目標表”步驟中隱式實作也可以，

Step 5: 將資料匯入到raw表

1）使用PolyBase技術，將外部資料源的資料接入到raw表中

-- ===============

-- 從外部檔案加載資料

-- ===============

declare @root nvarchar(400) = 'src-data/adventureworks2019dw/bcp/'

SELECT CONCAT('TRUNCATE TABLE raw.', a.name,'_', b.name ,';BULK INSERT raw.', a.name,'_', b.name, ' FROM ''' , @root, a.name,'.', b.name,'.txt'' WITH (DATA_SOURCE = ''azblobadvtwksblob'', FIRSTROW =1,FIELDTERMINATOR = ''|'' );')

from sys.schemas a

inner join sys.objects b on a.schema_id = b.schema_id

where (b.name like 'dim%' or b.name like 'fact%') and b.type='u' and a.name='dbo'

-- 示例

TRUNCATE TABLE raw.dbo_DimAccount;

BULK INSERT raw.dbo_DimAccount

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimAccount.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

TRUNCATE TABLE raw.dbo_DimCurrency;

BULK INSERT raw.dbo_DimCurrency

FROM 'src-data/adventureworks2019dw/bcp/dbo.DimCurrency.txt'

WITH (DATA_SOURCE = 'azblobadvtwksblob', FIRSTROW =1,FIELDTERMINATOR = '|' );

3) 如前所述，如有必要加上CODEPAGE=‘65001’ （UTF8 Format）

參考 BULK INSERT (Transact-SQL) - SQL Server | Microsoft Learn

Step 6: 去除資料目標表的限制

1）備份資料庫的DACPAC（即備份表結構的DDL）

2）洗掉外鍵約束

SELECT concat('ALTER TABLE ',

SCHEMA_NAME(a.schema_id), '.', object_name(a.parent_object_id)

, ' drop constraint ' , a.name ,';')

FROM SYS.foreign_keys A

WHERE TYPE='F'

and (object_name(a.parent_object_id) like 'dim%' or object_name(a.parent_object_id) like 'fact%') and schema_name(a.schema_id)='dbo'

order by SCHEMA_NAME(a.schema_id), object_name(a.parent_object_id)

3）洗掉視圖，因為視圖有的會和表做schema binding

select concat('drop view ', schema_name(a.schema_id) , '.', a.name )

from sys.views a

where (A.name like 'dim%' or A.name like 'fact%') and schema_name(a.schema_id)='dbo'

Step 7: 從raw表查詢資料插入到目標表

1）如有必要，清空目標表

2）判斷identity 列；如果表有Identity列，則需要Set IDENTITY_INSERT ON，并使用明確列選擇；資料入表完成后，再Set IDENTITY_INSERT OFF

-- ===============

-- 從raw匯入資料

-- ===============

; with c1 as

(

SELECT

isIdentity = sum(case when b.is_identity = 1 then 1 else 0 end) ,

schemaName = SCHEMA_NAME(a.schema_id),

tableName = a.name,

columnNames = STRING_AGG( '['+ b.name + ']', ',')

FROM SYS.objects a

left join sys.columns b on a.object_id = b.object_id

WHERE a.type='u'

and (A.name like 'dim%' or A.name like 'fact%') and schema_name(a.schema_id)='dbo'

and b.is_computed = 0

group by a.schema_id, a.name

)

SELECT

pk_identity_prefix = case when (isIdentity >0 ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' On;') else '' end

load_script=concat('TRUNCATE TABLE ', schemaName, '.',tableName

, '; INSERT INTO ' ,schemaName, '.',tableName , '(',columnNames,')'

, ' SELECT ', columnNames

,' FROM raw.',schemaName, '_',tableName,' ;')

pk_identity_prefix = case when (isIdentity >0 ) then concat( ' SET IDENTITY_INSERT ' , schemaName, '.',tableName, ' OFF;') else '' end

FROM c1

order by schemaName, tableName

--打開IDENTITY_INSERT

SET IDENTITY_INSERT dbo.DimAccount On;

--清空

TRUNCATE TABLE dbo.DimAccount;

--插入

INSERT INTO dbo.DimAccount([AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions])

SELECT [AccountKey],[ParentAccountKey],[AccountCodeAlternateKey],[ParentAccountCodeAlternateKey],[AccountDescription],[AccountType],[Operator],[CustomMembers],[ValueType],[CustomMemberOptions] FROM raw.dbo_DimAccount ;

--還原

SET IDENTITY_INSERT dbo.DimAccount OFF;

Step 8: 還原外鍵、視圖等

1) 使用SQLPACKAGE工具，利用資料庫的DACPAC，對資料庫進行還原，

2) 或者使用Visual Studio的Database Project 對資料庫進行還原，本例就是用的這個方式，

附

1） BCP 工具的地址：

通常使用Visual Studio 的CMD可用自動找到，如果沒有，可用試試類似路徑：

C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\170\Tools\Binn\bcp.exe

2） BCP的命令幫助

usage: bcp {dbtable | query} {in | out | queryout | format} datafile

[-m maxerrors] [-f formatfile] [-e errfile]

[-F firstrow] [-L lastrow] [-b batchsize]

[-n native type] [-c character type] [-w wide character type]

[-N keep non-text native] [-V file format version] [-q quoted identifier]

[-C code page specifier] [-t field terminator] [-r row terminator]

[-i inputfile] [-o outfile] [-a packetsize]

[-S server name] [-U username] [-P password]

[-T trusted connection] [-v version] [-R regional enable]

[-k keep null values] [-E keep identity values][-G Azure Active Directory Authentication]

[-h "load hints"] [-x generate xml format file]

[-d database name] [-K application intent] [-l login timeout]

強調一點的是，如果是直接匯出表的資料，用指令”out”; 如果是匯出查詢的資料，用指令”queryout”，

3） AzCopy 工具的介紹：

參考： azcopy | Microsoft Learn

4） SQLPACKAGE工具的介紹：

參考：SqlPackage.exe - SQL Server | Microsoft Learn

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/516465.html

標籤：SQL Server

上一篇：資料庫選型思考(PostgreSQL,MySQL,Oracle)

下一篇：Redis 知識總結