ablog

不器用で落着きのない技術者のメモ

Oracle Database から Aurora MySQL互換 に DMS でデータをロードする

Oracle MySQL

Oracle Database から Aurora with MySQL Compatibility にテーブルのデータを AWS Database Migration Service (DMS) でフルロードしてみたメモ。

ロード後のソースDBとターゲットDBのデータ比較

  • ソースDB（Oracle Database）
SQL> select * from test_timestamp;

COL_TS                          COL_TS6                         COL_TS9
------------------------------- ------------------------------- -------------------------------
20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM 20-SEP-17 01.23.39.000000000 AM
20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM 20-SEP-17 01.23.54.502295000 AM
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> select * from TEST_TIMESTAMP;
+----------------------------+----------------------------+-------------------------------+
| COL_TS                     | COL_TS6                    | COL_TS9                       |
+----------------------------+----------------------------+-------------------------------+
| 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000 | 2017-09-20 01:23:39.000000000 |
| 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295 | 2017-09-20 01:23:54.502295000 |
+----------------------------+----------------------------+-------------------------------+
2 rows in set (0.01 sec)

テーブル定義の比較

  • ソースDB（Oracle Database）
SQL> info test_timestampTABLE: TEST_TIMESTAMP
	 LAST ANALYZED:2017-09-20 02:00:03.0
	 ROWS         :2
	 SAMPLE SIZE  :2
	 INMEMORY     :DISABLED
	 COMMENTS     :

Columns
NAME         DATA TYPE      NULL  DEFAULT    COMMENTS
 COL_TS      TIMESTAMP(6)   Yes
 COL_TS6     TIMESTAMP(6)   Yes
 COL_TS9     TIMESTAMP(9)   Yes
  • ターゲットDB(Aurora with MySQL Compatibility)
mysql> desc TEST_TIMESTAMP;
+---------+-------------+------+-----+---------+-------+
| Field   | Type        | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+-------+
| COL_TS  | datetime(6) | YES  |     | NULL    |       |
| COL_TS6 | datetime(6) | YES  |     | NULL    |       |
| COL_TS9 | varchar(37) | YES  |     | NULL    |       |
+---------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

手順

  • Oracle Database にテーブルを作成して、データを投入する。
create table test_timestamp(col_ts timestamp, col_ts6 timestamp(6), col_ts9 timestamp(9));
insert into test_timestamp values(sysdate, sysdate, sysdate);
insert into test_timestamp values(systimestamp, systimestamp, systimestamp);
commit;
  • Aurora with MySQL Compatibility にテーブルを作成する。
create table `test_timestamp` (
  `col_ts` datetime(6) default null,
  `col_ts6` datetime(6) default null,
  `col_ts9` varchar(37) default null
) engine=innodb default charset=utf8;

$ aws dms describe-replication-tasks --filters Name=replication-task-id,Values=test-timestamp
{
    "ReplicationTasks": [
        {
            "SourceEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskIdentifier": "test-timestamp",
            "ReplicationInstanceArn": "arn:aws:dms:ap-northeast-1:***************:rep:...",
            "TableMappings": "{\"rules\":[{\"rule-type\":\"selection\",\"rule-id\":\"1\",\"rule-name\":\"1\",\"object-locator\":{\"schema-name\":\"AWSUSER\",\"table-name\":\"TEST_TIMESTAMP\"},\"rule-action\":\"include\"},{\"rule-type\":\"transformation\",\"rule-id\":\"2\",\"rule-name\":\"2\",\"rule-target\":\"schema\",\"object-locator\":{\"schema-name\":\"AWSUSER\"},\"rule-action\":\"rename\",\"value\":\"mydb\"}]}",
            "ReplicationTaskStartDate": 1506052627.915,
            "ReplicationTaskStats": {
                "TablesLoading": 0,
                "TablesQueued": 0,
                "TablesErrored": 0,
                "FullLoadProgressPercent": 100,
                "TablesLoaded": 1,
                "ElapsedTimeMillis": 1413
            },
            "Status": "stopped",
            "ReplicationTaskArn": "arn:aws:dms:ap-northeast-1:***************:task:...",
            "StopReason": "Stop Reason FULL_LOAD_ONLY_FINISHED",
            "ReplicationTaskCreationDate": 1506052582.138,
            "MigrationType": "full-load",
            "TargetEndpointArn": "arn:aws:dms:ap-northeast-1:***************:endpoint:...",
            "ReplicationTaskSettings": "{\"TargetMetadata\":{\"TargetSchema\":\"\",\"SupportLobs\":false,\"FullLobMode\":false,\"LobChunkSize\":64,\"LimitedSizeLobMode\":true,\"LobMaxSize\":32,\"LoadMaxFileSize\":0,\"ParallelLoadThreads\":0,\"ParallelLoadBufferSize\":0,\"BatchApplyEnabled\":false},\"FullLoadSettings\":{\"TargetTablePrepMode\":\"DO_NOTHING\",\"CreatePkAfterFullLoad\":false,\"StopTaskCachedChangesApplied\":false,\"StopTaskCachedChangesNotApplied\":false,\"MaxFullLoadSubTasks\":8,\"TransactionConsistencyTimeout\":600,\"CommitRate\":10000},\"Logging\":{\"EnableLogging\":true,\"LogComponents\":[{\"Id\":\"SOURCE_UNLOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"SOURCE_CAPTURE\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_LOAD\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TARGET_APPLY\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"},{\"Id\":\"TASK_MANAGER\",\"Severity\":\"LOGGER_SEVERITY_DEFAULT\"}],\"CloudWatchLogGroup\":\"dms-tasks-replication-instance-1\",\"CloudWatchLogStream\":\"dms-task-R32JDUMJAF4ADCBYRWYGMN6PIE\"},\"ControlTablesSettings\":{\"historyTimeslotInMinutes\":5,\"ControlSchema\":\"\",\"HistoryTimeslotInMinutes\":5,\"HistoryTableEnabled\":false,\"SuspendedTablesTableEnabled\":false,\"StatusTableEnabled\":false},\"StreamBufferSettings\":{\"StreamBufferCount\":3,\"StreamBufferSizeInMB\":8,\"CtrlStreamBufferSizeInMB\":5},\"ChangeProcessingDdlHandlingPolicy\":{\"HandleSourceTableDropped\":true,\"HandleSourceTableTruncated\":true,\"HandleSourceTableAltered\":true},\"ErrorBehavior\":{\"DataErrorPolicy\":\"LOG_ERROR\",\"DataTruncationErrorPolicy\":\"LOG_ERROR\",\"DataErrorEscalationPolicy\":\"SUSPEND_TABLE\",\"DataErrorEscalationCount\":0,\"TableErrorPolicy\":\"SUSPEND_TABLE\",\"TableErrorEscalationPolicy\":\"STOP_TASK\",\"TableErrorEscalationCount\":0,\"RecoverableErrorCount\":-1,\"RecoverableErrorInterval\":5,\"RecoverableErrorThrottling\":true,\"RecoverableErrorThrottlingMax\":1800,\"ApplyErrorDeletePolicy\":\"IGNORE_RECORD\",\"ApplyErrorInsertPolicy\":\"LOG_ERROR\",\"ApplyErrorUpdatePolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationPolicy\":\"LOG_ERROR\",\"ApplyErrorEscalationCount\":0,\"ApplyErrorFailOnTruncationDdl\":false,\"FullLoadIgnoreConflicts\":true,\"FailOnTransactionConsistencyBreached\":false,\"FailOnNoTablesCaptured\":false},\"ChangeProcessingTuning\":{\"BatchApplyPreserveTransaction\":true,\"BatchApplyTimeoutMin\":1,\"BatchApplyTimeoutMax\":30,\"BatchApplyMemoryLimit\":500,\"BatchSplitSize\":0,\"MinTransactionSize\":1000,\"CommitTimeout\":1,\"MemoryLimitTotal\":1024,\"MemoryKeepTime\":60,\"StatementCacheSize\":50}}"
        }
    ]
}

$ aws dms start-replication-task --replication-task-arn arn:aws:dms:ap-northeast-1:***:task:*** --start-replication-task-type reload-target

参考