Azure Synapse Analytics - Pipeline API source with pagination

Question

Azure Synapse Analytics - Pipeline API source with pagination

Nico Deunk | Bliksems! 1

We use a pipeline in Azure Synapse Analytics to retrieve data from a REST API. This API has pagination support. The response contains a 'nextLink' value when there is more data to retrieve. Het nextLink value is a relative path to the API base URL. The problem is that Synapse determines the base URL itself and does not take the base URL from the integration dataset.

Setup
Integration dataset base URL: https://api.<system>.nl/wfm/
Relative URL in nextLink response: /v1.0/persons?key=...

Synapse will create a request to: https://api.<system>.nl/v1.0/persons?key=...
So the /wfm/ part is missing.

Anyone suggestions how to resolve this? Thanks.

1 answer

Your answer

Answer 1

Wilko van de Velde 2,231

Hi @Nico Deunk | Bliksems! ,

It is possible to create a dynamic relative URL in the dataset:
User's image

Use the concat function to combine several strings, for example:

User's image

Here I combine the string '/wfm/' with a dataset parameter.

Hope this will help

Kind Regards,

Wilko

Please do not forget to "Accept the answer” wherever the information provided helps you, this can be beneficial to other community members. If you have extra questions about this answer, please click "Comment".

Nico Deunk | Bliksems! 1 Reputation point

2023-12-14T08:49:09.5933333+00:00

Hi Wilko,

Thanks for your response.

I'm looking for a way to get my 'pagination' working. See my setup below.

Synapse takes my 'Base URL' without /wfm/ for the second request (triggered by the pagination rule).

I don't think your solutions will help my with this, isn't it?

Nico
Wilko van de Velde 2,231 Reputation points

2023-12-14T10:06:43.8333333+00:00

Strange, I tested it with a free online API (https://pokeapi.co/api/v2/pokemon?limit=100&offset=0) and it did not remove anything from the base URL.

Dataset:

Source:

The API also contains a "next" value, but this is the complete URL, so I used "count" just to test it.

As expected, I got a 404 not found error with the following url: https://pokeapi.co/api/v2/1292 . This is the Base URL + the value from count.

In the screenshots I did not use a parameter for the relative url, but I also tested that and it works as well.
Nico Deunk | Bliksems! 1 Reputation point

2023-12-14T11:43:45.24+00:00

Hi Wilko,

Really cool you're taking time to help me. Thanks.

I've almost the same setup indeed. Strange it doesn't work with me. I investigated a little further, and I know what's causing the error. My 'nextLink' value starts with an / .

If I test with the 'nextLink' value as my relative URL and remove that starting /, the call succeeds. If I don't, I get the same error message as with the pagination rule active.

So the question is, how to remove the first character of the nextLink value. :)

With the starting /

Without the starting /
Hulshof E. (Eric) 0 Reputation points

2024-03-05T17:50:41.09+00:00

Hey Nico, did you happen to get a fix for this? I am encountering the same problem at the moment.
Nico Deunk | Bliksems! 1 Reputation point

2024-03-06T09:44:49.6433333+00:00

Hi Eric,

No, I didn't. I implemented a custom 'nextLink' process (set variable > while loop > copy data > lookup new value) .

Annoying, but I couldn't get it fixed in any other way.
Hulshof E. (Eric) 0 Reputation points

2024-03-06T09:50:46.19+00:00

Thankyou for getting back to me!

We will write the whole process in Python then.
Also, to complete this thread i'd like to add the following:

Azure Data Factory behaviour is RFC-compliant behaviour. RFC-standard defines that relative url's SHOULD NOT start with / -character. Using / -character will cause the path to be assumed as absolute path to the domain. Standard here: https://datatracker.ietf.org/doc/html/rfc1808#section-5

So it makes a bit more sence as to why it is not working.
Hulshof E. (Eric) 0 Reputation points

2024-03-06T09:56:10.42+00:00

Hey Nico,

Thankyou for reaching out so fast! We will be building this solution in python.

I'd just like to add this piece of information to this thread for the next one with this problem:

Azure Data Factory behaviour is RFC-compliant behaviour. RFC-standard defines that relative url's SHOULD NOT start with / -character. Using / -character will cause the path to be assumed as absolute path to the domain. Standard here: https://datatracker.ietf.org/doc/html/rfc1808#section-5

So it makes a bit more sence to why it is not working the way we would like it to.
Nico Deunk | Bliksems! 1 Reputation point

2024-03-06T12:07:02.4333333+00:00

Thanks for the information Eric! Great.

I'll reach out to the developers of the API, maybe they will change the nextLink value. :)

Share via

Azure Synapse Analytics - Pipeline API source with pagination

1 answer

Your answer