Part1-3：开发环境搭建(3)----Hive

 环境配置:

//vi hive-env.sh

HADOOP_HOME=/home/hadoop001/app/hadoop-2.6.0-cdh5.7.0

//vi hive-site.xml

<configuration>
<property>
　　<name>javax.jdo.option.ConnectionURL</name>
　　<value>jdbc:mysql://192.168.6.188:3306/hive?characterEncoding=UTF-8</value>
</property>
<property>
　　<name>javax.jdo.option.ConnectionDriverName</name>
　　<value>com.mysql.jdbc.Driver</value>
</property>
<property>
　　<name>javax.jdo.option.ConnectionUserName</name>
　　<value>root</value>
</property>
<property>
　　<name>javax.jdo.option.ConnectionPassword</name>
　　<value>root</value>
</property>
</configuration>

//启用hive(bin目录下)

./hive

启动成功后，创建表 报如下错误：

Error, return code 1 from org.apache.Hadoop.hive.ql.exec.DDLTask. MetaException

退出hive,开发hive debug模式

//打开hive DEBUG日志

hive -hiveconf hive.root.logger=DEBUG,console(报以下错误)

//-----------------------------------------------------------------------------------------------------------------------------------------------------

错误集：包含InvalidObjectException(message:All is already granted by admin)

Logging initialized using configuration in jar:file:/home/hadoop001/app/hive-1.1.0-cdh5.7.0/lib/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties
18/04/08 17:13:30 [main]: INFO SessionState:
Logging initialized using configuration in jar:file:/home/hadoop001/app/hive-1.1.0-cdh5.7.0/lib/hive-common-1.1.0-cdh5.7.0.jar!/hive-log4j.properties
18/04/08 17:13:30 [main]: DEBUG conf.VariableSubstitution: Substitution is on: hive
18/04/08 17:13:30 [main]: DEBUG lib.MutableMetricsFactory: field org.apache.hadoop.metrics2.lib.MutableRate org.apache.hadoop.security.UserGroupInformation$UgiMetrics.loginSuccess with annotation @org.apache.hadoop.metrics2.annotation.Metric(about=, always=false, sampleName=Ops, type=DEFAULT, valueName=Time, value=[Rate of successful kerberos logins and latency (milliseconds)])
18/04/08 17:13:30 [main]: DEBUG lib.MutableMetricsFactory: field org.apache.hadoop.metrics2.lib.MutableRate org.apache.hadoop.security.UserGroupInformation$UgiMetrics.loginFailure with annotation @org.apache.hadoop.metrics2.annotation.Metric(about=, always=false, sampleName=Ops, type=DEFAULT, valueName=Time, value=[Rate of failed kerberos logins and latency (milliseconds)])
18/04/08 17:13:30 [main]: DEBUG lib.MutableMetricsFactory: field org.apache.hadoop.metrics2.lib.MutableRate org.apache.hadoop.security.UserGroupInformation$UgiMetrics.getGroups with annotation @org.apache.hadoop.metrics2.annotation.Metric(about=, always=false, sampleName=Ops, type=DEFAULT, valueName=Time, value=[GetGroups])
18/04/08 17:13:30 [main]: DEBUG impl.MetricsSystemImpl: UgiMetrics, User and group related metrics
18/04/08 17:13:30 [main]: DEBUG util.KerberosName: Kerberos krb5 configuration not found, setting default realm to empty
18/04/08 17:13:30 [main]: DEBUG security.Groups: Creating new Groups object
18/04/08 17:13:30 [main]: DEBUG util.NativeCodeLoader: Trying to load the custom-built native-hadoop library...

.......................................

18/04/08 17:13:30 [main]: INFO metastore.ObjectStore: ObjectStore, initialize called
18/04/08 17:13:31 [main]: DEBUG bonecp.BoneCPDataSource: JDBC URL = jdbc:mysql://218.108.6.188:3306/hive?createDatabaseIfNotExist=true, Username = task, partitions = 1, max (per partition) = 10, min (per partition) = 0, idle max age = 60 min, idle test period = 240 min, strategy = DEFAULT
18/04/08 17:13:31 [main]: INFO metastore.ObjectStore: Setting MetaStore object pin classes with hive.metastore.cache.pinobjtypes="Table,StorageDescriptor,SerDeInfo,Partition,Database,Type,FieldSchema,Order"
18/04/08 17:13:32 [main]: DEBUG bonecp.BoneCPDataSource: JDBC URL = jdbc:mysql://218.108.6.188:3306/hive?createDatabaseIfNotExist=true, Username = task, partitions = 1, max (per partition) = 10, min (per partition) = 0, idle max age = 60 min, idle test period = 240 min, strategy = DEFAULT
18/04/08 17:13:33 [main]: ERROR DataNucleus.Datastore: An exception was thrown while adding/validating class(es) : Specified key was too long; max key length is 767 bytes
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Specified key was too long; max key length is 767 bytes
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 com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2758)
at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:894)
at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:626)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
at org.apache.hadoop.util.RunJar.main(RunJar.java:136)

18/04/08 17:13:33 [main]: DEBUG metastore.MetaStoreDirectSql: Direct SQL query in 0.957176ms + 0.063289ms, the query is [SET @@session.sql_mode=ANSI_QUOTES]
18/04/08 17:13:33 [main]: INFO metastore.MetaStoreDirectSql: Using direct SQL, underlying DB is MYSQL
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: RawStore: org.apache.hadoop.hive.metastore.ObjectStore@759fad4, with PersistenceManager: org.datanucleus.api.jdo.JDOPersistenceManager@76884e4b created in the thread with id: 1
18/04/08 17:13:33 [main]: INFO metastore.ObjectStore: Initialized ObjectStore
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Open transaction: count = 1, isActive = true at:
org.apache.hadoop.hive.metastore.ObjectStore.getMSchemaVersion(ObjectStore.java:7098)
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Commit transaction: count = 0, isactive true at:
org.apache.hadoop.hive.metastore.ObjectStore.getMSchemaVersion(ObjectStore.java:7111)
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Found expected HMS version of 1.1.0
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Open transaction: count = 1, isActive = true at:
org.apache.hadoop.hive.metastore.ObjectStore$GetHelper.start(ObjectStore.java:2611)
18/04/08 17:13:33 [main]: DEBUG metastore.MetaStoreDirectSql: Direct SQL query in 1.061361ms + 0.019434ms, the query is [SET @@session.sql_mode=ANSI_QUOTES]
18/04/08 17:13:33 [main]: DEBUG metastore.MetaStoreDirectSql: getDatabase: directsql returning db default locn[hdfs://192.168.186.129:8020/user/hive/warehouse] desc [Default Hive database] owner [public] ownertype [ROLE]
org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3439)
18/04/08 17:13:33 [main]: DEBUG metastore.HiveMetaStore: admin role already exists
InvalidObjectException(message:Role admin already exists.)
at org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3430)
................
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Open transaction: count = 2, isActive = true at:
org.apache.hadoop.hive.metastore.ObjectStore.getMRole(ObjectStore.java:3746)
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Commit transaction: count = 1, isactive true at:
org.apache.hadoop.hive.metastore.ObjectStore.getMRole(ObjectStore.java:3752)
18/04/08 17:13:33 [main]: DEBUG metastore.ObjectStore: Rollback transaction, isActive: true at:
org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3439)
18/04/08 17:13:33 [main]: DEBUG metastore.HiveMetaStore: public role already exists
InvalidObjectException(message:Role public already exists.)
at org.apache.hadoop.hive.metastore.ObjectStore.addRole(ObjectStore.java:3430)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
............................
org.apache.hadoop.hive.metastore.ObjectStore.grantPrivileges(ObjectStore.java:4341)
18/04/08 17:13:33 [main]: DEBUG metastore.HiveMetaStore: Failed while granting global privs to admin
InvalidObjectException(message:All is already granted by admin)
at org.apache.hadoop.hive.metastore.ObjectStore.grantPrivileges(ObjectStore.java:4174)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.hadoop.hive.metastore.RawStoreProxy.invoke(RawStoreProxy.java:114)
at com.sun.proxy.$Proxy6.grantPrivileges(Unknown Source)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles_core(HiveMetaStore.java:728)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.createDefaultRoles(HiveMetaStore.java:694)
at org.apache.hadoop.hive.metastore.HiveMetaStore$HMSHandler.init(HiveMetaStore.java:479)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.<init>(RetryingHMSHandler.java:78)
at org.apache.hadoop.hive.metastore.RetryingHMSHandler.getProxy(RetryingHMSHandler.java:84)
at org.apache.hadoop.hive.metastore.HiveMetaStore.newRetryingHMSHandler(HiveMetaStore.java:5891)
at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:198)
at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:74)
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)
hive> 18/04/08 17:13:44 [IPC Client (1584833211) connection to hadoop001/192.168.3.210:8020 from hadoop001]: DEBUG ipc.Client: IPC Client (1584833211) connection to hadoop001/192.168.3.210:8020 from hadoop001: closed
18/04/08 17:13:44 [IPC Client (1584833211) connection to hadoop001/192.168.3.210:8020 from hadoop001]: DEBUG ipc.Client: IPC Client (1584833211) connection to hadoop001/192.168.3.210:8020 from hadoop001: stopped, remaining connections 0

//解决方案

mysql>SET GLOBAL binlog_format = 'ROW';

mysql>COMMIT;