> ## Documentation Index
> Fetch the complete documentation index at: https://cubed3-feat-druid-driver-streaming.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Querying data APIs

> Cross-cutting query behavior in Cube—default ordering and limits, query kinds, and how REST (JSON), GraphQL, and SQL APIs interpret them.

After creating a [data model][ref-data-model], you would like to *ask questions
to it*, i.e., run queries against this data model. This page describes the common
concepts of querying Cube through its [data APIs][ref-apis].

All queries share the same [query defaults](#query-defaults) and each query fall
into one of [query types](#query-types).

## Query defaults

The following defaults apply to all queries run by Cube.

### Order

By default, for [REST (JSON) API][ref-rest-api] and [GraphQL API][ref-graphql-api]
queries, Cube will apply ordering to [any query](#query-types) that does not
set it explicitly. The default heuristics are to sort the result set by a time
dimension, if any; otherwise, sort it by the first measure, if any; otherwise,
sort it by the first dimension, if any. These heuristics provide a result set
that works great for most data visualization purposes. You can opt out from
default ordering by passing an empty array (`[]`) object as `order`.

[SQL API][ref-sql-api] queries do not get a default ordering in case there's no
`ORDER BY` clause in the query. You can opt in for default ordering for SQL API
queries (except for [ungrouped](#ungrouped-query) ones) by setting
`CUBESQL_SQL_NO_IMPLICIT_ORDER` to `false`.

### Row limit

By default, [any query](#query-types) that does not explicitly specify the row
limit, will return no more than 10,000 rows in the result set. You can use the
[`CUBEJS_DB_QUERY_DEFAULT_LIMIT`](/reference/configuration/environment-variables#cubejs_db_query_default_limit) environment variable to override it. This serves
as a safeguard against data scraping and denial-of-service (DoS) attacks if Cube
is exposed to untrusted environments.

The maximum allowed limit is 50,000 rows. You can use the [`CUBEJS_DB_QUERY_LIMIT`](/reference/configuration/environment-variables#cubejs_db_query_limit)
environment variable to override it. You can also implement
[pagination][ref-pagination-recipe] to fetch more rows than the maximum limit.

If you're using the [SQL API][ref-sql-api], you can enable
[streaming mode][ref-sql-api-streaming] to bypass the maximum row limit for
queries with an explicit `LIMIT` clause. Without an explicit `LIMIT`,
`CUBEJS_DB_QUERY_DEFAULT_LIMIT` still applies even in streaming mode.

### Time zone

All time-based calculations performed by Cube are time zone-aware.

By default, Cube assumes that time values in your queries (e.g., in date range
filters) are in the [UTC time zone][wiki-utc-time-zone]. Similarly, it will use
the same time zone for time dimension values in result sets. You can use the
[`CUBEJS_DEFAULT_TIMEZONE`](/reference/configuration/environment-variables#cubejs_default_timezone) environment variable to override it.

You can also use the `timezone` option with [REST (JSON) API][ref-rest-api-query-format-options]
or [GraphQL API][ref-ref-graphql-api-args] to specify the time zone for a query.
Also, you can use the [`SQL_UTILS` context variable][ref-sql-utils] to apply the
time zone conversion to dimensions that are not used as time dimensions in a query.

Additionally, note that time zones have impact on [pre-aggregation
matching][ref-matching-preaggs-time-dimensions].

## Query types

Most commonly, you will run [regular queries](#regular-query). See the table
and the sections below for details on each query type.

| Query type                                                | Supported by [APIs][ref-apis]                                                           | Supported in [Playground][ref-playground] |
| --------------------------------------------------------- | --------------------------------------------------------------------------------------- | ----------------------------------------- |
| [Regular query](#regular-query)                           | [SQL API][ref-sql-api], [REST (JSON) API][ref-rest-api], [GraphQL API][ref-graphql-api] | ✅ Yes                                     |
| [Query with post-processing](#query-with-post-processing) | [SQL API][ref-sql-api] only                                                             | ❌ No                                      |
| [Query with pushdown](#query-with-pushdown)               | [SQL API][ref-sql-api] only                                                             | ❌ No                                      |
| [Compare date range query](#compare-date-range-query)     | [REST (JSON) API][ref-rest-api] only                                                    | ❌ No                                      |
| [Total query](#total-query)                               | [REST (JSON) API][ref-rest-api] only                                                    | ❌ No                                      |
| [Ungrouped query](#ungrouped-query)                       | [SQL API][ref-sql-api], [REST (JSON) API][ref-rest-api], [GraphQL API][ref-graphql-api] | ❌ No                                      |

### Regular query

**This is the most common type of queries.** Regular queries include:

* Lists of dimensions and measures that you'd like to see in the result set.
* Optionally, filters to apply before returning the result set.
* Optionally, a [row limit](#row-limit) and an offset for the result set.

For regular queries, Cube generates the SQL for the upstream [data
sources][ref-data-sources] that always includes all dimensions in the `GROUP BY`
statement. See [ungrouped queries](#ungrouped-query) if you'd like to override
this behavior.

Regular queries immensely benefit from [in-memory cache and
pre-aggregations][ref-caching]. They can also be modified before execution with
[`query_rewrite`][ref-query-rewrite].

#### Example

See an example of a regular query using the SQL API syntax:

```sql theme={null}
SELECT
  users.state,
  users.city,
  orders.status,
  MEASURE(orders.count)
FROM orders
CROSS JOIN users
WHERE
  users.state != 'us-wa'
  AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01'
GROUP BY 1, 2, 3
LIMIT 10;
```

The same query using the REST (JSON) API syntax looks as follows:

```json theme={null}
{
  "dimensions": [
    "users.state",
    "users.city",
    "orders.status"
  ],
  "measures": [
    "orders.count"
  ],
  "filters": [
    {
      "member": "users.state",
      "operator": "notEquals",
      "values": ["us-wa"]
    }
  ],
  "timeDimensions": [
    {
      "dimension": "orders.created_at",
      "dateRange": ["2020-01-01", "2021-01-01"]
    }
  ],
  "limit": 10
}
```

### Query with post-processing

**Queries with post-processing are specific to the [SQL API][ref-sql-api].**
Generally, they are structured in such a way that a [regular query](#regular-query) is
part of a `FROM` clause or a common table expression (CTE):

```sql theme={null}
-- Regular query in FROM
SELECT ...
FROM (
  -- Regular query
) AS regular_query_in_from
GROUP BY ...
ORDER BY ...

-- Regular query in CTE
WITH regular_query_in_cte AS (
  -- Regular query
)
SELECT ...
FROM regular_query_in_cte
GROUP BY ...
ORDER BY ...
```

To execute the *regular query* part, Cube will generate the SQL for an upstream
[data source][ref-data-sources]. Then, Cube will do *post-processing* by
executing the rest of the query using an internal SQL execution engine.
Learn more in the [SQL API documentation][ref-sql-api-qpp].

Queries with post-processing, since they include regular queries, benefit from
[in-memory cache and pre-aggregations][ref-caching]. They can also be modified
before execution with [`query_rewrite`][ref-query-rewrite].

However, the trade-off is that queries with post-processing support only a
limited set of SQL functions and operators.

#### Example

The simplest example of a query with post-processing:

```sql theme={null}
SELECT VERSION();
```

This query invokes a function that is implemented by the SQL API and executed without
querying the upstream data source.

Now, see a more complex example of a query with post-processing. In this query, we derive
new dimensions, post-aggregate measures, and perform additional filtering:

```sql theme={null}
SELECT
  UPPER(SUBSTRING(state FROM 4)) AS state,
  CHAR_LENGTH(city) AS city_name_length,
  CASE WHEN status = 'completed' THEN 'done' ELSE 'pending' END AS status,
  SUM(count) AS total_count
FROM (
  SELECT
    users.state,
    users.city,
    orders.status,
    MEASURE(orders.count) AS count
  FROM orders
  CROSS JOIN users
  WHERE
    users.state != 'us-wa'
    AND orders.created_at BETWEEN '2020-01-01' AND '2021-01-01'
  GROUP BY 1, 2, 3
  LIMIT 10
) AS regular_query
GROUP BY 1, 2, 3
HAVING city_name_length > 10;
```

Let's assume that we run the query above and the following pre-aggregation
is defined:

```yaml theme={null}
pre_aggregations:
  - name: main
    measures:
      - orders.count
    dimensions:
      - users.state
      - users.city
      - orders.status
    time_dimension: orders.created_at
    granularity: day
```

Cube will use this pre-aggregation, run the following SQL in Cube Store for
the regular query, and then do post-processing to produce the result set:

```sql theme={null}
SELECT
  `users__state`,
  `users__city`,
  `orders__status`,
  sum(`orders__count`) `orders__count`
FROM
  prod_pre_aggregations.orders_main_s3ebmau3_mw1bchy_1j4umn8
WHERE
  (
    `users__state` <> 'us-wa'
    OR `users__state` IS NULL
  )
  AND (
    `orders__created_at_day` >= to_timestamp('2020-01-01T00:00:00.000')
    AND `orders__created_at_day` <= to_timestamp('2021-01-01T23:59:59.999')
  )
GROUP BY 1, 2, 3
ORDER BY 4 DESC
LIMIT 10;
```

### Query with pushdown

**Queries with pushdown are specific to the [SQL API][ref-sql-api].**
Unlike [queries with post-processing](#query-with-post-processing), they can
have arbitrary structure and contain pretty much any SQL functions and operators.

Thus, queries with pushdown are not reducible to [regular
queries](#regular-query) and post-processing on top of them. To execute a query
with pushdown, Cube will need to transform it and generate the SQL for an
upstream [data source][ref-data-sources].
Learn more in the [SQL API documentation][ref-sql-api-qpd].

Queries with pushdown, since they don't include regular queries, can not
utilize pre-aggregations; however, they still benefit from [in-memory
cache][ref-caching]. Queries with pushdown can also be modified before
execution with [`query_rewrite`][ref-query-rewrite]; however, only applying
additional filters is supported.

#### Example

See an example of a query with pushdown. In this query, the filter in `WHERE`
references a subquery. So, it can't be executed as a query with post-processing.
It will be executed as a query with pushdown instead:

```sql theme={null}
SELECT
  users.state,
  users.city
FROM users
WHERE
  users.orders_made = (
    SELECT MAX(orders_made)
    FROM users
  )
GROUP BY 1, 2;
```

### Compare date range query

**Compare date range queries are special cases of regular queries.**
Similarly to [regular queries](#regular-query), they include lists of
dimensions and measures, filters, etc. and return a result set.

However, unlike regular queries, they provide a convenient way to retrieve
measure values for *more than one date range* for a time dimension.

You can make a compare date range query by using the `compareDateRange`
option with the [REST (JSON) API][ref-rest-api-query-format-options-tdf]. Note that
you also need to set the `queryType` parameter of the [`/v1/load`
endpoint][ref-rest-api-load] to `multi`.

#### Example

```json theme={null}
{
  "dimensions": ["orders.city"],
  "measures": ["orders.amount"],
  "filters": [
    {
      "member": "orders.status",
      "operator": "equals",
      "values": ["shipped"]
    }
  ],
  "timeDimensions": [{
    "dimension": "orders.created_at",
    "compareDateRange": [
      ["2024-01-01", "2024-12-31"],
      ["2023-01-01", "2023-12-31"],
      ["2022-01-01", "2022-12-31"],
    ],
    "granularity": "year",
  }],
  "limit": 100
}
```

See [this blog post][blog-compare-date-range] for more details and examples.

For the SQL API, you can write an equivalent query using the `UNION ALL` statement.

### Total query

**Total queries are special cases of regular queries.**
Similarly to [regular queries](#regular-query), they include lists of
dimensions and measures, filters, etc. and return a result set.

In addition to that, they provide a convenient way to retrieve the total number
of rows in the result set as if no [row limit](#row-limit) or offset are set in
the query. This is useful for creating user interfaces with
[pagination][ref-pagination-recipe].

You can make a total query by using the `total` option with the [REST
API][ref-rest-api-query-format-options]. For the SQL API, you can write an
equivalent query using the `UNION ALL` statement.

### Ungrouped query

Similarly to [regular queries](#regular-query), ungrouped queries include
lists of dimensions and measures, filters, etc. and return a result set.

However, unlike for regular queries, Cube will not add the
`GROUP BY` statement when generating the SQL for an upstream data source.
Instead, raw results after filtering and joining will be returned without any
grouping. Measures will be rendered as their `sql` without any aggregation.
Time dimensions will be truncated by granularity as usual, however, not grouped by.

You can make a regular query ungrouped by using the `ungrouped` option with
[REST (JSON) API][ref-rest-api-query-format-options] or [GraphQL API][ref-ref-graphql-api-args].
For the [SQL API][ref-sql-api], you can omit the `GROUP BY` statement from the
SQL API query.

By default, for security purposes, ungrouped queries require [primary
keys][ref-primary-key] of all cubes involved in a query to be added as
dimensions. You can use the [`allow_ungrouped_without_primary_key` configration
option][ref-conf-allow-ungrouped] to override this.

Additionally, note that ungrouped queries have additional requirements for
[pre-aggregation matching][ref-matching-preaggs-ungrouped].

## Troubleshooting

### No `count` measure

You might encounter the following error when running an [ungrouped query](#ungrouped-query)
that includes `HAVING COUNT(1) > 0` (or a similar expression) against a cube or a view that
have no measure called `count`. [Tableau][ref-tableau] is knows to generate such queries.

```text theme={null}
Failed to deserialize: invalid type: unit value, expected struct JoinDefinitionStatic
```

The rrot cause is that Cube needs to know how to calculate `COUNT(1)` and it expects a measure
called `count` to be defined in the cube or view. Please add one to resolve this issue.

[wiki-utc-time-zone]: https://en.wikipedia.org/wiki/Coordinated_Universal_Time

[ref-data-model]: /docs/data-modeling/overview

[ref-playground]: /docs/explore-analyze/playground

[ref-apis]: /reference

[ref-sql-api]: /reference/core-data-apis/sql-api

[ref-sql-api-qpp]: /reference/core-data-apis/sql-api/query-format#query-post-processing

[ref-sql-api-qpd]: /reference/core-data-apis/sql-api/query-format#query-pushdown

[ref-rest-api]: /reference/core-data-apis/rest-api

[ref-rest-api-load]: /reference/core-data-apis/rest-api/reference#v1load

[ref-graphql-api]: /reference/core-data-apis/graphql-api

[ref-data-sources]: /admin/connect-to-data/data-sources

[ref-rest-api-query-format-options]: /reference/core-data-apis/rest-api/query-format#query-properties

[ref-rest-api-query-format-options-tdf]: /reference/core-data-apis/rest-api/query-format#time-dimensions-format

[ref-ref-graphql-api-args]: /reference/core-data-apis/graphql-api/reference#cubequeryargs

[ref-sql-utils]: /reference/data-modeling/context-variables#sql_utils

[ref-matching-preaggs-time-dimensions]: /docs/pre-aggregations/matching-pre-aggregations#matching-time-dimensions

[ref-matching-preaggs-ungrouped]: /docs/pre-aggregations/matching-pre-aggregations#matching-ungrouped-queries

[ref-pagination-recipe]: /reference/recipes/pagination

[ref-primary-key]: /reference/data-modeling/dimensions#primary_key

[ref-conf-allow-ungrouped]: /reference/configuration/config#allow_ungrouped_without_primary_key

[ref-caching]: /docs/pre-aggregations

[ref-query-rewrite]: /reference/configuration/config#query_rewrite

[blog-compare-date-range]: https://cube.dev/blog/comparing-data-over-different-time-periods

[ref-sql-api-streaming]: /reference/core-data-apis/sql-api#streaming

[ref-tableau]: /admin/connect-to-data/visualization-tools/tableau