Our solution allows to perform data-migration in fully automatic mode by calling web-API methods. The samples with the basic curl commands below:
export host=172.31.23.191
export username=admin
export pass=admin
...
Sample workflow creation and text version of these instructions attached
| View file | ||
|---|---|---|
|
| View file | ||
|---|---|---|
|
#01 Login
curl "http://:8086$host/webresources/user/login" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"name":"$username",
"password":"$pass"
}"
...
curl "http://:8086$host/webresources/connections/1" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"name":"$dbSource",
"type":$type,
"agent":"Manager_with_Agent",
"host":"$src_db_host",
"port":$port,
"dbname":"$dbname",
"userName":"$db_user",
"password":"$db_pass"
}"
Values for Type
1 - MySQL
2 - PostgreSQL
3 - Oracle
4 - CSV
04 Assign db connection to specific workflow (as source or/and destination) - Options section can be removed
...
curl "http://:8086$host/webresources/workflows/1/$workflow_id" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM="
10
...
curl "http://:8086$host/webresources/workflows/1/$workflow_id" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"id" : $workflow_id,
"migrationStatus" : 2,
"name" : "$workflow_name",
"source" : $dbsourceid,
"destination" : $dbdestid,
"options" : {
"overwrite" : "Databases",
"numOfProcesses" : 4,
"stagingDirectory" : "stage",
"logDirectory" : "log",
"continueOnErrors" : true,
"preScript" : null,
"postScript" : null
},
"databases" : [ {
"isActive" : true,
"name" : "$SRCschemaName",
"destinationName" : "$DSTschemaName"
} ],
"tables" : [ {
"isActive" : true,
"destinationDbName" : "$DSTschemaName",
"schema" : "",
"tableName" : "$sourceTableName",
"destinationTableName" : "$destinationTableName",
"whereClause" : null,
"isMasked" : false,
"isChunked" : false,
"dbname" : "$SRCschemaName"
} ]
}"
...
Get the schema for a specific table
curl "http://:8086$host/webresources/tablemasking/1/$workflow_id/getSingleTableMasking" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"wfName": "$workflow_name",
"sourceDatabaseName": "$dbSourcename",
"sourceTableName": "$dbsourcetable"
}"
...
11 Mask specific columns
curl "http://:8086$host/webresources/tablemasking/1/$workflow_id/updateSingleTableMasking" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"wfName" : "$workflow_name",
"connectioName" : null,
"sourceDatabaseName" : "$dbSourcename",
"schemaName" : "",
"sourceTableName" : "$dbsourcetable",
"columns" : [ {
"columnName" : "$columnname",
"methodId" : $maskingmethodid,
"methodName" : "$maskingmethodname",
"parameters" : null,
"active" : true
} ]
}"
...
12 run the workflow
curl "http://:8086$host/webresources/migrations/1/$workflow_id/run" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{"run_mode":null}"
...
13 Check the status of the workflow
curl "http://:8086$host/webresources/migrations/1/$workflow_id" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM="
...
curl "http://:8086$host/webresources/workflows/1/$workflow_id" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM="
#Add a pre/postScript
curl "http://:8086$host/webresources/workflows/1/$workflowid" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"id":$workflowid,
"migrationStatus":2,
"source":$dbsourceid,
"destination":$dbdestid,
"options":
{
"overwrite":"Databases",
"numOfProcesses":4,
"stagingDirectory":"stage",
"logDirectory":"log",
"continueOnErrors":true,
"preScript":null,
"postScript":"UPDATE Users_table SET PASSWORD_FILE='password123';"},
| Code Block |
|---|
\"databases\":[],
\"tables\":[] |
}
}"
Update masking setup for the tables that you want to mask
curl "http://:8086$host/webresources/workflows/1/$workflow_id" -H "accept: application/json" -H "Content-Type: application/json" -H "Authorization: Basic YWRtaW46MjEyMzJGMjk3QTU3QTVBNzQzODk0QTBFNEE4MDFGQzM=" --data-raw "{
"id" : $workflow_id,
"migrationStatus" : 2,
"name" : "$workflow_name",
"source" : $dbsourceid,
"destination" : $dbdestid,
"options" : {
"overwrite" : "Databases",
"numOfProcesses" : 4,
"stagingDirectory" : "stage",
"logDirectory" : "log",
"continueOnErrors" : true,
"preScript" : null,
"postScript" : null
},
"databases" : [ {
"isActive" : true,
"name" : "$SRCschemaName",
"destinationName" : "$DSTschemaName"
} ],
"tables" : [ {
"isActive" : true,
"destinationDbName" : "$DSTschemaName",
"schema" : "",
"tableName" : "$sourceTableName",
"destinationTableName" : "$destinationTableName",
"whereClause" : null,
"isMasked" : false,
"isChunked" : false,
"dbname" : "$SRCschemaName"
} ]
}"