AWS Database Migration Service (DMS) MongoDB to CSV in S3

Question

I need to use AWS Database Migration Service (DMS) to move data from MongoDB to CSV in S3. I can export to CSV in S3 using the table mode and include a header row, however nested arrays within the MongoDB document are being exported as a string breaking the CSV structure:

Mongo:

{ "_id" : 1234,
  "dealershipName": "Bills",
   "cars": [
       {"year": 2013,
        "model": "MongoCar",
        "mechanicNotes": "Runs great!"},
       {"year": 1985,
        "model": "DMC-12",
        "mechanicNotes": "Great Scott!"}
 ]
}

CSV in S3:

124,"Bills","[{"year":2013", .........}]",

Can anyone advise how to export the nested arrays inside MongoDB documents to CSV using DMS while maintaining the CSV format (i.e not having a string of the array as a field in the CSV, but broken out into fields - maybe in a separate CSV somehow)?

score 0 · Accepted Answer · answered Dec 04 '20 at 10:29

It's not supported yet. AWS DMS doesn't have much-advanced features to parse array into separate columns and rows. You should use some other ETL tools to do so like

Pentaho is opensource you can use it to set source (MongoDB) and target (AWS S3)
If you want to be in AWS then use AWS Glue for ETL and transformation.
Stitch data ETL tool is best, within a few clicks you can do so.

AWS Database Migration Service (DMS) MongoDB to CSV in S3

1 Answers1