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

3） AzCopy 工具的介绍：

参考： azcopy | Microsoft Learn

4） SQLPACKAGE工具的介绍：

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