Query data by API
After you have created one or more tables and ingested data, you can use the Query API to run queries against your data. For information on how to write SQL queries in Imply Polaris, see Querying data.
This topic shows how to query a table in Polaris using Druid SQL.
As of 2022.08.10, you no longer need to include the project ID for a query when you use the API vanity URL. See the API migration guide for more information.
In the examples below, replace ORGANIZATION_NAME
with the custom domain through which you access Polaris. For example, https://ORGANIZATION_NAME.app.imply.io
.
Prerequisites
This topic assumes you have an API key with the ReadDataSources
permission.
In the examples below, the key value is stored in the variable named POLARIS_API_KEY
.
See API key authentication to obtain an API key and assign permissions.
Visit Permissions reference for more information on permissions.
Query using Druid SQL
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/query/sql' \
--user ${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"
}'
import os
import requests
import json
url = "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/query/sql"
apikey = os.getenv("POLARIS_API_KEY")
payload = json.dumps({
"query": "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" GROUP BY 1 ORDER BY counts DESC"
})
headers = {
'Accept': 'application/json',
'Content-Type': 'application/json',
'Authorization': f'Basic {apikey}'
}
response = requests.request("POST", url, headers=headers, data=payload)
print(response.text)
Sample response
The following example shows a successful response containing the query result:
[
{
"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
}
]
Obtain results in 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/query/sql' \
--user ${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"
}'
import os
import requests
import json
url = "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/query/sql"
apikey = os.getenv("POLARIS_API_KEY")
payload = json.dumps({
"query": "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" GROUP BY 1 ORDER BY counts DESC",
"resultFormat": "csv"
})
headers = {
'Accept': 'application/json',
'Content-Type': 'application/json',
'Authorization': f'Basic {apikey}'
}
response = requests.request("POST", url, headers=headers, data=payload)
print(response.text)
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
Download results to a file
To save the query results to a file, use the curl -o, --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.txt
:
curl --location --request POST 'https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/query/sql' \
--user ${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.txt
import os
import requests
import json
url = "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/query/sql"
apikey = os.getenv("POLARIS_API_KEY")
payload = json.dumps({
"query": "SELECT continent, COUNT(*) AS counts FROM \"Koalas to the Max\" GROUP BY 1 ORDER BY counts DESC"
})
headers = {
'Accept': 'application/json',
'Content-Type': 'application/json',
'Authorization': f'Basic {apikey}'
}
response = requests.request("POST", url, headers=headers, data=payload)
with open("output.txt", "w") as f:
f.write(response.text)
Sample response
The file output.txt
contains the query results:
[
{
"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
}
]
Learn more
See the following topics for more information:
- Query API for details on the Polaris Query API.
- Druid SQL documentation for reference on Druid SQL queries.
- Query using JDBC for querying data in Polaris using JDBC.