MogDB
Ecological Tools

Example files

Example

{
   --  Configuration information of the source database
  "source": {
    -- Source database type, which is case-insensitive, supports MySQL, Oracle, DB2, and openGauss.
    "type": "oracle",
    -- Connection information of the source database
    "connect": {
      -- The source database version does not need to be specified, which is automatically queried during database connection.
      "version": "11.2.0.4.0",
      -- The host where the source database is located. The value can be the IP address, host name, or domain name.
      "host": "192.168.56.65",
      -- The source database user must has the permission to query the data dictionary and data.
      "user": "system",
      -- Port of the source database
      "port": 1521,
      -- Password of the source database user
      "password": "oracle",
      -- Name of the source database. Databases of some types may not need it.
      -- Database name of Mysql/Postgres/Opengauss/DB2
      -- Oracle Service Name
      -- Oracle     Default : orcl
      -- Mysql      Default : mysql
      -- Postgre    Default : postgres
      -- openGauss  Default : postgres
      "dbName": "orcl",
      -- Complete string for connecting to the source database, which is used in specified scenarios.
      "dsn": ""
    },
  },
  --  Configuration information of the target database
  "target": {
    -- Target database type, which is case-insensitive and supports MySQL, Oracle, Postgres, openGauss, and file.
    "type": "postgres",
    -- Connection information of the target database
    "Connect": {
      -- The target database version does not need to be specified, which is automatically queried during database connection.
      "version": "12.3",
      -- The host where the target database is located. The value can be the IP address, host name, or domain name.
      "host": "127.0.0.1",
      -- The user of the target database must have the permission to create schema, sequences, tables, indexes, constraints, views and others.
      "user": "postgres",
      -- Port of the target database
      "port": 5433,
      -- Password of the target database user
      "password": "root",
      -- Name of the target database. Databases of some types may not need it.
      -- Database name of Mysql/Postgres/Ppengauss/DB2
      -- Oracle Service Name ,
      "dbName": "postgres",
      -- Complete string for connecting to the target database, which is used in specified scenarios.
      "dsn": ""
    },
    -- Related parameters
    "parameter": {
      -- Deletes the existing object. Please manually delete it if certain risk may occur.
      "dropExistingObject":false,
      -- Truncates table. Used for data conflict when only data is migrated
      "truncTable": true,
      -- Indicates the directory for storing the file to which the data is to be exported.
      "path": "./data/postgres",
      -- When data is exported to a file, the file types can be CSV and SQL.
      "fileType": "sql",
      -- When data is exported to a file, if the file size is not specified, the file size is not limited.
      "fileSize": "500MiB",
      -- When data is exported a file of the SQL type, the SQL file will be generated.
      -- If data is migrated from Oracle to MySQL, the MySQL data insertion syntax is generated by default.
      "sqlDBType": "oracle",
      -- When data is exported to a CSV file, the file will include a CSV header. By default, the header is not exported.
      "csvHeader": true,
      -- Default CSV delimiter
      "csvFieldDelimiter": "|",
      -- CSV Optionally Enclosed
      "csvOptionallyEnclosed": "'",
      -- Parallel insert in the test phase
      "parallelInsert": 1,
      -- Time format
      "timeFormat": "HH:MI:SS",
      -- Date format
      "dateFormat": "YYYY-MM-DD",
      -- Date time format
      "dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
      -- When this parameter is configured, the partition tables not supported will be converted to common tables, and the unsupported attributes of the subpartition tables will be automatically removed.
      -- The List and Hash partitioned tables in Oracle are common tables in openGauss.
      -- The composite range-list partitioned table in Oracle is a range partitioned table in MogDB. The subpartition tables will be automatically removed.
      "noSupportPartTabToNormalTab": false,
      -- In the version that does not support virtual columns, virtual columns are converted to normal columns
      "virtualColToNormalCol": false,
      -- Automatically skip over the MySQL wrong time, such as the datetime of 0000-00-00 13:14:15.
      "mySQLSkipErrorDateTimeData": false,
      -- Newly add the table structure comparison function, which prevents comparison bugs from being ignored when this parameter is added because the bugs occur.
      "ignoreTableDDLCompErr": false,
      -- That the default value of a column is not supported is ignored, which is supported, such as sys_guid in Oracle.
      "ignoreNotSupportDefault": false,
      "remapSchema":{
        "MTK1":"MTK1_NEW",
        "SOE":"SOE_NEW"
      },
      -- During creation in the target database, "" or '' will be automatically added and the keyword will be operated with case conversion automatically.
      -- Format: Column name : Case identification.  1 indicates lowercase; 2 indicates uppercase; keep unchanged for others
      "colKeyWords": {
        "STREAM": 1,
        "TID": 1
      },
      -- Keyword of the same column name. Keyword of reserved objects
      "objKeyWords": {}"virtualColConv": {
        "TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) minutes": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME, 'HH') + (MINUTE(SNAPTIME) / 10 * 10 + 10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME,'HH') + (MINUTE(SNAPTIME) / 10 * 10 + 10) minutes": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "LEFT(HOST,POSSTR(HOST,':')-1)": "SPLIT_PART(HOST,':',1)"
      }
    }
  },
  "limit": {
    -- Indicates the degree of parallelism. This parameter is used only when table data is migrated. It will be later used during index creation.
    "parallel": 2,
    -- fetchSize indicates the fetch size (number of rows to be fetched from a result set every time)

This parameter can be used when a table contains several millions of rows and you need to avoid memory insufficiency.
    "fetchSize": 1000,
    -- batchSize indicates the batch size in batch insert.

The default value is 1000. If memory insufficiency occurs, lower the value.
    "batchSize": 1000,
    -- bufferSize is not used currently and can be ignored.
    "bufferSize": 4
  },
  "object": {
    -- Case-sensitive
    -- Migration user, database, and schema
    -- schema = mysql database
    -- schema = oracle user
    -- schema = postgres schema
    -- schema = db2 schema
    -- Note: Parameters **schemas** and **tables** are mutually exclusive.
If both parameters are configured, the error "mtk-1002 schema and table cannot exist together" will occur during migration.
    "schemas": [
      "SOE",
      "BT_X",
      "CUST_X"
    ],
    "tables": [
      "TABLE1",
      "MTK.TABLE2"
    ],
    -- Defines which tables are excluded. schema table
    "excludeTable":{
      "MTK":[
        "TABLE_SKIP1",
        "TABLE_SKIP2"
      ]
    },
    -- Manually defines the table granularity parallelism.
    "tableSplit":{
      "MTK":{
        "TABLE_4":[
          "ID < 10000 ",
          "ID < 20000 AND ID >=10000",
          "ID < 30000 AND ID >=20000",
          "ID < 40000 AND ID >=30000",
          "ID < 50000 AND ID >=40000",
          "ID < 60000 AND ID >=50000",
          "ID < 70000 AND ID >=60000",
          "ID < 80000 AND ID >=70000",
          "ID < 90000 AND ID >=80000",
          "ID >= 90000"
        ]
      }
    }
  },
  -- dataOnly indicates Whether to migrate only data.
  "dataOnly": false,
  -- Indicates Whether to migrate only the data structure.
  "schemaOnly": true,
  -- Disables the table data comparison function.
  "disableTableDataComp": false,
  -- Indicates the migration report directory. For MTK of a version earlier than v0.0.18, the migration report is shown in HTML. For MTK v0.0.18 or later, the migration report is stored in a directory.
  "reportFile": "./data/postgres/report_to_db.html",
  -- Indicates whether to enable the log debug mode.
  "debug": false
}

Oracle to openGauss

{
  "source": {
    "type": "oracle",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "system",
      "port": 1521,
      "password": "oracle",
      "dbName": "orcl",
      "dsn": ""
    },
    "parameter": {
      "debugTest": false
    }
  },
  "target": {
    "type": "opengauss",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "gaussdb",
      "port": 26000,
      "password": "mtkOP@128",
      "dbName": "postgres",
      "dsn": ""
    },
    "parameter": {
      "dropExistingObject":false,
      "truncTable": false,
      "path": "./data/oracle",
      "fileType": "sql",
      "fileSize": "500MiB",
      "csvHeader": true,
      "csvFieldDelimiter": "|",
      "csvOptionallyEnclosed": "'",
      "parallelInsert": 1,
      "timeFormat": "HH:MI:SS",
      "dateFormat": "YYYY-MM-DD",
      "dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
      "noSupportPartTabToNormalTab": false,
      "virtualColToNormalCol": false,
      "mySQLToOgCharExpression": "* 3",
      "mySQLSkipErrorDateTimeData": false,
      "ignoreTableDDLCompErr": true,
      "remapSchema":{
        "A1": "A1_1",
        "A2": "A2_1"
      },
      "colKeyWords": {},
      "objKeyWords": {}
    }
  },
  "limit": {
    "parallel": 4,
    "fetchSize": 1500,
    "batchSize": 1500,
    "bufferSize": 8
  },
  "object": {
    "tables": [
      "MTK.AAA"
    ],
    "schemas": [
      "MTK"
    ],
    "excludeTable":{
      "MTK":[
        "TABLE_SKIP1",
        "TABLE_SKIP2"
      ]
    },
    "tableSplit":{
      "MTK":{
        "TABLE_4":[
          "ID < 10000 ",
          "ID < 20000 AND ID >=10000",
          "ID < 30000 AND ID >=20000",
          "ID < 40000 AND ID >=30000",
          "ID < 50000 AND ID >=40000",
          "ID < 60000 AND ID >=50000",
          "ID < 70000 AND ID >=60000",
          "ID < 80000 AND ID >=70000",
          "ID < 90000 AND ID >=80000",
          "ID >= 90000"
        ]
      }
    }

  },
  "dataOnly": false,
  "schemaOnly": false,
  "disableTableDataComp": false,
  "reportFile": "./report",
  "debug": false
}

DB2 to openGauss

{
  "source": {
    "type": "db2",
    "connect": {
      "host": "127.0.0.1",
      "user": "db2inst1",
      "port": 50000,
      "password": "db2inst1",
      "dbName": "db2inst1"
    }
  },
  "target": {
    "type": "opengauss",
    "connect": {
      "host": "127.0.0.1",
      "user": "gaussdb",
      "port": 26000,
      "password": "gasssdb@123",
      "dbName": "postgres"
    },
    "parameter": {
      "dropExistingObject": false,
      "truncTable": false,
      "caseSensitive": 0,
      "quoteMark": false,
      "path": "./data/db2",
      "fileType": "",
      "fileSize": "",
      "csvHeader": false,
      "csvNullValue": "NULL",
      "csvFieldDelimiter": ",",
      "csvOptionallyEnclosed": "\"",
      "parallelInsert": 1,
      "remapSchema": {},
      "remapTable": {},
      "timeFormat": "HH:MI:SS",
      "dateFormat": "YYYY-MM-DD",
      "dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
      "noSupportPartTabToNormalTab": false,
      "virtualColToNormalCol": false,
      "mySQLSkipErrorDateTimeData": false,
      "ignoreTableDDLCompErr": false,
      "ignoreDB2PartINCLUSIVE": false,
      "ignoreNotSupportDefault": false,
      "colKeyWords": {},
      "objKeyWords": {},
      "virtualColConv": {
        "TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME,'HH')+ (MINUTE(SNAPTIME)/10*10 +10) minutes": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME, 'HH') + (MINUTE(SNAPTIME) / 10 * 10 + 10) MINUTES": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "TRUNC_TIMESTAMP(SNAPTIME,'HH') + (MINUTE(SNAPTIME) / 10 * 10 + 10) minutes": "date_trunc('hour',snaptime) + (date_part('minute',snaptime) / 10 +1)::int * interval '10 min'",
        "LEFT(HOST,POSSTR(HOST,':')-1)": "SPLIT_PART(HOST,':',1)"
      }
    }
  },
  "limit": {
    "parallel": 4,
    "fetchSize": 2000,
    "batchSize": 2000,
    "bufferSize": 8,
    "cpBufferSize": 8,
    "oracleSelectParallel": 2,
    "channelCacheNum": 10000
  },
  "object": {
    "tables": [],
    "schemas": [
      "SYSIBM"
    ],
    "excludeTable": {
      "MTK": [
        "MTK_TAB1",
        "MTK_TAB2"
      ],
      "SYS": [
        "TAB$",
        "OBJ$"
      ]
    },
    "tableSplit": {
      "MTK": {
        "TAB_1": [
          " rowid between 'AAAeoSAAEAAAACpAAA' and 'AAAeoSAAEAAAA4oEI/'",
          " rowid between 'AAAeoSAAEAAAA4pAAA' and 'AAAeoSAAEAABVSoEI/'",
          " rowid between 'AAAeoSAAEAABVSpAAA' and 'AAAeoSAAFAABOEoEI/'",
          " rowid between 'AAAeoSAAFAABOEpAAA' and 'AAAeoSAAFAABOGIEI/'"
        ],
        "TAB_2": [
          "MOD(\"ID\",4)=0",
          "MOD(\"ID\",4)=1",
          "MOD(\"ID\",4)=2",
          "MOD(\"ID\",4)=3"
        ],
        "TAB_3": [
          "MOD(`ID`,4)=0",
          "MOD(`ID`,4)=1",
          "MOD(`ID`,,4)=2",
          "MOD(`ID`,4)=3"
        ]
      }
    }
  },
  "dataOnly": false,
  "schemaOnly": false,
  "reportFile": "./report",
  "debug": false
}

MySQL to openGauss

{
  "source": {
    "type": "mysql",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "root",
      "port": 3306,
      "password": "root",
      "dbName": "mysql",
      "dsn": ""
    }
  },
  "target": {
    "type": "openGauss",
    "connect": {
      "version": "",
      "host": "127.0.0.1",
      "user": "gaussdb",
      "port": 26000,
      "password": "gaussdb@123",
      "dbName": "postgres",
      "dsn": ""
    },
    "parameter": {
      "dropExistingObject":false,
      "truncTable": false,
      "path": "./data/mysql",
      "fileType": "sql",
      "fileSize": "500MiB",
      "csvHeader": true,
      "csvFieldDelimiter": "|",
      "csvOptionallyEnclosed": "'",
      "parallelInsert": 1,
      "timeFormat": "HH:MI:SS",
      "dateFormat": "YYYY-MM-DD",
      "dateTimeFormat": "YYYY-MM-DD HH24:MI:SS",
      "noSupportPartTabToNormalTab": false,
      "virtualColToNormalCol": false,
      "mySQLToOgCharExpression": "* 3",
      "mySQLSkipErrorDateTimeData": false,
      "ignoreTableDDLCompErr": true,
      "remapSchema":{
      },
      "colKeyWords": {},
      "objKeyWords": {}
    }
  },
  "limit": {
    "parallel": 4,
    "fetchSize": 1000,
    "batchSize": 25000,
    "bufferSize": 32
  },
  "object": {
    "tables": [
    ],
    "schemas": [
      "mtk"
    ],
    "querySQL": [],
    "excludeTable":{
      "MTK":[
        "TABLE_SKIP1",
        "TABLE_SKIP2"
      ]
    },
    "tableSplit":{
      "MTK":{
        "TABLE_4":[
          "ID < 10000 ",
          "ID < 20000 AND ID >=10000",
          "ID < 30000 AND ID >=20000",
          "ID < 40000 AND ID >=30000",
          "ID < 50000 AND ID >=40000",
          "ID < 60000 AND ID >=50000",
          "ID < 70000 AND ID >=60000",
          "ID < 80000 AND ID >=70000",
          "ID < 90000 AND ID >=80000",
          "ID >= 90000"
        ]
      }
    }
},
  "dataOnly": false,
  "schemaOnly": false,
  "reportFile": "./report"
  "debug": false
}