In this tutorial, we’ll show you how to export your MongoDB data directly to a live SQL database using Studio 3T and our new MongoDB to SQL Migration feature.
First things first
- Studio 3T: Make sure you have Studio 3T up and running.
- SQL database: Have your SQL connection details ready. I’ll be using SQL Server for this tutorial, but Studio 3T officially supports MySQL, PostgreSQL, and Oracle as well.
For our MongoDB to SQL migration, we’ll make use of the two datasets:
- Raleigh Durham Airport Weather History
- Air Traffic Passenger Statistics
You’ll find the collections in a zip file called ‘Aviation Data’ at the bottom of this article.
Prefer to watch? Skip to the video tutorial.
1 – Open SQL Migration
Click on SQL Migration from the Global Toolbar then choose MongoDB → SQL Migration.
Alternatively, right click in the Connection Tree, go to SQL Migration, and click on MongoDB → SQL Migration.
2 – Choose the MongoDB source connection
Now we need to connect to our MongoDB server that holds the collections we wish to migrate.
To do this, click on Click here to connect to a server.
Now we have two options: connect to an existing MongoDB server or create a new connection, which first-time users will likely need to do.
To create a new MongoDB connection, click on New Connection.
Enter the relevant details in the Server, Authentication, SSL, SSH Tunnel, Proxy and Advanced tabs.
Click Save, then Connect.
You can then simply choose this connection from the list for your next migration.
3 – Choose your SQL target connection or directory
The next step is to choose our target SQL database.
Studio 3T lets us directly connect to a SQL server and immediately migrate our data, or save this migration as a SQL file path that can be run later at our convenience.
If SQL target = Server
Click on Click here to connect to a server.
Similar to Step 2, this will open up the SQL Connection Manager.
Create a new SQL connection by clicking on New Connection. Fill out the Server and SSH Tunnel tabs with the details needed.
Click Save. Then click OK. You can then simply choose the saved connection for future migrations.
If SQL target = Directory
If you choose to save to a SQL file path, Studio 3T will let you choose the location of the statement file as well as the database type.
4 – Add export units
Now that we’ve successfully connected to both our MongoDB and SQL servers, it’s time to choose our data.
Click on Add export units. Each export unit corresponds to one single export of a source collection.
Here we’re going to choose the collections we wish to migrate to MySQL, which are:
- Raleigh Durham Airport Weather History
- Air Traffic Passenger Statistics
Be sure that you have downloaded the zip file containing both collections, which is attached at the end of this article.
Click OK.
Upon selecting the databases, Studio 3T will generate a SQL Table Preview of your data. Here we can see the SQL table(s) that will be made from our MongoDB collections.
You can further configure how map MongoDB fields should be mapped to SQL tables and columns by making use of the Mappings functionality.
5 – Run the export
Is the SQL Table Preview looking good? Then it’s time to run the export by clicking on Run Migration in the top toolbar.
Track the export in the Operations window which you’ll find in the bottom left hand corner of Studio 3T.
Once the export is complete, open your SQL database and hit refresh to see your newly imported data.
With some combinations of SQL clients and databases, results might not show up immediately. A reconnect to the database should help in those cases.
Video tutorial
Next steps
Check out our section on Working with MongoDB and SQL and find helpful tutorials on how to query SQL with MongoDB, transfer data between the two databases, and more.
