Skip to main content

Data cube query API

You can use the Data cube query API to query Imply Polaris data cubes.

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​

This topic assumes you have an API key with at least one of the following permissions:

  • AccessVisualization
  • AccessDataCubes
  • AccessDashboards

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.

Data cube ID​

You must provide the ID of the data cube in your requests.

You can locate the data cube ID in a few ways:

  • Send a GET request to the Data cubes v1 API to list all data cubes. Find your data cube title and its associated ID in the request response.
  • View the data cube ID in the Polaris UI. See View data cubes for details.

Query a data cube​

URL​

POST pivot/data-cube-sql/query

Request body​

Submit your query as the value of a queryString field in the JSON object within the request payload.

In queryString:

  • reference a single data cube in the FROM clause using datacube as the namespace, followed by the data cube ID. For example, "datacube"."wikipedia123".
  • refer to dimensions wherever column names are allowed, in either of the following ways:
    • using the dimension ID prefixed with DIM:. For example, DIM:__time.
    • using the DIMENSION_BY_ID function. For example, DIMENSION_BY_ID('__time') .
  • refer to measures wherever expressions are allowed using the MEASURE_BY_ID function. For example, MEASURE_BY_ID('sum_added').
info

DIMENSION_BY_ID and MEASURE_BY_ID replaced DIMENSION_BY_NAME and MEASURE_BY_NAME in March 2024. The behavior is the same and the old functions still work. We recommend that you use DIMENSION_BY_ID and MEASURE_BY_ID in your API requests going forward.

exactResultsOnly is an optional property that controls the precision of TopN and COUNT DISTINCT queries. Set it to true to improve accuracy in reported metrics—note that this setting can affect query performance. Defaults to false if unspecified.

For example:

"queryString": "SELECT \"DIM:countryName\" AS \"Country Name\", MEASURE_BY_ID('sum_added') AS \"Added\" FROM \"datacube\".\"Wikipedia2628\" WHERE NOT(\"DIM:countryName\" IS NULL) GROUP BY 1 ORDER BY 2 DESC LIMIT 2,
"exactResultsOnly": true

Responses​

The Data cube query API returns results as a JSON object containing an array of arrays:

  • The first array contains the result column names.
  • The second array contains the Druid data types of the columns.
  • The third array contains the SQL data types for the columns.
  • The remaining arrays contain the result values.

Polaris formats the results based upon the following Druid SQL API parameters:

  • resultFormat: array
  • header: true
  • typesHeader: true
  • sqlTypesHeader: true

You can't change or override these parameters for the Data cube query API.

OK.

The request succeeded, and the query result was returned in the response body.

Example body:

{
    "data": [
        [
            "Country Name",
            "Added"
        ],
        [
            "STRING",
            "LONG"
        ],
        [
            "VARCHAR",
            "BIGINT"
        ],
        [
            "Russia",
            47540
        ],
        [
            "United States",
            39921
        ]
    ]
}

Sample request​

The following request displays the number of events and comment length for the top 5 United States cities in the data cube with ID Wikipedia2628, for a specified period of time:

curl --location --request POST "https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v0/projects/PROJECT_ID/pivot/data-cube-sql/query" \
--header "Authorization: Basic $POLARIS_API_KEY" \
--header "Accept: application/json" \
--header "Content-Type: application/json" \
--data-raw '{
    "queryString": "SELECT \"DIM:cityName\" AS \"City Name\", MEASURE_BY_ID('\''sum_commentLength'\'') AS \"Comment Length\", COUNT(*) AS \"Number of Events\" FROM \"datacube\".\"Wikipedia2628\" WHERE \"DIM:countryName\" = '\''United States'\'' AND NOT(\"DIM:cityName\" IS NULL) AND (TIME_SHIFT(TIMESTAMP '\''2016-06-27 21:32:00'\'', '\''P1D'\'', -1, '\''Etc/UTC'\'') <= \"DIM:__time\" AND \"DIM:__time\" < TIMESTAMP '\''2016-06-27 21:32:00'\'') GROUP BY 1 ORDER BY 3 DESC LIMIT 5",
    "exactResultsOnly": true
}'

Sample response​

Show sample response
{
    "data": [
        [
            "City Name",
            "Comment Length",
            "Number of Events"
        ],
        [
            "STRING",
            "LONG",
            "LONG"
        ],
        [
            "VARCHAR",
            "BIGINT",
            "BIGINT"
        ],
        [
            "New York",
            660,
            13
        ],
        [
            "Chicago",
            137,
            10
        ],
        [
            "White Plains",
            162,
            10
        ],
        [
            "Brooklyn",
            179,
            7
        ],
        [
            "Houston",
            123,
            6
        ]
    ]
}

Access filters​

A data cube can have one or more access filters applied. An access filter applies an additional SQL filter to each data cube query to limit the results returned to particular user groups and Polaris API keys.

If you try to query a data cube via API and the request returns the following error, the data cube has an access filter that doesn't include the API key you're using:

{
    "error": "Cannot query this data cube. User is not a member of any of the required groups"
}

Return access filters​

To return the details of a data cube's access filters, send a GET request to the Data cubes v1 API. For example:

GET https://ORGANIZATION_NAME.REGION.CLOUD_PROVIDER.api.imply.io/v1/projects/PROJECT_ID/data-cubes/DATACUBE_ID

Access filters are defined in the groupFilters (user groups) and userFilters (API keys) objects. For example:

"groupFilters": {
   "66882a6f-1133-4e38-a786-b835a9a38fdb": {
      "name": "Group filter",
      "formula": "t.\"Event type\" = 'earthquake' and t.Intensity > 2"
   }
},
"userFilters": {
   "9vbf874d-ef25-4483-808a-1fbdbfc65c51": {
      "name": "API filter",
      "formula": "t.\"Event type\" = 'earthquake' and t.Intensity > 2"
   }
}

Create an access filter​

To update a data cube to include an access filter for your Polaris API key, send a PUT request to the Data cubes v1 API that includes a userFilters object to define the filter.

Include the ID of the Polaris API key and define the name and formula for the access filter. You can use the API Keys API to find the ID of your API key.

For example, the following userFilters object defines an access filter for the API key with ID 9vbf874d-ef25-4483-808a-1fbdbfc65c51:

"userFilters": {
   "9vbf874d-ef25-4483-808a-1fbdbfc65c51": {
      "name": "IP access filter",
      "formula": "t.\"Event type\" = 'earthquake' and t.Intensity > 2"
   }
}

See the Data cube v1 API documentation for information on managing data cubes by API.

Limitations​

The Data cube query API doesn't work with third-party tools that utilize a JDBC driver.