HIVE2.3.6部署

使用hive的前提是，在服务器已经部署好了hadoop

1.使用软件版本：

apache-hive-2.3.6-bin.tar.gz
mysql-8.0.17-linux-glibc2.12-x86_64.tar.xz
mysql-connector-java-8.0.17.jar
hadoop-2.8.5.tar.gz

2.解压

tar -xzcf apache-hive-2.3.6-bin.tar.gz -C /opt/module/
cd /opt/module/
mv apache-hive-2.3.6-bin/ hive-2.3.6-bin

3.添加环境变量

vi /etc/profile
#HIVE_HOME
HIVE_HOME=/opt/module/hive-2.3.6-bin
PATH=$PATH:$HIVE_HOME 

使环境变量生效

source /etc/profile

4.修改hive配置文件

cd /opt/module/hive-2.3.6-bin/conf
cp hive-log4j2.properties.template  hive-log4j2.propertie

cp hive-env.sh.template hive-env.sh

*补充：hive-site.xml是从模版文件复制出来的*
cp hive-default.xml.template hive-site.xml
vi hive-site.xml   #hive-site用于hive连接MySQL数据库

修改内容如下：

[root@bigdata hive-2.3.6-bin]# vi conf/hive-site.xml 
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
 <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://192.168.228.128:3306/onhive?allowPublicKeyRetrieval=true&amp;useSSL=false</value>
    <description>JDBC connect string for a JDBC metastore</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.cj.jdbc.Driver</value>
    <description>Driver class name for a JDBC metastore</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>hive</value>
    <description>Username to use against metastore database</description>
  </property>
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>hive</value>
    <description>password to use against metastore database</description>
  </property>
</configuration>

注：新版本驱动名称更为：com.mysql.cj.jdbc.Driver，低版本为com.mysql.jdbc.Driver
url中添加参数通常由&分隔，useSSL较为特殊，需要使用&amp；隔开

5.添加Java-jdbc驱动

驱动下载地址：http://mvnrepository.com/artifact/mysql/mysql-connector-java
下载文件：mysql-connector-java-8.0.17.jar
将驱动添加到：HIVE_HOME/lib

[root@bigdata software]# cp mysql-connector-java-8.0.17.jar /opt/module/hive-2.3.6-bin/lib/

6.创建MySQL用户并赋权（bigdata是主机名）

mysql> create user hive@'bigdata' identified by 'hive';
Query OK, 0 rows affected (0.15 sec)

mysql> grant all on *.* to hive@'bigdata' ;
Query OK, 0 rows affected (0.11 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> set gloable time_zone='+8:00';

7.初始化

schematool -dbType mysql -initSchema

初始化成功是这样的：

[root@bigdata hive-2.3.6-bin]# schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hive-2.3.6-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL:        jdbc:mysql://192.168.228.128:3306/onhive?
Metastore Connection Driver :    com.mysql.cj.jdbc.Driver
Metastore connection User:       hive
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.mysql.sql
Initialization script completed
schemaTool completed

初始化失败的案例：
注1：初始化时需要注意（MySQL）用户的权限问题，以下报错就是因为没有’hive’@'bigdata’用户或此用户没有权限造成的

[root@bigdata hive-2.3.6-bin]# schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hive-2.3.6-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL:        jdbc:mysql://192.168.228.128:3306/onhive
Metastore Connection Driver :    com.mysql.cj.jdbc.Driver
Metastore connection User:       hive
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user 'hive'@'bigdata' (using password: YES)
SQL Error code: 1045
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

解决：

mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| hive             | localhost |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
| root             | master    |
+------------------+-----------+
7 rows in set (0.02 sec)

mysql> create user hive@'bigdata' identified by 'hive';   #此处创建为hive@'%'应该也可以，下边对应改成对hive@'%'赋权，%表示任何主机
Query OK, 0 rows affected (0.15 sec)

mysql> grant all on *.* to hive@'bigdata' ;
Query OK, 0 rows affected (0.11 sec)

mysql> flush privileges;

注2：初始化需注意TIME_ZONE问题，即服务器本地时间与MySQL数据库时间是否一致，此版本在部署时就出现此问题
问题：

[root@bigdata hive-2.3.6-bin]# schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hive-2.3.6-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Metastore connection URL:        jdbc:mysql://192.168.228.128:3306/onhive
Metastore Connection Driver :    com.mysql.cj.jdbc.Driver
Metastore connection User:       hive
org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : The server time zone value 'EDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.
SQL Error code: 0
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

解决：

mysql> set gloable time_zone='+8:00';

8.启动

[root@bigdata hive-2.3.6-bin]# hive
which: no hbase in (/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/opt/module/jdk1.8.0_221/bin:/opt/module/hadoop-2.8.5/sbin:/opt/module/hadoop-2.8.5/bin:/opt/module/zookeeper-3.4.10/bin:/opt/module/apache-hive-1.2.2-bin/bin:/usr/local/mysql/lib:/usr/local/mysql/bin:/root/bin:/opt/module/jdk1.8.0_221/bin:/opt/module/hadoop-2.8.5/sbin:/opt/module/hadoop-2.8.5/bin:/opt/module/zookeeper-3.4.10/bin:/opt/module/hive-2.3.6-bin/bin:/usr/local/mysql/lib:/usr/local/mysql/bin)
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/module/hive-2.3.6-bin/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/module/hadoop-2.8.5/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]

Logging initialized using configuration in file:/opt/module/hive-2.3.6-bin/conf/hive-log4j2.properties Async: true
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> 

9.退出hive

hive的退出有两种方式：

hive> exit;
hive> quit;

exit:先隐性提交数据，再退出；
quit:不提交数据，退出；

10.问题处理(新加）

由于后边跟换了5.1JDBC驱动包，在进入hive时提示如下警告：

Logging initialized using configuration in file:/opt/module/hive-2.3.6-bin/conf/hive-log4j2.properties Async: true
Wed Sep 18 00:32:24 EDT 2019 WARN: Establishing SSL connection without server's identity verification is not recommended. According to MySQL 5.5.45+, 5.6.26+ and 5.7.6+ requirements SSL connection must be established by default if explicit option isn't set. For compliance with existing applications not using SSL the verifyServerCertificate property is set to 'false'. You need either to explicitly disable SSL by setting useSSL=false, or set useSSL=true and provide truststore for server certificate verification.

解决:修改$HIVE_HOME/conf/hive-site.xml
添加useSSL=false

    <value>jdbc:mysql://192.168.228.128:3306/onhive?useSSL=false</value>