CODEWITHSUNDEEP
mysqlgoogle-app-enginegoogle-cloud-platformgoogle-cloud-datastore
danielz
danielz

Reputation: 1767

Migrate data from MySQL with auto-increment Ids to the Google Datastore?

I am trying to migrate some data from MySql to the Datastore. I have a table called User with auto-increment primary keys (Bigint(20)). Now I want to move the data from the User table to the datastore.

My plan was let the Datastore generate new Ids for the migrated users and all the new user created after the migration is done. However we have many services (notifications, urls etc) that depend on the old ids. So I want to use the old ids for the migrated user, however how can I guarantee that all new generated ids won't collide with the migrated Ids?

Upvotes: 1

Views: 629

Answers (2)

Jeffrey Rennie
Jeffrey Rennie

Reputation: 3443

Record the maximum and minimum ids before migrating. Migrate all the sql rows to datastore entities, setting entity.key.id = sql.row.id.

To prevent new datastore ids from colliding with the old ones, always call AllocateIds() to allocate new ids. In C#, the code looks like this:

        Key key;
        Key incompleteKey = _db.CreateKeyFactory("Task").CreateIncompleteKey();
        do
        {
            key = _db.AllocateId(incompleteKey);
        } while (key.Path[0].Id >= minOldId && key.Path[0].Id <= maxOldId);
        // Use new key for new entity.

In reality, you are more likely to win the lottery than to see a key collide, so it won't cost anything more to check against the range of old ids.

Upvotes: 1

Sai Pullabhotla
Sai Pullabhotla

Reputation: 2207

You cannot hint/tell the Datastore to reserve specific IDs. So, if you manually set IDs when inserting existing data, and later have the Datastore assign an ID, it my pick an ID that you have already used. Depending on the operation you are using (e.g. INSERT or UPSERT), the operation may fail or overwrite the existing entity.

You need to come up with a migration plan to map existing IDs to Datastore IDs. Depending on the number of tables you have and the complexity of relations between them, this could become a time consuming project, but you should still be able to do it.

Let's take a simple example and assume you have two tables:

USER (USER_ID is primary key) USER_DATA (USER_ID is foreign key)

You could possibly add another column to USER (or another way) to map the USER_ID to DATASTORE_ID. Here, you call Datastore's allocateID method for the Kind you want to use and store the returned ID into the new column.

Now, you can move USER data to Cloud Datastore ignoring the MySQL User ID, instead use the ID from the new column.

To migrate the data from USER_DATA, do a join between the two tables and push the data using datastore ID.

Also, note that using sequential IDs (referred to as monotonically increasing values) could cause performance issues with Datastore. So, you probably want to use IDs that are generated by the Datastore.

Upvotes: 0

Related Questions