Pagination of API responses

[yesoreyeram]

Available from: Infinity version 2.0.0
Supported types: JSON
Supported parsers: Backend

Pagination

When calling an API, if the underlying system have lots of results typically the server will send responses in multiple pages.

Pagination in Infinity

Introducing pagination of API response in Infinity 2.0. Prior to infinity version 2.0.0, you can only one page at a time. To call multiple pages and consolidate, you might be adding multiple queries manually and use merge transformation.

Types of Pagination

In the modern API world, pagination is implemented in different ways. Following are the common pagination mechanisms supported in infinity

• Offset based pagination
• Page number based pagination
• Cursor based pagination

Offset based pagination

TODO: yet to be documented

Page number based pagination

TODO: yet to be documented

Cursor based pagination

TODO: yet to be documented

Iterating URLs in a loop

Apart from the regular pagination methods mentioned above, From infinity 2.0, you have the ability to iterate the URLs based on comma separated values.

For example, we would like to call 3 different pokemons API items and show in a single result. We will be using following three URL.

• https://pokeapi.co/api/v2/pokemon/bulbasaur
• https://pokeapi.co/api/v2/pokemon/ivysaur
• https://pokeapi.co/api/v2/pokemon/venusaur

As you see, we have three different URLs with 3 different paths. So I am going to to parameterise this as follows.

• https://pokeapi.co/api/v2/pokemon/{{ pokemon_name }}

Now in the infinity pagination, I can instruct it to replace {{ pokemon_name }} part of the URL with bulbasaur,ivysaur,venusaur. Under the hood, infinity will split the values by , and then replace {{ pokemon_name }} with each item in the list and make the API call.

Below screenshot is an example of how to use it.

When used with dashboard variables, you can replace the hardcoded list of items to point a variable. Example: When you have a multi-value variable called pokemon_id, you can refer it as ${pokemon_id:csv} in the list value.

Limitation

Number of maximum pages per query is limited to 5. This is to avoid resource exhaustion and DDOSing of the target system.

[ColdFire87]

@yesoreyeram this looks pretty good and useful. Do you have any date in mind releasing this? Thanks!

[mrdeadlocked]

Is it possible to up the 5 page limit? My REST API currently has a limit of 500, I can set up pagination but I only see 2500 results of the 20,000 we have. Can I increase the limit on my end someplace from this 5 page limit?

[chi-miel]

Hello,
Hello, does this feature also allow pagination in a graphql api ?

In my case, i use body type GraphQL and i need to explicitly indicate page number like :

query {
  list_server(name_search:"server_name",page:1){
    name
  }
}

[yesoreyeram]

@chi-miel - yes you can use. use the replace URL in the field type of your page parameter and then use the corresponding page tokens in the graphql query/post body.

[yesoreyeram]

@rucciva - yes it does. Use the replace URL in the field type.

[ac3xx]

Is pagination via the next page cursor still unsupported? Hitting a similar issue with the GitLab GraphQL API currently.

[dharman0125]

The hard limit of 5 pages max is too low of a limit. Understand trying to protect the source however for large datasets more pages are required.

[yesoreyeram]

Then potentially you are using wrong tool for the job.
What's the use case ?

[dharman0125]

A source tool with a hard limit of 500 datapoints per query and pagenates and I need more then 2500 datapoints.

[vesenthil]

Hi ,
My api will have more then 60000 data .i want to use pagination to retrieve the data in single hit. Which type of pagination is suitable?

[vesenthil]

I used offset pagination and getting below error.

[yesoreyeram]

60000 records

It looks like Grafana infinity is not the right choice for querying such a huge dataset. Anything involve more than 5 API calls / 10000 records, I will consider as huge performance impact. Consider querying aggregated results instead.

[jpike88]

@yesoreyeram with all due respect, I know better what tools to use for the job, I get paid to be good at that.

Instead, I see people here doing the painstaking workaround of making A,B,C queries because you limited your tool's functionality.

If 1 page however is only 10/20 records, that's just 100 records, or 10 records depending on the volume of data being fetched.

In other words please loosen the restriction and let developers make their own decisions.

[CoolZeroNL]

I'm also facing the issue of the hard limit of 5 pages max is too low of a limit. The target API im using has a hard limit of 13 records (dont ask me why) and cant be changed by input. So in this case it will only pull 65 records what is to low because in my case there are more records to process.

[CoolZeroNL]

Regarding the way of paging is working. I have some trouble te find out how to parse nested data when they are been paged. There seems is a differand behaver when the data is collected when using paging, and the inline option.

So when the data is been loaded trough the api, and is paged, we have/see the data returned in the 5 rows.

The nested data we want is that in "hits" from the api, there are 13 items in it. So now i will tell the plugin that the root is: hits and this returned in a error:

logger=plugin.yesoreyeram-infinity-datasource t=2024-02-29T16:18:19.239291243Z level=info msg="performing QueryData in infinity datasource" summarizeExpression= endpoint=queryData dsUID=ac4b8026-c324-4671-8f40-bb410b5372bd filterExpression= source=url url="http://127.0.0.1:9002/" dsName=infinity-datasource-csrf-xsrf-proxy pluginID=yesoreyeram-infinity-datasource root_selector=hits settings.AuthenticationMethod=none settings.OAuth2Settings.OAuth2Type= parser=backend type=json uname=admin uql=parse-json
logger=plugin.yesoreyeram-infinity-datasource t=2024-02-29T16:18:19.655897329Z level=error msg="plugin process exited" plugin=/opt/grafana/plugins/yesoreyeram-infinity-datasource/gpx_infinity_linux_amd64 id=108341 error="exit status 2"
logger=context userId=1 orgId=1 uname=admin t=2024-02-29T16:18:19.655916109Z level=error msg="Internal server error" error="[plugin.downstreamError] client: failed to query data: Failed to query data: rpc error: code = Unavailable desc = error reading from server: EOF" remote_addr=127.0.0.1 traceID=
logger=context userId=1 orgId=1 uname=admin t=2024-02-29T16:18:19.655997872Z level=error msg="Request Completed" method=POST path=/api/ds/query status=500 remote_addr=127.0.0.1 time_ms=432 duration=432.325643ms size=116 referer="https://fdn/d/d94b141c-abd3-4a85-862b-559fd4618ab1/dashboard?editPanel=3&orgId=1&tab=query" handler=/api/ds/query

When i change it to: [hits] i will get the hits, but they will not be extracted as expected.

So i have striped down the json, create a array of 2 and use it as inline data:

[{
    "hits": [
        {
            "id": 1,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "1",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        },
        {
            "id": 2,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "2",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        }
    ],
    "center": {
        "lat": 1.87,
        "lon": 1.45
    },
    "total": 87,
    "pages": 7,
    "page": 1,
    "totalDisplay": 87,
    "description": ""
},
{
    "hits": [
        {
            "id": 3,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "3",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        },
        {
            "id": 4,
            "provider": "x",
            "description": "x",
            "published": 1,
            "properties": {
                "A": true,
                "B": true

            },
            "media": [
                {
                    "id": "123",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                },
                {
                    "id": "456",
                    "formats": {
                        "xxs": "xxs.jpg",
                        "xs": "xs.jpg"
                    },
                    "orientation": "landscape"
                }
                
            ],
            "location": {
                "lat": "1.32",
                "lon": "1.01"
            },
            "address": "x",
            "agent": {
                "id": 1,
                "name": "x"
            },
            "city": "x",
            "features": [],
            "_id": "4",
            "priceInfo": {
                "price": "1.00"
            },
            "photo": {
                "id": "123",
                "formats": {
                    "xxs": "xxs.jpg",
                    "xs": "xs.jpg"
                },
                "type": 2,
                "orientation": "landscape"
            },
            "link": "x"
        }
    ],
    "center": {
        "lat": 1.87,
        "lon": 1.45
    },
    "total": 87,
    "pages": 7,
    "page": 2,
    "totalDisplay": 87,
    "description": ""
}]

Whenever i now apply the paring hits the field is as expected selected as root, and the 2 enties are been extracted and showed as seperated rows.

ps, after some hours debugging the json file , seems that there is a strange behaver of it and found that the items in hits are not correct been used as in when there is no number, the API return "" instead of null. Is this something for the pluging to catch an fix presend failing the query ?

{
    "hits": [
        {
            "anumber": "",
            "anumber_base": ""
        },
        {
            "anumber": "121",
            "anumber_base": 121
        }
    ]
}

[wastegum]

Hi, I'm having some trouble using the pagination feature with a json "next" cursor. My API response data is formatted as follows:

{
  "somekey": "somevalue",
  "results": [
    {
      "date": "some timestamp",
      "result": "data I care about"
    },
    {
      "date": "some other timestamp",
      "result": "more data I care about"
    }
  ],
  "_links": {
    "next": {
      "href": "https://myapiurl/myendpoint?cursor=foobar"
    }
  }
}

As you can see, the data I care about is nested inside an array called "results", and I have no problem extracting this data by adding "results[]" in the "Rows/Root" parsing field with the Backend parser.

The reference to the next page exists outside of the results[] array and the API is providing me with a complete URL instead of a "next" marker. It seems that the plugin should support this via URL replacement, but no matter what I try I can't get it to pull in additional pages.

Here is the latest config I have tried:

Any help would be appreciated because without this plugin I'll need to either go write a custom plugin for this data source, or resort to replicating the data elsewhere in a data warehouse which seems like a waste of time and effort.

[jkroepke]

There is an open PR to support this scenario: #784

[cm-slu]

Hello, your solution is great.
Is there a solution to have more than 5 pages?
in my case I have 16 pages but I can have even more

[bahadir-be]

agree!
Can you make more than 5 pages in the new version?
We are using Azure managed grafana solution and we can not edit source code of this plugin :/

[GeoHaz7]

@yesoreyeram hello this is amazing stuff thank you for your work, i have a question thought.

when using Iterating URLs in a loop is there a way to add the variable that i used to loop as a column to have that value in each row to know to which variable value it belongs to?

[benayyad12]

Hello,

I solved pagination problem by adding multiple queries A,B,C for example. in A i used per_page=100 and number page_number 1 , for B per_page=100 and page_number 2. Then I merge using the transformation Merge by Id. I got all elements of page one and two in the same dashboard.

This is without BACKEND parser, it works with UQL

Thank you
Abdessalam BENAYYAD

[frbosch]

Hi, i am trying to use the pagination feature with Azure IoT-Hub. IoT-Hub API uses headers for a cursor:

x-ms-max-item-count request header to set the page size.
X-Ms-Continuation response/request header for the cursor.

I am using this configuration and it does not work:

Do i need to set something in "Extraction path"? What does it extract using "Headers" field type?

Thanks a lot.

[Tukkan]

Correct me if I'm wrong, but I don't really understand the 5 pages limitation. If the data is paginated, why is the number of pages a performance issue?
If number of items is 20, you're fetching always 20 items anyways and the total number of items is usually passed in response data or header.
Thanks

[yesoreyeram]

Hiya.. Sorry that you are having trouble with max pages. Can you provide your thoughts and usecases in reply to #601 (comment) ? The more usecases we get, we can expedite this feature.

[Walkablenormal]

This has been mentioned numerous times in this thread, but I also believe the '5 pages' limitation should be configurable. Even when examining Grafana's native APIs, such as the Grafana OnCall API, some routes return 50 records per page. The page size is not configurable. In my opinion, a limit of 250 records is far too conservative.

[yesoreyeram]

Hiya.. Sorry that you are having trouble with max pages. Can you provide your thoughts and usecases in reply to #601 (comment) ? The more usecases we get, we can expedite this feature.

[isaacsanders]

Check out the GitHub GraphQL API for an application in GraphQL for this. It might be nice to give the GraphQL mode access to pagination.

[jpike88]

My eventual solution:

Fivetran + neon postgresql database (both have a free tier)

Works wonders

[jrote1]

There are many cases where the API that's being called is an internal API so it should be down to the developer to choose what the max number of pages is, can the limit be removed from max pages?

[yesoreyeram]

Hiya.. Sorry that you are having trouble with max pages. Can you provide your thoughts and usecases in reply to #601 (comment) ? The more usecases we get, we can expedite this feature.

[yulipumal]

Also facing the 5 pages limitation problem on an internal API.

[yesoreyeram]

Hiya.. Sorry that you are having trouble with max pages. Can you provide your thoughts and usecases in reply to #601 (comment) ? The more usecases we get, we can expedite this feature.

[valentintraen]

Same problem with pagination. 5 is very very low

[yesoreyeram]

Hiya.. Sorry that you are having trouble with max pages. Can you provide your thoughts and usecases in reply to #601 (comment) ? The more usecases we get, we can expedite this feature.

[yesoreyeram]

Background and current status of pagination feature ( Nov 2024 )

Pagination feature was introduced in infinity backend parser an year ago as an experimental/beta/undocumented feature. As there are different types of pagination and much complexities behind handling them, still we aren't in a position to make it out of beta. The complexities include how to handle error in one of the pages, when to stop the pagination, how to handle partial data, how to handle no data vs error, how to stop racing condition, making parallel requests for paginated queries to improve the performance etc.

Though this feature is in beta, We love the enthusiasm of the community who tried the pagination feature and providing continuous feedback and exploring the potentials of the infinity plugin.

Maximum number of pages option & issues behind

When the pagination feature was introduced, I strongly believed that getting paginated results in grafana is a unusual/unconventional use case. With Grafana's use cases user should query aggregated results instead of pulling every data and post processing/filtering/aggregation in the grafana where possible. Also loading large number of results with pagination into grafana's memory will put additional stress to grafana server. If one of the poor query triggers heavy pagination, it will affect all other grafana users in that instance.

While considering the pagination, we do consider not only the requests coming from dashboards but also other cron jobs such as alerting, recorded queries etc. Increasing the max pagination will increase the grafana server memory/cpu in such cases as well.

I agree that there are workarounds that a user can trigger multiple paginated queries via options such as repeat panel etc. That still doesn't justify the needs to increase the max pagination size.

Path/Options forward

Though this is beta/experimental feature, we understand that pagination is important for many users and some use cases. Also removing the limitation on max pagination is also crucial for some users.

Note

We haven't ignored the option for max page customisation. Instead, we are exploring safer options such as setting the option via environment variable so that grafana admins can take informed decision about this configuration. This will also protect our grafana cloud and partner cloud hosted grafana environments.

Also we have prioritised other important features such as supporting oAuth scenarios, making the backend parser as default, improving the documentation etc to make this plugin better. Also we are running low in bandwidth to implement/enhance the pagination. But we are open for enhancement once we have enough use case information around this.

Looking for feedback & use-cases

The more use cases we can gather from the community, the better we are able to design and prioritise a solution for the pagination feature. Feel free to provide the use-cases and feedback in this discussion.

[jkroepke]

One of my Use-Cases:

Do requests against the Azure Resource Manager API to gain live data about Costs, Advisories, or other information, which are not availible the Azure Monitor or Azure Monitor Datasource.

Since Requests against the Azure Resource Manager API are always scoped at subscription level, I have to loop over all subscriptions, using dashboard variables. Most of our customer have more than 5 subscriptions.

Another use-case is requesting the Service Health of M365. The MS Graph API is designed make an LIST Request first to gain all services, then request each service about health informations. Again, there are more than 5 services. It's the API design of the MS Graph API to get all items first, then loop over. Additionally, Microsoft have API rate-limits per subscriptions scope. It's an intentional design.

The List and GET approach work well in combination with Grafana dashboard variables (where I can use the Azure Monitor DS to get all subscriptions first), then fire an request against each subscription.

[yesoreyeram]

Thanks @jkroepke for your use-case information. The more use-cases we get, we can design this better to serve all.

[isaacsanders]

I use this plugin a lot with the GitHub GraphQL and REST APIs, so making pagination work with those would be a game changer

[valentintraen]

I use it with Rundeck API to retrieve informations that I can't get from database. For exemple, to get projects wich need to be synch with gitlab, I have to ask the api once for each Rundeck projects and I have many of them. I had to create 20 Grafana variables to get 5 distinct project each time and create 20 queries in my panel with a pagination of 5 for each.

[mattselkey]

I'm trying to use it to grab configuration properties from an internal cloud service (VMware cloud director), the data is used to correlate configuration changes in those properties vs deployment issues across Kubernetes monitored clusters. It's a really weird use case and the vendor provided tooling just doesn't surface the data that the vendor's api dopes, but that data is paginated.

Having it in Grafana where all the other infra is monitored would be amazing, at the moment I am using some hacky scripts which run some much more slowly than the api direct, it's seconds to many minutes difference. Thank you for considering the change!! 👏