# Python3 query with large results, best approach

**URL:** <https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402>\
**Category:** Elasticsearch\
**Created:** [December 2, 2020, 5:42pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402 "2020-12-02T17:42:20Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 2, 2020, 5:42pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/1 "2020-12-02T17:42:20Z")

</div>

hi, i'm throwing a huge amount of log data into ES each month.

i'm trying to create a python script to build a report at the end of the month.

my first query i need to make is to query the index and get all the unique IP addresses and the count of how many times those IP addresses hit our network. I have millions of log entries.. and probably 100,000 unique IP addresses.

the query listed below will work, but only returns about 5000, if i increase the size above 5000, it returns 0.

what am i doing wrong? is there a better approach?  
def search(self):  
# creates the es object  
es = Elasticsearch(hosts=[self.host], timeout=60, max\_retries=3, retry\_on\_timeout=True)  
dataDict = {}

```
    es_body=body={
        "size":1, #EVERY example says set this to 0 to only get agg results, but i get an error when i set this to 0.. WHY? 
        "query": {
            "bool": {
                "must": {
                    "range": {"@timestamp": {"gte": self.start_date, "lte": self.end_Date}}
                } # must
            } # bool
        }, # query
                "aggs":{
                    "by_ip":{
                        "terms":{
                            "field": self.field,
                            "size":5000
                        }#terms
                    }#by_ip
                },#aggs
                "size":1
        } #end body

    # this gets a rough estimate of how many records will be returned
    page = es.search(
        index=self.index,
        scroll='20m',
        body=es_body
    )
    pprint(page)
```

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 3, 2020, 2:06am UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/2 "2020-12-03T02:06:52Z")

</div>

> [@stcdarrell](#):
>
> `#EVERY example says set this to 0 to only get agg results, but i get an error when i set this to 0.. WHY? `

Sharing the error you are seeing would be helpful 🙂

---

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 3, 2020, 8:13pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/3 "2020-12-03T20:13:56Z")

</div>

```
    when size is set to 0, i get this error:

```

elasticsearch.exceptions.RequestError: RequestError(400, 'action\_request\_validation\_exception', 'Validation Failed: 1: [size] cannot be [0] in a scroll context;')

but it seems every example online has size set to 0.

my basic query:  
` es_body={ "size":0, "query": { "bool": { "must": { "range": {"@timestamp": {"gte": self.start_date, "lte": self.end_Date}} } # must } # bool }, # query "aggs":{ "by_ip":{ "terms":{ "field": self.field, }#terms }#by_ip },#aggs } #end body`

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 3, 2020, 8:16pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/4 "2020-12-03T20:16:06Z")

</div>

It does, yes, but you cannot use `size` with `scroll` as the error highlights.

---

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 3, 2020, 8:19pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/5 "2020-12-03T20:19:13Z")

</div>

along the same question, dealing with the same problem.  
i'm trying to get the unique IP addresses and the count of how many times they occur in some logs, its ALOT of data. its my understanding the "agg" "cardinality" will give me an estimate of entries so i can calculate the paging i will need.

When i do this i get this result: Estimated Entries: 77352

when i run the same query with "terms" instead of "cardniality" i get a DRASTICALLY different number:  
'doc\_count\_error\_upper\_bound': 361980,  
'sum\_other\_doc\_count': 147144334}}

what am i missing here.. ? shouldnt these be close to the same?

---

<div class="post-metadata">

**Author:** ![warkolm](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/warkolm/32/39224_2.png) [@warkolm](https://discuss.elastic.co/u/warkolm)\
**Post date:** [December 3, 2020, 9:34pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/6 "2020-12-03T21:34:45Z")

</div>

Check out [https://www.elastic.co/guide/en/elasticsearch/reference/7.10/search-aggregations-metrics-cardinality-aggregation.html#\_counts\_are\_approximate](https://www.elastic.co/guide/en/elasticsearch/reference/7.10/search-aggregations-metrics-cardinality-aggregation.html#_counts_are_approximate)

---

<div class="post-metadata">

**Author:** ![Emanuil](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/emanuil/32/36783_2.png) [@Emanuil](https://discuss.elastic.co/u/Emanuil)\
**Post date:** [December 3, 2020, 11:25pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/7 "2020-12-03T23:25:33Z")

</div>

You might find [this blog post](https://www.elastic.co/blog/improving-the-performance-of-high-cardinality-terms-aggregations-in-elasticsearch) on high cardinality terms aggregation optimisations useful. [This forum topic](https://discuss.elastic.co/t/high-cardinality-aggregation-alternate-approaches-2-million-buckets/222814) may also help you, in particular the examples of [aggregation partitioning](https://www.elastic.co/guide/en/elasticsearch/reference/7.10/search-aggregations-bucket-terms-aggregation.html#_filtering_values_with_partitions) being discussed.

---

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 6, 2020, 2:58pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/8 "2020-12-06T14:58:04Z")

</div>

thank you that helped a lot, i think i got it.. just for future reference.. this seems to work.. its just test code, so its not pretty but you'll get the idea.

```
def query_test(self,es_host, es_index, es_field, startDate, endDate):
    # Define a default Elasticsearch client

    client = connections.create_connection(hosts=[es_host], timeout=90)
    a2=A('cardinality', field=es_field)
    s1 = Search(using=client, index=es_index).extra(size=0).filter('range' , **{'@timestamp': {'gte': startDate , 'lt': endDate}})

    s1.aggs.bucket('cardinality', a2)
    s1_results = s1.execute()
    print (s1_results)
    cardinality=s1_results.aggregations.cardinality.value
    print ("Cardinality", cardinality)

    i = 0
    partitions = (cardinality/9000)
    print("Partitions Float:", partitions)
    partitions=math.ceil(partitions)
    print ("Partitions Rounded:", partitions)

    itemTotal=0
    dataDict={}
    while i < partitions:
        s = Search(using=client, index=es_index).extra(size=0).filter('range' , **{'@timestamp': {'gte': startDate , 'lt': endDate}})
        a=A('terms', field=es_field, size=99999999, include={"partition": i, "num_partitions": partitions})
        s.aggs.bucket('catagory_terms', a)
        s_results = s.execute()

        for item in s_results.aggregations.catagory_terms.buckets:
            #print (item['key'], ":", item['doc_count'])
            dataDict[item['key']]=item['doc_count']
        i = i + 1

        #print (type(s), s)
        s_dict=s_results.to_dict()
        #print (s_dict['aggregations']['catagory_terms']['buckets'])
        print(" Query Iterator:", i, " Total:", len(s_dict['aggregations']['catagory_terms']['buckets']))
        itemTotal+=len(s_dict['aggregations']['catagory_terms']['buckets'])
    print ("Total Items:", itemTotal)
    print ("Items in Data Dict:", len(dataDict.keys()))

    for item in dataDict:
        #do work here
        print (item, ":", dataDict[item])
```

---

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 6, 2020, 3:00pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/9 "2020-12-06T15:00:28Z")

</div>

this works.. and i'm getting the results i should get. It seems to work fine on fields with text in them. I have a field that is a number, its a port number. such as:  
21 : FTP  
80: HTTP

etc. it crashes with this numerical field. i'd tried src\_port.keyword, that doesnt work either.. is the best approach just to re-map that field to text?

thank you

---

<div class="post-metadata">

**Author:** ![rugenl](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/rugenl/32/12887_2.png) [@rugenl](https://discuss.elastic.co/u/rugenl)\
**Post date:** [December 6, 2020, 3:09pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/10 "2020-12-06T15:09:44Z")

</div>

Beware of [https://www.elastic.co/guide/en/elasticsearch/reference/current/search-settings.html#search-settings-max-buckets](https://www.elastic.co/guide/en/elasticsearch/reference/current/search-settings.html#search-settings-max-buckets). I think your search will be limited to that number of buckets, whatever your environment setting might be.

I have some python code that uses filtering queries and scan, then do the bucketing in Python.

---

<div class="post-metadata">

**Author:** ![stcdarrell](https://avatars.discourse-cdn.com/v4/letter/s/a183cd/32.png) [@stcdarrell](https://discuss.elastic.co/u/stcdarrell)\
**Post date:** [December 6, 2020, 7:01pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/11 "2020-12-06T19:01:45Z")

</div>

thank you, so you use the "scan" to see how many buckets you will need? then use that calculation to parition ? how is that different than cardinatlity?

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [January 3, 2021, 7:01pm UTC](https://discuss.elastic.co/t/python3-query-with-large-results-best-approach/257402/12 "2021-01-03T19:01:49Z")

</div>

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.