Import Config Module

Import Config Module

Use Case

Used as an import module to control which schemas, tables and a columns are read from the import module to the export module. This module allows also to add options for custom views, views materialization, table or view filtering and database related information such as users, roles, privileges, routines or table oriented like triggers and much more.

dbptk migrate -i import-config --import-file <path-to-import-config> -e <exportModule> [export module options]

Used as an export module creates the import, schemas and ignore sections template to be edited.

dbptk migrate -i <importModule> [import module options] -e import-config --export-file <path-to-import-config> 

File Structure

The file generated by the import-config module has Yaml syntax (more information). The file structure is divided in three different sections: import, schemas, and ignore.

Import section

This sections holds information about the import module that will be used. Some rules must be follow to fill up this section. The module name must be supported by DBPTK Developer. Parameters should appear as a key-value pair and all the specific module required parameters must be declared. Bellow you can find an import section configuration example for MySQL database.

import:
  module: "mysql"
  parameters:
    hostname: "localhost"
    port-number: "3306"
    username: "root"
    password: "123456"

Via SSH

import:
  module: "mysql"
  parameters:
    hostname: "localhost"
    port-number: "3306"
    username: "root"
    password: "123456"
    ssh: "true"
    ssh-host: "192.168.2.57"
    ssh-port: "22"
    ssh-user: "root"
    ssh-password: "123456"

Schemas section

This section allows to select which tables and views the user wants to import, define the set of columns to import from a certain table, or apply filtering to a table. For instance, the bellow configuration example will import the table actor from the schema sakila (more information) with the columns first_name, and last_name, a filter to include only rows in which last_update is between 2011/02/25 and 2011/02/27, and a sort by actor_id ascending. The where and sort parameters utilizes the SQL syntax. Will also import the view actor_info with the original columns but not materialized. By default DBPTK Developer will not materialized views unless the option is set as true.

schemas:
  sakila:
    tables:
      - name: "actor"
        columns:
        - name: "first_name"
        - name: "last_name"
        where: "last_update between '2011/02/25' and '2011/02/27'"
        sortBy: "actor_id ASC"
    views:
      - name: "actor_info"
        materialize: false
        columns:
        - name: "actor_id"
        - name: "first_name"
        - name: "last_name"
        - name: "film_info"
        where: ""
        sortBy: ""

External Lobs

DBPTK Developer allows to fetch and save a LOB that is stored outside the DBMS environment. However those columns must be referenced as pointers to the LOB location. In other words, they should be added to this configuration file. There are two parameters that can be defined for this type of referencing columns: base path and access method. The base path can be used in case columns contains relative paths. The access method can be one of this two types: file-system and remote-ssh.

Bellow you can find an example of how to configure a column to be interpreted as external LOB.

schemas:
  sakila:
    tables:
      - name: "table_with_external_lobs"
        columns:
        (...)
        - name: "column_reference"
          externalLOB:
            basePath: ""
            accessMethod: "file-system"
        (...)

For more information refer to External LOBs Filter Module page.

Merkle Tree Filter

By default every column is used in the calculation of the Merkle tree hash, although the user have the ability to choose which columns should be used for the Merkle tree calculation. For that just mark the desired columns with the property merkle to false.

Bellow you can find an example of how to configure a column to be ignored.

schemas:
  sakila:
    tables:
      - name: "actor"
        columns:
        - name: "first_name"
          merkle: false
        - name: "last_name"
        where: "last_update between '2011/02/25' and '2011/02/27'"
        sortBy: "actor_id ASC"
    (...)

Inventory Filter

Unlike the previous module, by default, all columns are disabled for the extraction of the DB records, and the user must indicate which columns must be marked to the filter can proceed with their extraction. For that just mark the desired columns with the property inventory to true.

Bellow you can find an example of how to configure a column to be extracted.

schemas:
  sakila:
    tables:
      - name: "actor"
        columns:
        - name: "actor_id"
        - name: "first_name"
          inventory: true
        - name: "last_name"
          inventory: true
        - name: "last_update"
        where: "last_update between '2011/02/25' and '2011/02/27'"
        sortBy: "actor_id ASC"
    (...)

Advanced configuration

Custom Views

This section allows to configure a set of queries that will behave as a materialized view. This must be included at tables or views level as shown in the example bellow.

schemas:
  sakila:
    custom:
      - name: <table_name>
        description: <description_content>
        query: <sql_query>
    tables:
      - name: actor
      (...)

Variables

As for DBPTK Developer version 2.8.0 it is now possible to define variables inside the import-config module that can be translated through arguments passed via command line. For instance, lets define the variables START_DATE and END_DATE in the import-config YAML file. Bellow you can find an example of how to do it.

schemas:
  sakila:
    tables:
      - name: "actor"
        columns:
        - name: "first_name"
        - name: "last_name"
        where: "last_update between '{{START_DATE}}' and '{{END_DATE}}'"
        sortBy: "actor_id ASC"
    (...)

In order to substitute the variable with the actual values use the following command:

dbptk migrate -i import-config -if <path> -ip START_DATE:2011/02/25;END_DATE:2011/02/25 -e siard-2 -ef <path>

Ignore section

This section has options to ignore a set of DBMS-related information. By default none is ignored. The example bellow list all the DBMS-related information that can be ignored.

‼️ Be aware that if views is set to true will override any other configuration set in the schemas section.

ignore:
  users: false
  roles: false
  privileges: false
  routines: false
  triggers: false
  primaryKeys: false
  candidateKeys: false
  foreignKeys: false
  checkConstraints: false
  views: false

Example

Examples of import-config module files can be consulted here.