preparation in advance

Complete hadoop installation
Complete the installation of mysql

Download Hive

wget http://mirror.bit.edu.cn/apache/hive/hive-2.3.0/apache-hive-2.3.0-bin.tar.gz
Or go to the official website and install it.

Unzip to the specified installation directory

Pass the installation package to opt/hadoop with xftp

Decompression:
tar -zxvf apache-hive-2.3.0-bin.tar.gz

Modify the folder name:
mv ./apache-hive-2.3.0-bin ./hive-2.3.0

Modifying environment variables

vi /etc/profile

insert

export HIVE_HOME=/opt/hadoop/hive-2.3.0
export PATH=$HIVE_HOME/bin:$PATH

Make its modification take effect immediately
source /etc/profile

Login to mysql database

And create a metastore database, close the read-only property of the new master library, and authorize it (for storing the initial configuration of hive)

create database metastore;

set global read_only=0;
grant all on metastore.* to hive@'%'  identified by 'hive';
grant all on metastore.* to hive@'localhost'  identified by 'hive';

flush privileges;

If the read-only property of the database is not closed, execute
Grant all on metastore. * to hive @'%'identified by'hive'; error will be reported when

Download jdbc connector

Click link Connector/J 5.1.44 Download to the local host and then upload to it

/opt/hadoop/hive-2.3.0/lib

Modify hive configuration file

cd /opt/hadoop/hive-2.3.0/conf/

rename profile

cp hive-env.sh.template hive-env.sh
cp hive-default.xml.template hive-site.xml
cp hive-log4j2.properties.template hive-log4j2.properties
cp hive-exec-log4j2.properties.template hive-exec-log4j2.properties

Modify the hive-env.sh file

export JAVA_HOME=/opt/java/jdk1.8.0_201    ##Java path, according to the path configuration of your own jdk installation
export HADOOP_HOME=/opt/hadoop/hadoop-2.8.0   ##Hadoop installation path
export HIVE_HOME=/opt/hadoop/hive-2.3.0    ##Hive installation path
export HIVE_CONF_DIR=/opt/hadoop/hive-2.3.0/conf   ##Hive profile path

hadoop   fs   -mkdir   -p   /usr/hive/warehouse
hadoop   fs   -chmod   777   /usr/hive/warehouse 
hadoop   fs   -mkdir  -p   /tmp/hive/
hadoop   fs   -chmod  777   /tmp/hive
hadoop   fs   -ls   /usr/hive/
hadoop   fs   -ls   /tmp/

Modify the temporary directory in hive-site.xml

Replace the ${system:java.io.tmpdir} in the hive-site.xml file with the temporary directory of hive, such as / root/hive/tmp, which is created manually if it does not exist, and gives read and write permission.

Replace ${system:user.name} with root

Modify the configuration of hive-site.xml database

Search the javax. jdo. option. Connection URL and change the value corresponding to that name to the address of MySQL, for example, when I modify it:

<name>javax.jdo.option.ConnectionURL</name>  
<value>jdbc:mysql://172.18.74.236:3306/hive?createDatabaseIfNotExist=true</value>

Search for javax.jdo.option.ConnectionDriverName and change the value corresponding to that name to the MySQL driver class path. For example, my modification is:

 <property> 
       <name>javax.jdo.option.ConnectionDriverName</name> 
       <value>com.mysql.jdbc.Driver</value> 
</property>      

Search javax.jdo.option.ConnectionUserName and change the corresponding value to the MySQL database login name:

<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>

Search for javax.jdo.option.ConnectionPassword and change the corresponding value to the login password of MySQL database:

     <name>javax.jdo.option.ConnectionPassword</name>
     <value>******</value>

Modify your password

Search hive.metastore.schema.verification and change the corresponding value to false:

    <name>hive.metastore.schema.verification</name>
     <value>false</value>

Start and test

Initialization of MySQL database

Enter the bin directory of hive to execute the command:

schematool -initSchema -dbType mysql

Two errors were reported:

First:

Fri Mar 30 14:55:35 CST 2018 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.

Looking at it carefully, I found that the configuration item of SSL was not configured in the string.

You need to add useSSL=false/true configuration to the connection string.

<value>jdbc:mysql://172.18.74.236:3306/hive?createDatabaseIfNotExist=true</value>

Modified to

 <value>jdbc:mysql://172.18.74.236:3306/hive?createDatabaseIfNotExist=true&amp;characterEncoding=UTF-8&amp;useSSL=false</value>

The second:

Error reporting when installing Hive:

org.apache.hadoop.hive.metastore.HiveMetaException: Failed to get schema version.
Underlying cause: java.sql.SQLException : Access denied for user 'root'@'master.hadoop' (using password: YES)

Solution:

1. First login to mysql with root user
   mysql -u root -p

2. Look under root
   mysql> select user,host from mysql.user where user='root';
   
   The reason is that the'root'@'master' user has insufficient permissions, so the root user is used to assign permissions to the user.
   MySQL > grant all on *. * to'root'@'Master' identified by'your database password'
   
   Exit reinitialization
   Success!