Query API
Project-less regional API resources have been deprecated and will be removed by the end of September 2024. See Migrate to project-scoped URL for more information.
You can issue SQL queries in Imply Polaris using the Query API.
Polaris offers two endpoints to query data, /query/sql
and /query/sql/statement
.
See Query data by API to determine which endpoint fits your use case and view example queries.
In this topic, all API endpoints are relative to the project-scoped base URL https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/projects/PROJECT_ID
.
Prerequisites
You must have an API key with the AccessQueries
permission.
Querying from deep storage also requires the ManageIngestionJobs
permission.
In the examples below, the key value is stored in the variable named POLARIS_API_KEY
.
For information about how to obtain an API key and assign permissions, see API key authentication.
For more information on permissions, visit Permissions reference.
Query cached data
Queries cached data.
URL
POST
/query/sql
Request body
Submit your query as the value of a query
field in the JSON object within the request payload.
For example:
{"query" : "SELECT COUNT(*) FROM polaris_table WHERE foo = 'bar'"}
You can optionally specify the following fields in the JSON object:
resultFormat
: Format of the query results. Select any of the following options:object
: Returns a JSON array of JSON objects with the HTTP response headerContent-Type: application/json
.array
: Returns a JSON array of JSON arrays with the HTTP response headerContent-Type: application/json
.objectLines
: Returns newline-delimited JSON objects with a trailing blank line. Returns the HTTP response headerContent-Type: text/plain
.arrayLines
: Returns newline-delimited JSON arrays with a trailing blank line. Returns the HTTP response headerContent-Type: text/plain
.csv
: Returns comma-separated values with one row per line and a trailing blank line. Returns the HTTP response headerContent-Type: text/csv
.
header
: Adds a header row in the query results when set totrue
. The fields of the header row are the column names pertaining to the query results. The header row contains null values by default and can hold data types controlled bytypesHeader
andsqlTypesHeader
, if specified.typesHeader
: Returns the data types in the header row when set totrue
. Requires thatheader
is also set totrue
.When
header
andtypesHeader
are specified astrue
in the request body, the query result includes an extra row. For example:{
"continent": {
"type": "STRING"
},
"counts": {
"type": "LONG"
}
}sqlTypesHeader
: Returns the SQL data types in the header row when set totrue
. Requires thatheader
is also set totrue
.When
header
andsqlTypesHeader
are specified astrue
in the request body, the query result includes an extra row. For example:{
"continent": {
"sqlType": "VARCHAR"
},
"counts": {
"sqlType": "BIGINT"
}
}context
: JSON object for optional query context parameters, such as to set the query ID, time zone, and whether to use an approximate algorithm for distinct count. For example:{
"query" : "SELECT COUNT(*) FROM data_source WHERE foo = 'bar' AND __time > TIMESTAMP '2000-01-01 00:00:00'",
"context" : {
"sqlTimeZone" : "America/Los_Angeles"
}
}parameters
: Array of query parameters for parameterized queries. Each element of the array should be a JSON object containing the parameter's SQL data type and value. For example, the following query returns results with timestamps later than2019-08-21 00:00:00
:{
"query": "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" WHERE __time > ? GROUP BY 1 ORDER BY counts DESC",
"parameters": [
{
"type": "TIMESTAMP",
"value": "2019-08-21 00:00:00"
}
]
}
Responses
- 200 SUCCESS
- 404 ERROR
OK.
The request succeeded, and the query result was returned in the response body.
Not Found.
Example body:
{
"error": "Plan validation failed",
"errorMessage": "org.apache.calcite.runtime.CalciteContextException: From line 1, column 15 to line 1, column 39: Object 'TABLE_NAME' not found",
"errorClass": null,
"host": null
}
Sample request
The following example shows how to query cached data:
curl --location --request POST "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/projects/PROJECT_ID/query/sql" \
--header "Authorization: Basic $POLARIS_API_KEY" \
--header "Accept: application/json" \
--header "Content-Type: application/json" \
--data-raw '{
"query": "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" GROUP BY 1 ORDER BY counts DESC"
}'
Sample response
The following example shows a successful response containing the query result:
View the response
[
{
"continent": "North America",
"counts": 241671
},
{
"continent": "Europe",
"counts": 169433
},
{
"continent": "Oceania",
"counts": 35905
},
{
"continent": "Asia",
"counts": 29902
},
{
"continent": "South America",
"counts": 25336
},
{
"continent": "Africa",
"counts": 2263
},
{
"continent": "",
"counts": 922
}
]
Query data in deep storage
This is a beta feature available to select customers. Imply must enable the feature for you. Contact your Polaris support representative to find out more.
Deep storage encompasses all data, including real-time, cached, and non-cached data.
You query data that resides in deep storage as well as real-time data asynchronously and load the data on demand using the /query/sql/statements
endpoint.
For more information and examples, see Query data by API.
For details on setting a cache policy to manage data caching,
see Offload data from cache.
Submit a query
Queries data that resides in deep storage asynchronously.
URL
POST
/query/sql/statements
Request body
Generally, the /query/sql
and /query/sql/statements
endpoints support the same response body fields with minor differences. For information about the available fields, see Query cached data.
You can set query context parameters
using the context
property like when querying cached data.
However, the following context parameters are restricted when querying deep storage:
selectDestination
restricted todurableStorage
onlyexecutionMode
restricted toasync
onlyfaultTolerance
defaults totrue
maxNumTasks
defaults to10
and can be set from 2 to 10 (inclusive)
Responses
- 200 SUCCESS
- 400 BAD REQUEST
Successfully queried from deep storage
Error thrown due to bad query. Returns a JSON object detailing the error with the following format:
{
"error": "Summary of the encountered error.",
"errorClass": "Class of exception that caused this error.",
"host": "The host on which the error occurred.",
"errorCode": "Well-defined error code.",
"persona": "Role or persona associated with the error.",
"category": "Classification of the error.",
"errorMessage": "Summary of the encountered issue with expanded information.",
"context": "Additional context about the error."
}
Get query status
Retrieves information about the query associated with the given query ID. The response matches the response from the POST API if the query is accepted or running. In addition to the fields that this endpoint shares with POST /query/sql/statements
, a completed query's status includes the following:
result
object that summarizes information about your results, such as the total number of rows and sample records.pages
object that includes the following information for each page of results:numRows
: Number of rows in the page of results.sizeInBytes
: Size of the page.id
: Page number that you can use to reference a specific page when you get query results.
URL
GET
/query/sql/statements/{queryId}
Responses
- 200 SUCCESS
- 400 BAD REQUEST
Successfully retrieved query status
Error thrown due to bad query. Returns a JSON object detailing the error with the following format:
{
"error": "Summary of the encountered error.",
"errorCode": "Well-defined error code.",
"persona": "Role or persona associated with the error.",
"category": "Classification of the error.",
"errorMessage": "Summary of the encountered issue with expanded information.",
"context": "Additional context about the error."
}
Get query results
Retrieves results for completed queries. Results are separated into pages, so you can use the optional page
parameter to refine the results you get. Polaris returns information about the composition of each page and its page number (id
). For information about pages, see Get query status.
If you don't specify a page number, the API returns all results sequentially in the same response.
Getting the query results for an ingestion query returns an empty response.
URL
GET
/query/sql/statements/{queryId}/results
Query parameters
page
(optional)- Type: Int
- Fetch results based on page numbers. If not specified, all results are returned sequentially starting from page 0 to N in the same response.
resultFormat
(optional)- Type: String
- Defines the format in which the results are presented. The following options are supported
arrayLines
,objectLines
,array
,object
, andcsv
. The default isobject
.
Responses
- 200 SUCCESS
- 400 BAD REQUEST
- 404 NOT FOUND
- 500 SERVER ERROR
Successfully retrieved query results
Query in progress. Returns a JSON object detailing the error with the following format:
{
"error": "Summary of the encountered error.",
"errorCode": "Well-defined error code.",
"persona": "Role or persona associated with the error.",
"category": "Classification of the error.",
"errorMessage": "Summary of the encountered issue with expanded information.",
"context": "Additional context about the error."
}
Query not found, failed or canceled
Error thrown due to bad query. Returns a JSON object detailing the error with the following format:
{
"error": "Summary of the encountered error.",
"errorCode": "Well-defined error code.",
"persona": "Role or persona associated with the error.",
"category": "Classification of the error.",
"errorMessage": "Summary of the encountered issue with expanded information.",
"context": "Additional context about the error."
}
Cancel a query
Cancels a running or accepted query.
URL
DELETE
/query/sql/statements/{queryId}
Responses
- 200 OK
- 202 ACCEPTED
- 404 SERVER ERROR
A no operation since the query is not in a state to be cancelled
Successfully accepted query for cancellation
Invalid query ID. Returns a JSON object detailing the error with the following format:
{
"error": "Summary of the encountered error.",
"errorCode": "Well-defined error code.",
"persona": "Role or persona associated with the error.",
"category": "Classification of the error.",
"errorMessage": "Summary of the encountered issue with expanded information.",
"context": "Additional context about the error."
}