Dremio Rest API not retrieving 500 records with offset and limit

1

We are using Dremio server 3.1.6 on Linux. Dremio Rest API not retrieving 500 records with offset and limit. Is this 500 configurable?

2

The 500 limit per call is not configurable. Are you saying that using limit/offset to retrieve job results is not working correctly?

REST is not really designed to get large amounts of data which is why we have to limit each call.

3

Thanks for the reply Doron.

One observation when using the API ’ select distinct col from table ***" , dremio job shows 2000+ records
Where as API retrieves first 500 and later calls with offset and limit randomly gets less than 500. So instead of 2000+ we get 650+ < 800.

it’s only to do with that distinct query. The other queries with 20000 records all work fine.

Work around is create a data set and use , data set also we have to use limit in where clause.
or if the original select query has limit, then it works fine.

It has some thing to do with distinct query I guess. Any inputs that you can suggest ?

4

I am also facing the same issue.

I have a query like this :

select distinct columnA "value" from "spaceA"."datasetA" where columnA is not null and columnA not in (select distinct "value" from "spaceA"."datasetB" where "type" = 'someType')

This query returns 27,000+ results.

I am using SQL rest API to execute this.

Then I use jobs API to retrieve results in batches, each of size 500 (max limit), by explicitly setting offset and limit in each rest call like this :

I start from offset 0 and keep shifting in each call.

http://localhost:9047/api/v3/job/21365044-f881-d591-7e91-3dc1284fb400/results?offset=2000&limit=500

The rows list returned after offset 2000 is empty… In all, I don’t get results more than 2.5k…

Response with empty result:

{
    "rowCount": 27527,
    "schema": [
        {
            "name": "value",
            "type": {
                "name": "VARCHAR"
            }
        }
    ],
    "rows": []
}

I am expecting close to millions of records like this, to be retrieved sequentially.

Where am I going wrong ?

Is there a setting/config which I have missed ? Please advice.

5

@Farhan, this is a known issue. We are currently working on a fix and will update the community threads when it’s available.

6

Is my understanding of this issue that REST pagination is broken?
I just want to confirm that’s the same issue I’m seeing.
If so then this is a critical problem.
What is the status of the fix?

1 Like
7

@mingfang,

The pagination was “broken” but the issue has been addressed in a release from the past month. Please upgrade to the most recent version of Dremio at your convenience.