Version 2 Compass APIs to page a query result

The version 2 Compass APIs provide a method to query Compass and return the results in smaller, more manageable, record sets.

This allows you to select an optimal size to download query results based on hardware constraints and network timeouts. This also allows you to re-retrieve a portion of the result in the event of a connection failure. The result of a query is static. So, the result does not change when data is added or updated in the Data Lake. The page size is based on rows, with a maximum limit of 100,000 rows with a 10 MB size cap. The version 2 Compass APIs functionality uses OFFSET and LIMIT parameters for the result set retrieved. Query results are available for roughly 20 hours after receiving the FINISHED status. The query is associated with the method used to run the query. Consequently, you cannot run a query in the Compass UI and then check the status or retrieve the results through the API. The query is only available to the tenant and principal ID that submitted the job. If you run a query, someone else cannot query for the status or retrieve the results .

The API Gateway suite introduced quota policies to prevent overloading the tenant by too many calls.

This table shows the imposed quotas per tenant:


Method	Calls
/v2/compass/jobs	up to 100 calls per minute
/v2/compass/status	up to 1000 calls per minute
/v2/compass/result	up to 10000 calls per minute
/v2/compass/cancel	up to 1000 calls per minute

This table shows an overview of the available API methods:


Method	Type	Description
v2/compass/jobs	POST	Use this API to submit a Compass query as an asynchronous job. Provide the SQL statement in the request body. The response includes a Query ID. Use the `queryID` to submit status requests to check the status until the query is finished. Use the `queryID` to retrieve the query results.
v2/compass/{queryID}/status	POST	Use this API with the `queryID` to check the status of a query submitted through the Compass job API. The response indicates the current query status. The polling internal can be used to specify the long-polling timeout period. Possible values for "status" property in the response: RUNNING - indicates that the query is executing. FINISHED - indicates that the query completed successfully. Use the GET result API call to retrieve the results. The result includes the `rowCount`, which is the total number of rows in the full query result set. You may get the results. FAILED - indicates that the query is finished and ended in error. Use the GET result API call to retrieve the error details. CANCELLED - indicates that the query has been canceled.
v2/compass/jobs/{queryId}/result	GET	Use this API to retrieve the query results for the `queryID` provided by the Compass Jobs API. Use this API after the Compass Status API indicates that the query finished successfully, failed, or was canceled. A query that finished successfully returns the query results. A query that fails or was canceled shows an error message. Use the `resultFormat` to specify the format of the results. The options are `text/csv` or `application/x-ndjson`, which renders newline-delimited JSON. Use `offset` to specify the row offset, which is 0-based. Use `limit` to specify the maximum number of rows. The limit is 100000 with a maximum 10MB cap size.
v2/compass/{queryId}/cancel	PUT	Use this API to cancel a query using the `queryID`.
/v2/payloads/splitquery	GET	If the filter query that is provided as the method parameter produces more than 10K results, you can show all the results. The filter query must be refined to respond with less than 10K results. The purpose of this API is to allow a client to retrieve such a large amount of results when required. The response of this API is a set of filters, that when used (for example `Streambyfilter` API) ensure less than 10K results in the response. Combining the split filters from the response, and using them in sequence, provides the same result as the original large filter query. If the original filter response is less than 10K objects, the API responds with the original query. The use of the filter parameter is identical as documented in the `/v1/payloads/list` API method. V2 allows clients to specify the size of the results produced from the split filter.