Long Running REST with Pagination

[KHSDM]

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.

[p_verkooijen]

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

[KHSDM]

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

[p_verkooijen]

Hi @KHSDM ,

The REST connector has no solution for this.

[marksouzacosta]

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

[p_verkooijen]

@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