# Select \* works but select count(\*) fails. please help **URL:** \ **Category:** Elasticsearch\ **Tags:** es-hadoop\ **Created:** [March 22, 2017, 11:40pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660 "2017-03-22T23:40:52Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![dixitnikhil2004](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dixitnikhil2004](https://discuss.elastic.co/u/dixitnikhil2004)\ **Post date:** [March 22, 2017, 11:40pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/1 "2017-03-22T23:40:52Z")
**works fine** : select \* from hivetbl\_betateamcity\_webhook\_payloads **fails:** select count(1) from hivetbl\_betateamcity\_webhook\_payloads **fails:** select count(\*) from hivetbl\_betateamcity\_webhook\_payloads **External table definition** CREATE EXTERNAL TABLE HiveTbl\_betateamcity\_webhook\_payloads ( datetimestamp timestamp, version string, Buildlog\_url string, Teamcity\_buildid string, Teamcity\_url string, build\_status string, headers\_request\_uri string, message string) STORED BY 'org.elasticsearch.hadoop.hive.EsStorageHandler' TBLPROPERTIES ( 'es.nodes' = '10.212.46.2', 'es.index.auto.create' = 'false', 'es.resource' = 'betateamcity\_webhook\_payloads', 'es.query' = '?q=\*', 'es.mapping.names' = 'datetimestamp:@timestamp , version:@version, Buildlog\_url:Buildlog\_url, Teamcity\_buildid:Teamcity\_buildid, Teamcity\_url:Teamcity\_url, build\_status:build.status, headers\_request\_uri: headers.request\_uri, message:message ' ); **Hive error:** 17/03/22 16:38:38 ERROR status.SparkJobMonitor: Status: Failed 17/03/22 16:38:38 INFO log.PerfLogger: \ 17/03/22 16:38:38 ERROR ql.Driver: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask 17/03/22 16:38:38 INFO log.PerfLogger: \ 17/03/22 16:38:38 INFO ql.Driver: Completed executing command(queryId=hive\_20170322163838\_05bab898-7bf3-4523-83ef-87376c67af0e); Time taken: 1.079 seconds 17/03/22 16:38:38 INFO log.PerfLogger: 17/03/22 16:38:38 INFO log.PerfLogger: \ 17/03/22 16:38:38 ERROR operation.Operation: Error running hive query: org.apache.hive.service.cli.HiveSQLException: Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:374) at org.apache.hive.service.cli.operation.SQLOperation.runQuery(SQLOperation.java:180) at org.apache.hive.service.cli.operation.SQLOperation.access$100(SQLOperation.java:72) at org.apache.hive.service.cli.operation.SQLOperation$2$1.run(SQLOperation.java:232) at java.security.AccessController.doPrivileged(Native Method) at javax.security.auth.Subject.doAs(Subject.java:415) at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1693) at org.apache.hive.service.cli.operation.SQLOperation$2.run(SQLOperation.java:245) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:471) at java.util.concurrent.FutureTask.run(FutureTask.java:262) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:745) **Note** : _here my hive.execution.engine=spark; but we are getting this error with mr as well._ **Note:** _Logstash is writing to the same IP address and not localhost._ **please help, kind of urgent!** ---
**Author:** ![james.baiera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/james.baiera/32/10209_2.png) [@james.baiera](https://discuss.elastic.co/u/james.baiera)\ **Post date:** [March 23, 2017, 3:24pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/2 "2017-03-23T15:24:36Z")
Could you include the logs from the failed tasks? Generally Hive does not send back the actual error that occurred on the workers. ---
**Author:** ![dixitnikhil2004](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dixitnikhil2004](https://discuss.elastic.co/u/dixitnikhil2004)\ **Post date:** [March 23, 2017, 10:44pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/3 "2017-03-23T22:44:03Z")
thanks for yor response James. We are using cloudera(CDH). where in CDH can i get the actual hive task logs? please advise! ---
**Author:** ![james.baiera](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/james.baiera/32/10209_2.png) [@james.baiera](https://discuss.elastic.co/u/james.baiera)\ **Post date:** [March 27, 2017, 3:52pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/4 "2017-03-27T15:52:03Z")
@dixitnikhil2004 it depends on which UI you are using, but when Hive stands up a job it is either executing the job locally (just running over an open stream of data for something like a simple select) or on the execution nodes (for things like joins and groupings). Depending on which framework you are using to execute Hive against, I would search for where the logs for the executors live. ---
**Author:** ![dixitnikhil2004](https://avatars.discourse-cdn.com/v4/letter/d/b9bd4f/32.png) [@dixitnikhil2004](https://discuss.elastic.co/u/dixitnikhil2004)\ **Post date:** [March 28, 2017, 7:59pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/5 "2017-03-28T19:59:00Z")
Hi James, i ran the command to query ES external hive table from hive comand line and here's the error [builder@monkey ~]$ hive -e 'Select \* from hivetbl\_betateamcity\_webhook\_payloads where build\_status = "success"' 2017-03-27 16:36:15,604 WARN [main] mapreduce.TableMapReduceUtil: The hbase-prefix-tree module jar containing PrefixTreeCodec is not present. Continuing without it. Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.7.0-1.cdh5.7.0.p0.45/jars/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties FAILED: ParseException line 1:81 character '' not supported here ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [April 25, 2017, 7:59pm UTC](https://discuss.elastic.co/t/select-works-but-select-count-fails-please-help/79660/6 "2017-04-25T19:59:12Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.