Using Hackolade to configure Studio 3T SQL Migrations

Studio 3T has an extensive range of operations it can perform to merge data in a relational schema (and usually SQL) into a document centric schema. For some users, they wish to approach this task using modeling tools such as Hackolade which can assist with the field mappings and leave a documentable pathway and records for future use. For those users, we have the Hackolade integration.

Starting A Migration

A migration, using Hackolade, begins at the source SQL database; you should export the schema DDL you wish to migrate there as a DDL formatted backup. That file typically requires a `.sql` extension. Once you have this file, move to Hackolade.

In Hackolade, create a new model using Hackolade➡New Model.

Important: Studio 3T needs the lineage data that Hackolade models optionally generate. On creating the new model, select the node at the top of the object browser tree and then, on the opposite side of the screen, under Properties and the Details tab, check the Lineage checkbox.

Select Tools➡Reverse Engineer➡Data Definition Language file… to begin the process of creating the model. If you have not turned on Lineage at this point, Hackolade will warn you with this dialog and an opportunity to turn Lineage on:

Hackolade will then import the DDL file and convert it to a Hackolade model.

You can now continue your work with Hackolade, reorganizing and denormalizing the model. Hackolade is able to suggest denormalizations using Tools➡Suggest Denormalization. This will show which tables that can be denormalized or linked with a two way reference.

Using suggested denormalization is an effective way of reducing the relational tables down to manageable documents. Hackolade provides more extensive guidance on this process and in depth examples in their SQL DDL documentation. Once your remodeling is complete, save the Hackolade model.

Running A Migration

Switch to Studio 3T and select the SQL Migration➡SQL→MongoDB Migration option from the global toolbar at the top of the screen. You may be presented with a tutorial, click on the Overview tab to dismiss it. You are now at the Migration overview tab where you can configure one or more migrations.

To start configuring, click on the link next to Source SQL connection: which will allow you to create a new SQL database connection or reuse an existing configuration. Connect to your source database here.

Next, select the target MongoDB database connection using the link next to the Target MongoDB connection, choosing the MongoDB database that will be the destination for the migration.

Importing A Hackolade Model

Now, click on the + Add from Hackolade button in the toolbar immediately above the migrations list and select your Hackolade model file. It will then be imported and interpreted as a set of Studio 3T SQL import sources. Once complete, the overview will display each migration source in a list. Each row of the list shows source table, target database and collection, along with the insertion mode for handling id collisions.

As these are now import units, you can open the mappings editor either by double clicking a unit in the list, or by selecting a unit and clicking the Mappings button.

The Mappings editor shows the exact field mappings and lets the user preview the JSON which will be generated with live data. If you wish, you can also make final modifications to the migration mappings. Any changes made here are not, currently, recorded in Hackolade.

Running A Migration In Studio 3T

When you are happy with the migration, you can click Run Migration and the migration will be performed.

The Migration’s progress appears in the Operations pane.  It is also possible to save this migration as a Task, with Save Task and to use the Studio 3T Task Scheduler to run it at specific dates and times in the future.