The data has to come back to SQL

MongoToDB » Use cases » Migrate MongoDB to SQL

Download MongoToDB Free Trial »     Buy MongoToDB Now »

The situation

A MongoDB database is being retired, a report needs the data in a relational warehouse, or the company has standardised on MySQL and the application data still lives in documents. Either way the shape has to change: a collection is not a table, a document is not a row, and there is no schema to copy - the columns have to be decided.

What you do

1. Connect to the MongoDB server and to the target database (MySQL, SQL Server, Oracle, PostgreSQL, SQLite, Access, DB2, DBF or Redshift).
2. Open Collections to Tables: the collections are listed with a destination table name each, which you can accept or change.
3. Review the field-to-column mapping for each table - the destination table is created from the documents, with a type chosen for each field.
4. Run. Progress is shown per collection, so a problem in one of them does not mean starting again from the first.

Everything runs from your machine: the documents are read over the connection you configured and written straight into the target database. Nothing is staged in the cloud, and the whole migration is a saved session, so moving the next environment is one click or one command.

What it will not do

• It does not design a normalised schema. One collection becomes one table. Splitting a document across parent and child tables, or normalising repeated values, is a modelling decision it will not make for you.
• It does not migrate references into foreign keys. An _id stored in another document becomes a column value, not a constraint, and no join is created.
• It does not reconcile documents that disagree. In a collection where one document has a field the others do not, that field becomes a column that is null for most rows - or, depending on how you configure the fields, is left out.
• It does not run continuously. A migration is a snapshot. Repeated runs are handled by the scheduled route, or by re-running the same task.

Related

Documents with sub-fields: flatten nested documents into columns. No direct access to the target: write the result as a SQL file. One collection only: collection to table, or from a query: query to table. Collections that should share a table: similar collections to one table.