Cosmos DB Migration Tool - SQL Database to Cosmos DB

Question

Hi there,

I have data saved in SQL Database, which I'm migrating. I used to save Lat and Lon into a separate column, but for the new implementation, I'm saving lat and lon as GeoJSON point.

While migration, I want to save Lat and Lon as GeoJSON on the fly, I have found I can run CosmosDB sp while migration, but I'm not sure how it will work in this scenario. Any sample/ example would be great.

Thanks

Answer 1

Hi @teshayat - The following is an example of creating GeoJSON document in Node.js and .NET (Please see: Creating documents with spatial data).

Node.js

 var userProfileDocument = {
     "id":"cosmosdb",
     "location":{
         "type":"Point",
         "coordinates":[ -122.12, 47.66 ]
     }
 };
    
 client.createDocument(`dbs/${databaseName}/colls/${collectionName}`, userProfileDocument, (err, created) => {
     // additional code within the callback
 });

.NET

 using Microsoft.Azure.Cosmos.Spatial;
    
 public class UserProfile
 {
     [JsonProperty("id")]
     public string id { get; set; }
    
     [JsonProperty("location")]
     public Point Location { get; set; }
    
     // More properties
 }
    
 await container.CreateItemAsync( new UserProfile
     {
         id = "cosmosdb",
         Location = new Point (-122.12, 47.66)
     });

As for the actual migration, you might benefit from Azure Data Factory, as you can implement a transformation of the normalized SQL to denormalized CosmosDB container. See: Migrate normalized database schema from Azure SQL Database to Azure CosmosDB denormalized container (Link)

Other options will to maintain a 1:1 between source and target where Azure Data Factory will allow for an ETL approach. Using the other options, load the data as is and then create a procedure to perform an UPSERT to create the required GeoJSON document format per the examples provided above.

Please let me know if you have additional questions.

Regards,
Mike