Azure Data Factory dataflow bad schema mapping

Question

Azure Data Factory dataflow bad schema mapping

Carrie 50

Hi

I'm new to ADF. I think maybe it's because of my bad setting.

Recently we're planning to migrate our Alteryx tasks to ADF.

Our procedures are like below:

Connect to Workday soap API, get all the data and sink to data lake, which might generate around 140 Json files.
Creating data flow to manipulate the data which are generated at previous procedure
My data flow settings are like below:

User's image

And here is the issue:

I found for some fields their type are not what I expect.

Like for wd:Address_Data, wd:Email_Address_Data, wd:Phone_Data,I checked the data on local, they are complex :

wd:Email_Address_Data User's image

wd:Phone_Data User's image

wd:Address_Data User's image

But they are changed to be string when I import projection. Sometimes all of them are string, sometimes part of them are string User's image

User's image

Is it because my bad setting? How could I get the right schema mapping

Best regards,

Carrie

Harishga 6,005 Reputation points Microsoft External Staff

2024-02-26T08:07:13.41+00:00
Hi @Zhenqi Liu

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Based on your description, it seems like the issue is related to schema mapping in your data flow. By default, Azure Data Factory uses sample rows to infer the schema of the source data, and the inferred schema is used to read the data. In some cases, the inferred schema may not match the actual schema of the source data, resulting in incorrect data types.

To resolve this issue, you can customize the schema of the source data in your data flow. ADF provides options for you to specify additional/missing columns that could be missing in the schema-infer-result in the data flow source projection to read the data.

Here are the steps to customize the schema of the source data in your data flow:

Use the data flow source Debug Settings to have Import projection with sample files/tables to get the complete schema.

Change the Debug Settings back to use the source dataset for the remaining data transformation.

If you are familiar with the schema and DSL language of the source data, you can manually update the data flow source script to add additional/missed columns to read the data.

when you customize the schema using the options provided by ADF, the ADF data flow runtime will use the customized schema to read data from the original data store. It ensures that the additional/missing columns are included in the data flow and are transferred to the sink data stores.

Reference
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format

I hope this helps you resolve your issue. Let me know if you have any further questions or concerns.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-27T08:02:19.1533333+00:00

@Zhenqi Liu
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-28T03:55:35.4433333+00:00

@Zhenqi Liu
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Carrie 50 Reputation points

2024-02-28T06:02:00.0766667+00:00

Hi @Harishga Thank you for your reply! I tried to option1, still get bad schema, it only set type of wd:Email_Address_Data as complex, but for Address_Data, Phone_Data still show as String

I checked on Visual Studio, they are complex
Carrie 50 Reputation points

2024-03-04T09:44:48.49+00:00

Hi @Harishga

I think I figured out the reason.

But still not sure how to fix it.

I found the type for Address_data and Phone_Data are mixed , I think array type should be correct.

The data I got from Workday with soap API is XML, and sink it to Azure Data Lake as json file directly.

So the problem would be why its set to be json when there is only one data, and change to be array when there are more than one data for Address_data and Phone_Data?

Is there any way to fix this issue?

Best regards,

Carrie
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-04T12:34:24.9633333+00:00
@Zhenqi Liu
Based on your updated information, it seems like the issue is related to the format of the data that you are importing from Workday. As you mentioned, the data is in XML format, but you are sinking it to Azure Data Lake as a JSON file. This could be causing the issue with the schema mapping.

To resolve this issue, you can try converting the XML data to JSON format before sinking it to Azure Data Lake. You can use the XML to JSON transformation in your data flow to convert the data. Here are the steps to do this:

Add a Derived Column transformation to your data flow.

In the Derived Column transformation, add a new column and use the toJSON function to convert the XML data to JSON format.

Use the new column with the JSON data as the source for your sink.

By converting the XML data to JSON format before sinking it to Azure Data Lake, you can ensure that the schema mapping is correct and that the data types are preserved.

I hope this helps you resolve your issue. Let me know if you have any further questions or concerns.
Carrie 50 Reputation points

2024-03-05T01:43:37.02+00:00

@HarishgaI'm using Soap api to get data from Workday, and I can not get all the data in one response, I think I need to use Copy Activity, and copy activity does not support XML for sink.
Carrie 50 Reputation points

2024-03-05T01:52:32.6666667+00:00

I'm not sure, but other fields all work perfect, just for these fields(Address_Data, Phone_Data), as there might be only one data or even 0 data.

If there are more than one data in them(Address_Data, Phone_Data), it will put it in an array, but when there is only one data, will not put it in array, but set it directly as json.
Carrie 50 Reputation points

2024-03-05T06:15:51.51+00:00

@Harishga

Sorry, I failed to mention you in my previous comment.

I'm using Soap api to get data from Workday, and I can not get all the data in one response, I think I need to use Copy Activity, and copy activity does not support XML for sink.

I'm not sure, but other fields all work perfect, just for these fields(Address_Data, Phone_Data), as there might be only one data or even 0 data.

If there are more than one data in them(Address_Data, Phone_Data), it will put it in an array, but when there is only one data, will not put it in array, but set it directly as json.
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-05T11:03:10.5033333+00:00

@Zhenqi Liu
Regarding the issue with the Address_Data and Phone_Data fields, it seems like the data is not consistent in terms of the number of records. When there is only one record, it is not put in an array, but when there are more than one records, they are put in an array. To fix this issue, you can try modifying the XML data to ensure that the data is consistent in terms of the number of records. Alternatively, you can modify the schema mapping in your data flow to handle this inconsistency.

I hope this Information helps you.
Carrie 50 Reputation points

2024-03-05T13:31:51.9533333+00:00

@Harishga

The XML data is from workday API, as you can see, seems sink does not support XML format.

Is there any other way to save workday API XML response?

you can modify the schema mapping in your data flow to handle this inconsistency

Do you mean modify schema mapping in copy data pipeline?

If so, I tried it before, but as the the data is very complex, it does not support

I checked the answer here too
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-06T10:29:31.4633333+00:00

@Zhenqi Liu
To save the Workday API XML response, you can use a different sink that supports XML format, such as Azure Blob Storage or Azure SQL Database.
As you mentioned, the data is very complex, and it may not be supported by the schema mapping feature in the copy data pipeline. In this case, you can try using Azure Data Factory Mapping Data Flows to transform the data. Mapping Data Flows provides a visual interface for building complex data transformations, and it supports a wide range of data sources and sinks. You can use Mapping Data Flows to transform the XML data to JSON format and then sink it to Azure Data Lake.
Carrie 50 Reputation points

2024-03-10T08:08:52.9433333+00:00

Hi, @Harishga

I tried to use Azure Blob Storage, but still not showing XML in the option.

Not sure whether I'm referring the right official document, but in the documentation below, it says it does not support Sink.

https://learn.microsoft.com/en-us/azure/data-factory/format-xml
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-11T12:04:21.8533333+00:00

@Zhenqi Liu
Yes, you are correct that Azure Blob Storage does not support XML as a sink format.

To sink XML data to Azure Data Lake, you can use Mapping Data Flows in Azure Data Factory to transform the data to JSON format using the Derived Column transformation. The expression toJSON(toBinary(toBase64(xmlField) can be used to convert the XML data to JSON format. Once the data is transformed, it can be sunk to Azure Data Lake using the Azure Data Lake Storage Gen1 or Gen2 connector in Mapping Data Flows.
Carrie 50 Reputation points

2024-03-12T08:24:46.8533333+00:00
@Harishga

I think I did not explain very well before.(It's okay that if there is no solution. then we can use other tool or try some other method)

Please let me make it clear again.

Our procedure was like below:

Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json

Create a Data Flow in ADF to do some transformation, using the json files created in step1

Original Problem:

When import schema in Step 2, we found that some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Contact_Data:[{name:AAAA},{name:BBB}]}, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Contact_Data:{name:AAAA}},

Current problem

You suggested that we can try to save response in XML format and convert XML to json, so currently the problem is not how to convert XML to json, is how to save API response in XML format, as I can not see any function in Copy activity support XML format https://stackoverflow.com/questions/72599418/azure-job-to-extract-the-data-to-xml-format

And it seems there is no toJson() function in data flow

Best regards,

Carrie

1 answer

Your answer

Harishga 6,005 Reputation points Microsoft External Staff

2024-02-26T08:07:13.41+00:00

Hi @Zhenqi Liu

Welcome to Microsoft Q&A platform and thanks for posting your question here.

Based on your description, it seems like the issue is related to schema mapping in your data flow. By default, Azure Data Factory uses sample rows to infer the schema of the source data, and the inferred schema is used to read the data. In some cases, the inferred schema may not match the actual schema of the source data, resulting in incorrect data types.

To resolve this issue, you can customize the schema of the source data in your data flow. ADF provides options for you to specify additional/missing columns that could be missing in the schema-infer-result in the data flow source projection to read the data.

Here are the steps to customize the schema of the source data in your data flow:

Use the data flow source Debug Settings to have Import projection with sample files/tables to get the complete schema.

Change the Debug Settings back to use the source dataset for the remaining data transformation.

If you are familiar with the schema and DSL language of the source data, you can manually update the data flow source script to add additional/missed columns to read the data.

when you customize the schema using the options provided by ADF, the ADF data flow runtime will use the customized schema to read data from the original data store. It ensures that the additional/missing columns are included in the data flow and are transferred to the sink data stores.

Reference
https://learn.microsoft.com/en-us/azure/data-factory/data-flow-troubleshoot-connector-format

I hope this helps you resolve your issue. Let me know if you have any further questions or concerns.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-27T08:02:19.1533333+00:00

@Zhenqi Liu
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Harishga 6,005 Reputation points Microsoft External Staff

2024-02-28T03:55:35.4433333+00:00

@Zhenqi Liu
We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution, please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Carrie 50 Reputation points

2024-02-28T06:02:00.0766667+00:00

Hi @Harishga Thank you for your reply! I tried to option1, still get bad schema, it only set type of wd:Email_Address_Data as complex, but for Address_Data, Phone_Data still show as String

I checked on Visual Studio, they are complex
Carrie 50 Reputation points

2024-03-04T09:44:48.49+00:00

Hi @Harishga

I think I figured out the reason.

But still not sure how to fix it.

I found the type for Address_data and Phone_Data are mixed , I think array type should be correct.

The data I got from Workday with soap API is XML, and sink it to Azure Data Lake as json file directly.

So the problem would be why its set to be json when there is only one data, and change to be array when there are more than one data for Address_data and Phone_Data?

Is there any way to fix this issue?

Best regards,

Carrie
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-04T12:34:24.9633333+00:00

@Zhenqi Liu
Based on your updated information, it seems like the issue is related to the format of the data that you are importing from Workday. As you mentioned, the data is in XML format, but you are sinking it to Azure Data Lake as a JSON file. This could be causing the issue with the schema mapping.

To resolve this issue, you can try converting the XML data to JSON format before sinking it to Azure Data Lake. You can use the XML to JSON transformation in your data flow to convert the data. Here are the steps to do this:

Add a Derived Column transformation to your data flow.

In the Derived Column transformation, add a new column and use the toJSON function to convert the XML data to JSON format.

Use the new column with the JSON data as the source for your sink.

By converting the XML data to JSON format before sinking it to Azure Data Lake, you can ensure that the schema mapping is correct and that the data types are preserved.

I hope this helps you resolve your issue. Let me know if you have any further questions or concerns.
Carrie 50 Reputation points

2024-03-05T01:43:37.02+00:00

@HarishgaI'm using Soap api to get data from Workday, and I can not get all the data in one response, I think I need to use Copy Activity, and copy activity does not support XML for sink.
Carrie 50 Reputation points

2024-03-05T01:52:32.6666667+00:00

I'm not sure, but other fields all work perfect, just for these fields(Address_Data, Phone_Data), as there might be only one data or even 0 data.

If there are more than one data in them(Address_Data, Phone_Data), it will put it in an array, but when there is only one data, will not put it in array, but set it directly as json.
Carrie 50 Reputation points

2024-03-05T06:15:51.51+00:00

@Harishga

Sorry, I failed to mention you in my previous comment.

I'm using Soap api to get data from Workday, and I can not get all the data in one response, I think I need to use Copy Activity, and copy activity does not support XML for sink.

I'm not sure, but other fields all work perfect, just for these fields(Address_Data, Phone_Data), as there might be only one data or even 0 data.

If there are more than one data in them(Address_Data, Phone_Data), it will put it in an array, but when there is only one data, will not put it in array, but set it directly as json.
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-05T11:03:10.5033333+00:00

@Zhenqi Liu
Regarding the issue with the Address_Data and Phone_Data fields, it seems like the data is not consistent in terms of the number of records. When there is only one record, it is not put in an array, but when there are more than one records, they are put in an array. To fix this issue, you can try modifying the XML data to ensure that the data is consistent in terms of the number of records. Alternatively, you can modify the schema mapping in your data flow to handle this inconsistency.

I hope this Information helps you.
Carrie 50 Reputation points

2024-03-05T13:31:51.9533333+00:00

@Harishga

The XML data is from workday API, as you can see, seems sink does not support XML format.

Is there any other way to save workday API XML response?

you can modify the schema mapping in your data flow to handle this inconsistency

Do you mean modify schema mapping in copy data pipeline?

If so, I tried it before, but as the the data is very complex, it does not support

I checked the answer here too
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-06T10:29:31.4633333+00:00

@Zhenqi Liu
To save the Workday API XML response, you can use a different sink that supports XML format, such as Azure Blob Storage or Azure SQL Database.
As you mentioned, the data is very complex, and it may not be supported by the schema mapping feature in the copy data pipeline. In this case, you can try using Azure Data Factory Mapping Data Flows to transform the data. Mapping Data Flows provides a visual interface for building complex data transformations, and it supports a wide range of data sources and sinks. You can use Mapping Data Flows to transform the XML data to JSON format and then sink it to Azure Data Lake.
Carrie 50 Reputation points

2024-03-10T08:08:52.9433333+00:00

Hi, @Harishga

I tried to use Azure Blob Storage, but still not showing XML in the option.

Not sure whether I'm referring the right official document, but in the documentation below, it says it does not support Sink.

https://learn.microsoft.com/en-us/azure/data-factory/format-xml
Harishga 6,005 Reputation points Microsoft External Staff

2024-03-11T12:04:21.8533333+00:00

@Zhenqi Liu
Yes, you are correct that Azure Blob Storage does not support XML as a sink format.

To sink XML data to Azure Data Lake, you can use Mapping Data Flows in Azure Data Factory to transform the data to JSON format using the Derived Column transformation. The expression toJSON(toBinary(toBase64(xmlField) can be used to convert the XML data to JSON format. Once the data is transformed, it can be sunk to Azure Data Lake using the Azure Data Lake Storage Gen1 or Gen2 connector in Mapping Data Flows.
Carrie 50 Reputation points

2024-03-12T08:24:46.8533333+00:00

@Harishga

I think I did not explain very well before.(It's okay that if there is no solution. then we can use other tool or try some other method)

Please let me make it clear again.

Our procedure was like below:

Create a Copy Activity in ADF pipeline: calling workday API, and sink response in Azure Data Lake Storage Gen2 as json

Create a Data Flow in ADF to do some transformation, using the json files created in step1

Original Problem:

When import schema in Step 2, we found that some of the fields expected to be array, but resulted in string. We found those fields which has bad schema might because that their types in json files are strange: when 2 elements in those fields, their types shows like array e.g. {Contact_Data:[{name:AAAA},{name:BBB}]}, but when there are less than 2 elements in those fields, their types shows as json. e.g. {Contact_Data:{name:AAAA}},

Current problem

You suggested that we can try to save response in XML format and convert XML to json, so currently the problem is not how to convert XML to json, is how to save API response in XML format, as I can not see any function in Copy activity support XML format https://stackoverflow.com/questions/72599418/azure-job-to-extract-the-data-to-xml-format

And it seems there is no toJson() function in data flow

Best regards,

Carrie

Answer 1

Carrie 50

I found the similar case, maybe the function to convert to json has some bug?

https://stackoverflow.com/questions/37164399/jackson-desrialize-when-jsonproperty-is-sometimes-array-and-sometimes-a-single-o

[http://michaelcummings.net/mathoms/using-a-custom-jsonconverter-to-fix-bad-json-results/]

Harishga 6,005 Reputation points Microsoft External Staff

2024-03-14T04:30:24.9466667+00:00

@Zhenqi Liu
We are reaching out to the internal team to get more information related to your query and will get back to you as soon as we have an update.

Share via

Azure Data Factory dataflow bad schema mapping

1 answer

Your answer