Data Migration

The Data Migration task powers all the ETL work that takes place in Data Governor. Data Migrations are much easier to configure than a traditional set of SQL Queries and can easily be modified and fine tuned to meet you requirements.

Selecting your Source and Target Connections

Data Migrations simply consist of two connections, the source and the target. Once you’ve selected the Data Migration task type from the task form, the second page will prompt you to select the two connections used in the migration.

picking a source connection

Data Governor will guide you through selecting the source and target connections and possibly schemas/file definitions (if the connection type supports it), the agent loading available entities such as schemas, tables, columns and other pieces of metadata as you progress through the system.

Working with File System File Definitions

If you have created a file system file definition and want to use it as a source or target in your migration, click the file icon next to either the source or target connection field and the UI will switch to the file definition view where you can easily pick an available definition from the drop down.

picking a file system definition

Custom Output Schemas

If you provide a custom output schema for the migration, Data Governor will create the schema on migration.

Advanced Options

Ticking the “Show Advanced Options” checkbox will display additional options you can use to configure your Data Migrations behaviour.

parallel config

Enabling Parallel Data Migration will allow the Data Governor agent to migrate multiple tables over in parallel. This can result in faster migrations as more tables can be brought over in a shorter amount of time.

The “Max Degree of Parallelism” sets how many tables can be migrating concurrently at any given time. For example if you set this to 4, Data Governor will limit itself to only migrating up to 4 tables at a time meaning that additional tables would be migrated once a table migration completed in the currently running migrations.

Configuring Max Degree of Parallelism can be touchy and it is recommended that you work your way up to higher degrees of parallelism rather than choose an obscenely high number. The recommended starting value is 8.

Selecting your Tables to Migrate

Once you’ve picked both a source and target connection, moving onto the next page will show you the migration builder. The builder splits the screen into top and bottom, source and target respectively.

Building a migration is as easy as finding the tables you wish to migrate in the top half of the screen, clicking the add button next to the table and seeing how it gets pushed to the target list in the bottom half.

how easy is migrating

Configuring Source Tables

Query as Source

query as a source

If you wish to use a SQL Query as a source table, click the Query tab of the Source area.

Here you are given a text editor where you can insert a SQL Query that can be used to mock a table to migrate.

Query as a Source tables are not locked to the source schema and so you will need to specify the schema you are querying from as part of the query.

Importing all Tables with Detect Mode

detect mode

For instances where the tables/files in a source may grow with time, Data Migrations can be configured to use Detect Mode which ensures that additional objects that are added to the source are automatically imported with every migration.

An example of when this may be useful is for importing from a folder of flat files which gets additional files every so often-Data Governor Online will retrieve all the available files from the folder and add them automatically to the Data Migration task.

Detect Mode also supports an optional Regex based Filter Pattern which can be used for only importing objects which match the pattern. For example if you wanted to only import objects that began with the word “Sales” you could use the pattern ^Sales.

Configuring Target Tables

You’ll notice that target tables have a set of actions associated with them. Besides the obvious minus to remove the table from the target, the other two actions from left to right include:

Incremental Config

By default, Data Migrations will drop any matching target tables to the source. If Incremental Config is enabled, the migration will insert the new records into the existing table.

inc config modal

Table Options
  • Incremental
    • This enables the basic feature of incremental migrations.
  • Primary keys
    • If enabled, primary key constraints will be re-created on the target table as part of the migration.
  • Indexes
    • If enabled, indexes from the source table will be re-created on the target table as part of the migration.
Currency Configuration

Once “Incremental” is enabled, you are given the option to define a currency column and how often this column will “refresh” with new data being brought in.

The Currency Column is the column Data Governor will check to determine what records need to be brought in as part of the migration and which records are considered to be already migrated. This column by default can be either a numeric value (such as a primary key) or a DateTime value.

Once a currency column is selected, you can set the conditions for how to compare the data in the source with the target based on that column. The Refresh Period Type allows for you to set what measurement you use for comparison, whilst the Refresh Period is the threshold used for determining what records shall be migrated.

If you want to do basic numeric comparison for a currency column, use “TransactionID”.

Select Columns

Using the Select Columns configuration, you can easily migrate specific columns from the source to the target table. This is as easy as opening up the select columns menu and checking the columns you wish to migrate.

selecting columns

Target Filters

Tables can be filtered using a Target Filter. We have provided a simple interface where you can select available columns in a table and provide a value to use as your filter.

How to filter a table

Once you have created a Data Migration task and selected your tables, there will be an option beside the table called Target Filter.

target Filter Button

In this modal, you can select your column under Column Name from the drop-down list.

Then select your Comparison type from the drop-down list.

target Filter Types

Comparison Type Description Usage Scenario
Equals (=) Filters out results to only include records with the same column value. Find all employees with the name ‘Steve’.
Greater than (>) Filters results to only include records that are greater than the provided column value. Find all customers over the age of 50.
Less than (<) Filters results to only include records that are less than the provided column value. Find all products that have sold less than 10,000 items.
Not equal to (<>) Filters user’s results so that it does not include records of the provided value. Find all sales made that day that were not sold with a discount.
Similar results (LIKE) Filters results to provide records similar to the provided value. Find products with a name that contains ‘bar’.

For the LIKE operator, Data Governor Online Supports standard SQL wildcard patterns as explained here.

Then provide a Value to filter the columns.

Add the filter using the Add Filter button beside it.

It will appear below (if there is more than one filter it will appear in list form), and you can delete filters using the button beside it.

Save the filters and once you submit the task, you will import only the rows that are relevant to your filters.

You can view how many rows were migrated in the Execution log.

Execution log

You can edit filters by clicking on Edit Task and going back to the Target Filter menu.

View and Edit Query

When editing a Task you can view and edit the Source Query by clicking the View Query in the Target section next to your selected Source Query, and in this pop up window you can then edit the query.

View query