Skip to main content

Data cube query API

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

info

You can't include access filters in Data cube query API requests. See the feature limitations for more information.

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.

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
        ]
    ]
}

Limitations​

The following limitations apply to the Data cube query API:

  • The API doesn't work with third-party tools that utilize a JDBC driver.
  • You can't include access filters in requests to provide user groups with a filtered view of the data. API tokens follow the OAuth flow and operate at a higher access level than user groups, so they don't honor any access filters applied to a data cube. Personal Access Tokens are not currently supported for this route.