Copying data from MySQL to Amazon DynamoDB

Question

I have a table in MySQL Containing 500 million records. I want to import this table to Amazon DynamoDB.I understand there are two ways to do it:

1. JAVA Api: The problem with this approach is that it is slow, also the connection to database gets dropped sometimes.

2. Amazon Data Import Pipeline : Seems promising, But how do I export the data from MySQL to the format recognized by DynamoDB?

Please let me the best possible approach between the two.

Answer 1

I found the easiest way for me was to write a script to transfer all information into a json file in the format specified here : AWS Load Data

{
    "ProductCatalog": [
        {
            "PutRequest": {
                "Item": {
                    "Id": {
                        "N": "101"
                    },
                    "Title": {
                        "S": "Book 101 Title"
                    },
                    "ISBN": {
                        "S": "111-1111111111"
                    },
                    "Authors": {
                        "L": [
                            {
                                "S": "Author1"
                            }
                        ]
                    },
                    "Price": {
                        "N": "2"
                    },
                    "Dimensions": {
                        "S": "8.5 x 11.0 x 0.5"
                    },
                    "PageCount": {
                        "N": "500"
                    },
                    "InPublication": {
                        "BOOL": true
                    },
                    "ProductCategory": {
                        "S": "Book"
                    }
                }
            }
        },
        {
            "PutRequest": {
                "Item": {
                    "Id": {
                        "N": "103"
                    },
                    "Title": {
                        "S": "Book 103 Title"
                    },
                    "ISBN": {
                        "S": "333-3333333333"
                    },
                    "Authors": {
                        "L": [
                            {
                                "S": "Author1"
                            },
                            {
                                "S": "Author2"
                            }
                        ]
                    },
                    "Price": {
                        "N": "2000"
                    },
                    "Dimensions": {
                        "S": "8.5 x 11.0 x 1.5"
                    },
                    "PageCount": {
                        "N": "600"
                    },
                    "InPublication": {
                        "BOOL": false
                    },
                    "ProductCategory": {
                        "S": "Book"
                    }
                }
            }
        },
        {
            "PutRequest": {
                "Item": {
                    "Id": {
                        "N": "205"
                    },
                    "Title": {
                        "S": "18-Bike-204"
                    },
                    "Description": {
                        "S": "205 Description"
                    },
                    "BicycleType": {
                        "S": "Hybrid"
                    },
                    "Brand": {
                        "S": "Brand-Company C"
                    },
                    "Price": {
                        "N": "500"
                    },
                    "Color": {
                        "L": [
                            {
                                "S": "Red"
                            },
                            {
                                "S": "Black"
                            }
                        ]
                    },
                    "ProductCategory": {
                        "S": "Bicycle"
                    }
                }
            }
        }
    ]
}

and then create the tables and run the code from my console

aws dynamodb batch-write-item --request-items file://ProductCatalog.json

To download and configure aws cli :https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/Tools.CLI.html

Answer 2

In addition to other answer, I would like to mention that dynamodb recognizes csv or tsv format files for importing. We can also use HIVE sql using Elastic Map Reduce to bulk load data from a csv file. The only thing we need to consider is - if we use windows to dump table to csv then we must make sure that the line ending of windows system \r\n be replaced by \n to make it compliant with amazon.

Answer 3

If you have a portal like PhpMyAdmin you can easily export your data to a JSON format.

Next you create the table manually.

Finally, with Dynabase you can import JSON files to DynamoDB tables. Even though Dynabase is a commercial tool, it does unlock all required features even with the trial license.

The MySQL JSON export sometimes also stores additional information about the table. If necessary, I do edit the file manually to just keep the data-portion of the file. The JSON files that I import have an array in their root, e.g.

[ 
  { "id": 1 , "foo" : "bar" }, 
  { "id": 361, "foo" : "baz" } 
]

You probably don't need to convert the format of your data.