In this article we will have a quick demo on how to save JSON data as string in Azure Synapse Analytics SQL Pool using “Map complex values to string” feature of mapping
This article will help you to solve below issue as mentioned in image:
“The retrieved type of data JArray is not supported yet. Please either remove the targeted column ‘OrderJson’ or enable skip incompatible row to skip the issue rows”
Now let us have a quick demo to copy & save the JSON as string into synapse table:
- Source Dataset is created based on the JSON file of Azure Data Lake. This JSON file is individual customer record with address and purchase details.
- Destination Dataset is created based on ‘OrderNew‘ table of Azure Synapse SQL Pool table. one table ‘OrderNew’ with required columns is already created.
Let’s have a look at below image for the source dataset and preview the data.
Here we will save “orders” hierarchical data will be saved as string in a column and save order number, order date, all customer object property in respective column.
Let’s have quick look at below image having target synapse OrderNew table with a varchar column to save order “orders” hierarchical data
Now let’s focus only on mapping tab of Copy activity:
Please refer below image and follow the step subsequently
First we will not check the “Map complex values to string” feature of mapping as per below image.
Now complete the mapping and trigger the run debug on the pipeline and monitor the pipeline run
The pipeline execution will fail displaying the below error:
- Error Type: “User configuration issue”
- Error Message: “The retrieved type of data JArray is not supported yet. Please either remove the targeted column ‘OrderJson’ or enable skip incompatible row to skip the issue rows”
Error message clearing stating to remove target OrderJson column which actually causing the issue.
Now let’s check the “Map complex values to string” feature of mapping and run the trigger the debug to run the pipeline.
Now we can see the copy is successful as per below image:
Let’s verify the same in Azure Synapse OrderNew table as below images:
Here we can see the entire order json is saved in OrderJson column of OrderNow table of Azure Synapse Analytics SQL Pool. Please refer above source preview data to compare this table data and source data.
So in this article we saw how json data can be saved in string column using Azure Data Factory copy activity and its “Map complex values to string” feature of mapping.
Thanks for reading the article; Please let me know your query/thought on this in comment section below.