Best way to migrate (and Transform) data from Azure Table Storage to Cosmos DB Sql API

Question

I have a Azure Table that is storing a Customer object with a nested address object as per following.

public class Customer {
   public Guid Id { get; set; }
   public string Name { get; set; }
   public Address Address { get; set; }
}

public class Address {
  public string AddressLine1 { get; set; }
  public string AddressLine2 { get; set; } 
  public string City { get; set; }
  public string Postcode { get; set; }
}

The Customer object gets stored in a Azure Table with columns like this:

Id	Name	Address_AddressLine1	Address_AddressLine2	Address_City	Address_Postcode

Child object gets flattened and gets columns at the same level as Table Storage doesn't support nested objects.

I want to migrate this to Cosmos DB SQL API. What's the best way to migrate this data so that I end up with a nested json document instead of a flat one with these underscore columns?

I want to migrate this data so that it looks something like this in Cosmos:

{ 
  Id: 2fca57ec-8c13-4f2c-81c7-d6b649ca6296,
  Name: "John Smith",
  Address: {
    AddressLine1: '123 Street',
    AddressLine2: '',
    City: 'City',
    Postcode: '1234'
  }
 }

I have tried using Cosmos Data Migration tool (deprecated?) and Azure Data factory but couldn't figure out how to convert the Address_* columns to a nested Address object instead of ending up as flat attributes in the json document.

Is there a way to easily map it to a nested child object Or will I have to write custom code to do the migration?

Answer 1

Unfortunately, there is no out of the box solution for this kind of migration. Easier option would be to write custom code to loop through the TableEntities, construct the document object and add the item to your Cosmos container.

Answer 2

There is no straightforward solution offered by Microsoft (Azure Storage Explorer) to overcome this challenge but leveraging a third-party tool like Cerebrata (https://cerebrata.com/) could help you migrate your data from Azure Table Storage to Cosmos DB SQL API in a simple copy/paste model.

This way you can also avoid spending a good amount of time on custom coding and also view your migrated data in a table format rather than a complicated JSON format.

Disclaimer: It’s purely based on my experience