# Hive queries to read ES data taking too long | Need suggestions to improve **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [April 7, 2016, 10:37am UTC](https://discuss.elastic.co/t/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662 "2016-04-07T10:37:33Z")\ **Posts on this page:** 5\ **Page:** 1
**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 7, 2016, 10:37am UTC](https://discuss.elastic.co/t/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662/1 "2016-04-07T10:37:33Z")
Hello, My ES version is 1.3.2 I setup an AWS EMR cluster (emr-4.2.0) with Hive 1.0.0 to provide an SQL-like interface to our data in ES. Since, our Analytics team prefers SQL like queries rather than ES. I'm using the latest ES-Hadoop connector (elasticsearch-hadoop-2.2.0) The setup was pretty easy and I'm able to connect to my ES (which is on a remote server) from Hive console. However, queries that typically take few seconds in ES are taking 2 hours from Hive! For e.g. select count(\*) from my\_table where member\_id = 1234; I'm looking for suggestions to make this faster. Am I doing anything incorrectly? Thanks... ---
**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 7, 2016, 10:58am UTC](https://discuss.elastic.co/t/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662/2 "2016-04-07T10:58:17Z")
To add some more information: the time taken for the above Hive query (select count(\*) from my\_table where member\_id = 1234;) across different shards was: 1. Shard 0, which has 3.18 GB data, 8102573 docs took 59 mins. 2. Shard 8, which has 0.02 GB data, 32432 docs took 16 secs. Some other shards have 7 GB of data, looks like it'll take 2-3 hrs on those shards. ---
**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 11, 2016, 2:44pm UTC](https://discuss.elastic.co/t/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662/3 "2016-04-11T14:44:48Z")
There's not much that can be done here since Hive does not pushes down the query. That is the count in Hive means actually moving all the data from ES to Hive so it can count it. ES-Hadoop has no visibility into the query being executed and thus cannot push it down (like in Spark SQL). P.S. Upgrading your software stack should help - even if not performance wise, it will eliminate a good chunk of bugs. ---
**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:39am UTC](https://discuss.elastic.co/t/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662/4 "2016-04-13T11:39:49Z")
Thanks for your reply Costin, I am now trying Spark SQL and the pushdown is working great! I've created 2 more queries: > [@Spark SQL : How to specify a date range in WHERE clause?](https://discuss.elastic.co/t/spark-sql-how-to-specify-a-date-range-in-where-clause/47240/1): > > 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'm stuck with how to specify a date range in the WHERE clause, so that it gets pushed down to ES? I've tried: SELECT count(member\_id),member\_id FROM data WHERE ( response\_timestamp \> CAST('2015-03-01' AS date) AND response\_timestamp \< CAST('2015-03-31' AS date) ) GRO… > [@Spark SQL: count(\*) fetches all data](https://discuss.elastic.co/t/spark-sql-count-fetches-all-data/47242/1): > > 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 … ---
**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/hive-queries-to-read-es-data-taking-too-long-need-suggestions-to-improve/46662/5 "2017-07-06T13:25:11Z")