I have a java microservice where I’m using MySql as database.

For multiple reasons I have to change sometimgs on it and I would like to change the MySql database to a Dynamodb database.

Currently on my sql db I already stored some data and I’m using an UUID string as primary key.

The question is, is it possible to migrate the data from MySql to Dynamo db with a possibility to reuse these already geberated primary keys?

I mean, my idea is to write a script in order to migrate the data… but is there any possibility or problems reusing the uuid primary keys?

CodePudding user response：

Dynamo is a NoSql db. That means it does not have tables and relationships. It has two keys, Partition and Sort, and the combination of these two make up a Primary Composite key (though you can just do a single Partition Key)

If you do not have a Sort Key assigned, then you Cannot re-use partition keys (uuid in your case) as it will overwrite (if using update) the existing item, or refuse to add (if using put_item)

In addition, because Dynamo is a noSQL, you do not have multiple tables. You have a single table. You cannot build multiple dynamo tables and make JOINS across them either - they are all separate entities

Not saying that moving data from an SQL to a Dynamo is impossible - but you have a lot of things to consider. One of the main things is how you access data. Since you cannot do JOINS and the like on a Dyanmo, you can't make any custom query you want whenever you want. The way your data is stored in dynamo determines how you would retrieve it - to expand, you want to store your data in a dynamo along Access Patterns - i.e. I always have data a, and I want to use it to get information, perhaps filtered along some operations against data b (such as > or <)

Simply having a Partition Key of a UUID is entirely possible, but it means that unless you choose to Scan the table (which touches every item, and is very expensive in time and potentially money if you have a lot of data) you MUST have that uuid in order to access the data that is associated with it.

SQL dbs and Dynamo sever two very different purposes in database storage. Dynamo is great for fast, quick reads where you know the starting data every time - Say you have a username, and you use that as your partition key, and then your sort keys are all the different data of the user: the avatar information, their access information, their stats on a game - you want to display all their games, you can query against user: games: and receive a list of data where each game is an entry.

SQLs are much better if you don't know what your access pattern is going to be. If you are not sure exactly what you may need to look up at any given time, SQL is far better, because of the ability to craft different queries at any point using JOINS and the like in order to manage data however you feel you might need it at that given point. Dynamo cannot do this - once you have your access patterns set, adding new ones is very annoying, and potentially costly, and trying to get data sets out of the table that it was never designed to be able to provide is always a difficult afair.