Skip to main content

Query precached data by API

Precached data is a subset of data that is stored in deep storage but is also loaded into a caching layer in Imply Polaris. To use the Query API to query precached data, use the /query/sql endpoint.

If you configure a custom cache policy, Polaris only keeps data precached when it's within the specified time period. Data outside this time period resides in deep storage. You can't use the /query/sql endpoint to query data that's not precached. Instead, use the /query/sql/statements endpoint to query data that's both precached and in deep storage. For more information, see Query deep storage data.

To submit SQL queries using the SQL workbench in the UI, see Query data. For information on how to write SQL queries, see Druid SQL overview.

tip

For optimal performance, cache the data you expect to query often.

Prerequisites​

You must have an API key with the AccessQueries 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.

Submit a query​

To submit a Druid SQL query via the API, send a POST request along with a JSON body containing your query. Change the SQL statement in the query field to modify the query.

Sample request​

The following example shows how to submit a query using the API:

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 and return CSV format​

Specify resultFormat in the JSON object to set the format of the query results. You can return results as JSON objects or arrays or in CSV format.

For additional fields you can define for Query API request payloads, see Query API.

Sample request​

The following example shows how to submit a query and return CSV-formatted results:

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",
    "resultFormat": "csv"
}'

Sample response​

The following example shows a successful response containing the query result in CSV format:

North America,241671
Europe,169433
Oceania,35905
Asia,29902
South America,25336
Africa,2263
,922

Query and save results to a file​

To save the query results to a file, use the curl --output option or the file handling capabilities of your application's programming language.

Sample request​

The following example shows how to submit a query and save the results to a file named output.json:

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"
}' \
--output output.json

Sample response​

The file output.json contains the query results.

Learn more​

See the following topics for more information: