topic Re: Get data from REST API source with iterated nested API calls (with Data Pipelines) in Pipelines

Get data from REST API source with iterated nested API calls (with Data Pipelines)

iBusinessBI — Fri, 21 Jul 2023 12:26:34 GMT

I have a working Dataflow that does the following:

1. Pulls a Customers list from an API call (https://api.somecrm.com/v1/customers).
2. For EACH Customer it calls another API to get his/hers purchase history (https://api.somecrm.com/v1/purchasehist/{customerid})

3. I have 3 other nested queries, but I'll skip them for simplicity.

I am trying to build the same solution in the new Pipelines in the Fabric. Two reasons for this:

1. Although the Dataflows works fine, it sometimes gets: "429 Too Many Requests error", so I feel that Pipelines will give me more control, such as "Wait" step.

2. I would like to save some outputs as JSON files in a Lakehouse, not just tables.
I am new to this environment so I'd like to learn how can I implement this scenario.

I've seen several documentations and videos, but I am looking for the this specific scenario - several API calls with an iteration...

I tried to build but got stuck in the ForEach activity with a syntax - how do I tell it how to get a specific customerID...

Please help

Thanks

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

HimanshuS-msft — Sun, 30 Jul 2023 18:11:50 GMT

Hello iBusinessBI

Can you please elaborate
"I tried to build but got stuck in the ForEach activity with a syntax - how do I tell it how to get a specific customerID."?
I am confused as the FOREACH will iterate all the CustimerID and not any specific one .
Thanks
Himanshu

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

iBusinessBI — Mon, 31 Jul 2023 08:33:06 GMT

HimanshuS-msft
Sure.

Here is my screenshot.

The Web1 activity calls an API that returns JSON with many customers including id and other fields.

For every customerid I need to make another API call.

What do I write in "Items"?

Thanks

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

ajarora — Mon, 31 Jul 2023 19:09:04 GMT

You should specify the json property name (or path if its nested under a different top level property), which yields the collection of customers.

In general, one good way to debug is by providing a dummy property, and at runtime the failure message will indicate to some extent what properties were available to choose from.

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

iBusinessBI — Mon, 31 Jul 2023 19:22:59 GMT

Exactly my question.

What is the exact syntax for specifying the json path+property (it is nested).

I've googled but couldn't find samples.

Also, sholud I use Web activity for the first call? I saw people use Lookup activity, too.

Which one is the correct?

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

Gregory_Pinard — Wed, 02 Aug 2023 18:30:18 GMT

Try @activity('Web1').output.value
Then, in your nested activities, you can have a parameter referencing customer id and other fields with this pipeline expression : @item().customerid

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

PanuO — Wed, 11 Oct 2023 05:36:59 GMT

Is it possible to use the @item() properties in mapping? I tried directly use the iteration variable, but I got an error about typing. The UI does not allow manually setting type, so next I tried to create dynamic mapping and define the type there, but then I got an error about invalid JSON (even though JSON is valid).

"source": {
"path": "@item().id"
},

"JsonException,Message=Array index expected."

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

iBusinessBI — Mon, 30 Oct 2023 18:35:59 GMT

Please vote for this idea to make pulling data from API sources easier:
https://ideas.fabric.microsoft.com/ideas/idea/?ideaid=2bc79710-f776-ee11-a81c-000d3adc66b1

Re: Get data from REST API source with iterated nested API calls (with Data Pipelines)

Ayybee — Fri, 22 Mar 2024 22:02:21 GMT

First, you need to set up a WEB Activity that pulls the customer list by calling the API/customers.
Once you have the customer list, you have to set up a ForEach Activity.
In ForEach Activity, go to Settings -> Items -> Add Dynamic Content. Array(activity([NAMEOFWEBACTIVITY]).output.data)
Here, you need to access the data from JSON.
Now, add a copy activity inside the ForEach Activity in order to save the JSON files into the lakehouse directly.
To access the JSON objects via "customerId" go to the CopyActivity inside ForEach Activity.
Source -> Add the connection here.
In Realtive URL -> Add dynamic content @concat('purchaselist/',item().customerId)
Set up the destination where you want to save the JSON objects as JSON files and run the pipeline.