Azure Data Factory: Copy activity to save Json from Rest API as CSV/Parquet to ADLS Gen2

Question

Trying to save Json output from Rest API as CSV/Parquet file to ADLS Gen2 using Copy activity.

Successful connection to Rest API and can Preview data in Source tab on Copy activity.

Object:
[
{
"Assets": [
{
"ActivationStatus": 0,
"AssetDescription": null,
"AssetId": 2517322
} ,
...
],
"TotalCount": 3436
}
]

But no data is being saved to CSV, the file is empty.
Would like to get the schema automatically to target, but tried manual mapping as well to see if data can be saved. But no luck.

Answer 1

Hello @Leela Yarlagadda ,

Thanks for the question and using MS Q&A platform.

As per my understanding you are trying to save the API response (JSON) to a CSV/Parquet file in your ADLS Gen2 using ADF copy activity, but you are seeing an empty file generated. Please correct if my understanding is incorrect.

I just tried with a free API to test it out noticed the same behavior as yours at first go, then under mapping section of copy, I tried to import schemas and then specified the JSONPath of the nested JSON array for cross-apply (nothing but selecting the Collection Reference) and then for all the null types in source columns, select schema type as string for corresponding sink columns and the data copy was successful. And I can then see the data available in CSV file.

Here is the API I tried to reproduce the problem:

• Base URL : https://www.thecocktaildb.com/
• Relative URL: api/json/v1/1/search.php?s=margarita

Below is my source structure which is pretty much similar to your source schema structure. Here is the sample data: 189886-apiresponsejsonarray.txt

Below are the mapping settings I have configured to avoid mapping issues because of null data types from source.

Here is the sample output copied to my sink file:

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators