hive问题汇总

1.  进行sql 查询，count（*） 时报错，如下

hive> select count(*) from duan;
Query ID = root_20190104154949_2c2d73dc-3059-455b-b845-1ee824f2fd82
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=<number>
In order to limit the maximum number of reducers:
  set hive.exec.reducers.max=<number>
In order to set a constant number of reducers:
  set mapreduce.job.reduces=<number>
Starting Job = job_1546564955240_0001, Tracking URL = http://servers3:8088/proxy/application_1546564955240_0001/
Kill Command = /opt/cloudera/parcels/CDH-5.12.2-1.cdh5.12.2.p0.4/lib/hadoop/bin/hadoop job  -kill job_1546564955240_0001
Hadoop job information for Stage-1: number of mappers: 5; number of reducers: 1
2019-01-04 15:53:03,609 Stage-1 map = 80%,  reduce = 0%
Ended Job = job_1546564955240_0001 with errors
Error during job, obtaining debugging information...
Examining task ID: task_1546564955240_0001_m_000003 (and more) from job job_1546564955240_0001
Examining task ID: task_1546564955240_0001_r_000000 (and more) from job job_1546564955240_0001

Task with the most failures(4): 
-----
Task ID:
  task_1546564955240_0001_m_000001

URL:
  http://servers3:8088/taskdetails.jsp?jobid=job_1546564955240_0001&tipid=task_1546564955240_0001_m_000001
-----
Diagnostic Messages for this Task:


FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
MapReduce Jobs Launched: 
Stage-Stage-1: Map: 5  Reduce: 1   FAIL
Total MapReduce CPU Time Spent: -1 msec

　　查看日志：

yarn logs -applicationId application_1546564955240_0001

2019-01-04 15:51:58,453 WARN [main] org.apache.hadoop.mapred.YarnChild: Exception running child : java.net.NoRouteToHostException: No Route to Host from servers3/192.168.86.8 to servers1:44423 failed on socket timeout exception: java.net.NoRouteToHostException: No route to host; For more details see: http://wiki.apache.org/hadoop/NoRouteToHost
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
at org.apache.hadoop.net.NetUtils.wrapWithMessage(NetUtils.java:791)
at org.apache.hadoop.net.NetUtils.wrapException(NetUtils.java:757)
at org.apache.hadoop.ipc.Client.call(Client.java:1508)
at org.apache.hadoop.ipc.Client.call(Client.java:1441)
at org.apache.hadoop.ipc.WritableRpcEngine$Invoker.invoke(WritableRpcEngine.java:246)
at com.sun.proxy.$Proxy8.getTask(Unknown Source)
at org.apache.hadoop.mapred.YarnChild.main(YarnChild.java:132)
Caused by: java.net.NoRouteToHostException: No route to host

 原因分析：Hadoop集群开启了防火墙，44423 是socket临时端口，原因是搭建集群时，角色分配出现问题。官网建议（https://www.cloudera.com/documentation/enterprise/5-12-x/topics/cm_ig_host_allocations.html#Untitled1）

Cluster Size	Master Hosts	Utility Hosts	Edge Hosts	Worker Hosts
Very Small, without High Availability Up to 10 worker hosts High availability not enabled	Master Host 1: NameNode YARN ResourceManager JobHistory Server ZooKeeper Impala StateStore	One host for all Utility and Edge roles: Secondary NameNode Cloudera Manager Cloudera Manager Management Service Hive Metastore HiveServer2 Impala Catalog Hue Oozie Flume Gateway configuration		3 - 10 Worker Hosts: DataNode NodeManager Impalad
Small, with High Availability Up to 20 worker hosts High availability enabled	Master Host 1: NameNode JournalNode FailoverController YARN ResourceManager ZooKeeper JobHistory Server Master Host 2: NameNode JournalNode FailoverController YARN ResourceManager ZooKeeper Impala StateStore	One host for all Utility and Edge roles: Cloudera Manager Cloudera Manager Management Service Hive Metastore HiveServer2 Impala Catalog Hue Oozie Flume Gateway configuration ZooKeeper (requires dedicated disk) JournalNode (requires dedicated disk)		3 - 20 Worker Hosts: DataNode NodeManager Impalad

解决办法是将Hive Metastore和HiveServer2分配到一台主机上。