topic Re: Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful in Pipelines

Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful

bala_ci — Mon, 07 Aug 2023 08:35:11 GMT

Hi All,

My goal: Loading Json response to multiple tables

Steps in the pipeline to the above:

1) Copy acitivity - which will connect REST API and JSON response saved as json file in the Lakehouse file section - Successful

2) On completion of above activity (1), Copy activity which reads the json file from the lakehouse and thru Import schema - Mapping done for "Sales" (Json parent records) and copied to a delta table "sales" - Successful

3) On completiono of above activity (1), Copy activity which reads the json file from the lakehouse file and thru Import schema Mapping done for a inside Json array. For example, ['sales'][0]['orders'] - Project reference to import the schemas and store it to different tables "orders". - Successfull but only records in the array[0] is copied to the tables not all the records in the Json.

4) On completion of above activity (1), Copy activity which reads the json file from the lakehouse file and thru Import schema Mapping done for a inside Json array 'discounts'. - Not successful. Not all sales have discounts json array. Even though I struggled to map the discounts json format but records are not stored into tables. First json records does not have discounts so it stopped there.

My queries:

1) How to debug the pipelines activity?

2) Why only one array values are copied to the tables not all the records in the json?

3) Why if the json contains a empty json array are not skipped instead entire copy process is stopped?

Please help me to solve the above issues. If you need any clarification, please let me know.

Thanks

Bala.

Re: Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful

Anonymous — Mon, 07 Aug 2023 16:10:28 GMT

Hi bala_ci Thanks for posting your question in Microsoft Fabric Community

Currently, I am checking with the Internal team on your ask and provide you with more details once I hear back from the team.

Regards

Geetha

Re: Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful

bala_ci — Tue, 08 Aug 2023 16:08:33 GMT

Any Updates

Re: Loading Json (Json inside array of json) response to a Lakehouse Delta tables was not successful

ajarora — Tue, 08 Aug 2023 21:50:04 GMT

For question#2: you may need to specify that `sales` is a collection, have you tried using the setting: "collection as reference" ?

More details here: https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-schema-and-type-mapping#hierarchical-source-to-tabular-sink

For question#3: I am not sure about your question, are you expecting the copy activity to have not stopped when the array was empty, and keep proceeding to copy the rest of the data ? What do you mean it stopped, did the activity fail ?

Can you try to enable "Skip incompatible rows" option under Settings -> Fault Tolerance and see if it helps ?