Hive配置问题

老师好,配置hive-site.xml的时候,连接的用户名和密码到底指代什么?

javax.jdo.option.ConnectionUserName
root
Username to use against metastore database


javax.jdo.option.ConnectionPassword
zhudejunsql
password to use against metastore database


这里面的设置是指我在mysql -u root -p中的用户名和密码吗?还是说进入
mysql>
这里面的create database hive后,针对hive设置的用户名和密码?
已邀请:

wangxiaolei

赞同来自: zhudejun_1985

将MySQL驱动包拷贝到Hive 的lib目录中:
cp  mysql-connector-java-5.1.25-bin.jar   $HIVE_HOME/lib/
只需要这一个步骤。

jline-2.12.jar这个是是因为版本问题造成的,之前训练营的其他学员也遇到过。

wangxiaolei

赞同来自: zhudejun_1985

配置文件的用户名和密码就是mysql -u root -p中的用户名和密码

wangxiaolei

赞同来自: zhudejun_1985

DX2提供的有,自己去下载
http://DX2/software/

wangxiaolei

赞同来自: zhudejun_1985

也太粗心了,怎么会把用户设置成root@zhumac1
自己查一下吧
+--------------+-------------------------------------------+-----------+
| User         | Password                                  | Host      |
+--------------+-------------------------------------------+-----------+
| root         | *0E688F6C93302C8994E4A7D612A951F41865C406 | localhost |
| root         | *0E688F6C93302C8994E4A7D612A951F41865C406 | 127.0.0.1 |
| root@zhumac1 | *0E688F6C93302C8994E4A7D612A951F41865C406 | %         |
很明显的错误。

再发一遍命令,
GRANT ALL PRIVILEGES ON *.* TO 'root'@'zhumac1' IDENTIFIED BY 'zhudejunsql' WITH GRANT OPTION;
FLUSH PRIVILEGES;
 

zhudejun_1985 - 天下大事,必作于细

老师,还有个问题,我查资料,为什么还有这两个步骤?
将MySQL驱动包拷贝到Hive 的lib目录中:
cp  mysql-connector-java-5.1.25-bin.jar   $HIVE_HOME/lib/
 
将Hive jline包拷贝到Hadoop的 yarn lib 目录中:
cp  $HIVE_HOME/lib/jline-2.12.jar  $HADOOP_HOME/share/hadoop/yarn/lib/
 
请问这两个步骤什么意思?必须要做吗?如果是的话,这两个jar包我怎么得到?谢谢。

zhudejun_1985 - 天下大事,必作于细

雷雷,请问这个驱动包在哪儿?具体路径呢?cp  mysql-connector-java-5.1.25-bin.jar   $HIVE_HOME/lib/
 
mysql-connector-java-5.1.25-bin.jar这个包的位置?或者我需要单独下载?

zhudejun_1985 - 天下大事,必作于细

NestedThrowables:
java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://zhumac1:3306/hive?characterEncoding=UTF-8, username = root. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
java.sql.SQLException: null,  message from server: "Host 'zhumac1' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1037)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
    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:377)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:395)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:325)
    at java.sql.DriverManager.getConnection(DriverManager.java:664)
    at java.sql.DriverManager.getConnection(DriverManager.java:208)
    at com.jolbox.bonecp.BoneCP.obtainRawInternalConnection(BoneCP.java:361)
    at com.jolbox.bonecp.BoneCP.(BoneCP.java:416)
    at com.jolbox.bonecp.BoneCPDataSource.getConnection(BoneCPDataSource.java:120)
    at org.datanucleus.store.rdbms.ConnectionFactoryImpl$ManagedConnectionImpl.getConnection(ConnectionFactoryImpl.java:483)
    at org.datanucleus.store.rdbms.RDBMSStoreManager.(RDBMSStoreManager.java:296)
    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.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:606)
    at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:301)
    at org.datanucleus.NucleusContextHelper.createStoreManagerForProperties(NucleusContextHelper.java:133)
    at org.datanucleus.PersistenceNucleusContextImpl.initialise(PersistenceNucleusContextImpl.java:420)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:821)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.createPersistenceManagerFactory(JDOPersistenceManagerFactory.java:338)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.getPersistenceManagerFactory(JDOPersistenceManagerFactory.java:217)
    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 javax.jdo.JDOHelper$16.run(JDOHelper.java:1965)
    at java.security.AccessController.doPrivileged(Native Method)
    at javax.jdo.JDOHelper.invoke(JDOHelper.java:1960)
    at javax.jdo.JDOHelper.invokeGetPersistenceManagerFactoryOnImplementation(JDOHelper.java:1166)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:808)
    at javax.jdo.JDOHelper.getPersistenceManagerFactory(JDOHelper.java:701)
    at org.apache.hadoop.hive.metastore.ObjectStore.getPMF(ObjectStore.java:397)
    at org.apache.hadoop.hive.metastore.ObjectStore.getPersistenceManager(ObjectStore.java:426)
    at org.apache.hadoop.hive.metastore.ObjectStore.initialize(ObjectStore.java:320)
Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://zhumac1:3306/hive?characterEncoding=UTF-8, username = root. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
java.sql.SQLException: null,  message from server: "Host 'zhumac1' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1037)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at org.datanucleus.plugin.NonManagedPluginRegistry.createExecutableExtension(NonManagedPluginRegistry.java:606)
    at org.datanucleus.plugin.PluginManager.createExecutableExtension(PluginManager.java:301)
    at org.datanucleus.NucleusContextHelper.createStoreManagerForProperties(NucleusContextHelper.java:133)
    at org.datanucleus.PersistenceNucleusContextImpl.initialise(PersistenceNucleusContextImpl.java:420)
    at org.datanucleus.api.jdo.JDOPersistenceManagerFactory.freezeConfiguration(JDOPersistenceManagerFactory.java:821)
    ... 49 more
Caused by: java.sql.SQLException: null,  message from server: "Host 'zhumac1' is not allowed to connect to this MySQL server"
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:935)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:924)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1037)
    at com.mysql.jdbc.ConnectionImpl.coreConnect(ConnectionImpl.java:2234)
    at com.mysql.jdbc.ConnectionImpl.connectOneTryOnly(ConnectionImpl.java:2265)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2064)
    at com.mysql.jdbc.ConnectionImpl.(ConnectionImpl.java:790)
    at com.mysql.jdbc.JDBC4Connection.(JDBC4Connection.java:44)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
配置完成后,启动hive直接报这个错误,为何连接不上mysql呢?
1、hive和mysql装在那台机器上?
2、检查下zhumac1对应的IP是否是mysql安装的那台机器?
3、给root用户赋远程访问的权限没有?
 

zhudejun_1985 - 天下大事,必作于细

1,装在zhumac1上面  2, 就是在主机1上面安装的mysql
3,远程访问这个没有特意去设置,这个也需要?

zhudejun_1985 - 天下大事,必作于细

mysql> select User,Password,Host from user;
+------+-------------------------------------------+-----------+
| User | Password                                  | Host      |
+------+-------------------------------------------+-----------+
| root | *0E688F6C93302C8994E4A7D612A951F41865C406 | localhost |
| root | *0E688F6C93302C8994E4A7D612A951F41865C406 | 127.0.0.1 |
+------+-------------------------------------------+-----------+
2 rows in set (0.00 sec)

mysql> 
请问雷雷如何让我在zhumac1上可以访问这个mysql呢?顺利的启动hive
如果mysql和hive都安装在zhumac1上访问就需要用root用户进入mysql,然后执行下面的命令:
1、赋予权限:GRANT ALL PRIVILEGES ON *.* TO 'root'@'zhumac1' IDENTIFIED BY '这里写root用户的密码' WITH GRANT OPTION;
2、刷新权限:FLUSH PRIVILEGES;
 

zhudejun_1985 - 天下大事,必作于细

上述步骤都执行完了,还是报连接被拒绝,最后的那句提示using password: YES,很疑惑,为什么呢?
 Caused by: java.sql.SQLException: Unable to open a test connection to the given database. JDBC url = jdbc:mysql://zhumac1:3306/hive?characterEncoding=UTF-8, username = root. Terminating connection pool (set lazyInit to true if you expect to start your database after your app). Original Exception: ------
java.sql.SQLException: Access denied for user 'root'@'zhumac1' (using password: YES)
hive-site.xml文件在那个目录下,怎么配置的?

zhudejun_1985 - 天下大事,必作于细

/root/hive-2.0.1/conf/hive-site.xml,除了课堂上的几个修改配置,我在参考书上的修改了几个地方,hive.exec.local.scratchdir , hive.downloaded.resources.dir , hive.querylog.location , hive.server2.logging.operation.log.location,但是这几个都没什么影响吧?和连接拒绝没什么直接关系。

zhudejun_1985 - 天下大事,必作于细

启动成功了,但是有这个提示,未来跑mapreduce真的会有问题吗?
 Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases.
hive> 
提示你用Hive1.X版本

zhudejun_1985 - 天下大事,必作于细

刚才换成了hive-1.2.1版本,然后讲上面两个步骤都执行了
1:将MySQL驱动包拷贝到Hive 的lib目录中:
cp  mysql-connector-java-5.1.25-bin.jar   $HIVE_HOME/lib/

 2:将Hive jline包拷贝到Hadoop的 yarn lib 目录中:
cp  $HIVE_HOME/lib/jline-2.12.jar  $HADOOP_HOME/share/hadoop/yarn/lib/
 
也为root授权了,最后还是报这个错误,是因为刚才hive-2.0.1版本的冲突吗?如何解决呢?
# ./schematool -initSchema -dbType mysql
Metastore connection URL:     jdbc:mysql://zhumac1:3306/hive?characterEncoding=UTF-8
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     root
Starting metastore schema initialization to 1.2.0
Initialization script hive-schema-1.2.0.mysql.sql
Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
*** schemaTool failed ***
 
hive-1.2.1的版本,为什么要拷贝这个jline-2.12.jar包。版本都不一样,相差太大
 
对不熟悉的版本兼容,最好的做法就是hadoop集群是cdh版本的,hive也要用相应的版本。
没有必要学hive搭建,随便选个apache hive的版本。
出现问题了,比较浪费时间。

cdh的版本最大的好处就是做了兼容性测试,而cdh版本的源代码就是apache版本的。
这个问题在开始讲课的时候,就在强调了。没有必要一定要用apache的,兼容这方面做的不好。

要回复问题请先登录注册