Q: What’s the difference between Studio 3T SQL Export and SQL Migration?
Welcome to the latest Studio 3T Ask Manatees Anything – #Studio3T_AMA. Want to know how to use Studio 3T to make your MongoDB life better? Just tweet and use #Studio3T_AMA or email [email protected].
TL;DR: Migration lets you reshape your MongoDB documents for immediate consumption by an SQL database. Export, on the other hand, flattens your schema before saving SQL in a file.
A: On paper, Export and Migration sound like two very similar things. Tools that take MongoDB documents and turn them into SQL. The difference is all in the details though:
SQL Export
Studio 3T’s SQL Export is about getting MongoDB data out of the database in a format that an SQL database can use. SQL Export does this by writing SQL statements to a file. That file can then be read by an SQL client application and run on the SQL database. Export handles collections in isolation, without reference to other collections. Each one has its embedded content flattened into SQL columns for easy reference and access. It’s a simple and efficient way to generate SQL data and doesn’t need immediate SQL database access.
In this screenshot, you can see the MongoDB collection being mapped directly to SQL fields, with arrays (like the genres field) being turned into multiple columns. Below that, you can see a preview of the SQL file that will be generated. That’s complete with SQL table creation (and table dropping if needed).
SQL Migration
Studio 3T’s SQL Migration, from MongoDB to SQL databases, may at first glance appear to do the same thing as Export. SQL Migration can write SQL to files for manual importing into relational databases. Unlike Export though, Migration can also directly connect to a running SQL database, creating schemas and inserting new data as it finds it. This reduces the steps needed to get the data into the target database.
More importantly though, Migration allows you to reshape the MongoDB data into a more relational form. Rather than flattening them, Migration can make arrays and embedded documents into extra tables. These tables will have keys generated between the main table and sub tables. That enables SQL SELECT and JOIN queries to handle the rows in a more normalized form. This mapping process can also flatten fields where needed and extract normal fields into their own linked column.
In this screenshot, we can see how extensive a Migration can be in terms of remapping data. This collection is full of arrays and Migration will automatically expand that out to 11 other tables and create the relations between them. In our previous export, the genres array was simply flattened. Here it is being mapped to a movies_genres table with each genre entry getting its own row (or column – it’s configurable). Below, you can see the generated tables and a preview of the main table which will appear in the target SQL database.
Common Features
Both Export and Migration do have common functionality. In the Ultimate edition of Studio 3T, both support Incremental Execution to only export or migrate new data and Data masking which can obscure or remove sensitive data while retaining the nature of that data (making it great for populating test databases). Tasks can also use SQL Export and SQL Migration, allowing you to run either on your own timetable.
Export and Migration to SQL offer power routes to get your MongoDB data onto an SQL platform. Pick the one that’ll save you the most time.
