阿里云搭建大数据平台（6）：Hive和mysql安装和配置

一、环境

1.CentOS7.4

2.hive-1.1.0-cdh5.15.0

3.mysql5.7

二、Hive安装和配置

1.解压：

tar -zxvf /opt/softwares/hive-1.1.0-cdh5.15.0.tar.gz -C /opt/modules/

2.配置环境：

   修改名称：mv hive-env.sh.template hive-env.sh
   修改：

    # Set HADOOP_HOME to point to a specific hadoop install directory
    HADOOP_HOME=/opt/modules/hadoop-2.6.0-cdh5.15.0

    # Hive Configuration Directory can be controlled by:
    export HIVE_CONF_DIR=/opt/modules/hive-1.1.0-cdh5.15.0/conf

3.修改配置参数

hive-default.xml.template  修改名称为 hive-site.xml

备注：这个在hive-1.1.0-cdh5.15.0/conf里面是没有的，自己从其他版本拷贝过来的。配置hive仓库位置：hive.metastore.warehouse.dir，即为在hdfs上的目录。

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?><!--
   Licensed to the Apache Software Foundation (ASF) under one or more
   contributor license agreements.  See the NOTICE file distributed with
   this work for additional information regarding copyright ownership.
   The ASF licenses this file to You under the Apache License, Version 2.0
   (the "License"); you may not use this file except in compliance with
   the License.  You may obtain a copy of the License at

       http://www.apache.org/licenses/LICENSE-2.0

   Unless required by applicable law or agreed to in writing, software
   distributed under the License is distributed on an "AS IS" BASIS,
   WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
   See the License for the specific language governing permissions and
   limitations under the License.
--><configuration>
	<property>
		<name>hive.metastore.warehouse.dir</name>
		<value>/user/hive/warehouse</value>
		<description>location of default database for the warehouse</description>
	</property>

	
</configuration>

4.在hadoop上创建目录：

   启动hive之前一定要启动hadoop（hdfs、yarn）
   如果说tmp目录已经存在，就不需要执行创建了

bin/hdfs dfs -mkdir       /tmp
bin/hdfs dfs -mkdir -p  /user/hive/warehouse
bin/hdfs dfs -chmod g+w   /tmp
bin/hdfs dfs -chmod g+w   /user/hive/warehouse

5.启动hive shell

  (1)启动namenode,datanode
  (2)启动hive

bin/hive

6.查看有哪些数据库：show databases;

   创建数据库：create database hadoop;
   查看表：show tables;
   使用数据库：use hadoop;

7.hive常用优化配置

（1）hive的日志

 重命名con/下的hive-log4j.properties.template为hive-log4j.properties

修改hive-log4j.properties中属性

建立目录
mkdir logs
chmod 777 logs/
修改hive-log4j.properties
hive.log.dir=/opt/modules/hive-1.1.0-cdh5.15.0/logs

（2）显示当前数据库和头部信息，修改hive-site.xml

  <property>
    <name>hive.cli.print.current.db</name>
    <value>true</value>
  </property>
  <property>
    <name>hive.cli.print.header</name>
    <value>true</value>
  </property>

三、mysql安装配置以及和hive集成

1.下载可用的rpm包

sudo rpm -ivh https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm

2.安装

sudo yum install mysql-server

3.启动

​systemctl start mysqld.service       #启动mysql服务​

其他操作：

		systemctl stop mysqld.service        #关闭mysql服务
		systemctl restart mysqld.service     #重启mysql服务
		systemctl status mysqld.service      #检查mysql服务状态

4.在日志文件中找出此时root用户临时密码：

grep "password" /var/log/mysqld.log
	结果：
	2018-11-03T10:58:01.476977Z 1 [Note] A temporary password is generated for root@localhost: _&#tBh,%P8t5

5.登陆mysql

mysql -uroot -p
输入：
_&#tBh,%P8t5

6.改密码

mysql>set password for root@localhost = password('123456');

报错：ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

原因：太简单不行

解决方法：关闭mysql密码强度验证(validate_password)
　　　第一步：编辑配置文件：

                   vim /etc/my.cnf
                   增加这么一行
                   validate_password=off

　　　第二步：编辑后重启mysql服务：

                    systemctl restart mysqld.service

7、查看对应的权限（把表清空）

mysql> select user,host from mysql.user;
mysql> delete from mysql.user where host='localhost';

8、设置用户连接

grant all privileges on *.* to 'root'@'%' identified by '123456' with grant option;
注释：
*.*：数据库.表明
%：任意的ip都可以登录
identified by '123456'：使用123456密码登录

9、刷新权限设置

mysql> flush privileges;

10、重启mysql服务

systemctl restart mysqld.service

11、修改hive配置文件conf/hive-site.xml

（1）修改默认DB数据库

<property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://hadoop:3306/metastore?createDatabaseIfNotExist=true</value>
</property>

（2）添加mysql驱动名称

<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>123456</value>
</property>

12.将mysql驱动包放到lib目录下

cp /opt/software/mysql-connector-java-5.1.27-bin.jar /opt/modules/hive-1.1.0-cdh5.15.0/lib/

13.最后执行脚本bin/hive启动hive

# bin/hive

14、简单说下mysql相关的路径信息

数据库文件存放路径：/var/lib/mysql/
mysql可执行的文件脚本路径：/usr/bin/mysql
mysql配置文件路径：/etc/my.cnf 

四、hive启动metastore服务，并且开启hiveserver2

1.配置metastore，并且开启服务，参考：
https://blog.youkuaiyun.com/u010886217/article/details/83926317

2.开启hive server2服务，参考：
https://blog.youkuaiyun.com/u010886217/article/details/83926160
 

（测试成功~）