活了二十多年,没能为祖国、为人民做点什么,每思及此,伤心欲绝 !

hive常见问题

大数据 keguang 691℃ 0评论

1 hiveserver2 不识别udf函数

新写的UDF在hive cli注册以后,无法在hiveserver2远程连接时使用,show functions没有这个函数。

在无法使用UDF的 HiveServer2 上,执行 reload function 命令,将MetaStore中新增的UDF信息同步到HiveServer2内存中,即可。

2 修复大量分区

在复制hive分区表的时候,将数据复制到hive表对应目录后需要修复元数据,如果一次修复的分区数据太大,会报错:

hive> MSCK REPAIR TABLE employee;
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask

设置:

set hive.msck.path.validation=ignore;

即可解决。

3 hive on spark问题

hive on spark执行引擎下可能会出现任务创建失败的问题,报错信息类似于如下:

Job aborted due to stage failure: Aborting TaskSet 2.0 because task 8 (partition 8) cannot run anywhere due to node and executor blacklist. Blacklisting behavior can be configured via spark.blacklist.*.

可能由于集群资源不足,这时候可以将执行引擎切换为mr:

set hive.execution.engine = mr;

4 关联查询 map join

mapjoin可能由于数据量过大,导致hashtable过大,信息通常如下:

2018-08-14 13:45:17	Starting to launch local task to process map join;	maximum memory = 3121545216
2018-08-14 13:45:22	Processing rows:	200000	Hashtable size:	199999	Memory usage:	81173200	percentage: 0.026
2018-08-14 13:45:22	Processing rows:	300000	Hashtable size:	299999	Memory usage:	96965848	percentage: 0.031
2018-08-14 13:45:22	Processing rows:	400000	Hashtable size:	399999	Memory usage:	119570528	percentage: 0.038
2018-08-14 13:45:23	Processing rows:	500000	Hashtable size:	499999	Memory usage:	150950528	percentage: 0.048
2018-08-14 13:45:23	Processing rows:	600000	Hashtable size:	599999	Memory usage:	167669424	percentage: 0.054
2018-08-14 13:45:23	Processing rows:	700000	Hashtable size:	699999	Memory usage:	186097456	percentage: 0.06
2018-08-14 13:45:24	Processing rows:	800000	Hashtable size:	799999	Memory usage:	214899496	percentage: 0.069
2018-08-14 13:45:24	Processing rows:	900000	Hashtable size:	899999	Memory usage:	253081672	percentage: 0.081
2018-08-14 13:45:24	Processing rows:	1000000	Hashtable size:	999999	Memory usage:	269860032	percentage: 0.086
2018-08-14 13:45:25	Processing rows:	1100000	Hashtable size:	1099999	Memory usage:	288428040	percentage: 0.092
2018-08-14 13:45:25	Processing rows:	1200000	Hashtable size:	1199999	Memory usage:	305203024	percentage: 0.098
2018-08-14 13:45:25	Processing rows:	1300000	Hashtable size:	1299999	Memory usage:	329101832	percentage: 0.105
2018-08-14 13:45:26	Processing rows:	1400000	Hashtable size:	1399999	Memory usage:	346750928	percentage: 0.111
2018-08-14 13:45:26	Processing rows:	1500000	Hashtable size:	1499999	Memory usage:	364632376	percentage: 0.117
2018-08-14 13:45:27	Processing rows:	1600000	Hashtable size:	1599999	Memory usage:	400485488	percentage: 0.128
2018-08-14 13:45:27	Processing rows:	1700000	Hashtable size:	1699999	Memory usage:	423592528	percentage: 0.136
2018-08-14 13:45:27	Processing rows:	1800000	Hashtable size:	1799999	Memory usage:	441001320	percentage: 0.141
2018-08-14 13:45:28	Processing rows:	1900000	Hashtable size:	1899999	Memory usage:	480393760	percentage: 0.154
2018-08-14 13:45:28	Processing rows:	2000000	Hashtable size:	1999999	Memory usage:	496646872	percentage: 0.159
2018-08-14 13:45:29	Processing rows:	2100000	Hashtable size:	2099999	Memory usage:	394929688	percentage: 0.127
2018-08-14 13:45:30	Processing rows:	2200000	Hashtable size:	2199999	Memory usage:	413376272	percentage: 0.132
2018-08-14 13:45:30	Processing rows:	2300000	Hashtable size:	2299999	Memory usage:	432619704	percentage: 0.139
2018-08-14 13:45:30	Processing rows:	2400000	Hashtable size:	2399999	Memory usage:	449086760	percentage: 0.144
2018-08-14 13:45:31	Processing rows:	2500000	Hashtable size:	2499999	Memory usage:	473588744	percentage: 0.152
2018-08-14 13:45:31	Processing rows:	2600000	Hashtable size:	2599999	Memory usage:	490273968	percentage: 0.157
2018-08-14 13:45:32	Processing rows:	2700000	Hashtable size:	2699999	Memory usage:	473517936	percentage: 0.152
2018-08-14 13:45:33	Processing rows:	2800000	Hashtable size:	2799999	Memory usage:	487290736	percentage: 0.156
2018-08-14 13:45:33	Processing rows:	2900000	Hashtable size:	2899999	Memory usage:	505882208	percentage: 0.162
2018-08-14 13:45:33	Processing rows:	3000000	Hashtable size:	2999999	Memory usage:	544769232	percentage: 0.175
2018-08-14 13:45:35	Processing rows:	3100000	Hashtable size:	3099999	Memory usage:	513517056	percentage: 0.165
2018-08-14 13:45:36	Processing rows:	3200000	Hashtable size:	3199999	Memory usage:	581674824	percentage: 0.186
2018-08-14 13:45:36	Processing rows:	3300000	Hashtable size:	3299999	Memory usage:	589911608	percentage: 0.189
2018-08-14 13:45:38	Processing rows:	3400000	Hashtable size:	3399999	Memory usage:	595126712	percentage: 0.191
2018-08-14 13:45:38	Processing rows:	3500000	Hashtable size:	3499999	Memory usage:	628082248	percentage: 0.201
2018-08-14 13:45:38	Processing rows:	3600000	Hashtable size:	3599999	Memory usage:	658258048	percentage: 0.211
2018-08-14 13:45:39	Processing rows:	3700000	Hashtable size:	3699999	Memory usage:	661142776	percentage: 0.212
2018-08-14 13:45:39	Processing rows:	3800000	Hashtable size:	3799999	Memory usage:	690296592	percentage: 0.221
2018-08-14 13:45:39	Processing rows:	3900000	Hashtable size:	3899999	Memory usage:	718094024	percentage: 0.23
2018-08-14 13:45:41	Processing rows:	4000000	Hashtable size:	3999999	Memory usage:	693047888	percentage: 0.222
2018-08-14 13:45:42	Processing rows:	4100000	Hashtable size:	4099999	Memory usage:	725455224	percentage: 0.232
2018-08-14 13:45:42	Processing rows:	4200000	Hashtable size:	4199999	Memory usage:	748960112	percentage: 0.24
2018-08-14 13:45:42	Processing rows:	4300000	Hashtable size:	4299999	Memory usage:	773314560	percentage: 0.248
2018-08-14 13:45:43	Processing rows:	4400000	Hashtable size:	4399999	Memory usage:	798369360	percentage: 0.256
2018-08-14 13:45:43	Processing rows:	4500000	Hashtable size:	4499999	Memory usage:	786897120	percentage: 0.252
2018-08-14 13:45:44	Processing rows:	4600000	Hashtable size:	4599999	Memory usage:	809939296	percentage: 0.259
2018-08-14 13:45:46	Processing rows:	4700000	Hashtable size:	4699999	Memory usage:	814803248	percentage: 0.261
2018-08-14 13:45:46	Processing rows:	4800000	Hashtable size:	4799999	Memory usage:	835553080	percentage: 0.268
2018-08-14 13:45:46	Processing rows:	4900000	Hashtable size:	4899999	Memory usage:	856034840	percentage: 0.274
2018-08-14 13:45:47	Processing rows:	5000000	Hashtable size:	4999999	Memory usage:	871461528	percentage: 0.279
2018-08-14 13:45:47	Processing rows:	5100000	Hashtable size:	5099999	Memory usage:	888611904	percentage: 0.285
2018-08-14 13:45:47	Processing rows:	5200000	Hashtable size:	5199999	Memory usage:	906493000	percentage: 0.29
2018-08-14 13:45:48	Processing rows:	5300000	Hashtable size:	5299999	Memory usage:	930761688	percentage: 0.298
2018-08-14 13:45:48	Processing rows:	5400000	Hashtable size:	5399999	Memory usage:	946063224	percentage: 0.303
2018-08-14 13:45:48	Processing rows:	5500000	Hashtable size:	5499999	Memory usage:	961584224	percentage: 0.308
2018-08-14 13:45:51	Processing rows:	5600000	Hashtable size:	5599999	Memory usage:	913930208	percentage: 0.293
2018-08-14 13:45:52	Processing rows:	5700000	Hashtable size:	5699999	Memory usage:	933328544	percentage: 0.299
2018-08-14 13:45:52	Processing rows:	5800000	Hashtable size:	5799999	Memory usage:	942696712	percentage: 0.302
2018-08-14 13:45:52	Processing rows:	5900000	Hashtable size:	5899999	Memory usage:	952058760	percentage: 0.305
2018-08-14 13:45:52	Processing rows:	6000000	Hashtable size:	5999999	Memory usage:	1012596112	percentage: 0.324
2018-08-14 13:45:53	Processing rows:	6100000	Hashtable size:	6099999	Memory usage:	1027281416	percentage: 0.329
2018-08-14 13:45:53	Processing rows:	6200000	Hashtable size:	6199999	Memory usage:	1036580240	percentage: 0.332
2018-08-14 13:45:57	Processing rows:	6300000	Hashtable size:	6299999	Memory usage:	1081630784	percentage: 0.347
2018-08-14 13:45:57	Processing rows:	6400000	Hashtable size:	6399999	Memory usage:	1142482952	percentage: 0.366
2018-08-14 13:45:57	Processing rows:	6500000	Hashtable size:	6499999	Memory usage:	1150178272	percentage: 0.368
2018-08-14 13:45:58	Processing rows:	6600000	Hashtable size:	6599999	Memory usage:	1150398272	percentage: 0.369
2018-08-14 13:45:58	Processing rows:	6700000	Hashtable size:	6699999	Memory usage:	1150190856	percentage: 0.368
2018-08-14 13:45:58	Processing rows:	6800000	Hashtable size:	6799999	Memory usage:	1210452176	percentage: 0.388
2018-08-14 13:45:59	Processing rows:	6900000	Hashtable size:	6899999	Memory usage:	1217737040	percentage: 0.39
2018-08-14 13:45:59	Processing rows:	7000000	Hashtable size:	6999999	Memory usage:	1216875392	percentage: 0.39
2018-08-14 13:45:59	Processing rows:	7100000	Hashtable size:	7099999	Memory usage:	1215820016	percentage: 0.389
2018-08-14 13:46:00	Processing rows:	7200000	Hashtable size:	7199999	Memory usage:	1276674880	percentage: 0.409
2018-08-14 13:46:00	Processing rows:	7300000	Hashtable size:	7299999	Memory usage:	1284471328	percentage: 0.411
2018-08-14 13:46:00	Processing rows:	7400000	Hashtable size:	7399999	Memory usage:	1284847336	percentage: 0.412
2018-08-14 13:46:01	Processing rows:	7500000	Hashtable size:	7499999	Memory usage:	1346027176	percentage: 0.431
2018-08-14 13:46:04	Processing rows:	7600000	Hashtable size:	7599999	Memory usage:	1342277184	percentage: 0.43
2018-08-14 13:46:05	Processing rows:	7700000	Hashtable size:	7699999	Memory usage:	1341694488	percentage: 0.43
2018-08-14 13:46:05	Processing rows:	7800000	Hashtable size:	7799999	Memory usage:	1402649104	percentage: 0.449
2018-08-14 13:46:05	Processing rows:	7900000	Hashtable size:	7899999	Memory usage:	1391400192	percentage: 0.446
2018-08-14 13:46:06	Processing rows:	8000000	Hashtable size:	7999999	Memory usage:	1379990208	percentage: 0.442
2018-08-14 13:46:06	Processing rows:	8100000	Hashtable size:	8099999	Memory usage:	1447460824	percentage: 0.464
2018-08-14 13:46:06	Processing rows:	8200000	Hashtable size:	8199999	Memory usage:	1436037336	percentage: 0.46
2018-08-14 13:46:07	Processing rows:	8300000	Hashtable size:	8299999	Memory usage:	1424036912	percentage: 0.456
2018-08-14 13:46:07	Processing rows:	8400000	Hashtable size:	8399999	Memory usage:	1485287400	percentage: 0.476
2018-08-14 13:46:07	Processing rows:	8500000	Hashtable size:	8499999	Memory usage:	1480217504	percentage: 0.474
2018-08-14 13:46:08	Processing rows:	8600000	Hashtable size:	8599999	Memory usage:	1540824376	percentage: 0.494
2018-08-14 13:46:08	Processing rows:	8700000	Hashtable size:	8699999	Memory usage:	1528675464	percentage: 0.49
2018-08-14 13:46:08	Processing rows:	8800000	Hashtable size:	8799999	Memory usage:	1517746424	percentage: 0.486
2018-08-14 13:46:09	Processing rows:	8900000	Hashtable size:	8899999	Memory usage:	1584652752	percentage: 0.508
2018-08-14 13:46:13	Processing rows:	9000000	Hashtable size:	8999999	Memory usage:	1560332000	percentage: 0.50
2018-08-14 13:46:13	Processing rows:	9100000	Hashtable size:	9099999	Memory usage:	1620469560	percentage: 0.519
2018-08-14 13:46:13	Processing rows:	9200000	Hashtable size:	9199999	Memory usage:	1599945184	percentage: 0.513
2018-08-14 13:46:14	Processing rows:	9300000	Hashtable size:	9299999	Memory usage:	1580137272	percentage: 0.506
2018-08-14 13:46:14	Processing rows:	9400000	Hashtable size:	9399999	Memory usage:	1641482312	percentage: 0.526
2018-08-14 13:46:14	Processing rows:	9500000	Hashtable size:	9499999	Memory usage:	1614488336	percentage: 0.517
2018-08-14 13:46:15	Processing rows:	9600000	Hashtable size:	9599999	Memory usage:	1675293336	percentage: 0.537
2018-08-14 13:46:15	Processing rows:	9700000	Hashtable size:	9699999	Memory usage:	1657061280	percentage: 0.531
2018-08-14 13:46:15	Processing rows:	9800000	Hashtable size:	9799999	Memory usage:	1717088536	percentage: 0.55
2018-08-14 13:46:16	Processing rows:	9900000	Hashtable size:	9899999	Memory usage:	1692437760	percentage: 0.542
2018-08-14 13:46:16	Processing rows:	10000000	Hashtable size:	9999999	Memory usage:	1753456128	percentage:	0.562
2018-08-14 13:46:16	Processing rows:	10100000	Hashtable size:	10099999	Memory usage:	1727535632  percentage:	0.553
2018-08-14 13:46:18	Processing rows:	10200000	Hashtable size:	10199999	Memory usage:	1818240240  percentage:	0.582
2018-08-14 13:46:18	Processing rows:	10300000	Hashtable size:	10299999	Memory usage:	1785491472  percentage:	0.572
2018-08-14 13:46:19	Processing rows:	10400000	Hashtable size:	10399999	Memory usage:	1761082392  percentage:	0.564
2018-08-14 13:46:19	Processing rows:	10500000	Hashtable size:	10499999	Memory usage:	1828164792  percentage:	0.586
2018-08-14 13:46:25	Processing rows:	10600000	Hashtable size:	10599999	Memory usage:	1702168256  percentage:	0.545
2018-08-14 13:46:25	Processing rows:	10700000	Hashtable size:	10699999	Memory usage:	1761954576  percentage:	0.564
2018-08-14 13:46:25	Processing rows:	10800000	Hashtable size:	10799999	Memory usage:	1728189176  percentage:	0.554
2018-08-14 13:46:26	Processing rows:	10900000	Hashtable size:	10899999	Memory usage:	1795855152  percentage:	0.575
2018-08-14 13:46:26	Processing rows:	11000000	Hashtable size:	10999999	Memory usage:	1857233056  percentage:	0.595
2018-08-14 13:46:27	Processing rows:	11100000	Hashtable size:	11099999	Memory usage:	1819814648  percentage:	0.583
2018-08-14 13:46:27	Processing rows:	11200000	Hashtable size:	11199999	Memory usage:	1880245616  percentage:	0.602
2018-08-14 13:46:28	Processing rows:	11300000	Hashtable size:	11299999	Memory usage:	1849364328  percentage:	0.592
2018-08-14 13:46:28	Processing rows:	11400000	Hashtable size:	11399999	Memory usage:	1910297424  percentage:	0.612
2018-08-14 13:46:29	Processing rows:	11500000	Hashtable size:	11499999	Memory usage:	1873573456  percentage:	0.60
2018-08-14 13:46:29	Processing rows:	11600000	Hashtable size:	11599999	Memory usage:	1934965952  percentage:	0.62
2018-08-14 13:46:30	Processing rows:	11700000	Hashtable size:	11699999	Memory usage:	1906794512  percentage:	0.611
2018-08-14 13:46:30	Processing rows:	11800000	Hashtable size:	11799999	Memory usage:	1966693536  percentage:	0.63
2018-08-14 13:46:31	Processing rows:	11900000	Hashtable size:	11899999	Memory usage:	1930835264  percentage:	0.619
2018-08-14 13:46:31	Processing rows:	12000000	Hashtable size:	11999999	Memory usage:	1991556136  percentage:	0.638
2018-08-14 13:46:31	Processing rows:	12100000	Hashtable size:	12099999	Memory usage:	1960712184  percentage:	0.628
2018-08-14 13:46:32	Processing rows:	12200000	Hashtable size:	12199999	Memory usage:	2020968704  percentage:	0.647
2018-08-14 13:46:32	Processing rows:	12300000	Hashtable size:	12299999	Memory usage:	1986817352  percentage:	0.636
2018-08-14 13:46:33	Processing rows:	12400000	Hashtable size:	12399999	Memory usage:	2048177120  percentage:	0.656
2018-08-14 13:46:33	Processing rows:	12500000	Hashtable size:	12499999	Memory usage:	2017107208  percentage:	0.646
2018-08-14 13:46:40	Processing rows:	12600000	Hashtable size:	12599999	Memory usage:	2165199152  percentage:	0.694
2018-08-14 13:46:41	Processing rows:	12700000	Hashtable size:	12699999	Memory usage:	2225404272  percentage:	0.713
2018-08-14 13:46:41	Processing rows:	12800000	Hashtable size:	12799999	Memory usage:	2285611600  percentage:	0.732
2018-08-14 13:46:42	Processing rows:	12900000	Hashtable size:	12899999	Memory usage:	2237191376  percentage:	0.717
2018-08-14 13:46:42	Processing rows:	13000000	Hashtable size:	12999999	Memory usage:	2297577448  percentage:	0.736
2018-08-14 13:46:42	Processing rows:	13100000	Hashtable size:	13099999	Memory usage:	2358896560  percentage:	0.756
2018-08-14 13:46:43	Processing rows:	13200000	Hashtable size:	13199999	Memory usage:	2301714744  percentage:	0.737
2018-08-14 13:46:43	Processing rows:	13300000	Hashtable size:	13299999	Memory usage:	2369063880  percentage:	0.759
2018-08-14 13:46:44	Processing rows:	13400000	Hashtable size:	13399999	Memory usage:	2311074456  percentage:	0.74
2018-08-14 13:46:44	Processing rows:	13500000	Hashtable size:	13499999	Memory usage:	2372441392  percentage:	0.76
2018-08-14 13:46:45	Processing rows:	13600000	Hashtable size:	13599999	Memory usage:	2433399456  percentage:	0.78
2018-08-14 13:46:45	Processing rows:	13700000	Hashtable size:	13699999	Memory usage:	2385617136  percentage:	0.764
2018-08-14 13:46:46	Processing rows:	13800000	Hashtable size:	13799999	Memory usage:	2446191864  percentage:	0.784
2018-08-14 13:46:46	Processing rows:	13900000	Hashtable size:	13899999	Memory usage:	2392632160  percentage:	0.766
2018-08-14 13:46:46	Processing rows:	14000000	Hashtable size:	13999999	Memory usage:	2454114736  percentage:	0.786
2018-08-14 13:46:47	Processing rows:	14100000	Hashtable size:	14099999	Memory usage:	2520376304  percentage:	0.807
2018-08-14 13:46:53	Processing rows:	14200000	Hashtable size:	14199999	Memory usage:	2466403888  percentage:	0.79
2018-08-14 13:46:53	Processing rows:	14300000	Hashtable size:	14299999	Memory usage:	2527498032  percentage:	0.81
2018-08-14 13:46:54	Processing rows:	14400000	Hashtable size:	14399999	Memory usage:	2466257960  percentage:	0.79
2018-08-14 13:46:55	Processing rows:	14500000	Hashtable size:	14499999	Memory usage:	2532743496  percentage:	0.811
2018-08-14 13:46:55	Processing rows:	14600000	Hashtable size:	14599999	Memory usage:	2593804768  percentage:	0.831
2018-08-14 13:46:56	Processing rows:	14700000	Hashtable size:	14699999	Memory usage:	2517593104  percentage:	0.807
2018-08-14 13:46:56	Processing rows:	14800000	Hashtable size:	14799999	Memory usage:	2579513248  percentage:	0.826
2018-08-14 13:46:56	Processing rows:	14900000	Hashtable size:	14899999	Memory usage:	2645665792  percentage:	0.848
2018-08-14 13:46:57	Processing rows:	15000000	Hashtable size:	14999999	Memory usage:	2567899120  percentage:	0.823
2018-08-14 13:46:57	Processing rows:	15100000	Hashtable size:	15099999	Memory usage:	2629490368  percentage:	0.842
2018-08-14 13:46:58	Processing rows:	15200000	Hashtable size:	15199999	Memory usage:	2690597416  percentage:	0.862
2018-08-14 13:46:58	Processing rows:	15300000	Hashtable size:	15299999	Memory usage:	2619078088  percentage:	0.839
2018-08-14 13:46:59	Processing rows:	15400000	Hashtable size:	15399999	Memory usage:	2679496480  percentage:	0.858
2018-08-14 13:46:59	Processing rows:	15500000	Hashtable size:	15499999	Memory usage:	2739121704  percentage:	0.877
2018-08-14 13:47:00	Processing rows:	15600000	Hashtable size:	15599999	Memory usage:	2662007064  percentage:	0.853
2018-08-14 13:47:00	Processing rows:	15700000	Hashtable size:	15699999	Memory usage:	2730572800  percentage:	0.875
2018-08-14 13:47:00	Processing rows:	15800000	Hashtable size:	15799999	Memory usage:	2790449144  percentage:	0.894
2018-08-14 13:47:01	Processing rows:	15900000	Hashtable size:	15899999	Memory usage:	2714047368  percentage:	0.869
2018-08-14 13:47:01	Processing rows:	16000000	Hashtable size:	15999999	Memory usage:	2773832008  percentage:	0.889
2018-08-14 13:47:02	Processing rows:	16100000	Hashtable size:	16099999	Memory usage:	2840990016  percentage:	0.91
Execution failed with exit status: 3
Obtaining error information

Task failed!
Task ID:
  Stage-7

Logs:

/tmp/hadoop/hive.log
FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.mr.MapredLocalTask
MapReduce Jobs Launched: 
Stage-Stage-3: Map: 3  Reduce: 1   Cumulative CPU: 55.34 sec   HDFS Read: 35756 HDFS Write: 114 SUCCESS
Stage-Stage-5: Map: 6   Cumulative CPU: 68.99 sec   HDFS Read: 1041020753 HDFS Write: 1329327464 SUCCESS
Total MapReduce CPU Time Spent: 2 minutes 4 seconds 330 msec

关闭本地map join优化即可。

set hive.auto.convert.join = false;  #取消小表加载至内存中

 

 

转载请注明:柯广的博客 » hive常见问题

喜欢 (3)or分享 (0)
发表我的评论
取消评论

表情

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址
(1)个小伙伴在吐槽
  1. 虽然不懂,但是必须得努力学习。
    好看的小说2019-08-12 21:44 回复