MongoDB to SQL MigrationMongoDB到SQL的迁移

Posted on: 22/05/2019 (last updated: 10/08/2021) by Stephan Weiss

MongoDB to SQL Migration makes it possible to export single or multiple MongoDB collections to a SQL file or database, and map fields to SQL tables and columns before the export is run.“MongoDB到SQL的迁移”可以将单个或多个MongoDB集合导出到SQL文件或数据库，并在导出运行之前将字段映射到SQL表和列。

Download Studio 3T to try it today.下载Studio 3T，立即试用。

MongoDB to SQL Migration currently supports MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and IBM DB2.MongoDB到SQL的迁移目前支持MySQL、PostgreSQL、Oracle、Microsoft SQL Server和IBM DB2。

Read our guide on migrating data in the opposite direction, SQL to MongoDB Migration.请阅读我们关于向相反方向迁移数据的指南，即SQL到MongoDB的迁移。

Introduction介绍

MongoDB to SQL Migration allows users to export MongoDB collections to a SQL file or database. “MongoDB到SQL迁移”允许用户将MongoDB集合导出到SQL文件或数据库。It is readily integrated with the Tasks feature which lets users automate migration.它很容易与任务功能集成，使用户可以自动迁移。

In a nutshell:简而言之：

  1. Each MongoDB to SQL Migration task allows the configuration of multiple export units.每个MongoDB到SQL迁移任务都允许配置多个导出单元。
  2. Each export unit configures the export of data from a MongoDB collection to a SQL file or directly to a (live) SQL database.每个导出单元配置将数据从MongoDB集合导出到SQL文件或直接导出到（实时）SQL数据库。
  3. Each export unit uses a fully configurable mapping, allowing full control over the mapping of each field to a column and/or row in SQL.每个导出单元使用完全可配置的映射，允许完全控制每个字段到SQL中的列和/或行的映射。
  4. It can preserve relationships between data in a MongoDB document by recreating them using corresponding foreign key constraints.它可以通过使用相应的外键约束重新创建MongoDB文档中的数据之间的关系来保留它们。
  5. It always creates and populates its own tables and makes no attempt to reuse any existing tables. 它总是创建和填充自己的表，并且不尝试重用任何现有表。It is safer and simpler this way.这样更安全、更简单。

Watch the video tutorial to see it in action.观看视频教程，了解其实际效果。

Open MongoDB to SQL Migration开放MongoDB到SQL迁移

A MongoDB to SQL Migration task can be created from within the Tasks dialog, from the main toolbar:可以从“任务”对话框中的主工具栏创建“MongoDB到SQL迁移”任务：

Or from the Connection Tree’s context menu:或者从连接树的上下文菜单：

If a collection is already selected, Studio 3T will directly create and open a new export unit with a default configuration for that collection.如果已经选择了一个集合，Studio 3T将直接创建并打开一个带有该集合默认配置的新导出单元。

The interface consists of an Overview Tab, where the source connection, target connection, and export units can be configured.该界面由一个“概览选项卡”组成，可以在其中配置源连接、目标连接和导出单元。

An export unit defines one single export of a source collection.导出单元定义源集合的单个导出。

Each export unit can be viewed and edited within an Export Unit Tab, providing full access to its configuration as well as offering previews, log messages and more.可以在“导出单元”选项卡中查看和编辑每个导出单元，提供对其配置的完全访问，并提供预览、日志消息等功能。

Overview Tab“概述”选项卡

The Overview Tab provides a common source and target configuration for the export task. “概述”选项卡提供导出任务的通用源和目标配置。It also displays a list of single export units, each handling one source collection.它还显示单个导出单元的列表，每个导出单元处理一个源集合。

MongoDB source connectionMongoDB源连接

The common MongoDB connection for all export units, used for accessing source collections.所有导出单元的通用MongoDB连接，用于访问源集合。

SQL target server/directorySQL目标服务器/目录

The common target for all export units, which can be either a directory or a connection to a SQL server.所有导出单元的公共目标，可以是“目录”，也可以是“与SQL server的连接”。

Studio 3T currently supports MySQL, PostgreSQL, Oracle, Microsoft SQL Server, and IBM DB2.Studio 3T目前支持MySQL、PostgreSQL、Oracle、Microsoft SQL Server和IBM DB2。

Export Units出口单位

The table of all export units defined in this export task. 此导出任务中定义的所有导出单位的表。The properties shown for each export unit (row) are:每个导出单元（行）的属性如下：

The tool bar provides actions to add, edit or remove export units. 工具栏提供了添加、编辑或删除导出单位的操作。These actions are also available through a context menu.这些操作也可以通过上下文菜单获得。

Additionally, collections of the selected source connection may be dragged from the connection tree and dropped into the export unit table to add new export units.此外，可以从连接树中拖动选定源连接的集合，并将其放入导出单元表中，以添加新的导出单元。

A user can easily change the order of export units using drag and drop.用户可以通过拖放轻松更改导出单位的顺序。

Single units can also be excluded from the export (see checkbox in first column of the Overview Tab).也可以从导出中排除单个单元（请参见“概述”选项卡第一列中的复选框）。

Adding units is only available with a valid source connection.添加单元仅在源连接有效的情况下可用。

Export Unit Tab“导出单位”选项卡

The Export Unit Tab shows the mapping configuration of the collection export to a SQL file or database. “导出单元”选项卡显示集合导出到SQL文件或数据库的映射配置。It also provides previews and live logs for exports.它还为导出提供预览和实时日志。

Initial Collection Scan首次集合扫描

The migration will automatically try to scan an export unit’s source collection as soon as a unit tab is opened the first time.第一次打开单元选项卡时，迁移将自动尝试扫描导出单元的源集合。

By default, the collection scan looks at the 50 newest documents 默认情况下，集合扫描会查看50个最新文档(which would be the last results returned by a simple find query).（这将是一个简单的find查询返回的最后一个结果）。

Studio 3T then creates default mappings based on the most frequently-occurring type found for a field.Studio 3T然后根据字段中最常见的类型创建默认映射。

If no type is found for a field, e.g. because all values seen are null or undefined, or if more than one field type has been detected, the type “Mixed” is chosen.如果找不到字段的类型，例如，因为看到的所有值都为null或未定义，或者如果检测到多个字段类型，则选择“混合”类型。

Mappings映射

The Mappings button will open the Export Unit Tab which contains the Mapping Tree. “映射”按钮将打开包含映射树的导出单元选项卡。

The Mapping Tree shows how the fields of the MongoDB collection will be mapped to their corresponding SQL tables and columns. 映射树显示MongoDB集合的字段将如何映射到相应的SQL表和列。Its structure mostly follows the collection’s document schema.它的结构主要遵循集合的文档模式。

The bulk of MongoDB to SQL mappings are done in the Mapping Type, Relationship Type, SQL Table, SQL Column and the (Map to) SQL Type columns:MongoDB到SQL的大部分映射是在“映射类型”、“关系类型”、“SQL表”、“SQL列”和“（映射到）SQL”类型列中完成的：

Mapping Type映射类型

The Mapping Type defines how a field’s data is mapped to columns in SQL.“映射类型”定义字段数据如何映射到SQL中的列。

Relationship Type关系类型

The Relationship Type defines in which SQL tables these columns will be stored, and how.“关系类型”定义这些列将存储在哪些SQL表中，以及如何存储。

Both Table, FK -> PK and Embed are incompatible with and therefore not available with the mapping type Array Elements To Rows.“表，FK->PK”和“嵌入”都与映射类型数组元素到行不兼容，因此不可用。

SQL TableSQL表

The SQL Table defines the target SQL table for a field mapping.“SQL表”定义字段映射的目标SQL表。

SQL ColumnSQL列

The SQL Column defines the target SQL column for a field mapping.“SQL列”定义字段映射的目标SQL列。

Map to SQL Type

The (Map to) SQL Type defines which SQL type the data should be mapped to.“（映射到）SQL类型”定义数据应映射到的SQL类型。

While the Mapping Tree allows a user to enter any table or column name, it will always show the effective, resulting name in SQL (WYSIWYG).虽然映射树允许用户输入任何表或列名，但它将始终以SQL(WYSIWYG)的形式显示有效的结果名称。

SQL Table Primary KeysSQL表主键

All tables created by the mapping will at least get an automatic primary key column that increments an integer value starting at zero.通过映射创建的所有表至少将获得一个自动主键列，该列从零开始递增一个整数值。

The root mapping may set its child mapping for the field _id to be used as primary key (see context menu), as long as it maps to a single column of the main table.根映射可以为要用作主键的字段_id设置其子映射（请参见上下文菜单），只要它映射到主表的一列。

By default, an _id field mapping will be used as provider for the primary key column if above requirements are met and its MongoDB value is not of a string type.默认情况下，如果满足上述要求且其MongoDB值不是字符串类型，则_id字段映射将用作主键列的提供程序。

Reason: Some databases, like MySQL, restrict the use of text types for primary key constraints. Of course, users may still manually set to use their string _id fields as primary keys.原因：一些数据库，比如MySQL，限制使用文本类型作为主键约束。当然，用户仍然可以手动设置为使用字符串id字段作为主键。

Edit Mapping Dialog编辑映射对话框

For more complex mappings, users can use the convenient Edit Mapping Dialog, which can be used to both create or edit mappings:对于更复杂的映射，用户可以使用方便的“编辑映射”对话框，该对话框可用于创建或编辑映射：

It divides the configuration into three sections, similar to the Mapping Tree: Field, Mapping and Relationship. 它将配置分为三个部分，类似于映射树：字段、映射和关系。SQL specific properties are available within their specific editors.特定于SQL的属性在其特定的编辑器中可用。

The Field section supports configuring the field of a mapping with an auto-completion of detected field names and types.“字段”部分支持配置映射的字段，自动完成检测到的字段名和类型。

The Mapping section supports configuring the mapping type, along with a little explanation of each available choice.“映射”部分支持配置映射类型，并对每个可用选项进行一些解释。

The Relationship section supports configuring the relationship type, again along with a little explanation of each available choice.Relationship（关系）部分支持配置关系类型，并对每个可用选项进行了简要说明。

Running a Monitored Export运行受监控的导出

The mapping configuration of an export unit can be run directly from the mapping tree tool bar:导出单元的映射配置可以直接从映射树工具栏运行：

Similar to an export started from the task bar, a background task will be queued for the its execution.与从任务栏启动的导出类似，后台任务将排队等待其执行。

Additionally, a monitored export provides a live monitor of the running export including log messages as well as detailed statistics:此外，受监控的导出提供了运行导出的实时监控，包括日志消息和详细统计信息：

Furthermore, a monitored export can be reverted after it has been finished or aborted using the tool bar action “Revert the monitored export …” next to “Run …”此外，受监控的导出可以在完成或中止后使用“运行”旁边的工具栏操作“还原受监控的导出”进行还原

New Mapping Dialog (Customize Default Mappings)新建映射对话框（自定义默认映射）

Each mapping configuration can be replaced with a new, customizable default mapping using the “New Mapping” action from the mapping tree tool bar (the blank document icon):可以使用映射树工具栏（空白文档图标）中的“新建映射”操作，将每个映射配置替换为新的、可自定义的默认映射：

It opens the New Mapping Dialog that allows the creation of a new default mapping for a collection, including an option to re-scan its document schema.

Export Previews导出预览

Previews use the first ten documents returned by a plain find query to the source collection, if available. Shown preview details are linked to the selection of the Mapping Tree.

SQL Table Preview

The tab SQL Table Preview in the lower part of the unit tab provides a visual live preview of SQL tables and columns that would result from the current configuration.

SQL Statement Preview

The tab SQL Statement Preview in the lower part of the unit tab provides a textual live preview of SQL statements written for an export with the current configuration.

The tool bar of the SQL statement preview provides some options to control the preview and its update behavior:

There is also the Log Messages Preview in the lower part of the unit tab, which provides a preview of log messages to expect for an export with the current configuration.在unit选项卡的下半部分还有“日志消息预览”，它提供了使用当前配置导出日志消息的预览。

With some combinations of SQL clients and databases, results might not show up immediately. 对于SQL客户机和数据库的某些组合，结果可能不会立即显示。A reconnect to the database should help in those cases.在这些情况下，重新连接数据库应该会有所帮助。

Export Log Files导出日志文件

With Studio 3T 2020.7, MongoDB to SQL Migration now also writes warnings and errors to a log file.在Studio 3T 2020.7中，MongoDB到SQL的迁移现在也会将警告和错误写入日志文件。

These log files are available for both live migrations and migrations saved as tasks.这些日志文件可用于实时迁移和保存为任务的迁移。

To view and save these log files locally, right-click on any MongoDB to SQL Migration export unit in the Operations window.要在本地查看和保存这些日志文件，请右键单击操作窗口中的任何MongoDB到SQL迁移导出单元。

Video tutorial视频教程

►https://www.youtube-nocookie.com/embed/yt8TdNPDvfY

Automate MongoDB to SQL Migrations自动化MongoDB到SQL的迁移

Interested in automating MongoDB to SQL migrations? Help us shape the future of job automation for MongoDB.有兴趣自动化MongoDB到SQL的迁移吗？帮助我们塑造MongoDB工作自动化的未来。

In the meantime, you can already run and schedule MongoDB to SQL migrations on your local machine using Studio 3T’s Task Scheduler tool.同时，您已经可以使用Studio 3T的任务调度器工具在本地机器上运行并计划MongoDB到SQL的迁移。

What’s next?接下来呢？