{"id":1017,"date":"2020-05-04T06:30:49","date_gmt":"2020-05-04T13:30:49","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/cosmosdb\/?p=1017"},"modified":"2020-05-06T09:11:22","modified_gmt":"2020-05-06T16:11:22","slug":"migrating-relational-data-into-azure-cosmos-db-using-only-azure-data-factory","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/cosmosdb\/migrating-relational-data-into-azure-cosmos-db-using-only-azure-data-factory\/","title":{"rendered":"Migrate relational data into Azure Cosmos DB using Azure Data Factory"},"content":{"rendered":"

In my previous post<\/a>, I explained how to use Azure Databricks and the Apache Spark collect_list<\/a> function to perform a two-table relational data migration to NoSQL, using the embedding approach to support a one-to-many relationship. I used Apache Spark because at that time we didn’t have the right native functions in Azure Data Factory (ADF) to support this transformation. Well, now we have it<\/a> and it is (not surprisingly) called collect<\/a>. This function will take multiple values and aggregate them into an array. We can use collect to create arrays or long strings:<\/p>\n\n\n\n\n
collect(ProductID)<\/code><\/td>\n[52734, 734625, 742325, 946256, 245462]<\/td>\n<\/tr>\n
toString ( reduce ( collect (ProductID) , '', #item + ', ' + #acc, #result) )<\/code><\/td>\n“52734, 734625, 742325, 946256, 245462”<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Or we can use them to create more complex structures:<\/p>\n\n\n\n
collect(@(prodId = ProductID,\nqty = OrderQty))<\/code><\/td>\n[<\/code>\n{<\/code>prodId: 52734,<\/code>qty: 24<\/code>},<\/code>\n{<\/code>prodId: 734625,<\/code>qty: 13<\/code>},<\/code>\n{<\/code>prodId: 742325,<\/code>qty: 18<\/code>},<\/code>\n{<\/code>prodId: 946256,<\/code>qty: 2<\/code>},<\/code>\n{<\/code>prodId: 245462,<\/code>qty: 71<\/code>}<\/code>\n]<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

This post will show you how to migrate relational data to Azure Cosmos DB using only Azure Data Factory, with no code needed. The use case is exactly the same as in my previous post, I’m adding it here again for quick reference:<\/p>\n

One-to-many relationships using the embedding approach<\/span><\/strong><\/h3>\n

In some One-to-Many scenarios, the recommended approach is to Embed the many side into the one side, thus eliminating the need for joins. A common example is when we have a master\/detail pair of tables like Order Header and Order Detail.<\/p>\n

\"Order<\/a><\/p>\n

Here we have one record for the Order Header and three corresponding records for the Order Detail. In a relational world, we are required to join these two tables (by SalesOrderID) to get a complete picture of sales data. When using the embedded approach to migrate this data to an Azure Cosmos DB (Core SQL API), the data will look like a single document with data for the order, and an array of elements representing data for the detail..<\/p>\n

\"Order<\/a><\/p>\n

Notice that I left the SalesOrderID element on the embedded documents just for reference. The final implementation will remove these elements as they are not necessary anymore.<\/p>\n

 <\/p>\n

The solution: migrating relational data<\/span><\/strong><\/h3>\n

The solution has a single Azure Data Factory pipeline with a single Mapping Data Flow activity that reads the relational data, transforms (embed) the data, and finally loads the data to migrate relational data into Azure Cosmos DB. The final data flow should look like this:<\/p>\n

\"example<\/a>
Azure Data Factory Mapping Data Flow<\/figcaption><\/figure><\/p>\n

 <\/p>\n

The DecimalToDouble transformation is required because Azure Cosmos DB can’t store Decimals with set precision. To create the required Mapping Data Flow:<\/p>\n

    \n
  1. First we add two Data Sources<\/strong>: Sales Order Header and Sales Order Detail. Optionally, we could set a hash partition by SalesOrderID on both datasets in the Optimize options.<\/li>\n
  2. Then, we add an Aggregate<\/strong> transform on the Sales Order Detail source grouping by SalesOrderID. We will add one single Aggregate column called Details. This will include all columns we want to “embed”. Make sure to wrap the structure into a collect<\/code> function. The expression for the Details field should be:\n\n\n\n
    collect(@(SalesOrderDetailID=SalesOrderDetailID,\nOrderQty=OrderQty,\nProductID=ProductID,\nUnitPrice=toDouble(UnitPrice),\nUnitPriceDiscount=toDouble(UnitPriceDiscount),\nLineTotal=toDouble(LineTotal),\nrowguid=rowguid,\nModifiedDate=ModifiedDate) )<\/code><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    We use toDouble<\/code> here to make sure we don’t send decimals to Azure Cosmos DB. The Data Preview on the new Aggregate step should look like this:<\/p>\n

    \"Image<\/a><\/li>\n

  3. Now we can Join<\/strong> the output of the Aggregate transformation with the original SalesOrderHeader source. We use an inner join by SalesOrderID. The output of step has all the columns we need to store in Azure Cosmos DB, and at the right level of granularity (SalesOrderID)\n\"Image<\/a><\/li>\n
  4. Then add a Derived Column transformation to cast all remaining Decimal columns to double. These are the four Decimal columns coming from SalesOrderHeader:\"Image<\/a><\/li>\n
  5. The final step is a Sink operation directly into our Azure Cosmos DB collection to load the transformed data. Make sure to Allow Schema Drift and to Remove Duplicated Input columns.<\/li>\n
  6. Once loaded into the collection, documents will look like this from Azure Cosmos DB Data Explorer<\/li>\n<\/ol>\n

    \"Order<\/a><\/p>\n

     <\/p>\n

    Implementation Notes<\/span><\/strong><\/h3>\n

    Using Azure Data Factory Mapping Data Flows no-code approach makes it really easy to migrate relational data to Azure Cosmos DB. You can use this same approach to create even more complex multi-level hierarchies or create arrays of values when needed. Read more about how to use Collect with Azure Cosmos DB<\/a>.<\/span><\/p>\n

    Get started with Azure Cosmos DB<\/strong><\/span><\/h3>\n