使用扩展的JSON将SQL Server数据迁移到MongoDB

为什么要迁移数据库?

在评估数据库系统的价值的时候,一个重要的目标就是能将数据存储到已有的数据库,也能将从已存在的数据库中的数据取出来。这篇文章就是从SQL Server数据库中获取数据迁移到MongoDB中,反之亦然。

每当在数据库策略做出修改,特别是在大型组织系统中引入数据库系统的时候,有个问题会浮现在我们脑海中:“怎样把数据库比较简单地迁移到新的平台,如果迁移到新平台有问题,我们怎样比较简单的迁移回去?”于此同时,很多其它的问题也浮现到我们面前,这总是让我们感觉到一种潜在的焦虑,即任何新的专有的系统都可能是一种难以驾驭的系统。数据是数据库中珍贵的财产,稍有不慎就会导致数据丢失。程序通常不易被移植,因为数据库程序处理代码,通常很少被标准化,但是数据库中的数据感觉更容易迁移。

基于数据之间的转换来判断一个数据库系统,是否感觉有点不公平,特别是基于文档型数据库和关系型数据库,本质上就存在不同。当你发现数据转换成为一种可能时,突然感觉有几分欣慰。

从SQL Server导入数据到MongoDB

从SQL Server数据库导入数据到MongoDB用很多中方法,比如通过SQLClinet和SMO客户端,基于mongodb.net库,通过代码的方式进行导入;比如使用MongoDB工具,如Studio 3T,用Node.js扩展插件来实现;比如用商业的ODBC驱动,用基于文档的方式进行迁移等。

还有很多其他的方式还没有尝试过,本文,我们将重点讨论基于文件的传输转换方式,我认为这是最快的方法。

基于文档的数据转换

MongoDB是基于字节拷贝的输入和输出的系统。因此我们认为,使用JSON格式的文档数据导入到MongoDB集合中,也可以用同样的方式进行输出。这从一定程度上是正确的,但是也存在一些障碍。比如我们首先确保数据类型是正确的。

数据类型问题

用RDBMS表中转换成JSON数据比较轻松,但是从BSON集合数据转换为关系型数据库中的数据就不是那么简单了,为什么呢?

关系型数据库中的数据表包括控制数据类型的所有规则,它为每列定义了数据类型,字段有时还定义了是否允许空值,值是否为唯一或是否符合表中数据规则的约束等。比如在在关系型数据库中插入一条数据,会检查该数据的标识规则或者地理编码。MongoDB有两种方法来确保JSON导入数据的完整性,一种是采用JSON Schema系统进行数据检查;另一种是采用扩展JSON数据类型来验证BSON数据导入导出的标准。JSON Schema方法非常适用于你对集合的数据类型及键值很了解,集合的组织风格越接近一张表的格式,这种方法越有价值。JSON模式只适用于组织了足够多的集合,使您能够了解数据的类型和使用的键。它越接近于一个表,就越简单,越有价值。

相关的阅读:How to Create and Validate JSON Schema in MongoDB Collections (怎样创建和验证MongoDB集合中的JSON Schema)
相关阅读网址:https://studio3t.com/knowledge-base/articles/json-schema-mongodb/

扩展的JSON定义了数据类型和每个不明显的值,它可以将数据的大小再增加三分之一,但是对于非结构化的数据来说是安全的。它的主要优点是,任何能够读取标准JSON的系统都可以读取它,因为数据类型信息是用JSON记录的。这里我们对数据类型要求比较严格么?我不认为这样想。比如你导入了包含日期的JSON格式的数据,由于JOSN没有标准的表示日期的方法,因此它将被解析成字符串。但是你想对文档基于日期排序,它将变得很棘手,如果你想基于日期进行索引查询,感觉行不通。因为十进制数据和浮点型数据有很大的不同,当你用浮点数进行金融计算,你就会很快会发现这一点。如果你想保存二进制数据信息,你还得保留其中的编码信息。通过这些发现,其实数据类型真的很重要。

如果你要导入的数据是表格格式的,有一种简单的方法,可以使用CSV或TSV与-columnsHaveTypes开关,来提供字段规范。在PowerShell中,我们可以很容易地证明这些:

实际上,MongoDB 导入有点问题,因为它将冗长的内容和错误信息发送到不寻常的地方,因此在PowerShell中不能很好地工作。(所谓“正确”,我的意思是,在PowerShell中触发错误的命令,它很快就会运行完,而发现不了真正的错误)。

使用旧的Windows命令行来尝试这个可能更容易:您不希望将标题行添加到已存在的大型CSV文件中,因此可以为这些标题指定一个文件。其中主要的问题是,使用CSV,MongoDB对通用的格式和基于逗号分隔(CSV)的MIME类型文件(RFC4180)感知特别好,但是SQL Server则没有。这样的方法可能有效,也可能无效,因为它有时行不通,而且还会陷入没有错误的困境。换言之,最好别这样操作!你用TAB分隔符来分隔列值中含有TAB制表符分隔的文件是没问题的(TSV的IANA标准只是禁止制表符)。如果你希望将数据从MongoDB导入SQL Server,只需使用JSON导出,因为所有检查都是在接收端完成。

要使用mongoimport导入MongoDB,最安全的方法是扩展JSON。MongoDB 图形界面 Studio 3T有多种导入方法,包括SQL导入导出,使用SQL连接可以在三分钟内读取200万条记录,但这是另一个快速迁移数据方法的主题。

相关的阅读请参考:Studio 3T’s SQL Import and Export Wizard documentation
参考网址:https://studio3t.com/knowledge-base/articles/mongodb-import-json-csv-bson/#import-sql-to-mongodb

主键问题

一般来说,关系数据库表要么有一个主键,要么有一个惟一的约束。没有它们,您无法轻松检索唯一的行。MongoDB集合是用聚集索引构建的[译者注1]。默认情况下,这只是一个随机object_id[译者注2]。这就失去了一个很好的索引机会,因为集群索引通常是主键的理想候选项。如果将表式数据库导入MongoDB,它将非常有效,如果对数据库进行正确的索引,并且通过创建有意义的集群索引,可以为每个表创建一个免费的、适当的索引。

我测试发现,使用_id字段,不管是用单列表示还是多列的复杂表示,只要保持这个KEY值和原来一样,无论谁使用数据库都有两种备选方案。下面是一个AdventureWorks示例,使用经典SQL Server示例数据库,移植数据到MongoDB。使用Studio 3T,我用SQL Query选项卡中完成了雇员的经典NAD(姓名和地址)视图。

SQL Query详细信息请参考:https://studio3t.com/knowledge-base/articles/sql-query/

你会发现,在可能的情况下,我充分利用了MongoDB中的集群索引_id。按照这种方式设计数据库,主键的设计经常被浪费掉了,所以不可能总是这样,在必要的地方创建索引更合适。在我的测试机上,它花费不到半秒的时间,这并不快,但也不是不合理。为了解决这两个问题,数据类型和主键都使用扩展JSON。

使用扩展的JSON

扩展JSON是可读的JSON,符合JSON RFC,但它为定义数据类型的每个值引入了额外的键/值对。任何可以使用JSON数据的进程都可以读取这种格式,但是只有MongoDB REST接口、mongoimport、Compass和mongo shell可以理解这种格式。重要的公共数据类型都在其中,但是也有一些数据类型只与MongoDB有关,或者用于MongoDB数据库迁移相关。它与CSV文件头部中的数据类型规范有很多共同点。

该标准有严格的“规范”模式和“放松”模式。不幸的是,MongoDB只能解析严格模式。MongoDB中还有第三种方言mongo shell模式,“helper”函数能展示描述BSON数据类型。mongoimport可以使用这两种类型,但是mongo shell模式不能识别标准JSON解析器。SQL Server以标准JSON导出,尽管它在CLR和不推荐的数据类型方面可能存在问题。因此,我们必须展示如何编写扩展JSON,将这种复杂性隐藏在存储过程中。

最简单的方法是,它在每个文件中生成这样的代码(我只显示了前面几个文档)。我选择检查是否有基于一列的主键,如果有,我使用它作为MongoDB键,通过使用保留标签“_id”来指示。我将SQL Server数据类型映射到等效的MongoDB BSON数据类型,在本例中,它是一个32位整数。

通过PowerShell导出JSON文件

JSON文件可以通过SQL Server使用修改的JSON,作为扩展的JSON格式导出,其中包含临时的存储过程,这些可以通过PowerShell或SQL完成。通过使用PowerShell,您可以避免打开SQL Server的“表面区域”,从而允许它运行的DOS命令将数据写入文件。我在另一篇文章中展示了使用SQL的更简单的技巧和方法。

参考网址:https://www.red-gate.com/simple-talk/sql/t-sql-programming/saving-data-to-various-formats-using-sql/

下面是一个PowerShell版本,它将数据库中的每个表保存到一个扩展的JSON文件中。它看起来有点复杂,但本质上它只是连接到一个数据库,对于每个表,它运行存储过程将数据转换为JSON。然后将其保存到指定的目录中。一旦你导出扩展的JSON文件之后,用Studio 3T将它们放置到适当的位置就很容易了。1

相关的阅读:How to Import JSON to MongoDB Using Studio 3T
相关阅读网址:https://studio3t.com/knowledge-base/articles/mongodb-import-json-csv-bson/#import-json-to-mongodb

接下来,在你的MongoDB数据库中有一个老旧的酒吧数据库,在旧的Sybase时代:2

问题是,我们并不总是希望依赖于交互式的方式。有时候,你只需要用脚本来做这些事情。通常,我提倡使用PowerShell,但是mongoimport工具有点古怪,最好通过命令提示符或批处理文件来完成上面的事情。批处理文件,是有点神秘,但工作的非常好!

从MongoDB导出数据到SQL Server

使用mongoexport工具导出扩展JSON,而不是普通标准JSON。为了获得纯JSON导出,您需要一个第三方实用工具,比如Studio 3T。SQL Server可以读取扩展JSON,但前提是为放入表中的每个集合提供显式模式。这并不完全令人满意,因为它很难自动化。下面是一个示例,使用mongoexport工具的输出示例进行说明。注意,到实际数据的路径在键字段上有双引号。这是因为在WITH显式模式语句中的路径表达式中,美元符号是不合法的

总结

我们可以非常容易地在SQL Server和MongoDB之间迁移数据库数据,但通常实际的表是我们最不需要考虑的问题。还需要考虑最重要的索引、视图、过程逻辑、计划任务和大量代码。我甚至不想考虑将关系系统移植到MongoDB,除非它只是一个初始阶段。在本例中,我将在SQL Server上创建集合,在源数据库上从它们的组成表创建集合,并对分层文档数据库的最佳设计做出判断。
我从本文中得出的结论是,使用扩展JSON提供了在两个数据库系统之间迁移数据的最佳方式,尤其是在它解决了两个问题的情况下,而且可以实现自动化。然而,像Studio 3T这样的第三方工具使这项工作的各个方面变得容易得多,尤其是在MongoDB中导入和导出大量扩展JSON文件的集合!

现在你不得不学习怎样在SQL Server和MongoDB之间导入和导出数据,学习连接如下:
how to paste the results of a SQL Server query directly into a MongoDB collection.
学习连接地址:https://studio3t.com/knowledge-base/articles/create-mongodb-collection-from-sql-server/

译者注1:文中翻译的聚集索引(clustered Index),译者理解是指MongoDB集合中,新增一个文档中的_id值, 这个_id在MongoDB集合中,是唯一的,作者可能认为MongoDB在分布式环境下,将索引描述成clustered index,感觉更加贴切形象。

译者注2:译者理解,如果用户在不指定_id值时,用户在创建一条数据,会默认生成一个ObjectId,这个ObjectId是MongoDB集合中是唯一的,并且是随机生成的。

原文作者简介:
Phil Factor:别名 Database Mole,有30多年的密集型数据库应用方面的经验,他的贡献请参考Phil Factor的MongoDB教程列表,详情请访问:https://studio3t.com/knowledge dbase/author/phil_factor/

原文标题:SQL Server to MongoDB Data Transfer Using Extended JSON
原文地址:https://studio3t.com/knowledge-base/articles/sql-server-to-mongodb-data-transfer/

发表评论