Hive And MySQL安装及设置

 

 Hive安装

 

解压

[root@centos local]# tar -zxvf hive-0.9.0

 配置/etc/profile环境变量

export HIVE_HOME=/usr/local/hive-0.9.0
export PATH=.:$HADOOP_HOME/bin:$HBASE_HOME/bin:$JAVA_HOME/bin:$HIVE_HOME/bin:$PATH

[root@centos local]# source /etc/profile

修改hadoop中hadoop-env.sh文件

 export HADOOP_CLASSPATH==.:$CLASSPATH:$HADOOP_CLASSPATH:$HADOOP_HOME/bin

修改配置文件名称

[root@centos conf]# pwd
/usr/local/hive-0.9.0/conf
[root@centos conf]# mv hive-default.xml.template hive-site.xml
[root@centos conf]# mv hive-env.sh.template hive-env.sh

 修改hive-config.xml配置文件，暴露java、hive、hadoop

[root@centos bin]# vim hive-config.sh
[root@centos bin]# more hive-config.sh
export JAVA_HOME=/usr/local/jdk1.6.0_24
export HIVE_HOME=/usr/local/hive-0.9.0
export HADOOP_HOME=/usr/local/hadoop-1.1.2

如果需要修改HDFS默认的存储位置hive-site.xml文件中

<property>
  <name>hive.metastore.warehouse.dir</name>
  <value>/hive</value>
  <description>location of default database for the warehouse</description>
</property>

执行hvie命令进入CLI

[root@centos bin]# hive
WARNING: org.apache.hadoop.metrics.jvm.EventCounter is deprecated. Please use org.apache.hadoop.log.metrics.EventCounter in all the log4j.properties files.
Logging initialized using configuration in jar:file:/usr/local/hive-0.9.0/lib/hive-common-0.9.0.jar!/hive-log4j.properties
Hive history file=/tmp/root/hive_job_log_root_201508170333_222296555.txt
hive>

 

安装MySQL

 

删除Linux上已经安装的MySQL

 

#查看是否有mysql
[root@centos conf]# rpm -qa | grep mysql
mysql-libs-5.1.66-2.el6_3.i686
#删除时有依赖
[root@centos conf]# rpm -e mysql-libs-5.1.66-2.el6_3.i686
error: Failed dependencies:
        libmysqlclient.so.16 is needed by (installed) postfix-2:2.6.6-2.2.el6_1.i686
        libmysqlclient.so.16(libmysqlclient_16) is needed by (installed) postfix-2:2.6.6-2.2.el6_1.i686
        mysql-libs is needed by (installed) postfix-2:2.6.6-2.2.el6_1.i686
#前置删除
[root@centos conf]# rpm -e mysql-libs-5.1.66-2.el6_3.i686 --nodeps

 安装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文件

        <property>
		<name>javax.jdo.option.ConnectionURL</name>
		<value>jdbc:mysql://centos:3306/hive?createDatabaseIfNotExist=true</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>

 让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 

 配置内容

<property>
	  <name>javax.jdo.option.ConnectionURL</name>
	  <value>jdbc:mysql://centos3:3306/hive?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>123</value>
	  <description>password to use against metastore database</description>
	</property>

 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;