Hive客户端多并发问题解决方法记录

问题出现场景：

如用户描述：（

 测试脚本如下:

echo " 
set hive.exec.dynamic.partition=true; 
set hive.exec.dynamic.partition.mode=nonstrict;
set hive.optimize.sort.dynamic.partition=false;
set hive.exec.max.dynamic.partitions.pernode=1000;
set hive.exec.max.dynamic.partitions=10000;
set mapred.child.java.opts=-Xmx3000m;
drop table tmp.test30;create table tmp.test30(v1 int);" | hive

     将以上脚本复制30份，修改其中的表名 test30 为 testn，n为1,2,3...）多个进程同时在操作元数据时，会报如下错误Log:

INFO 01-07 11:03:20,392 - 12 2 - (stderr) Exception in thread "main" java.lang.RuntimeException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:346)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:681)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:625)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at java.lang.reflect.Method.invoke(Method.java:597)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.util.RunJar.main(RunJar.java:156)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) Caused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.metastore.HiveMetaStoreClient
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1412)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:62)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:72)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:2453)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:2465)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:340)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) ... 7 more
INFO 01-07 11:03:20,393 - 12 2 - (stderr) Caused by: java.lang.reflect.InvocationTargetException
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
INFO 01-07 11:03:20,393 - 12 2 - (stderr) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1410)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) ... 12 more
INFO 01-07 11:03:20,394 - 12 2 - (stderr) Caused by: javax.jdo.JDODataStoreException: Exception thrown flushing changes to datastore
INFO 01-07 11:03:20,394 - 12 2 - (stderr) NestedThrowables:
INFO 01-07 11:03:20,394 - 12 2 - (stderr) java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at org.datanucleus.api.jdo.NucleusJDOHelper.getJDOExceptionForNucleusException(NucleusJDOHelper.java:451)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:165)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.ObjectStore.commitTransaction(ObjectStore.java:406)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.ObjectStore.grantPrivileges(ObjectStore.java:3877)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO 01-07 11:03:20,394 - 12 2 - (stderr) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO 01-07 11:03:20,462 - 12 2 - (stderr) at java.lang.reflect.Method.invoke(Method.java:597)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:108)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at $Proxy6.grantPrivileges(Unknown Source)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:567)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:398)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.<init>(HiveMetaStore.java:356)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:54)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:59)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.HiveMetaStore.newHMSHandler(HiveMetaStore.java:4944)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:171)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) ... 17 more
INFO 01-07 11:03:20,463 - 12 2 - (stderr) Caused by: java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:2007)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at com.mysql.jdbc.PreparedStatement.executeBatch(PreparedStatement.java:1443)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at com.jolbox.bonecp.StatementHandle.executeBatch(StatementHandle.java:424)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.rdbms.ParamLoggingPreparedStatement.executeBatch(ParamLoggingPreparedStatement.java:372)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.rdbms.SQLController.processConnectionStatement(SQLController.java:628)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.rdbms.SQLController.processStatementsForConnection(SQLController.java:596)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.rdbms.SQLController$1.transactionFlushed(SQLController.java:683)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.connection.AbstractManagedConnection.transactionFlushed(AbstractManagedConnection.java:86)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.store.connection.ConnectionManagerImpl$2.transactionFlushed(ConnectionManagerImpl.java:454)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.TransactionImpl.flush(TransactionImpl.java:203)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.TransactionImpl.commit(TransactionImpl.java:267)
INFO 01-07 11:03:20,463 - 12 2 - (stderr) at org.datanucleus.api.jdo.JDOTransaction.commit(JDOTransaction.java:98)

异常主要由这个原因引起：

 java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'–主键重复

分析：

在以上场景中，多个进程同时在删除表创建表导致metastore频繁的修改，导致datanucleus在操作mysql的catalog时产生很多的叫诸如DELETEME.TIMESTAMP的表。

这些表会被datanucleus马上删掉。同时另一边datanucleus有个线程在频繁的读取mysql所有表的所有列信息。mysql的JDBC驱动通过先查询所有的表，然后遍历所有

表的列来达到这个目的。如果在这个过程中有表被删了，mysql就会报一个异常。

然而这个异常被hive解释为“default”这个默认的数据库不存在，同时datanucleus.autoCreateScheme这个参数设为true和datanucleus.fixedStoredata设为false，所以

hive就会往metastore里面又加一条记录（对应于mysql的metastore.DBS这个表），从而引发了这个异常 java.sql.BatchUpdateException: Duplicate entry 'admin-ROLE-All-admin-ROLE' for key 'GLOBALPRIVILEGEINDEX'

解决方法：

设置datanucleus.autoCreateScheme=false:

如果设为true的话，hive会自动更新元数据，就像上面这样。

这个参数只是在初始化元数据的时候比较方便（让hive来帮助创建元数据），生产环境不用设为true。

设置：datanucleus.fixedStoredata为true，顾名思义固定住元数据不能修改。这样就不会出现上面的错误了。

转载于:https://www.cnblogs.com/hortonworks/p/6388195.html