数据库迁移：批量 JSON文件 导入 SQL Server

一、问题引入

在之前的博客中利用阿里开源软件（DataX）实现了数据库数据的迁移：SQL Server --> MongoDB ，并且成功将 2千万条 记录迁移至 MongoDB。当时顺便还记录了迁移速度，迁移方式：分批次迁移（1百万条/次）, DataX统计显示迁移速度： 7000+条/s。执行 10次 迁移任务，耗费时间约 48分钟 。

通过上一次的迁移经验，这次反过来迁移数据库数据：MongoDB --> SQL Server ，时间和效率理论上是可控的。 但是，问题来了。在执行一批次迁移 9千万+条 记录时，DataX统计显示迁移速度：200+条/s。
按照这样的迁移速度，需要耗费时间约：5.2天。迁移持续时间过长，一旦网络中断，一切归零，从来再来，只能另寻他法。

DataX统计记录中，观察到缺陷所在：MonggoDB 读数据时间很长，但SQL Server 写数据时间很短，问题出在MongoDB上。

经过一番查询，SQL Server支持 json 格式导入，而MongoDB 本身就是以 类json 格式存储的。

所以解决方法：MongoDB --> json文件 --> SQL Server

二、解决过程

按照解决方法分两步走:

第一步：MongoDB to json file

第二步：json file to SQL Server

2-1 MongoDB to json file

MongoDB官网上有工具（mongoexport.exe）可以将数据导出json格式文件。(耗费时间约20min)

mongoexport.exe的使用方法请参考官网mongoexport介绍，此处不做说明。

2-2 json file to SQL Server

💡 注意事项：

1、9千万+条数据导出json文件，文件会非常大
2、非常大的josn文件导入SQL Server会导致系统内存占用过大，而中止导入任务
3、MongoDB导出数据编码是utf-8，但SQL Server不支持
4、SQL Server支持导入数据编码格式是utf-16

经过一次小demo测试，单个json文件大小控制在 2百万条 记录，可以成功导入SQL Server。

json文件demo:

[{"_id":{"$oid":"63e9a2862650cff4d9214547"},"a":11,"b":"","c":"","d":"","e":2,"f":"","g":60},
{"_id":{"$oid":"63e9a2862650cff4d9214547"},"a":11,"b":"","c":"","d":"","e":4,"f":"","g":70}]

1️⃣ 将极大的json文件按照 单文件2百万条记录 进行分割，分割成 50个 utf-8的json文件 (耗费时间约：20min)

2️⃣ 将50个 utf-8的json文件 转码成 50个 utf-16的json文件（耗费时间约：20min）

3️⃣ 将50个 utf-16的json文件导入 SQL Server，T-SQL脚本如下（耗费时间约60min）

DECLARE @JSON NVARCHAR(max)
DECLARE @i INT = 0
DECLARE @file AS NVARCHAR(MAX)
DECLARE @stm nvarchar(max)
WHILE(@i<50)
BEGIN
    SET @file = 'G:\json\utf16\minis_10000w_ansi_' + CONVERT(varchar(2), @i) + '.json';
	PRINT @file
    SET @stm = N'
        SELECT @JSON = BulkColumn 
        FROM OPENROWSET (BULK ''' + @file + ''', SINGLE_NCLOB) AS j
    '
    EXEC sp_executesql @stm, N'@json AS VARCHAR(MAX) OUTPUT', @json OUTPUT
    INSERT INTO mini_data(a,b,c,d,e,f,g)
    SELECT *  
    FROM OPENJSON (@JSON)
    WITH 
    (
        a int, 
        b varchar(50),
        c nvarchar(50),
        d nvarchar(50), 
        e int, 
        f datetime2(2), 
        g int, 
    )
    SET @i = @i+1;
END 

三、反思总结

两种数据迁移方法对比：

方法A	方法B
耗时长（约5.2day），但步骤少	耗时短（约2hour），但步骤多

四、参考引用