CODEWITHSUNDEEP
azure-synapseazure-data-factory
mjimenez
mjimenez

Reputation: 41

Concatenate with a "," the responses from Rest DataSource in a Copy Activity of Azure Synpase/Data Factory

I have a copy activity that is using a REST data source to query an API with pagination the response format is JSON and a Azure Data Lake Gen 2 JSON file as the Synk, my issue is that the output of the copy activity is added each rest pagination response JSON object in each line of the output JSON file, making it a not valid JSON file.

in example if the source task of the copy activity query 3 pages from the API the output Json file will look like:

{items:[content of page 1]}
{items:[content of page 2]}
{items:[content of page 3]}

Sample REST output:

{
   items:[
      "property1: "value1",
      "property2: "value2",
      ...
   ]
}

I have tried doing this with a loop activity and writing a JSON file for each page/response from the API, but I feel that will incur on many I/O operations and therefore increase the cost.

I would like to format the output (JSON file) to a valid JSON format so it can be consumed for other services, in a single copy activity call: in example:

{items:[
{items:[content of page 1]},
{items:[content of page 2]},
{items:[content of page 3]},
]}

EDIT Screenshots of current configuration

Copy Activity: screen shot of copy activity

Output JSON File screen shot of output json file

Upvotes: 1

Views: 283

Answers (1)

Aswin
Aswin

Reputation: 7156

In order to concatenate all responses with , comma, you need to change the file pattern to Array of Objects. Below are the steps to change the file pattern.

  • Click the Sink settings tab of copy activity.
  • In File pattern, Select Array of objects. If this value is left blank or Set as Set of objects, it stores each Json response in a separate line.

enter image description here

Output data when File Pattern is Set of Objects: All Json object is stored in a separate line. enter image description here

Output data when File Pattern is Array of Objects: All Json objects are concatenated with commas. enter image description here

Upvotes: 0

Related Questions