- Querying
Pagination
We recommend paginating your query results if you received one of the following errors:
Query result contains more than 100,000 rows. Please contact Support to raise this limit for you.
Query result contains more than 100MiB. Please contact Support to raise this limit for you.
Rockset can now paginate query results if your result set exceeds either 100,000 documents or 100MiB.
Rockset recommends using async queries, which will automatically use the pagination functionality (no need to set the
paginate
parameter) whenclient_timeout_ms
elapses andtimeout_ms
is set with a longer duration.
With pagination, the query results are stored for you. You can retrieve them later using a Rockset-provided cursor and a specified number of documents.
Using pagination, your application can then process the paginated results, one result set at a time.
Using Query Results Pagination
To use pagination, invoke the /orgs/self/queries
endpoint and set the paginate
parameter to
true
in the body of your request. It is false
by default.
Request
The following example shows a request to the /orgs/self/queries
endpoint to fetch the first 1000
documents:
curl --request POST \
--url https://$ROCKSET_SERVER/v1/orgs/self/queries \
-H 'Authorization: ApiKey $API_KEY' \
-H 'Content-Type: application/json' \
-d '{
"sql": {
"parameters": [
{
"name": "_id",
"type": "string",
"value": "85beb391"
}
],
"query": "SELECT * FROM foo where _id = :_id",
"paginate": true,
"initial_paginate_response_doc_count": 1000
}
}'
The initial_paginate_response_doc_count
parameter specifies the number of documents to be returned
in the response. This optional parameter's default is 10,000 documents. For example, if your
application displays results to users in groups of 100, set initial_paginate_response_doc_count
to
100
.
Note: Setting the paginate
parameter to true
removes the size limit for the results. Your
query will still need to complete processing under the two-minute time limit. If your query times
out, consider adding a limit
with fewer documents, so the query can finish processing in time.
Response
The following shows an example response from Rockset for the request above:
{
"query_id": "5b596206-c632-4a08-8343-0c560f7ef7f1",
"results": [
{
...
}
],
...
"results_total_doc_count": 10000000, // This is the total number of documents included in the results.
"pagination": {
"current_page_doc_count": 1000, // This will be fewer if there are no more results.
"start_cursor": null
"next_cursor": "aabawe153wtea352" // This will be null if there are no more results than those returned in this response.
}
}
The results
collection contains the actual result set while the query_id
and pagination
fields
contain all of the information necessary to fetch the next page of results (described in Fetching
the Next Page of Results).
In order to fetch the first set of results from the start you can omit the cursor parameter and call the /orgs/self/queries/{query_id}/pages?docs=[number of docs]
endpoint with the optinal docs
parameter.
Fetching the Next Page of Results
Invoke the /orgs/self/queries/{query_id}/pages?cursor={next_cursor}&docs=[number of docs]
endpoint to fetch the next page
of results. Retrieve the values from the query_id
and next_cursor
fields from the response
above. Then pass them into the respective query parameters in the endpoint. The docs
parameter is
optional. If you choose not to add a docs
parameter, the default will be 10,000 documents.
Request
The following example shows how to invoke this endpoint using the query_id
and next_cursor
field
values returned from the previous response:
curl --request GET \
--url https://$ROCKSET_SERVER/v1/orgs/self/queries/5b596206-c632-4a08-8343-0c560f7ef7f1/pages?cursor=aabawe153wtea352&docs=500 \
-H 'Authorization: ApiKey <api_key>' \
-H 'Content-Type: application/json'
Response
The following shows an example response for the request:
{
"results": [
{
“Field1”: ”value1”
},
...
],
"results_total_doc_count": 10000000
"pagination": {
"current_page_doc_count": 500,
"next_cursor_offset": 1500, // This number is the number of documents before the current page.
"next_cursor": fds23jurzjsa31 // This value will be null if there are no more results.
}
}
Note: If there are no more documents after the current page, the value for next_cursor
will be
null.
Obtaining the Status of Paginated Queries
You can obtain the status of one or more paginated queries (e.g., to determine when they will expire).
Request
To get the status of all of the paginated queries for your organization, invoke the
/orgs/self/queries
endpoint:
curl --request GET \
--url https://$ROCKSET_SERVER/v1/orgs/self/queries \
-H 'Authorization: ApiKey <api_key>' \
-H 'Content-Type: application/json'
Response
The following shows an example response for the request:
{
"data": [
{
"query_id": "5b596206-c632-4a08-8343-0c560f7ef7f1",
"state": "RUNNING", // or "FINISHED"
"run_by": "xyz@rockset.com",
"run_at": "2001-08-28T00:23:41Z",
"expires_at": "2001-08-28T00:33:41Z",
"stats" : {
"elapsed_time_ms": 12000,
"throttled_time_ms": 126,
"doc_count": 27842,
"size_bytes": 455223422
},
"pagination": {
"start_cursor": null
}
},
... // Additional paginated queries will follow here.
]
}