Hive not running Map Reduce with “where” clause

Question

I'm trying out something simple in Hive on HDFS. The problem is that the queries are not running map reduce when I'm running a "where clause". However, it runs map reduce for count(*), and even group by clauses.

Here's data and queries with result:

Create External Table: CREATE EXTERNAL TABLE testtab1 ( id STRING, org STRING) row format delimited fields terminated by ',' stored as textfile location '/usr/ankuchak/testtable1';

Simple select * query:

0: jdbc:hive2://> select * from testtab1;

15/07/01 07:32:46 [main]: ERROR hdfs.KeyProviderCache: Could not find uri with key [dfs.encryption.key.provider.uri] to create a keyProvider !! OK +---------------+---------------+--+

| testtab1.id | testtab1.org |

+---------------+---------------+--+

| ankur | idc |

| user | idc |

| someone else | ssi |

+---------------+---------------+--+

3 rows selected (2.169 seconds)

Count(*) query

0: jdbc:hive2://> select count(*) from testtab1;

Query ID = ankuchak_20150701073407_e7fd66ae-8812-4e02-87d7-492f81781d15 Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer= In order to limit the maximum number of reducers: set hive.exec.reducers.max= In order to set a constant number of reducers: set mapreduce.job.reduces= 15/07/01 07:34:08 [HiveServer2-Background-Pool: Thread-40]: ERROR mr.ExecDriver: yarn 15/07/01 07:34:08 [HiveServer2-Background-Pool: Thread-40]: WARN mapreduce.JobResourceUploader: Hadoop command-line option parsing not performed. Implement the Tool interface and execute your application with ToolRunner to remedy this. Starting Job = job_1435425589664_0005, Tracking URL = http://slc02khv:8088/proxy/application_1435425589664_0005/ Kill Command = /scratch/hadoop/hadoop/bin/hadoop job -kill job_1435425589664_0005 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 15/07/01 07:34:16 [HiveServer2-Background-Pool: Thread-40]: WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead 2015-07-01 07:34:16,291 Stage-1 map = 0%, reduce = 0% 2015-07-01 07:34:23,831 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.04 sec 2015-07-01 07:34:30,102 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.41 sec MapReduce Total cumulative CPU time: 2 seconds 410 msec Ended Job = job_1435425589664_0005 MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 2.41 sec HDFS Read: 6607 HDFS Write: 2 SUCCESS Total MapReduce CPU Time Spent: 2 seconds 410 msec OK

+------+--+

| _c0 |

+------+--+

| 3 |

+------+--+

1 row selected (23.527 seconds)

Group by query:

0: jdbc:hive2://> select org, count(id) from testtab1 group by org;

Query ID = ankuchak_20150701073540_5f20df4e-0bd4-4e18-b065-44c2688ce21f Total jobs = 1 Launching Job 1 out of 1 Number of reduce tasks not specified. Estimated from input data size: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer= In order to limit the maximum number of reducers: set hive.exec.reducers.max= In order to set a constant number of reducers: set mapreduce.job.reduces= 15/07/01 07:35:40 [HiveServer2-Background-Pool: Thread-63]: ERROR mr.ExecDriver: yarn 15/07/01 07:35:41 [HiveServer2-Background-Pool: Thread-63]: WARN mapreduce.JobResourceUploader: Hadoop command-line option parsing not performed. Implement the Tool interface and execute your application with ToolRunner to remedy this. Starting Job = job_1435425589664_0006, Tracking URL = http://slc02khv:8088/proxy/application_1435425589664_0006/ Kill Command = /scratch/hadoop/hadoop/bin/hadoop job -kill job_1435425589664_0006 Hadoop job information for Stage-1: number of mappers: 1; number of reducers: 1 15/07/01 07:35:47 [HiveServer2-Background-Pool: Thread-63]: WARN mapreduce.Counters: Group org.apache.hadoop.mapred.Task$Counter is deprecated. Use org.apache.hadoop.mapreduce.TaskCounter instead 2015-07-01 07:35:47,200 Stage-1 map = 0%, reduce = 0% 2015-07-01 07:35:53,494 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 1.05 sec 2015-07-01 07:36:00,799 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 2.53 sec MapReduce Total cumulative CPU time: 2 seconds 530 msec Ended Job = job_1435425589664_0006 MapReduce Jobs Launched: Stage-Stage-1: Map: 1 Reduce: 1 Cumulative CPU: 2.53 sec HDFS Read: 7278 HDFS Write: 14 SUCCESS Total MapReduce CPU Time Spent: 2 seconds 530 msec OK

+-------+------+--+

| org | _c1 |

+-------+------+--+

| idc | 2 |

| ssi | 1 |

+-------+------+--+

2 rows selected (21.187 seconds)

Now the simple where clause:

0: jdbc:hive2://> select * from testtab1 where org='idc';

OK

+--------------+---------------+--+

| testtab1.id | testtab1.org |

+--------------+---------------+--+

No rows selected (0.11 seconds)

It would be great if you could provide me with some pointers. Please let me know if you need further information in this regard.

Regards, Ankur

This question may be a duplicate stackoverflow.com/questions/30908203/… — invoketheshell
Hi @invoketheshell , thanks a lot for that link. I read through it. And yes, I agree that for some cases only Maps will run and no reduce will run. But in this case, neither map nor reduce is running. — Ankur

invoketheshell invoketheshell · Accepted Answer · 2015-07-01T15:38:20

Map job is occuring in your last query. So it's not that map reduce is not happening. However, some rows should be returned in your last query. The likely culprit here is that for some reason it is not finding a match on the value "idc". Check your table and ensure that the group for Ankur and user contain the string idc.

Try this to see if you get any results:

Select * from testtab1 where org rlike '.*(idc).*';

or

Select * from testtab1 where org like '%idc%';

These will grab any row that has a value containing the string 'idc'. Good luck!

Hive not running Map Reduce with “where” clause

2 Answers