This is a failed optimization hive is trying to build the lookup table locally and then put it in the distributed cache and then to a map join. Look through your hive site for the configuration to turn these auto-map joins off. Based on your version the variables changed a names /deprecated etc so I can not tell you the exact ones.
On Fri, Jul 18, 2014 at 10:35 AM, Clay McDonald < stuart.mcdon...@bateswhite.com> wrote: > Hello everyone. I need some assistance. I have a join that fails with > return code 3. The query is; > > SELECT B.CARD_NBR AS CNT > FROM TENDER_TABLE A > JOIN LOYALTY_CARDS B > ON A.CARD_NBR = B.CARD_NBR > LIMIT 10; > > -- Row Counts > -- LOYALTY_CARDS = 43,876,938 > -- TENDER_TABLE = 1,412,228,333 > > The query execution output starts with; > > 2014-07-18 10:30:17 Starting to launch local task to process map join; > maximum memory = 1065484288 > > The last output is as follows; > > 2014-07-18 10:30:44 Processing rows: 3800000 Hashtable size: > 3799999 Memory usage: 969531248 percentage: 0.91 > > I ran SET mapred.child.java.opts=-Xmx4G; before the query but that did not > change the maximum memory. What am I not understanding and how should I > troubleshoot his issue? > > > hive> SELECT B.CARD_NBR AS CNT > > FROM TENDER_TABLE A > > JOIN LOYALTY_CARDS B > > ON A.CARD_NBR = B.CARD_NBR > > LIMIT 10; > Query ID = root_20140718103030_df1e7af9-7d66-4ba5-8d73-2d0bf58bb474 > Total jobs = 1 > 14/07/18 10:30:17 WARN conf.Configuration: > file:/tmp/root/hive_2014-07-18_10-30-15_081_1503496466695602651-1/-local-10006/jobconf.xml:an > attempt to override final parameter: > mapreduce.job.end-notification.max.retry.interval; Ignoring. > 14/07/18 10:30:17 WARN conf.Configuration: > file:/tmp/root/hive_2014-07-18_10-30-15_081_1503496466695602651-1/-local-10006/jobconf.xml:an > attempt to override final parameter: > mapreduce.job.end-notification.max.attempts; Ignoring. > 14/07/18 10:30:17 INFO Configuration.deprecation: mapred.reduce.tasks is > deprecated. Instead, use mapreduce.job.reduces > 14/07/18 10:30:17 INFO Configuration.deprecation: mapred.min.split.size is > deprecated. Instead, use mapreduce.input.fileinputformat.split.minsize > 14/07/18 10:30:17 INFO Configuration.deprecation: > mapred.reduce.tasks.speculative.execution is deprecated. Instead, use > mapreduce.reduce.speculative > 14/07/18 10:30:17 INFO Configuration.deprecation: > mapred.min.split.size.per.node is deprecated. Instead, use > mapreduce.input.fileinputformat.split.minsize.per.node > 14/07/18 10:30:17 INFO Configuration.deprecation: > mapred.input.dir.recursive is deprecated. Instead, use > mapreduce.input.fileinputformat.input.dir.recursive > 14/07/18 10:30:17 INFO Configuration.deprecation: > mapred.min.split.size.per.rack is deprecated. Instead, use > mapreduce.input.fileinputformat.split.minsize.per.rack > 14/07/18 10:30:17 INFO Configuration.deprecation: mapred.max.split.size is > deprecated. Instead, use mapreduce.input.fileinputformat.split.maxsize > 14/07/18 10:30:17 INFO Configuration.deprecation: > mapred.committer.job.setup.cleanup.needed is deprecated. Instead, use > mapreduce.job.committer.setup.cleanup.needed > Execution log at: > /tmp/root/root_20140718103030_df1e7af9-7d66-4ba5-8d73-2d0bf58bb474.log > 2014-07-18 10:30:17 Starting to launch local task to process map join; > maximum memory = 1065484288 > 2014-07-18 10:30:20 Processing rows: 200000 Hashtable size: > 199999 Memory usage: 53829960 percentage: 0.051 > 2014-07-18 10:30:21 Processing rows: 300000 Hashtable size: > 299999 Memory usage: 76926312 percentage: 0.072 > 2014-07-18 10:30:22 Processing rows: 400000 Hashtable size: > 399999 Memory usage: 105119456 percentage: 0.099 > 2014-07-18 10:30:23 Processing rows: 500000 Hashtable size: > 499999 Memory usage: 129079592 percentage: 0.121 > 2014-07-18 10:30:24 Processing rows: 600000 Hashtable size: > 599999 Memory usage: 151469744 percentage: 0.142 > 2014-07-18 10:30:24 Processing rows: 700000 Hashtable size: > 699999 Memory usage: 174968512 percentage: 0.164 > 2014-07-18 10:30:25 Processing rows: 800000 Hashtable size: > 799999 Memory usage: 207735176 percentage: 0.195 > 2014-07-18 10:30:25 Processing rows: 900000 Hashtable size: > 899999 Memory usage: 232306976 percentage: 0.218 > 2014-07-18 10:30:26 Processing rows: 1000000 Hashtable size: > 999999 Memory usage: 255813784 percentage: 0.24 > 2014-07-18 10:30:27 Processing rows: 1100000 Hashtable size: > 1099999 Memory usage: 280781448 percentage: 0.264 > 2014-07-18 10:30:27 Processing rows: 1200000 Hashtable size: > 1199999 Memory usage: 305606024 percentage: 0.287 > 2014-07-18 10:30:28 Processing rows: 1300000 Hashtable size: > 1299999 Memory usage: 323502504 percentage: 0.304 > 2014-07-18 10:30:28 Processing rows: 1400000 Hashtable size: > 1399999 Memory usage: 347450792 percentage: 0.326 > 2014-07-18 10:30:29 Processing rows: 1500000 Hashtable size: > 1499999 Memory usage: 372281800 percentage: 0.349 > 2014-07-18 10:30:30 Processing rows: 1600000 Hashtable size: > 1599999 Memory usage: 413191040 percentage: 0.388 > 2014-07-18 10:30:30 Processing rows: 1700000 Hashtable size: > 1699999 Memory usage: 438363432 percentage: 0.411 > 2014-07-18 10:30:31 Processing rows: 1800000 Hashtable size: > 1799999 Memory usage: 462137696 percentage: 0.434 > 2014-07-18 10:30:32 Processing rows: 1900000 Hashtable size: > 1899999 Memory usage: 486055520 percentage: 0.456 > 2014-07-18 10:30:32 Processing rows: 2000000 Hashtable size: > 1999999 Memory usage: 509470528 percentage: 0.478 > 2014-07-18 10:30:33 Processing rows: 2100000 Hashtable size: > 2099999 Memory usage: 532719808 percentage: 0.50 > 2014-07-18 10:30:34 Processing rows: 2200000 Hashtable size: > 2199999 Memory usage: 559536600 percentage: 0.525 > 2014-07-18 10:30:34 Processing rows: 2300000 Hashtable size: > 2299999 Memory usage: 582320848 percentage: 0.547 > 2014-07-18 10:30:34 Processing rows: 2400000 Hashtable size: > 2399999 Memory usage: 605378000 percentage: 0.568 > 2014-07-18 10:30:34 Processing rows: 2500000 Hashtable size: > 2499999 Memory usage: 631760096 percentage: 0.593 > 2014-07-18 10:30:37 Processing rows: 2600000 Hashtable size: > 2599999 Memory usage: 644527288 percentage: 0.605 > 2014-07-18 10:30:37 Processing rows: 2700000 Hashtable size: > 2699999 Memory usage: 670778416 percentage: 0.63 > 2014-07-18 10:30:37 Processing rows: 2800000 Hashtable size: > 2799999 Memory usage: 692955384 percentage: 0.65 > 2014-07-18 10:30:37 Processing rows: 2900000 Hashtable size: > 2899999 Memory usage: 719573912 percentage: 0.675 > 2014-07-18 10:30:37 Processing rows: 3000000 Hashtable size: > 2999999 Memory usage: 741345744 percentage: 0.696 > 2014-07-18 10:30:37 Processing rows: 3100000 Hashtable size: > 3099999 Memory usage: 768150432 percentage: 0.721 > 2014-07-18 10:30:40 Processing rows: 3200000 Hashtable size: > 3199999 Memory usage: 821525128 percentage: 0.771 > 2014-07-18 10:30:40 Processing rows: 3300000 Hashtable size: > 3299999 Memory usage: 848349472 percentage: 0.796 > 2014-07-18 10:30:41 Processing rows: 3400000 Hashtable size: > 3399999 Memory usage: 875173824 percentage: 0.821 > 2014-07-18 10:30:41 Processing rows: 3500000 Hashtable size: > 3499999 Memory usage: 895656992 percentage: 0.841 > 2014-07-18 10:30:41 Processing rows: 3600000 Hashtable size: > 3599999 Memory usage: 922231528 percentage: 0.866 > 2014-07-18 10:30:44 Processing rows: 3700000 Hashtable size: > 3699999 Memory usage: 943112616 percentage: 0.885 > 2014-07-18 10:30:44 Processing rows: 3800000 Hashtable size: > 3799999 Memory usage: 969531248 percentage: 0.91 > Execution failed with exit status: 3 > Obtaining error information > > Task failed! > Task ID: > Stage-4 > > Logs: > > /tmp/root/hive.log > FAILED: Execution Error, return code 3 from > org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask > > Thanks, Clay >
