Skip to main content

Druid native JSON query API

For customers migrating to Imply Polaris from Apache Druid®, Imply Enterprise, or Imply Hybrid, Polaris exposes the Druid JSON query API.

info

The JSON query API is intended for existing Druid users already familiar with Apache Druid. Note, you can also use the SQL workbench to submit JSON queries. Select Native as the engine for the query.

If you are new to Polaris, use the Polaris Query API to run queries against your data.

In this topic, all API endpoints are relative to the project-scoped base URL https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v0/projects/PROJECT_ID.

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.

Submit a JSON query​

This section outlines the API endpoint and syntax to submit a JSON query to Polaris.

URL​

POST /query/native

Query parameters​

  • pretty (optional) Format the results using indentation and line breaks.

Request body​

Submit native JSON queries in the request payload. For example, the following shows a topN query for the channels with the most changes:

--data-raw '{
  "queryType": "topN",
  "dataSource": "Wikipedia",
  "dimension": "channel",
  "metric": "delta",
  "threshold": 5,
  "granularity": "all",
  "aggregations": [
    {
      "type": "longSum",
      "name": "delta",
      "fieldName": "delta"
    }
  ],
  "intervals": [
    "2016-06-27T00:00:11.080Z/2016-06-28T00:00:11.080Z"
  ]
}'

Responses​

By default the Druid native JSON query API returns results as an array of JSON objects that represent the data timestamps and results.

When the request succeeds, Polaris returns the query result in the response body along with a 200 HTTP status code.

Example​

The following request returns the top 5 channels with edits in the wikipedia datasource for a specified period of time:

curl --location --request POST "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/projects/PROJECT_ID/query/native?pretty" \
--header "Authorization: Basic $POLARIS_API_KEY" \
--header "Accept: application/json" \
--header "Content-Type: application/json" \
--data-raw '{
  "queryType": "topN",
  "dataSource": "Wikipedia",
  "dimension": "channel",
  "metric": "delta",
  "threshold": 5,
  "granularity": "all",
  "aggregations": [
    {
      "type": "longSum",
      "name": "delta",
      "fieldName": "delta"
    }
  ],
  "intervals": [
    "2016-06-27T00:00:11.080Z/2016-06-28T00:00:11.080Z"
  ]
}'

Sample response​

The following is a sample response for the example topN query:

[{
"timestamp":"2016-06-27T00:00:11.080Z",
"result":[
    {"channel":"#ceb.wikipedia","delta":3253248},
    {"channel":"#sv.wikipedia","delta":2725041},
    {"channel":"#en.wikipedia","delta":1982582},
    {"channel":"#fr.wikipedia","delta":484118},
    {"channel":"#ru.wikipedia","delta":387116}]
}]

Usage notes​

The JSON query API in Polaris is similar to, but not exactly the same as, the Druid JSON querying API. In some instances, you may have to modify your existing queries to make them work with Polaris.

Lookups​

Polaris uses a unique syntax to access lookups within a JSON query. The following is an example lookup query from the Lookups tutorial:

SELECT
  "vid",
  "vid",
  TIMESTAMPDIFF(MINUTE, TIME_PARSE("start_time"), "__time") AS "elapsed_minutes",
  "len_miles",
  "avg_mph",
  LOOKUP("vid", 'vid-makes[vid][make]') AS "make",
  LOOKUP("vid", 'vid-makes[vid][model]') AS "model"
FROM "car-trips"
WHERE "len_miles" > 10 AND avg_mph > 30

The same query written in JSON:

{
"queryType": "scan",
"dataSource": {
  "type": "table",
  "name": "car-trips"
},
"intervals": {
  "type": "intervals",
  "intervals": [
      "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z"
  ]
},
"virtualColumns": [
  {
    "type": "expression",
    "name": "v0",
    "expression": "div((\"__time\" - timestamp_parse(\"start_time\",null,'UTC')),60000)",
    "outputType": "LONG"
  },
  {
  "type": "expression",
  "name": "v1",
  "expression": "lookup(\"vid\",'vid-makes[vid][make]')",
  "outputType": "STRING"
  },
  {
  "type": "expression",
  "name": "v2",
  "expression": "lookup(\"vid\",'vid-makes[vid][model]')",
  "outputType": "STRING"
  }],
"limit": 1001,
"filter": {
  "type": "and",
  "fields": [
    {
    "type": "range",
    "column": "len_miles",
    "matchValueType": "LONG",
    "lower": 10,
    "lowerOpen": "true"
    },
    {
    "type": "range",
    "column": "avg_mph",
    "matchValueType": "LONG",
    "lower": 30,
    "lowerOpen": "true"
    }]
  },
"columns": [
  "avg_mph",
  "len_miles",
  "v0",
  "v1",
  "v2",
  "vid"],
"legacy": "false",
"context": {
  "enableTimeBoundaryPlanning": "true",
  "sqlOuterLimit": 1001,
  "sqlStringifyArrays": "false",
  "useNativeQueryExplain": "true"
  },
"columnTypes": [
  "DOUBLE",
  "DOUBLE",
  "LONG",
  "STRING",
  "STRING",
  "STRING"],
"granularity": {
  "type": "all"
  }
}

Note how the lookup expressions are expressed as virtual columns.

Lookup aliases​

If you created aliases for your lookups according to Migrate lookups from Druid, use the alias in your virtual column. For example, the following JSON assigns aliases for lookups of makes and models from Create lookups by API:

...
{
  "type": "expression",
  "name": "v1",
  "expression": "lookup(\"vid\",'makes-alias')",
  "outputType": "STRING"
},
{
  "type": "expression",
  "name": "v2",
  "expression": "lookup(\"vid\",'models-alias')",
  "outputType": "STRING"
},
...

Learn more​

See the following topics for more information: