Search code examples
azuresnowflake-cloud-data-platformazure-data-factoryazure-cosmosdbazure-cosmosdb-mongoapi

How to copy JSON array columns from Azure Cosmos DB enabled Mongo DB document to Snowflake column (String/Variant) using Azure Data Factory V2?

I am trying to copy Azure Cosmos-DB enabled Mongo-DB collection Documents to the Snowflake table using Azure Data Factory V2. Some of the columns present in the Document are array (multiple values). I thought that: If I use String or Variant column at the destination "Snowflake", Azure Data Factory V2 will be able to copy it successfully but it is failing with error: (ErrorCode=JsonUnsupportedHierarchicalComplexValue,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=The retrieved type of data JArray is not supported yet. Please either remove the targeted column 'REQUESTDATE' or enable skip incompatible row to skip the issue rows.,Source=Microsoft.DataTransfer.Common,'):

Mapping:

enter image description here

Error:

enter image description here

I have tried everything but was unsuccessful. I also noticed that even Data Flow does not support Mongo DB as a source. Any assistance will be appreciated.

Thanks, Bilal

Solution

  • I tried the same and also failed for the same JArray data type may be it is taking it as Array directly To overcome this issue You can use this following workaround:

    • First you need to take on copy activity to copy data from Cosmos with mongo db to CSV file and, in the source add the Cosmos with mongo db and select your container.enter image description here
    • In sink add Blob storage with CSV file so it will copy data there. enter image description here
    • Import mapping and check theMap complex values to string enter image description here
    • Then take another Copy activity to copy data from CSV file to snowflake and, in the source add the Same CSV file where you copied data in earlier Copy activity. enter image description here
    • In sink add Snowflake dataset as destination. enter image description here
    • Add mapping according to your requirement. enter image description here
    • Run the pipeline It will copy the data in snowflake.