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

hive常见问题

大数据 keguang 476℃ 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 回复