migrate mongodb table by specefic columns only to mysql using aws dms

Question

I have a schema named reports in mongo and a collection named totals. The keys in it looks like:

 { "_id" : { "dt" : "2018-12-02", "dt2" : "2018-04-08", "num" : 1312312312 }, "str" : 1 }

I would like to use DMS to migrate this collection into mysql instance on aws. The table should look like:

create table tab(
dt date, 
dt2 date, 
num bigint)

Currently, I'm using dms with simple rule:

{
  "rules": [
    {
      "rule-type": "transformation",
      "rule-id": "1",
      "rule-name": "1",
      "rule-target": "table",
      "object-locator": {
        "schema-name": "reports",
        "table-name": "totals"
      },
      "rule-action": "rename",
      "value": "tab",
      "old-value": null
    },
    {
      "rule-type": "selection",
      "rule-id": "2",
      "rule-name": "2",
      "object-locator": {
        "schema-name": "reports",
        "table-name": "totals"
      },
      "rule-action": "include",
      "filters": []
    }
  ]
}

The result is not what I wanted:

MySQL [stats]> desc tab;
+-------+----------+------+-----+---------+-------+
| Field | Type     | Null | Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| _doc  | longtext | YES  |     | NULL    |       |
+-------+----------+------+-----+---------+-------+

MySQL [(none)]> select * from tab limit 1;
+------------------------------------------------------------------------------------------+
| _doc                                                                                     |
+------------------------------------------------------------------------------------------+
| { "_id" : { "dt" : "2018-12-02", "dt2" : "2018-04-08", "num" : 1312312312 }, "str" : 1 } |
+------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Nir Nir · Accepted Answer · 2021-01-26T16:45:44

Endpoint needed to have nestingLevel=ONE; instead of nestingLevel=NONE;. Basically it means look at the data as a table instead of a document.

migrate mongodb table by specefic columns only to mysql using aws dms

1 Answers