Hive超详细元数据配置到MySql

MySql安装

1. 查看mysql是否安装，如果安装了，卸载mysql

查看
[root@hadoop102 桌面]# rpm -qa|grep mysql
mysql-libs-5.1.73-7.el6.x86_64
卸载
[root@hadoop102 桌面]# rpm -e --nodeps mysql-libs-5.1.73-7.el6.x86_64

安装mysql服务端

1. 安装mysql服务端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-server-5.6.24-1.el6.x86_64.rpm

2. 查看产生的随机密码

[root@hadoop102 mysql-libs]# cat /root/.mysql_secret

3. 查看mysql状态

[root@hadoop102 mysql-libs]# service mysql status

4. 启动mysql

[root@hadoop102 mysql-libs]# service mysql start

安装MySql客户端

1. 安装mysql客户端

[root@hadoop102 mysql-libs]# rpm -ivh MySQL-client-5.6.24-1.el6.x86_64.rpm

2. 链接mysql

[root@hadoop102 mysql-libs]# mysql -uroot -pOEXaQuS8IWkG19Xs

3. 修改密码

mysql>SET PASSWORD=PASSWORD('000000');

4. 退出mysql

mysql>exit

MySql中user表中主机配置

配置只要是root用户+密码，在任何主机上都能登录MySQL数据库

1. 进入mysql

[root@hadoop102 mysql-libs]# mysql -uroot -p000000

2. 显示数据库

mysql>show databases;

3. 使用mysql数据库

mysql>use mysql;

4. 展示mysql数据库中的所有表

mysql>show tables;

5. 展示user表的结构

mysql>desc user;

6. 查询user表

mysql>select User, Host, Password from user;

7. 修改user表，把Host表内容修改为%

mysql>update user set host='%' where host='localhost';

8. 删除root用户的其他host

mysql>delete from user where Host='hadoop102';
mysql>delete from user where Host='127.0.0.1';
mysql>delete from user where Host='::1';

9. 刷新

mysql>flush privileges;

10. 退出

mysql>quit;

Hive元数据配置到MySql

驱动拷贝

拷贝mysql-connector-java-5.1.27-bin.jar到/opt/module/hive/lib/

[root@hadoop102 mysql-connector-java-5.1.27]# cp mysql-connector-java-5.1.27-bin.jar
 /opt/module/hive/lib/

配置Metastore到MySql

1. 在/opt/module/hive/conf目录下创建一个hive-site.xml

[liujh@hadoop102 conf]$ touch hive-site.xml
[liujh@hadoop102 conf]$ vi hive-site.xml

2. 根据官方文档配置参数，拷贝数据到hive-site.xml文件中
   https://cwiki.apache.org/confluence/display/Hive/AdminManual+MetastoreAdmin

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
 <property>
 <name>javax.jdo.option.ConnectionURL</name>
 <value>jdbc:mysql://hadoop102:3306/metastore?createDatabaseIfNotExist=true</value>
 <description>JDBC connect string for a JDBC metastore</description>
 </property>
 <property>
 <name>javax.jdo.option.ConnectionDriverName</name>
 <value>com.mysql.jdbc.Driver</value>
 <description>Driver class name for a JDBC metastore</description>
 </property>
 <property>
 <name>javax.jdo.option.ConnectionUserName</name>
 <value>root</value>
 <description>username to use against metastore database</description>
 </property>
 <property>
 <name>javax.jdo.option.ConnectionPassword</name>
 <value>000000</value>
 <description>password to use against metastore database</description>
 </property>
</configuration>

3. 配置完毕后，如果启动hive异常，可以重新启动虚拟机。（重启后，别忘了启动hadoop集群）

多窗口启动Hive测试

1. 先启动MySQL

[liujh@hadoop102 mysql-libs]$ mysql -uroot -p000000

查看有几个数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql             |
| performance_schema |
| test               |
+--------------------+

2. 再次打开多个窗口，分别启动hive

[liujh@hadoop102 hive]$ bin/hive

3. 启动hive后，回到MySQL窗口查看数据库，显示增加了metastore数据库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| metastore          |
| mysql             |
| performance_schema |
| test               |
+--------------------+

HiveJDBC访问

启动hiveserver2服务

[liujh@hadoop102 hive]$ bin/hiveserver2

启动beeline

[liujh@hadoop102 hive]$ bin/beeline
Beeline version 1.2.1 by Apache Hive
beeline>

连接hiveserver2

beeline> !connect jdbc:hive2://hadoop102:10000（回车）
Connecting to jdbc:hive2://hadoop102:10000
Enter username for jdbc:hive2://hadoop102:10000: liujh（回车）
Enter password for jdbc:hive2://hadoop102:10000: （直接回车）
Connected to: Apache Hive (version 1.2.1)
Driver: Hive JDBC (version 1.2.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://hadoop102:10000> show databases;
+----------------+--+
| database_name  |
+----------------+--+
| default        |
| hive_db2       |
+----------------+--+

简书：https://www.jianshu.com/u/0278602aea1d
CSDN：https://blog.csdn.net/u012387141