2022.06

2022.06

  • Imply
  • Ingest
  • Query
  • Visualize
  • Administer
  • Deploy

›Native query components

Overview

  • Querying data
  • Datasources

    • Datasources
    • Joins
    • Lookups
    • Query execution

    Query configuration

    • Query caching
    • Using query caching
    • Query context
  • Enhanced IP support
  • Multi-value dimensions
  • Multitenancy
  • Troubleshooting

Druid SQL

  • Overview and syntax
  • SQL data types
  • Druid SQL Functions

    • All functions
    • Operators
    • Scalar functions
    • Aggregation functions
    • Multi-value string functions
  • SQL metadata tables
  • SQL query context
  • Async SQL download
  • SQL query translation
  • Druid SQL APIs

    • Druid SQL API
    • Async SQL download API
    • JDBC driver API

Native queries

  • Native queries
  • Native query types

    • Timeseries
    • TopN
    • GroupBy
    • Scan
    • Search
    • TimeBoundary
    • SegmentMetadata
    • DatasourceMetadata

    Native query components

    • Filters
    • Granularities
    • Dimensions
    • Aggregations
    • Post-aggregations
    • Expressions
    • Having filters (groupBy)
    • Sorting and limiting (groupBy)
    • Sorting (topN)
    • String comparators
    • Virtual columns
    • Spatial filters

Sorting and limiting (groupBy)

Apache Druid supports two query languages: Druid SQL and native queries. This document describes the native language. For information about sorting in SQL, refer to the SQL documentation.

The limitSpec field provides the functionality to sort and limit the set of results from a groupBy query. If you group by a single dimension and are ordering by a single metric, we highly recommend using TopN Queries instead. The performance will be substantially better. Available options are:

DefaultLimitSpec

The default limit spec takes a limit and the list of columns to do an orderBy operation over. The grammar is:

{
    "type"    : "default",
    "limit"   : <optional integer>,
    "offset"  : <optional integer>,
    "columns" : [<optional list of OrderByColumnSpec>],
}

The "limit" parameter is the maximum number of rows to return.

The "offset" parameter tells Druid to skip this many rows when returning results. If both "limit" and "offset" are provided, then "offset" will be applied first, followed by "limit". For example, a spec with limit 100 and offset 10 will return 100 rows starting from row number 10. Internally, the query is executed by extending the limit by the offset and then discarding a number of rows equal to the offset. This means that raising the offset will increase resource usage by an amount similar to increasing the limit.

Together, "limit" and "offset" can be used to implement pagination. However, note that if the underlying datasource is modified in between page fetches in ways that affect overall query results, then the different pages will not necessarily align with each other.

OrderByColumnSpec

OrderByColumnSpecs indicate how to do order by operations. Each order-by condition can be a jsonString or a map of the following form:

{
    "dimension" : "<Any dimension or metric name>",
    "direction" : <"ascending"|"descending">,
    "dimensionOrder" : <"lexicographic"(default)|"alphanumeric"|"strlen"|"numeric">
}

If only the dimension is provided (as a JSON string), the default order-by is ascending with lexicographic sorting.

See Sorting Orders for more information on the sorting orders specified by "dimensionOrder".

Last updated on 6/16/2022
← Having filters (groupBy)Sorting (topN) →
2022.06
Key links
Try ImplyApache Druid siteImply GitHub
Get help
Stack OverflowSupportContact us
Learn more
Apache Druid forumsBlog
Copyright © 2022 Imply Data, Inc