Data conversion during copy in Azure Data Factory

Question

I'm new to Data Factory ecosystem. I'm trying to copy data from source MySQL database to sink CosmosDB for MongoDB. An example source schema that I have is something like this:

*inventory_warehouse_table*

--------------------------------------------------------
| id | warehouse_name | warehouse_address | lat | long |
--------------------------------------------------------

The sink schema is of the form like:

*inventory_warehouse_collection*

{
  id: <int>
  warehouse_name: <string>
  warehouse_address: <string>
  geo_coordinates: {
   type: "Point",
   coordinates: [lat, long]
  }
}

I don't see any schema mapping in copy data activity for achieving the same. How can I do this in Azure Data Factory? Is there any other pipeline I need to create for doing so?

Answer 1

• Source and sink are taken as in below images. Img:1 Source dataset

Img:2 Sink Dataset

• In mapping activity, when import schemas is clicked, Mapping between source and sink is seen. img-3 mapping setting

Except the last column, all other columns are mapped. Since last column has concat of lat, long columns, Dataflow is used to combine the columns. Below is the approach.

• Source is taken in dataflow. Then Derived column transformation is added and expression for new column is given as concat("[",toString(lat),",",toString(long),"]")

• Since cosmos dB - mongo API is not supported in dataflow, data is staged in blob storage.

• Then copy activity is used to copy data from blob to cosmos db-mongo api

• New row is inserted into cosmos db.