topic Re: Long Running REST with Pagination in Connectivity & Data Prep

Long Running REST with Pagination

KHSDM — Mon, 09 Dec 2024 07:08:13 GMT

Hi all,

Currently, I'm loading data from a REST connection with pagination. The REST query is authenticated using a JWT token. I'm using the WITH CONNECTION clause to insert the token in the header.

However, the query runs quite long, causing the token to expire and the loading to fail. Is there any workaround for this?

Thank you.

Using QSEoW.

Re: Long Running REST with Pagination

p_verkooijen — Mon, 09 Dec 2024 07:15:58 GMT

@KHSDM how do you manage the pagination? If you do this manually in a loop you could check the expiration during loops.

Re: Long Running REST with Pagination

KHSDM — Mon, 09 Dec 2024 07:35:19 GMT

I'm using the built-in pagination option. Using the Next URL option.

Re: Long Running REST with Pagination

p_verkooijen — Mon, 09 Dec 2024 13:45:41 GMT

Hi @KHSDM ,

The REST connector has no solution for this.

Re: Long Running REST with Pagination

marksouzacosta — Mon, 09 Dec 2024 14:21:48 GMT

Hi @KHSDM,

If you are setting the pagination yourself, I mean, it is not automatically set in the Data Connection configuration, you can do incremental loads before each pagination. But there may be a catch here. You should be able to retake from where you have left and that depends on how the REST API was setup on the backend. So, you could loop your REST Connection until the NextURL is blank.

Another possible alternative is to do data partition. Try to apply filters to your requests to reduce the number os data returned. For example, let's say you could loop through a list of countries to return subsets of data. Instead of returning all the records at once, you can return these lists per country and save all the data in the same QVD - or even partitioned QVDs, one for each country.

Regards,

Mark Costa

Re: Long Running REST with Pagination

p_verkooijen — Tue, 10 Dec 2024 14:40:53 GMT

@KHSDM this is a short example how to handle a NextURL manually using a loop and WITH CONNECTION

First create a subroutine to check if the authentication is still valid.

SUB Auth

IF ExpireDateTime<Now() THEN

**** your auth call****

ENDIF

End Sub

Then start the loop

LIB CONNECT TO 'REST_Connection';

SET NextURL=https://your.urltostart.with/api/orders;

Do While NextURL<>Null()

TRACE NextURL = $(NextURL);

CALL Auth;

RestConnectorMasterTable:

SQL SELECT

"Transfer-Encoding",

"Connection",

"Content-Encoding",

"Link",

"original-username",

"strict-transport-security",

"vary",

"Cache-Control",

"Content-Type",

"__KEY__response_header",

(SELECT

...........

FROM JSON "_response_header" PK "__KEY__response_header"

WITH CONNECTION(Url "$(NextURL)");

[_response_header]:

LOAD [Transfer-Encoding],

[Connection],

[Content-Encoding],

[Link],

[original-username],

[strict-transport-security],

[Cache-Control],

[Content-Type],

[__KEY__response_header]

RESIDENT RestConnectorMasterTable

WHERE NOT IsNull([__KEY__response_header]);

DROP TABLE RestConnectorMasterTable;

//COALESCE o.b.v. Textbetween iteratie 2 en dan 1

//- For the first response you need the first TextBetween <>, al subsequent need the second

//- At the last response there is no next link, only a previous. Therefore a WILDMATCH on *rel="next"*

LET NextURL=IF(WILDMATCH(PEEK('Link', 0, '_response_header'), '*next*'), COALESCE(TEXTBETWEEN(PEEK('Link', 0, '_response_header'),'<','>',2),TEXTBETWEEN(PEEK('Link', 0, '_response_header'),'<','>',1)), Null());

DROP TABLE _response_header;

Loop