While fetching data from cosmos db conatiner and persisting the json file in ADLS Gen 2 through Synapse Pipeline, some objects in my json file are appearing as blank string causing data loss. This is happening in PROD only not in UAT and DEV.

Vity Pandita 0

Hi,

The issue i am facing is with the Synapse Analytics Service - Pipeline. I have created a dataflow which is pulling data from cosmos db container in json format and storing that json file in ADLS Gen 2. When I check the json file in ADLS I see that some objects are appearing as blank String but when I query the cosmos db those objects are having some values. So where and why is my data getting lost. Also, there is no issue in UAT and DEV. Only PROD env is having this issue.

Already checked the link: https://github.com/Azure/azure-sdk-for-java/issues/29181

Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-04-08T08:28:38.32+00:00
Hi @Vity Pandita

Thanks for the additional information.

Based on your response, it seems that the issue is happening when the JSON file is being stored in ADLS Gen2. Some objects are appearing as blank strings in the JSON file, but when you query the Cosmos DB, those objects have some values.

To resolve this issue, you can try the following steps:

Check the encoding of the JSON file. Ensure that the encoding is compatible with the encoding of the corresponding field in the Cosmos DB container.

Check the JSON file format. Ensure that the JSON file is being stored in the correct format and that the objects are being serialized correctly.

Check the ADLS Gen2 storage account configuration. Ensure that the storage account is configured correctly, and that the JSON file is being stored in the correct format.

Check the Synapse Analytics Service - Pipeline logs for any errors or warnings related to the dataflow. This can help identify any issues with the pipeline.

Check the data type of the objects that are appearing as blank strings in the JSON file. Ensure that the data type is compatible with the data type of the corresponding field in the Cosmos DB container.

If the issue persists, you can try recreating the dataflow and pipeline in the PROD environment. Also, ensure that the PROD environment is configured the same as the UAT and DEV environments.

I hope this helps! Let me know if you have any further questions.
Vity Pandita 0 Reputation points

2024-04-09T09:21:53.5166667+00:00

Hi @Smaran Thoomu

Thank you for the response.

I Have already checked the mentioned pointers but have no luck.

PROD - In the Preview of the dataflow, the object appears as null and if we query the cosmos db, the object is there with values.

Repeating the same thing in UAT, in the preview the object is not null and has values.

Also, the json code, linked services(just url change) and configuration is same for UAT and PROD.

Thank you!
Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-04-19T09:12:31.5866667+00:00

@Vity Pandita Can you please confirm if you are able to perform a data preview in the data flow and see the desired values in the columns in the data preview? If not, can you provide more details on what you are seeing in the data preview and what you expect to see? This information will help us understand the issue better and provide you with more targeted solutions.
Vity Pandita 0 Reputation points

2024-04-19T10:34:18.2666667+00:00

Hi @Smaran Thoomu I have shared two snips. First one is what I am getting(from PROD) the same object is appearing as string.(Highlighted in yellow)

Second one is what is required( from our UAT). The object should be complex(highlighted in yellow)

Hope, I am clear now.

Thank you!

2 answers

Vinodh247-1375 11,211 Reputation points

2024-04-05T13:35:33.52+00:00

Sometimes the data type conversions or null value handling steps can affect the data. I hope you have already checked that.

With cosmosdb, check if the objects with missing values are being retrieved correctly in your query. In your pipeline, are there any custom expressions, aggregations, or conditional logic applied to your data? check any null handling or default values assigned during the transformation process. Also check if there are any data truncation issues that might cause loss of information.
Please sign in to rate this answer.
Vity Pandita 0 Reputation points

2024-04-05T13:59:13.1966667+00:00

Hi Vinodh,

Thanks for the reply!

The dataflow in my pipeline stores the json files as is in the adls gen2 layer. No change there. (just using default encoding UTF-8, sink Type - Inline, Inline dataset type- Json)

All the transformations are done after that step. I am losing the data in first step where i am just saving the file as is.
Sign in to comment
Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-04-10T08:38:28.9133333+00:00
Hi @Vity Pandita

I understand that you have already checked the mentioned pointers and have no luck. Based on your response, it seems that the issue is still happening in the PROD environment, but not in the UAT environment.

In this case, I would suggest checking the following:

Check the version of the Synapse Analytics Service - Pipeline in the PROD environment. Ensure that it is the same as the UAT environment.

Check the network connectivity between the Synapse Analytics Service - Pipeline and the Cosmos DB container in the PROD environment. Ensure that there are no network issues or firewall rules blocking the connection.

Check the permissions of the Synapse Analytics Service - Pipeline in the ADLS Gen2 storage account in the PROD environment. Ensure that the pipeline has the necessary permissions to write to the storage account.

Check the dataflow settings in the PROD environment. Ensure that the dataflow is configured correctly and that the objects are being serialized correctly.

Check the data type of the objects that are appearing as blank strings in the JSON file. Ensure that the data type is compatible with the data type of the corresponding field in the Cosmos DB container.

If the issue persists, you can try contacting Microsoft Support for further assistance.

I hope this helps! Let me know if you have any further questions.
Please sign in to rate this answer.
Vity Pandita 0 Reputation points

2024-04-15T06:43:17.7+00:00

Hi @Smaran Thoomu

I have checked the following in PROD :

Version of Synapse Analytics Service - same in all env.

Network connections and firewalls are checked

Permissions of the Synapse Analytics Service - Pipeline in the ADLS Gen2 storage

Dataflow settings and configuration

Data type of object is complex and some of the key value pairs inside are Boolean at the source(Cosmos db). e.g
"Category": { "color": "Red", "isColorBlack": false, "size": "10", "isSizeCorrect": false, "isWeightCorrect": false }

Smaran Thoomu 9,685 Reputation points Microsoft Vendor

2024-04-16T13:40:11.6366667+00:00

Vity Pandita Thank you for the additional information.

Based on the JSON example you provided, it seems that the issue may be related to the serialization of the "Category" object in the dataflow. The "Category" object contains several key-value pairs, some of which are Boolean values.

To resolve this issue, you can try the following steps:

Check the data type of the "Category" object in the dataflow. Ensure that it is being serialized correctly and that the Boolean values are being handled correctly.

Check the data type of the corresponding field in the Cosmos DB container. Ensure that it is compatible with the data type of the "Category" object in the dataflow.

Check the data type of the corresponding field in the ADLS Gen2 storage account. Ensure that it is compatible with the data type of the "Category" object in the dataflow.

Try using a different serialization format for the "Category" object, such as Avro or Parquet. These formats may handle Boolean values differently than JSON.

If the issue persists, you can try contacting Microsoft Support for further assistance.

I hope this helps! Let me know if you have any further questions.
Sign in to comment