Cursor Pagination in GraphQL API

[PS41]

My requirement is to extract data from GraphQL API and load into Azure SQL database which works fine for less volume of data.

When the data volume increases the GraphQL API returns a token as "next" which when called in the right way would allow me to access the next page of data. I understand this is cursor pagination where we need to store the primary key value to a context variable and request again to get the next page of data.

I have mapped the next token to a variable but unable to use it as a Query parameter in tRestClient within a loop to get the next set of data.

Could someone explain to me as what's the best way to implement it.

Thanks

[Anonymous]

Can you show us your job (a screenshot should do) and also include any code you are using? This isn't terribly difficult, so I suspect that you are more than likely nearly there, just need a few pointers.

[PS41]

Thanks @Richard Hall​ 

I have created the below parent job which fetched the graphQL API and extracts the fields and loads into the Azure SQL database

The second one is the child job where the “next” token value is stored in the key “nextpage”

If I add the token value as a query parameter in the RestAPICall it throws me an error. Tried the same token as query parameter it still fetches the first set of data. Unsure as to how to proceed in passing this “next” cursor value to bring the set of results until the cursor value is null.

Any pointers is much appreciated. 

[Anonymous · Accepted solution]

Ah I see. OK, take a look at this question here.....

https://community.talend.com/s/question/0D53p00007vCj0YCAS/iterative-data-extraction-pagination-and-polling-from-rest-api-using-trestclient

You are essentially doing something very similar. To get this to work for you you'll need to add a tLoop which is driven by the presence of a "next" token. The first time you run, you'll need to think about how to include the "next" token if it doesn't exist. But after that, base it on the presence of a value. The "next" token will come from your tExtraJSONFields component. You will need to add a tJavaFlex to carry out this part. You'll need to set the value of a globalMap key/value pair. The globalMap key/value pair will be used to drive the tLoop.

Have a play following the example and let me know if you have any further questions.

[PS41]

Thanks @Richard Hall​ , the above link helped me to arrive at a base framework for my job as below where I'm storing the nextToken as a global variable. But I'm unable to change the InputQuery to consume this nextToken within the "body" of the graphQL API.

Any pointers would be helpful to use a global variable within context variable.

[Anonymous]

It's difficult to tell in detail what you are doing, but I think I can guess at this stage. You are saving the nextToken in your SetNextToken tJavaFlex. At this point, the tLoop will fire the next loop. Are you updating the InputQuery with the nextToken at this point? If not, you need to do this here. If you are, I am guessing that maybe it is not being passed to be used by your RestAPI component.

If you are doing the above and it is not working, can you share your code and maybe I can see why it is not working from there.

[PS41]

I'm storing that next token value as a global variable. My context body for GraphQL API call is as below.

context.body = "{\"query\":\"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}\", \"variables\" : {\"siteId\":[\"2345\"],\"nextToken\": \"ytyunil348Wq\"}}";

Now I need to pass the next token dynamically which I have stored it in the global variable. I tried concatenation which din't work. Within Loop, I have set the condition as to run until the nextToken is null which means we have read the last page.

How do I pass the nextToken stored in the global variable into context.body and fetch the next page of data is my question.

Thanks

[Anonymous]

First of all, I would use the globalMap for your body. Context variables are great for setting values before a job starts, but I would use the globalMap for playing with values during the running of a job.

When you set your body originally, I presume that the above is what you need? If so, it would be like this using the globalMap....

globalMap.put("body", "{\"query\":\"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}\", \"variables\" : {\"siteId\":[\"2345\"],\"nextToken\": \"ytyunil348Wq\"}}");

To retrieve this to output as column data, it would carried out like this....

row1.columnname = ((String)globalMap.get("body"))

Now, to update your body, you can do it like this....

globalMap.put("body", "{\"query\":\"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}\", \"variables\" : {\"siteId\":[\"2345\"],\"nextToken\": \""+ ((String)globalMap.get("body")) +"\"}}");

You will need to figure out how you want to set your initial "nextToken". Maybe this could be set in the globalMap at the beginning of the job.

[PS41]

Thanks for your guidance @Richard Hall​ . I have changed the context variable to global variable now. But I still feel that global variable is unable to pick the value dynamically from the next Token variable.

I'm using a if else clause and if it's first instance I'm using the query without the next token and once the global variable contains a value it hits the else part. This s where I'm using the query with 'next token global variable', but it odes not pass dynamically. I printed the this body global variable and it just prints ((String)globalMap.get(\"nextToken\")) and not the actual value of the nextToken variable. I have added \ before and after the variable to avoid 400 bad request but still of no use.

globalMap.put("body", "{\"query\":\"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}\", \"variables\" : {\"siteId\":[\"2345\"],\"nextToken\": \""+ ((String)globalMap.get(\"nextToken\")) +"\"}}");

Do you have any other suggestions for this?

[Anonymous]

The code you have posted above seems to produce some JSON that may be incorrect. First of all, you do not need the "\" when it is used with the globalMap. The globalMap is a HashMap which is being referred to as pure Java. It is separate from the rest of the JSON String. So I edited this and tested this code.....

globalMap.put("body", "{\"query\":\"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}\", \"variables\" : {\"siteId\":[\"2345\"],\"nextToken\": \""+ ((String)globalMap.get("nextToken")) +"\"}}");

I then ran that and printed out the result. It produced this.....

{"query":"query Payers($siteId: [SiteId!]!,$nextToken: PayersCursor) {Payers{listBySiteIds(siteIds: $siteId,nextToken: $nextToken) { result {Id title total Date Number} next}}}", "variables" : {"siteId":["2345"],"nextToken": "12345ABC"}}

I set the value of "nextToken" to be 12345ABC.

Can you copy and paste the exact code that you are using for this AND the exact code you are using to set the "nextToken" value from the JSON?