ElasticSearch select distinct from a large number of documents?

user_148423 0
Viewed 961

I have an index with ~100 million documents. Sometimes I want to retrieve all documents, sometimes I want to group by a particular field, selecting only one document for each unique value. Something like:

SELECT * FROM documents GROUP BY my_field;

To illustrate, sometimes I want to get:

|id|my_field|thing  |
|--|--------|-------|
|1 |a       |red    |
|2 |b       |yellow |
|3 |b       |green  |
|4 |c       |brown  |

and sometimes

|id|my_field|thing  |
|--|--------|-------|
|1 |a       |red    |
|3 |b       |green  |
|4 |c       |brown  |

The ElasticSearch documents suggest that Terms Aggregation fits this use. However, there is the suggestion that this is only suitable for small cardinalities of the aggregated term, i.e. a small number of 'buckets':

Sometimes there are too many unique terms to process in a single request/response pair so it can be useful to break the analysis up into multiple requests

In addition to this, the Top Hits aggregation returns data in a format that doesn't look like it can be paginated easily.

If I had, for example, a million distinct terms, I looks like it wouldn't be well aligned with the intended design. Is this correct?

My alternative is to maintain an index and flag duplicates in my own code, but this is error-prone, subject to concurrency issues etc.

Is there a way to achieve what I want that plays to the strengths of ElasticSearch?

elasticsearch
2 Answers

It looks like this isn't the best use case for ElasticSearch indexes / aggregation. I solved my problem another way - by creating a separate type (or index) which stored the deduplicated view of my data. I used a hash of the unique pair as the _id field, so ElasticSearch automatically deduplicates documents as they come in.

As a bonus feature, using an external _timestamp allows me to choose which document gets stored in the case of duplicates.

user_1484230

I believe you are correct in your assessment that, while the cardinality agg fits here, the scale at which you may encounter distinct values would create problems. This type of aggregation is the closest thing to a "GROUP BY" that I'm aware of in ElasticSearch.

That being said, I think there is a way you can accomplish

selecting only one document for each unique value.

via multiple queries nested within a "Should" parent.

Your approach will vary much differently depending on if you are aiming only for aggregate counts, or returned values.

My initial inclination would be to treat your request as a set of sibling queries nested within should. The problem with that approach would be returning based on most fields matched, rather than the first document of each distinct query. As far as I'm aware of, there isn't any type of scoring for "should" queries that return a single match for each bucket.

With the goal of returning documents, I believe you would be best suited to execute a multi-search :

POST _msearch
{"index":"INDEX"}
{"query":{"match": {"a": "red"}},"size":1}
{"index":"INDEX"}
{"query":{"match": {"b": "yellow"}},"size":1}
{"index":"INDEX"}
{"query":{"match": {"b": "green"}},"size":1}

The above will return you a single document matching each query, giving you a response with an array of "result" objects.

Raw counts:

POST /INDEX/_search
{
  "size": 0, 
  "aggs": {
    "a_red": {
      "filter": {
        "term": {
          "a": {
            "value": "red"
          }
        }
      }
    },        
    "b_yellow": {
      "filter": {
        "term": {
          "b": {
            "value": "yellow"
          }
        }
      }
    },        
    "b_green": {
      "filter": {
        "term": {
          "b": {
            "value": "green"
          }
        }
      }
    }
  }
}

Note: the above example's agg bucket names (ex: "a_red") are just for clarity, they aren't required.

user_63687340
Related