Hive SQL执行失败问题记录
概述
Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
Hive数据源跑SQL失败报错信息：Error while processing statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.mr.MapRedTask
网上查找文档，几乎都是说：namenode内存空间不够，JVM剩余内存空间不够新job运行所致。考虑到impala SQL有set mem_limit=4G写法，测试验证下，Hive也支持如下写法；
Error while compiling statement: FAILED: Execution Error, return code 2 from org.apache.hadoop.hive.ql.exec.tez.TezTask. Vertex failed, is running beyond the ‘PHYSICAL’ memory limit.
解决方案：
set tez.grouping.min-size=1G;
set tez.grouping.max-size=1G;
HiveSQLException: AnalysisException: Table already exists
具体的报错信息：
ERROR c.x.c.a.b.d.b.JdbcDataProvider - jdbc execAutoWork 资金排量缺口 error: {}
org.apache.hive.service.cli.HiveSQLException: AnalysisException: Table already exists: rpt.
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:266)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:252)
at org.apache.hive.jdbc.HiveStatement.runAsyncOnServer(HiveStatement.java:318)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:259)
at org.apache.hive.jdbc.HiveStatement.executeUpdate(HiveStatement.java:487)
at com.alibaba.druid.pool.DruidPooledStatement.executeUpdate(DruidPooledStatement.java:336)
Error while compiling statement: FAILED: SemanticException org.apache.hadoop.hive.ql.metadata.InvalidTableException: Table not found
Error while cleaning up the server resources
TTransportException SocketException Broken pipe
00:30:07.590 [Druid-ConnectionPool-Destroy-1262024801] ERROR com.alibaba.druid.util.JdbcUtils - close connection error
java.sql.SQLException: Error while cleaning up the server resources
at org.apache.hive.jdbc.HiveConnection.close(HiveConnection.java:640)
at com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:85)
at com.alibaba.druid.pool.DruidDataSource.shrink(DruidDataSource.java:3194)
at com.alibaba.druid.pool.DruidDataSource$DestroyTask.run(DruidDataSource.java:2938)
at com.alibaba.druid.pool.DruidDataSource$DestroyConnectionThread.run(DruidDataSource.java:2922)
Caused by: org.apache.thrift.transport.TTransportException: java.net.SocketException: Broken pipe
at org.apache.thrift.transport.TIOStreamTransport.flush(TIOStreamTransport.java:161)
at org.apache.thrift.transport.TSaslTransport.flush(TSaslTransport.java:501)
at org.apache.thrift.transport.TSaslClientTransport.flush(TSaslClientTransport.java:37)
at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:73)
at org.apache.thrift.TServiceClient.sendBase(TServiceClient.java:62)
at org.apache.hive.service.cli.thrift.TCLIService$Client.CloseSession(TCLIService.java:165)
at org.apache.hive.jdbc.HiveConnection.close(HiveConnection.java:638)
Caused by: java.net.SocketException: Broken pipe
at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:109)
at java.net.SocketOutputStream.write(SocketOutputStream.java:153)
at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82)
at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140)
at org.apache.thrift.transport.TIOStreamTransport.flush(TIOStreamTransport.java:159)
statment语句执行一个sql时，在指定的时间内没有获取到结果，然后报超时异常后，紧接着去关闭connection 连接，而导致的异常。
一个可供参考的解决方案：
Statement stat = con.createStatement();
stat.setQueryTimeout(1800);
注意使用的hive-jdbc版本必须是2+。
但是代码改动上线之后，这个问题依旧存在。
TTransportException: null
基于上面的问题和解决方案，但是线上的任务执行一段时间后，再次出现另外一个报错：
com.alibaba.druid.util.JdbcUtils - close connection error
java.sql.SQLException: Error while cleaning up the server resources
at org.apache.hive.jdbc.HiveConnection.close(HiveConnection.java:730)
at com.alibaba.druid.util.JdbcUtils.close(JdbcUtils.java:85)
at com.alibaba.druid.pool.DruidDataSource.shrink(DruidDataSource.java:3194)
at com.alibaba.druid.pool.DruidDataSource$DestroyTask.run(DruidDataSource.java:2938)
at com.alibaba.druid.pool.DruidDataSource$DestroyConnectionThread.run(DruidDataSource.java:2922)
Caused by: org.apache.thrift.transport.TTransportException: null
at org.apache.thrift.transport.TIOStreamTransport.read(TIOStreamTransport.java:132)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.transport.TSaslTransport.readLength(TSaslTransport.java:376)
at org.apache.thrift.transport.TSaslTransport.readFrame(TSaslTransport.java:453)
at org.apache.thrift.transport.TSaslTransport.read(TSaslTransport.java:435)
at org.apache.thrift.transport.TSaslClientTransport.read(TSaslClientTransport.java:37)
at org.apache.thrift.transport.TTransport.readAll(TTransport.java:86)
at org.apache.thrift.protocol.TBinaryProtocol.readAll(TBinaryProtocol.java:429)
at org.apache.thrift.protocol.TBinaryProtocol.readI32(TBinaryProtocol.java:318)
at org.apache.thrift.protocol.TBinaryProtocol.readMessageBegin(TBinaryProtocol.java:219)
at org.apache.thrift.TServiceClient.receiveBase(TServiceClient.java:77)
at org.apache.hive.jdbc.HiveConnection.close(HiveConnection.java:728)
SQLException: Invalid columnIndex: 2
报错信息如下
java.sql.SQLException: Invalid columnIndex: 2
at org.apache.hive.jdbc.HiveBaseResultSet.getColumnValue(HiveBaseResultSet.java:418)
at org.apache.hive.jdbc.HiveBaseResultSet.getObject(HiveBaseResultSet.java:463)
at com.alibaba.druid.pool.DruidPooledResultSet.getObject(DruidPooledResultSet.java:438)
while (rs.next()) {
for (int i = 1; i <= size; i++) {
resultList.add(rs.getObject(i));
}
}
while (rs.next()) {
for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
resultList.add(rs.getObject(i));
}
}
The query did not generate a result set!
报错信息如下：
java.sql.SQLException: The query did not generate a result set!
at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:376)
at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:308)
报错代码行：
rs = ps.executeQuery(item);
分析：executeQuery()方法支持的是增删改查等DML语句，对于DDL需调用execute()来执行。
No such file or directory
报错信息如下：
java.sql.SQLException: Disk I/O error: Failed to open HDFS file hdfs://ppdhdpha/user/hive/warehouse/cszc.db/cs_phone_multi_call_pool_single/904924957a18ee1f-170b897c00000005_129981828_data.0.parq
Error(2): No such file or directory
Root cause: RemoteException: File does not exist: /user/hive/warehouse/cszc.db/cs_phone_multi_call_pool_single/904924957a18ee1f-170b897c00000005_129981828_data.0.parq
at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:85)
at org.apache.hadoop.hdfs.server.namenode.INodeFile.valueOf(INodeFile.java:75)
at org.apache.hadoop.hdfs.server.namenode.FSDirStatAndListingOp.getBlockLocations(FSDirStatAndListingOp.java:152)
at org.apache.hadoop.hdfs.server.namenode.FSNamesystem.getBlockLocations(FSNamesystem.java:1909)
at org.apache.hadoop.hdfs.server.namenode.NameNodeRpcServer.getBlockLocations(NameNodeRpcServer.java:735)
at org.apache.hadoop.hdfs.protocolPB.ClientNamenodeProtocolServerSideTranslatorPB.getBlockLocations(ClientNamenodeProtocolServerSideTranslatorPB.java:415)
at org.apache.hadoop.hdfs.protocol.proto.ClientNamenodeProtocolProtos$ClientNamenodeProtocol$2.callBlockingMethod(ClientNamenodeProtocolProtos.java)
at org.apache.hadoop.ipc.ProtobufRpcEngine$Server$ProtoBufRpcInvoker.call(ProtobufRpcEngine.java:523)
at org.apache.hadoop.ipc.RPC$Server.call(RPC.java:991)
at org.apache.hadoop.ipc.Server$RpcCall.run(Server.java:869)
at org.apache.hadoop.ipc.Server$RpcCall.run(Server.java:815)
at java.security.AccessController.doPrivileged(Native Method)
at javax.security.auth.Subject.doAs(Subject.java:422)
at org.apache.hadoop.security.UserGroupInformation.doAs(UserGroupInformation.java:1875)
at org.apache.hadoop.ipc.Server$Handler.run(Server.java:2675)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:279)
at org.apache.hive.jdbc.HiveStatement.executeQuery(HiveStatement.java:375)
at com.alibaba.druid.pool.DruidPooledStatement.executeQuery(DruidPooledStatement.java:308)
Disk I/O error: Failed to open HDFS file
参考
Error while cleaning up the server resources
hive-query-cant-generate-result-set-via-jdbc
query-did-not-generate-a-resultset
Hive SQLException: Method not supported问题
