当前位置:

首页 > 数据库 > T-SQL >

使用BCP + Polybase 实现本地数据迁移到Azure DB

使用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”。

出处：https://www.cnblogs.com/huwz/p/bcpmigratedata.html

栏目列表