MongoDB to SQL Migration

Introduction

MongoDB to SQL Migration allows users to export MongoDB collections to a SQL file or database. 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.
2. Each export unit configures the export of data from a MongoDB collection to a SQL file or directly to a (live) SQL database.
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.
4. It can preserve relationships between data in a MongoDB document by recreating them using corresponding foreign key constraints.
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.

Open MongoDB to SQL Migration

A MongoDB to SQL Migration task can be created from within the Tasks dialog, from the main toolbar:

Or from the Connection Tree’s context menu:

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 connection

The common MongoDB connection for all export units, used for accessing source collections.

SQL target server/directory

The common target for all export units, which can be either a directory or a connection to a SQL server.

• If a target directory is set, a user may choose any target database type and each export unit specifies a relative SQL file path for the export.
• If a target server is set, the target database type is locked to the server type and each export unit specifies a database name for the export.

Export Units

The table of all export units defined in this export task. The properties shown for each export unit (row) are:

• Source database – The name of the MongoDB source database.
• Source collection – The name of the actual MongoDB source collection to export.
• Target file/database – The name of the actual SQL file or SQL database to export to.
• Mapping – A short summary of the number of tables and columns that this unit exports.

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).

Export Unit Tab

The Export Unit Tab shows the mapping configuration of the collection export to a SQL file or database. 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 (which would be the last results returned by a simple find query).

Studio 3T then creates default mappings based on the most frequently-occurring type found for a field.

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.

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. Its structure mostly follows the collection’s document schema.

• The first column (Field) contains a field name for each mapping, starting with the root node with the collection name and descending into embedded fields of its documents.
• Special case for arrays: A user may decide to map array elements by their type instead of their index. For this, “virtual” fields with type names (e.g. “Object”) are shown.
• New field mappings may be added and existing ones may be reordered or removed. All field mappings can be edited directly within the Mapping Tree or with an edit dialog.
• An alternative to removing a field mapping is to not include it. Mappings excluded from the export are shown in grey and their children, hidden (e.g. “lexicalEntries”).

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:

Mapping Type

The Mapping Type defines how a field’s data is mapped to columns in SQL.

• Column exports a primitive field like a number, string, etc. to a single column in SQL.
• Object Fields exports an object field (embedded document) including its children to SQL.
• Array Elements To Rows exports an array (list) including its elements to rows in SQL.
• Array Elements To Columns exports an array (list) including its elements to columns in SQL.

Relationship Type

The Relationship Type defines in which SQL tables these columns will be stored, and how.

• Embed does not create a new table, but embeds new columns within the table of a parent mapping.
• Table, PK <- FK stores columns in its own, new table, along with a foreign key column, referring to the primary key of the parent mapping’s table.
• Table, FK -> PK stores columns in its own, new table, along with a foreign key column on the table of its parent mapping, referring to the new table’s primary key.
• Table, No Link stores columns its own, new table, but does not create any links to or from the parent mapping’s table. The relationship is not preserved

SQL Table

The SQL Table defines the target SQL table for a field mapping.

• For the relationship type Embed, a table from a valid, direct parent mapping may be selected here.
• For relationship types that introduce their own, new tables, a requested table name may be entered here.

SQL Column

The SQL Column defines the target SQL column for a field mapping.

• This property is available for mappings of type Column and allows a user to request a specific SQL column name.

Map to SQL Type

The (Map to) SQL Type defines which SQL type the data should be mapped to.

• This property is available for mappings of type Column and allows to request a specific SQL column type. It provides a recommended default.

SQL Table Primary Keys

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.

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.

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.

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.

The Field section supports configuring the field of a mapping with an auto-completion of detected field names and types.

• The option Automatically set type of known fields allows the dialog to update the field type when it detects a known field name while a user is typing.
• The option Warn when encountering a type mismatch enables logging of situations where a value during export does not match the type specified for the field mapping.

The Mapping section supports configuring the mapping type, along with a little explanation of each available choice.

• The mapping type Column allows to configure the name and type of its column in SQL.
• The mapping type Array Elements To Rows allows to configure the name of its index column in SQL.
• Both the mapping types Object Fields and Array Elements To Columns provide no further configuration options.

The Relationship section supports configuring the relationship type, again along with a little explanation of each available choice.

• The relationship type Embed allows to configure the parent mapping to inherit the table from.
• The relationship types Table, PK <- FK, Table, FK -> PK and Table, No Link allow to set the name of their own table and its primary key column in SQL.
• In addition to that, the relationship types Table, PK <- FK and Table, FK -> PK also allow to configure the name of their foreign key columns in SQL.

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:

• Update statement preview manually updates the preview, in case live updates are turned off.
• Auto update statement preview can be used to turn live updates of the preview on or off, e.g. to improve performance.
• Show insert statements for one preview document only tells the preview to preview INSERT statements for one preview document only.
• Show insert statements for all preview documents tells the preview to preview INSERT statements for all available preview documents.
• Auto Scroll To Statements allows to choose what type of statements to scroll to by default (CREATE or INSERT).
• Document allows to choose for which of the preview documents the view should scroll to the INSERT statements by default.

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.

Export Log Files

With Studio 3T 2020.7, MongoDB to SQL Migration now also writes warnings and errors to a log file.

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.

Automate MongoDB to SQL Migrations

Interested in automating MongoDB to SQL migrations? Help us shape the future of job automation for 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.

What’s next?

• Download Studio 3T to try out the bi-directional SQL Migration feature
• Check out the Export Wizard for more info on supported export formats
• Get the whitepaper, SQL Migration: MongoDB and SQL Coexistence, Made Easy