Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

SQL: improve handling of ES aggregations #35745

Closed
costin opened this issue Nov 20, 2018 · 7 comments
Closed

SQL: improve handling of ES aggregations #35745

costin opened this issue Nov 20, 2018 · 7 comments
Labels
:Analytics/SQL SQL querying >enhancement Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo)

Comments

@costin
Copy link
Member

costin commented Nov 20, 2018

Currently underlying ES aggregations are returned as are, both as sub-aggregations and bucket selector.
Unfortunately due to the underlying inconsistencies this can have undesirable effects, for example:

  1. MAX/MIN can work on date fields however they return an actual long instead of a time object causing failures
  2. aggregations on null/empty groups return their initial result, not null which again is problematic (see Introduce contract to know whether InternalAggs have computed results or not #34903).

In both example, this can be handled inside SQL by wrapping the aggregation to guarantee the expected value.

@elasticmachine
Copy link
Collaborator

Pinging @elastic/es-search-aggs

@polyfractal
Copy link
Contributor

polyfractal commented Nov 20, 2018

For 1), you can set the format property of min/max aggregations to format the long into a date. E.g.

GET /test/_search
{
  "size": 0,
  "aggs": {
    "the_max": {
      "max": {
        "field": "timestamp",
        "format": "yyyy-MM-dd"
      }
    }
  }
}

Returns:

{
  "took": 5,
  "timed_out": false,
  "_shards": {...},
  "hits": {...},
  "aggregations": {
    "the_max": {
      "value": 1542756678236,
      "value_as_string": "2018-11-20"
    }
  }
}

If you're working with the java objects, you should be able to set the format in the request, and then from the NumericMetricsAggregation.SingleValue object you can use getValueAsString(). It unfortunately looks like getValueAsString() is not part of the Max or Min interface at the moment, but I think this is probably an oversight instead of purposeful.

EDIT: Oh, if you were looking for the time object instead of the string then you can safely ignore everything I just said :)

@costin
Copy link
Member Author

costin commented Feb 6, 2019

@astefan can you please take a look at incorporating this into MetricAggExtractor? Thanks.

@astefan
Copy link
Contributor

astefan commented Jul 25, 2019

Second part of this issue has been implemented via #44745.

@rjernst rjernst added the Team:QL (Deprecated) Meta label for query languages team label May 4, 2020
@astefan astefan removed their assignment Aug 3, 2020
@arteam arteam added v8.1.0 and removed v8.0.0 labels Jan 12, 2022
@mark-vieira mark-vieira added v8.2.0 and removed v8.1.0 labels Feb 2, 2022
@mark-vieira mark-vieira added v8.5.0 and removed v8.4.0 labels Jul 27, 2022
@elasticsearchmachine
Copy link
Collaborator

Pinging @elastic/es-ql (Team:QL)

@csoulios csoulios added v8.6.0 and removed v8.5.0 labels Sep 21, 2022
@kingherc kingherc added v8.7.0 and removed v8.6.0 labels Nov 16, 2022
@rjernst rjernst added v8.8.0 and removed v8.7.0 labels Feb 8, 2023
@gmarouli gmarouli added v8.9.0 and removed v8.8.0 labels Apr 26, 2023
@quux00 quux00 added v8.11.0 and removed v8.10.0 labels Aug 16, 2023
@mattc58 mattc58 added v8.12.0 and removed v8.11.0 labels Oct 4, 2023
@wchaparro wchaparro removed the Team:QL (Deprecated) Meta label for query languages team label Jan 17, 2024
@elasticsearchmachine
Copy link
Collaborator

Pinging @elastic/es-analytical-engine (Team:Analytics)

@elasticsearchmachine elasticsearchmachine added the Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo) label Jan 17, 2024
@wchaparro wchaparro removed the v8.14.0 label Mar 21, 2024
@wchaparro
Copy link
Member

Superceded by ES|QL

@wchaparro wchaparro closed this as not planned Won't fix, can't repro, duplicate, stale Mar 21, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
:Analytics/SQL SQL querying >enhancement Team:Analytics Meta label for analytical engine team (ESQL/Aggs/Geo)
Projects
None yet
Development

No branches or pull requests