# Spark SQL: count(\*) fetches all data

**URL:** <https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242>\
**Category:** Elasticsearch\
**Tags:** es-hadoop\
**Created:** [April 13, 2016, 11:37am UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242 "2016-04-13T11:37:40Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Preeti\_Raj\_Buchhada](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/preeti_raj_buchhada/32/8971_2.png) [@Preeti\_Raj\_Buchhada](https://discuss.elastic.co/u/Preeti_Raj_Buchhada)\
**Post date:** [April 13, 2016, 11:37am UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242/1 "2016-04-13T11:37:40Z")

</div>

Hello,

I've got some success working with Spark SQL CLI to access our ES data.  
Environment:  
**ES** : 1.3.2  
**es-hadoop** : elasticsearch-hadoop-2.2.0  
**Spark** : spark-1.4.1-bin-hadoop2.6

The pushdown feature is working great!

However, I noticed that for queries like:

> SELECT count(member\_id),member\_id FROM data WHERE ( member\_id \> 2049510 AND member\_id \< 2049520 ) GROUP BY member\_id ;

es-hadoop ends up reading all data from ES.

And hence these queries are taking way too long. In my case it took 44 minutes!

> 5383 2049512  
> 875911 2049513  
> 363823 2049516  
> 840819 2049519  
> Time taken: 2644.187 seconds, Fetched 4 row(s)

Pushdown was working as confirmed from the logs:

> 2016-04-13 10:13:57 TRACE CommonsHttpTransport:463 - Tx [POST]@[x.x.x.x:9201][index/data/\_search?search\_type=scan&scroll=5m&size=50&\_source=member\_id&preference=\_shards:1;\_only\_node:ShzMilJKRU6rByUEQpbs0g] w/ payload [{"query":{"filtered":{ "query":{"query\_string":{ "query":"datatype:dal" }},"filter": { "and" : [**{"range":{"member\_id":{"gt" :2049510}}},{"range":{"member\_id":{"lt" :2049520}}}]** } }}}]

Is there a better way to get counts grouped by some field with the current es-hadoop version?

---

<div class="post-metadata">

**Author:** ![costin](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/costin/32/44950_2.png) [@costin](https://discuss.elastic.co/u/costin)\
**Post date:** [April 13, 2016, 11:59am UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242/2 "2016-04-13T11:59:56Z")

</div>

Not sure what you are asking and why you think es-hadoop reads _all_ the data. It's the Spark SQL planner that decides what queries to make which in turn use ES-Hadoop to get the data from ES; in some cases by pushing down, in others by requesting the data.  
Outside enabling logging in ES-Hadoop to see the queries that it makes, check out Spark SQL and its performance tuning section in the docs.

---

<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:** [July 6, 2017, 1:25pm UTC](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242/3 "2017-07-06T13:25:09Z")

</div>