Strange behaviour using stringify in synapse pipeline dataflow

Question

Strange behaviour using stringify in synapse pipeline dataflow

RickMsBi 86

Hi all,

We encountered an issue with Azure pipelines. We are using a dataflow task to convert some json files to parquet.

We've chosen to keep some complete json objects in the parquet files, so we can, at a later stage unroll them when needed.

The dataflow has 3 steps, source, stringify (for the array(s)) and a step to write to a parquet file.

In the source we defined the array with all the fields, including the field with strange behaviour. To keep things simple, we kept every field defined as a str.

So that looks like this in the dataflow (json code).

jaarloon as string

And, as you can see, the input for the stringify also shows string.

User's image

We convert about 700k json files, and all work well, except for one where the column contains this value.

                    "jaarloon": -14809425.49,

The column in the resulting parquet file that contains the stringified array suddenly has this value:

                "jaarloon": "-1.480942549E7"

This is weird behaviour, as I specifically told to handle this field as a string. So the engine is doing interpreting of some kind.

Any clues? Or is this a bug?

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-06-20T13:50:59.79+00:00

Hi RickMsBi ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you are facing data mismatch issue while trying to copy data from json to parquet file using mapping dataflow. Please let me know if the understanding about your query has any gap.

One possible explanation for this behavior might be that the Parquet file format has a different way of representing numeric values than JSON. When you define a field as a string in the dataflow, ADF will convert the numeric value to a string before writing it to the Parquet file. However, the Parquet file format may interpret the string representation of the numeric value differently than JSON.

To resolve this issue, you can try explicitly converting the numeric value to a string in the JSON file before converting it to Parquet format. For that , try toString() function in derived column transformation to convert the column value into string after stringify transformation and see if it helps.

Hope it helps. Please keep us posted on the resolution update. Thankyou
RickMsBi 86 Reputation points

2023-06-20T14:06:30.82+00:00

The issue is different. We are writing a complete json array to 1 column in the json file. In order to do so, we use stringify on the whole array. Then write this array as 1 field to the parquet file. So we basically we want to preserve the json array as is, in the parquet file. But if we then open the json array in the parquet file, it has changed "jaarloon" withing the array to a scientific notation. We use the stringify function before the sink, so I expect the json array to be a string and the fields in it to be a string too. Even in the source, we defined the fields within the array as string using the Json editor of the pipeline.

So to clarify, it's not about a single field, it's about "jaarloon" field within an array. The full array is stringified, and then the whole array is put into a single column in a parquet file. So I expect the array in the parquet to look exactly the same, as the array in the json file. But somehow, it changed a field within the array to this notation. I can't use derived column, as there is no column for "jaarloon", there's only the complete array that is passed to the parquet file.

Your answer

AnnuKumari-MSFT 34,556 Reputation points Microsoft Employee Moderator

2023-06-20T13:50:59.79+00:00

Hi RickMsBi ,

Thankyou for using Microsoft Q&A platform and thanks for posting your question here.

As per my understanding, you are facing data mismatch issue while trying to copy data from json to parquet file using mapping dataflow. Please let me know if the understanding about your query has any gap.

One possible explanation for this behavior might be that the Parquet file format has a different way of representing numeric values than JSON. When you define a field as a string in the dataflow, ADF will convert the numeric value to a string before writing it to the Parquet file. However, the Parquet file format may interpret the string representation of the numeric value differently than JSON.

To resolve this issue, you can try explicitly converting the numeric value to a string in the JSON file before converting it to Parquet format. For that , try toString() function in derived column transformation to convert the column value into string after stringify transformation and see if it helps.

Hope it helps. Please keep us posted on the resolution update. Thankyou
RickMsBi 86 Reputation points

2023-06-20T14:06:30.82+00:00

The issue is different. We are writing a complete json array to 1 column in the json file. In order to do so, we use stringify on the whole array. Then write this array as 1 field to the parquet file. So we basically we want to preserve the json array as is, in the parquet file. But if we then open the json array in the parquet file, it has changed "jaarloon" withing the array to a scientific notation. We use the stringify function before the sink, so I expect the json array to be a string and the fields in it to be a string too. Even in the source, we defined the fields within the array as string using the Json editor of the pipeline.

So to clarify, it's not about a single field, it's about "jaarloon" field within an array. The full array is stringified, and then the whole array is put into a single column in a parquet file. So I expect the array in the parquet to look exactly the same, as the array in the json file. But somehow, it changed a field within the array to this notation. I can't use derived column, as there is no column for "jaarloon", there's only the complete array that is passed to the parquet file.

Share via

Strange behaviour using stringify in synapse pipeline dataflow

Your answer