MongoDB to SQL Migration

Introduction

MongoDB to SQL Migration allows you to export MongoDB collections to a SQL file or database. You can automate MongoDB to SQL Migration using Studio 3T’s Tasks feature.

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

In the global toolbar, select MongoDB -> SQL Migration:

Or from the Connection Tree’s context menu, select SQL Migration > MongoDB -> SQL Migration:

MongoDB to SQL Migration consists of an Overview tab, where you configure the source connection and target connection, and tabs for each of the export units. An export unit defines one single export of a source collection. Each export unit provides 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 connection/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, you can 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 Overview tab displays a list of all the export units defined for 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 toolbar provides buttons to add export units, edit mappings for existing export units, or remove export units. These actions are also available through a right-click context menu.

Additionally, to add new export units, you can drag collections from the Connection Tree for the selected source connection and drop them onto the export unit list.

You can change the order of the export units using drag and drop.

To exclude single units from the export, clear the export unit’s checkbox under the Source database column.

Export Unit Tab

The Mappings button opens an Export Unit tab which shows the mapping configuration of the MongoDB collection to tables in a SQL file or database. The Export Unit tab also provides previews and live logs for exports.

Initial Collection Scan

When the Export Unit tab is opened for the first time, the migration tool scans the export unit’s source collection. 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, for example because all scanned values are null or undefined, or if more than one field type has been detected, the type “Mixed” is chosen.

Mappings

The mapping configuration 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: you 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 or by right-clicking to open an edit dialog.
• An alternative to removing a field mapping is to not include it. Mappings excluded from the export are shown in gray 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 the SQL tables in which 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 in 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 you to specify a particular 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 you to specify a particular 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 (right-click to see this option in the 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 the 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, you may still manually set string _id fields as primary keys.

Edit Mapping Dialog

For more complex mappings, you can use the Edit Mapping dialog to edit mappings (or the Add Mapping dialog if you’re creating mappings):

The mapping configuration is divided into the following sections: 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 you are typing.
• The option Warn when encountering a type mismatch enables logging of situations during export where a value does not match the type specified for the field mapping.

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

• The mapping type Column allows you to configure the name and type of the column in SQL.
• The mapping type Array Elements To Rows allows you to configure the name of the 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, with an explanation of each available choice.

• The relationship type Embed allows you to configure the parent mapping from which to inherit the table.
• The relationship types Table, PK <- FK, Table, FK -> PK and Table, No Link allow you to specify 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 you to configure the name of their foreign key columns in SQL.

Running a Monitored Export

You can run a monitored export directly from the export unit toolbar:

Similar to an export started from the task bar, a background task will be queued.

Additionally, a monitored export provides a live monitor of the running export including log messages and detailed statistics:

Furthermore, a monitored export can be reverted after it has finished or aborted using the toolbar button “Revert …” next to the “Run monitored export …” button.

New Mapping Dialog (Customize Default Mappings)

Each mapping configuration can be replaced with a new, customizable default mapping using the New mapping button:

It opens the New Mapping dialog which includes an option to re-scan the document schema.

Export Previews

Previews use the first ten documents returned by a plain find query on the source collection, if available. The preview details that are shown depend on the Field you have selected in the top half of the Export Unit tab.

SQL Table Preview

The SQL Table Preview tab shows a preview of the SQL tables and columns that would result from the current configuration.

SQL Statement Preview

The SQL Statement Preview tab shows a live preview of the SQL statements that Studio 3T has written for an export with the current configuration.

The toolbar of the SQL Statement Preview provides options to control the preview and its update behavior:

• Update statement preview manually updates the preview when live updates of the preview are turned off.
• Auto update statement preview turns live updates of the preview on or off, for example to improve performance.
• Show insert statements for one preview document only tells the preview to show INSERT statements for one preview document only.
• Show insert statements for all preview documents tells the preview to show INSERT statements for all available preview documents.
• Auto Scroll To Statements allows you to select the type of statements to scroll to by default (CREATE or INSERT).
• Document allows you to choose which documents to scroll to (for INSERT statements only).

Log Messages Preview

The Log Messages Preview provides a preview of log messages to expect for an export with the current configuration.

Export Log Files

MongoDB to SQL Migration can also write 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 sidebar.

Automate MongoDB to SQL Migrations

Run and schedule MongoDB to SQL migrations on your local machine using Studio 3T’s Task Scheduler tool.

Summary

Now that you’ve read the guide on Studio 3T’s MongoDB to SQL migration tool, here’s a summary of the steps to follow for exporting your MongoDB schema to a SQL database:

1. Select a MongoDB source connection and a SQL target destination (a server or a file).
2. Add one or more export units (an export unit corresponds to a MongoDB collection or view).
3. Map MongoDB fields to SQL columns.
4. Preview SQL tables and statements to check your migration is correct. Edit mappings as required.
5. Run your migration. You can run selected export units or the entire migration.

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

---