Studio 3T SQL Import or Migration? #STUDIO3T_AMA

Q: How do Studio 3T’s SQL Import and SQL Migration to MongoDB differ?

TL;DR: Migration lets you reshape your incoming SQL data into MongoDB documents. Import, on the other hand, just brings SQL data in as a “flat” collection.

A: Unlike SQL Export and SQL Migration, both Import and Migration work with live SQL databases – there’s no ability to take an SQL DDL file dump and import it. 

Like SQL Export and SQL Migration from MongoDB, the big difference is in the details.

Import deals purely with tables, while Migration deals with tables and their relations allowing you to remap and denormalize your data as it comes into the document-centric world of MongoDB. Let’s look more closely:

SQL Import

Studio 3T’s SQL Import is about transporting the tables that exist in an SQL database into MongoDB by a simple and direct mapping. Columns in SQL tables become fields in the documents. You can set a new field name for the destination field and you can set a MongoDB type for the field too.

That’s pretty much it, apart from being able to omit selected columns, turn dotted column names into embedded documents and handle arrays as fields. SQL Import works well for simple data movement, as long as you are prepared to rebuild all the data on the MongoDB side to avoid bad performance.

Just replicating a relational database structure on a document database will deliver bad performance. That’s why we have an SQL Migration tool.

SQL Migration into MongoDB

The SQL Migration into MongoDB is about making your data as amenable as possible to a document-centric environment. That means being able to migrate all the data in your SQL database by following the foreign keys to other tables and importing those tables too. And if that table points to another table, embed that data too. That might be hard to visualize so here’s a Migration to MongoDB being set up:

Here, we are taking a customer’s database and then for each customer creating an array of all the orders that the customer (using the customerid) has placed. Then we are embedding another array in the order, the order details which lists each ordered product (using orderdetailsid). And then, for each product, we are embedding the singular product data from the products table (using productid). 

This is migration, turning the database into a working document model so you can exploit the benefits of document databases with highly localized data access – once you’ve retrieved a customer you have all their information about them. 

Common Features

Both Import and Migration do have common functionality. In the Ultimate edition of Studio 3T, both support Incremental Execution to only import or migrate new data and Data masking which can obscure or remove sensitive data while retaining the nature of that data. SQL Import and SQL Migration are also both usable as scheduled Tasks allowing you to run either on your own timetable.

Import and Migration from SQL offer power routes to get your SQL data into the document-centric world of MongoDB. The other thing they have in common? They are both huge time savers.