In this exercise, we will set up Database Migration Service (DMS) jobs to migrate data from source MySQL database (relational view, tables) to Amazon DynamoDB.
Go back to AWS Console, AWS Database Migration Service screen, click on Endpoints and Create endpoint button
Create the source endpoint. Use the following parameters to configure the endpoint:
Parameter | Value |
---|---|
Endpoint type | Source endpoint |
Endpoint identifier | mysql-endpoint |
Source engine | MySQL |
Server name | From the EC2 dashboard, select MySQL-Instance and copy Public IPv4 DNS |
Port | 3306 |
SSL mode | none |
User name | Value of DbMasterUsername added as parameter during Configure MySQL Environment |
Password | Value of DbMasterPassword added as parameter during Configure MySQL Environment |
Open Test endpoint connection (optional) section, then in the VPC drop-down select DMS-VPC and click the Run test button to verify that your endpoint configuration is valid. The test will run for a minute and you should see a successful message in the Status column. Click on the Create endpoint button to create the endpoint.
Create the target endpoint. Repeat all steps to create the target endpoint with the following parameter values:
Parameter | Value |
---|---|
Endpoint type | Target endpoint |
Endpoint identifier | dynamodb-endpoint |
Target engine | Amazon DynamoDB |
Service access role ARN | CloudFormation template has created new role with full access to Amazon DynamoDB. Copy Role ARN from dynamodb-access role |
Open Test endpoint connection (optional) section, then in the VPC drop-down select DMS-VPC and click the Run test button to verify that your endpoint configuration is valid. The test will run for a minute and you should see a successful message in the Status column. Click on the Create endpoint button to create the endpoint.
Still in the AWS DMS console, go to Database migration tasks and click the Create Task button. We will create 3 replication jobs to migrate denormalized view, ratings (title_ratings) and regions/languages (title_akas) information.
Parameter | Value |
---|---|
Task identified | historical-migration01 |
Replication instance | mysqltodynamodb-instance-* |
Source database endpoint | mysql-endpoint |
Target database endpoint | dynamodb-endpoint |
Migration type | Migrate existing data |
Task settings: Editing mode | Wizard |
Task settings: Target table preparation mode | Do nothing |
Task settings: Enable CloudWatch logs | Checked |
Table mappings: Editing mode | Select JSON editor option and follow the instructions after below screenshots |
In this section we will create Table mappings JSON document. This document includes source to target mapping including any transformation on the records that will be performed during migration. To reduce the loading time during Immersion Day, we have narrowed down the migration list to selective movies. Below JSON document has list of 28 movies worked by Clint Eastwood. The remaining exercise will just focus on these movies. However, feel free to load remaining data in case you like to further explore. Some statistics around full dataset is give at the bottom of this chapter.
Copy list of selective movies by Clint Eastwood.
{
"filter-operator": "eq",
"value": "tt0309377"
},
{
"filter-operator": "eq",
"value": "tt12260846"
},
{
"filter-operator": "eq",
"value": "tt1212419"
},
{
"filter-operator": "eq",
"value": "tt1205489"
},
{
"filter-operator": "eq",
"value": "tt1057500"
},
{
"filter-operator": "eq",
"value": "tt0949815"
},
{
"filter-operator": "eq",
"value": "tt0824747"
},
{
"filter-operator": "eq",
"value": "tt0772168"
},
{
"filter-operator": "eq",
"value": "tt0498380"
},
{
"filter-operator": "eq",
"value": "tt0418689"
},
{
"filter-operator": "eq",
"value": "tt0405159"
},
{
"filter-operator": "eq",
"value": "tt0327056"
},
{
"filter-operator": "eq",
"value": "tt2310814"
},
{
"filter-operator": "eq",
"value": "tt2179136"
},
{
"filter-operator": "eq",
"value": "tt2083383"
},
{
"filter-operator": "eq",
"value": "tt1924245"
},
{
"filter-operator": "eq",
"value": "tt1912421"
},
{
"filter-operator": "eq",
"value": "tt1742044"
},
{
"filter-operator": "eq",
"value": "tt1616195"
},
{
"filter-operator": "eq",
"value": "tt6997426"
},
{
"filter-operator": "eq",
"value": "tt6802308"
},
{
"filter-operator": "eq",
"value": "tt3513548"
},
{
"filter-operator": "eq",
"value": "tt3263904"
},
{
"filter-operator": "eq",
"value": "tt3031654"
},
{
"filter-operator": "eq",
"value": "tt8884452"
}
Below JSON document will migrate denormalized view from imdb MySQL database (Task identified: historical-migration01). Replace the string “REPLACE THIS STRING BY MOVIES LIST” with list of movies copied earlier (Checkout following screenshot for any confusion).
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "imdb",
"table-name": "movies",
"table-type": "view"
},
"rule-action": "include",
"filters": [
{
"filter-type": "source",
"column-name": "tconst",
"filter-conditions": ["REPLACE THIS STRING BY MOVIES LIST"]
}
]
},
{
"rule-type": "object-mapping",
"rule-id": "2",
"rule-name": "2",
"rule-action": "map-record-to-record",
"object-locator": {
"schema-name": "imdb",
"table-name": "movies",
"table-type": "view"
},
"target-table-name": "movies",
"mapping-parameters": {
"partition-key-name": "mpkey",
"sort-key-name": "mskey",
"exclude-columns": [],
"attribute-mappings": [
{
"target-attribute-name": "mpkey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "${tconst}"
},
{
"target-attribute-name": "mskey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "DETL|${category}|${ordering}"
}
]
}
}
]
}
Go to the bottom and click on Create task. At this point the task will create will automatically start loading selective movies from source to target DynamoDB table.
You can move forward and create two more tasks with similar steps (historical-migration02 and historical-migration03).
Keep rest of the parameter as is except the JSON document. For historical-migration02 and historical-migration03 tasks use the JSON document mentioned below.
Below JSON document will migrate title_akas table from imdb MySQL database (Task identified: historical-migration02) Replace the string “REPLACE THIS STRING BY MOVIES LIST” with list of movies copied earlier.
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "imdb",
"table-name": "title_akas",
"table-type": "table"
},
"rule-action": "include",
"filters": [
{
"filter-type": "source",
"column-name": "titleId",
"filter-conditions": ["REPLACE THIS STRING BY MOVIES LIST"]
}
]
},
{
"rule-type": "object-mapping",
"rule-id": "2",
"rule-name": "2",
"rule-action": "map-record-to-record",
"object-locator": {
"schema-name": "imdb",
"table-name": "title_akas",
"table-type": "table"
},
"target-table-name": "movies",
"mapping-parameters": {
"partition-key-name": "mpkey",
"sort-key-name": "mskey",
"exclude-columns": [],
"attribute-mappings": [
{
"target-attribute-name": "mpkey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "${titleId}"
},
{
"target-attribute-name": "mskey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "REGN|${region}"
}
]
}
}
]
}
Below JSON document will migrate title_ratings table from imdb MySQL database (Task identified: historical-migration03) Replace the string “REPLACE THIS STRING BY MOVIES LIST” with list of movies copied earlier.
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "imdb",
"table-name": "title_ratings",
"table-type": "table"
},
"rule-action": "include",
"filters": [
{
"filter-type": "source",
"column-name": "tconst",
"filter-conditions": ["REPLACE THIS STRING BY MOVIES LIST"]
}
]
},
{
"rule-type": "object-mapping",
"rule-id": "2",
"rule-name": "2",
"rule-action": "map-record-to-record",
"object-locator": {
"schema-name": "imdb",
"table-name": "title_ratings",
"table-type": "table"
},
"target-table-name": "movies",
"mapping-parameters": {
"partition-key-name": "mpkey",
"sort-key-name": "mskey",
"exclude-columns": [],
"attribute-mappings": [
{
"target-attribute-name": "mpkey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "${tconst}"
},
{
"target-attribute-name": "mskey",
"attribute-type": "scalar",
"attribute-sub-type": "string",
"value": "RTNG"
}
]
}
}
]
}
The replication job for historical migration will start moving data from MySQL imdb.movies view, title_akas and title_ratings to DynamoDB table will start in a few minutes. If you are loading selective records based on the list above, it may take 5-10 minutes to complete all three jobs. For full loading below are the statistics.