个人中心
退出
hive常见问题
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; #取消小表加载至内存中
作者：柯广的网络日志
微信公众号：Java大数据与数据仓库
分类导航