hive to mysql_Hive And MySQL安装及设置

安装MySQL服务端

[root@centos local]# rpm -i MySQL-server-5.5.31-2.el6.i686.rpm

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !

To do so, start the server, then issue the following commands:

/usr/bin/mysqladmin -u root password 'new-password'

/usr/bin/mysqladmin -u root -h centos password 'new-password'

Alternatively you can run:

/usr/bin/mysql_secure_installation

which will also give you the option of removing the test

databases and anonymous user created by default. This is

strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

启动MySQL

[root@centos local]# mysqld_safe &

设置MySQL密码

[root@centos local]# mysql_secure_installation

Set root password? [Y/n] Y

Remove anonymous users? [Y/n] n

Disallow root login remotely? [Y/n] n

Remove test database and access to it? [Y/n] n

Reload privilege tables now? [Y/n] Y

安装MySQL客户端

[root@centos local]# rpm -i MySQL-client-5.5.31-2.el6.i686.rpm

登录

[root@centos local]# mysql

ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)

[root@centos local]# mysql -uroot -proot

使用MySQL作为Hive的metastroe

把mysql的jdbc驱动放置到hive的lib目录下

[root@centos local]# cp mysql-connector-java-5.1.10.jar /usr/local/hive-0.9.0/lib/

修改hive-site.xml文件

javax.jdo.option.ConnectionURL

jdbc:mysql://centos:3306/hive?createDatabaseIfNotExist=true

javax.jdo.option.ConnectionDriverName

com.mysql.jdbc.Driver

javax.jdo.option.ConnectionUserName

root

javax.jdo.option.ConnectionPassword

root

让MySQL的root用户允许远程连接访问

mysql> grant all on hive.* to 'root'@'%' identified by 'root';

Query OK, 0 rows affected (0.15 sec)

mysql> flush privileges;

Query OK, 0 rows affected (0.00 sec)

在MySQL查看hive所创建的表

mysql> show databases;

+--------------------+

| Database |

+--------------------+

| information_schema |

| hive |

| mysql |

| performance_schema |

| test |

+--------------------+

5 rows in set (0.02 sec)

mysql> use hive

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> show tables;

+-----------------+

| Tables_in_hive |

+-----------------+

| BUCKETING_COLS |

| CDS |

| COLUMNS_V2 |

| DATABASE_PARAMS |

| DBS |

| PARTITION_KEYS |

| SDS |

| SD_PARAMS |

| SEQUENCE_TABLE |

| SERDES |

| SERDE_PARAMS |

| SORT_COLS |

| TABLE_PARAMS |

| TBLS |

+-----------------+

14 rows in set (0.00 sec)

=========================================================================

1、检查Linux上是否安装mysql依赖的包

rpm -qa | grep mysql

rpm -e mysql-libs-5.1.66-2.el6_3.i686 --nodeps

rpm -ivh MySQL-server-5.1.73-1.glibc23.i386.rpm

rpm -ivh MySQL-client-5.1.73-1.glibc23.i386.rpm

2、启动，配置MySQL

service mysql start

/usr/bin/mysql_secure_installation

3、修改hive配置文件

cp hive-default.xml.template hive-site.xml

配置内容

javax.jdo.option.ConnectionURL

jdbc:mysql://centos3:3306/hive?createDatabaseIfNotExist=true

JDBC connect string for a JDBC metastore

javax.jdo.option.ConnectionDriverName

com.mysql.jdbc.Driver

Driver class name for a JDBC metastore

javax.jdo.option.ConnectionUserName

root

username to use against metastore database

javax.jdo.option.ConnectionPassword

123

password to use against metastore database

4、msyql连接jar copy到hive/bin中

5、在mysql上授权允许远程访问

#(执行下面的语句 *.*:所有库下的所有表 %：任何IP地址或主机都可以连接)

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

FLUSH PRIVILEGES;

#数据库在hdfs中的存储路径

select * from SDS;

#hive中表

select * from TBLS;

#表中的字段

select * from COLUMNS_V2;