Teach you how to build hive3.1.2 on Tencent Cloud

Environment Preparation

Before starting any operation, make sure that the hadoop version has been installed. For details, please refer to my other blog post on building hadoop3.x

mysql installation

First put the installation package in the software folder and unzip it into mysql-lib

[root@master software]# ll
Total usage 1422372
-rw-r--r-- 1 root root 312850286 July 2 15:00 apache-hive-3.1.2-bin.tar.gz
-rw-r--r-- 1 root root 338075860 Jul 2 09:39 hadoop-3.1.3.tar.gz
-rw-r--r-- 1 root root 195013152 July 2 09:36 jdk-8u212-linux-x64.tar.gz
-rw-r--r-- 1 root root 609556480 Jul 2 15:08 mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
-rw-r--r-- 1 root root 985600 Jul 2 14:44 mysql-connector-java-5.1.37.jar
[root@master software]# mkdir mysql-lib
[root@master software]# tar -xf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C ./mysql-lib/
[root@master software]# cd mysql-lib/
[root@master mysql-lib]# ll
Total usage 595272
-rw-r--r-- 1 7155 31415 45109364 Sep 30 2019 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 318768 September 30, 2019 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 7037096 Sep 30 2019 mysql-community-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 49329100 Sep 30 2019 mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 23354908 September 30, 2019 mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 136837816 Sep 30 2019 mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 4374364 Sep 30 2019 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 1353312 Sep 30 2019 mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 208694824 September 30, 2019 mysql-community-server-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 133129992 September 30, 2019 mysql-community-test-5.7.28-1.el7.x86_64.rpm

Deal with the environment first, because centos comes with mariadb, which will cause conflicts, so you need to uninstall it first

[root@master mysql-lib]# rpm -qa|grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
[root@master mysql-lib]# sudo rpm -e --nodeps mariadb-libs 
# Check if the uninstall is complete [root@master mysql-lib]# rpm -qa|grep mariadb
[root@master mysql-lib]# rpm -qa|grep mysql

There is no problem, you can install it. Be sure to install it in order.

[root@master mysql-lib]# ll
Total usage 595284
-rw-r--r-- 1 7155 31415 45109364 Sep 30 2019 mysql-community-client-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 318768 September 30, 2019 mysql-community-common-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 7037096 Sep 30 2019 mysql-community-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 49329100 Sep 30 2019 mysql-community-embedded-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 23354908 September 30, 2019 mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 136837816 Sep 30 2019 mysql-community-embedded-devel-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 4374364 Sep 30 2019 mysql-community-libs-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 1353312 Sep 30 2019 mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 208694824 September 30, 2019 mysql-community-server-5.7.28-1.el7.x86_64.rpm
-rw-r--r-- 1 7155 31415 133129992 September 30, 2019 mysql-community-test-5.7.28-1.el7.x86_64.rpm
[root@master mysql-lib]# sudo rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-common-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Upgrading/installing...
  1:mysql-community-common-5.7.28-1.e################################### [100%]
[root@master mysql-lib]# sudo rpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-libs-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Upgrading/installing...
  1:mysql-community-libs-5.7.28-1.el7################################### [100%]
[root@master mysql-lib]# sudo rpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Upgrading/installing...
  1:mysql-community-libs-compat-5.7.2################################### [100%]
[root@master mysql-lib]# sudo rpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-client-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Upgrading/installing...
  1:mysql-community-client-5.7.28-1.e################################### [100%]

When installing the 5th one, if centos is installed minimally, this problem will be reported

[root@master mysql-lib]# sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-server-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
ERROR: Dependency check failed:
    libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64 libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64 libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.28-1.el7.x86_64

[root@master mysql-lib]# yum install -y libaio
[root@master mysql-lib]# yum install -y numactl

Then try to install it again and it will be fine

[root@master mysql-lib]# sudo rpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpm
Warning:mysql-community-server-5.7.28-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, Key ID 5072e1f5: NOKEY
Preparing... ################################# [100%]
Upgrading/installing...
  1:mysql-community-server-5.7.28-1.e################################### [100%]

Clean up historical residual files

[root@master mysql-lib]# cat /etc/my.cnf
# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

I saw that the mysql directory is /var/lib/mysql

#Enter this address to see if there are any historical residual files [root@master mysql-lib]# cd /var/lib/mysql
# If there is nothing, then it is fine. If there is something, just delete it. [root@master mysql]# ll
Total usage 0

Initialize mysql

[root@master mysql]# sudo mysqld --initialize --user=mysql

Check the default password

[root@master mysql]# cat /var/log/mysqld.log 
2020-07-02T07:29:10.095244Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2020-07-02T07:29:11.331738Z 0 [Warning] InnoDB: New log files created, LSN=45790
2020-07-02T07:29:11.498492Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2020-07-02T07:29:11.590436Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: b94654a6-bc35-11ea-a160-525400f3e388.
2020-07-02T07:29:11.600554Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2020-07-02T07:29:13.093996Z 0 [Warning] CA certificate ca.pem is self signed.
2020-07-02T07:29:13.522160Z 1 [Note] A temporary password is generated for root@localhost: p;oK<M)<_8p/

The password is p;oK<M)<_8p/ , copy it and enter mysql

Enter mysql

Start mysql service

[root@master mysql]# systemctl start mysqld
[root@master mysql]# systemctl status mysqld
● mysqld.service – MySQL Server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  Active: active (running) since Thursday 2020-07-02 15:31:00 CST; 10s ago
   Docs: man:mysqld(8)
      http://dev.mysql.com/doc/refman/en/using-systemd.html
 Process: 5617 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
 Process: 5497 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 5624 (mysqld)
  Tasks: 27
  Memory: 205.4M
  CGroup: /system.slice/mysqld.service
      └─5624 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid...

Jul 02 15:30:55 master systemd[1]: Starting MySQL Server...
Jul 02 15:31:00 master systemd[1]: Started MySQL Server.

Enter mysql

$ mysql -u root -p

When prompted to enter a password, enter the password you just copied.

After successfully entering mysql, set our mysql password, remember this yourself~

#Set the password to your passwordmysql> set password = password("your password");

Next, set the root user's permissions to allow any IP connection to prepare for the subsequent hive connection

mysql> use mysql;
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| localhost | mysql.session |
| localhost | mysql.sys |
| localhost | root |
+-----------+---------------+
3 rows in set (0.00 sec)

mysql> update mysql.user set host='%' where user='root';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# The ones with higher permissions are at the top (optimized in the new version)
mysql> select host,user from user;
+-----------+---------------+
| host | user |
+-----------+---------------+
| % | root |
| localhost | mysql.session |
| localhost | mysql.sys |
+-----------+---------------+
3 rows in set (0.00 sec)

--This step must be done to refresh the configuration, otherwise the password will not take effectmysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit

Then try to log in with the changed password. If successful, the configuration is complete.

Install Hive

Go to the /home/centos/software directory and unzip apache-hive-3.1.2-bin.tar.gz to the /home/centos/module/ directory

[root@master software]# tar -zxvf apache-hive-3.1.2-bin.tar.gz -C /home/centos/module/

Enter the module folder and change the file name

[root@master module]# ll
Total dosage 12
drwxr-xr-x 9 root root 4096 July 2 15:40 apache-hive-3.1.2-bin
drwxr-xr-x 11 centos centos 4096 July 2 11:51 hadoop-3.1.3
drwxr-xr-x 7 10 143 4096 April 2 2019 jdk1.8.0_212
[root@master module]# mv apache-hive-3.1.2-bin/ hive
[root@master module]# ll
Total dosage 12
drwxr-xr-x 11 centos centos 4096 July 2 11:51 hadoop-3.1.3
drwxr-xr-x 9 root root 4096 July 2 15:40 hive
drwxr-xr-x 7 10 143 4096 April 2 2019 jdk1.8.0_212

Configuring environment variables

[root@master module]# vim /etc/profile.d/my_env.sh

#HIVE_HOME
export HIVE_HOME=/home/centos/module/hive
export PATH=$PATH:$HIVE_HOME/bin

Load the environment variables and verify whether they are successful

[root@master module]# source /etc/profile
[root@master module]# echo $HIVE_HOME
/home/centos/module/hive

Resolving log Jar package conflicts

There is a log4j version conflict between hadoop and hive. You need to change the name of the jar package in the hive lib folder so that it cannot be enabled. Just add .bak to the end of the file name.

[atguigu@hadoop102 lib]$ mv log4j-slf4j-impl-2.10.0.jar log4j-slf4j-impl-2.10.0.jar.bak

[atguigu@hadoop102 lib]$ ll | grep log4j
-rw-rw-r--. 1 atguigu atguigu 63835 Apr 15 00:34 log4j-1.2-api-2.10.0.jar
-rw-rw-r--. 1 atguigu atguigu 255485 Apr 15 00:24 log4j-api-2.10.0.jar
-rw-rw-r--. 1 atguigu atguigu 1597622 Apr 15 00:24 log4j-core-2.10.0.jar
-rw-rw-r--. 1 atguigu atguigu 24173 Apr 15 00:24 log4j-slf4j-impl-2.10.0.jar.bak
-rw-rw-r--. 1 atguigu atguigu 32060 Apr 15 00:34 log4j-web-2.10.0.jar

Hive metadata configuration to MySQL

Copy the MySQL JDBC driver to the Hive lib directory

[root@master lib]# cp /home/centos/software/mysql-connector-java-5.1.37.jar ./

Check if the copy is successful

[root@master lib]# ll | grep mysql-con
-rw-r--r-- 1 root root 985600 Jul 2 15:47 mysql-connector-java-5.1.37.jar

Configure metastore to mysql

Create a new hive-site.xml file in the $HIVE_HOME/conf directory.

[root@master conf]# vim hive-site.xml

There are three values you need to change.

Tencent Cloud intranet IP
mysql password
Local hostname

<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl" rel="external nofollow" ?>
<configuration>
  <!-- URL for jdbc connection -->
  <property>
    <name>javax.jdo.option.ConnectionURL</name>
    <value>jdbc:mysql://Tencent Cloud intranet ip:3306/metastore?useSSL=false</value>
</property>

  <!-- JDBC connection Driver -->
  <property>
    <name>javax.jdo.option.ConnectionDriverName</name>
    <value>com.mysql.jdbc.Driver</value>
</property>

    <!-- jdbc connection username -->
  <property>
    <name>javax.jdo.option.ConnectionUserName</name>
    <value>root</value>
  </property>

  <!-- Password for jdbc connection -->
  <property>
    <name>javax.jdo.option.ConnectionPassword</name>
    <value>Your mysql password</value>
  </property>
  <!-- Hive's default working directory in HDFS -->
  <property>
    <name>hive.metastore.warehouse.dir</name>
    <value>/user/hive/warehouse</value>
  </property>
  
  <!-- Verification of Hive metadata storage version -->
  <property>
    <name>hive.metastore.schema.verification</name>
    <value>false</value>
  </property>
  <!-- Specify the address to connect to for storing metadata -->
  <property>
    <name>hive.metastore.uris</name>
    <value>thrift://Tencent Cloud intranet ip:9083</value>
  </property>
  <!-- Specify the port number for hiveserver2 connection-->
  <property>
  <name>hive.server2.thrift.port</name>
  <value>10000</value>
  </property>
  <!-- Specify the host to which hiveserver2 connects -->
  <property>
    <name>hive.server2.thrift.bind.host</name>
    <value>Your local hostname</value>
  </property>
  <!-- Metadata storage authorization -->
  <property>
    <name>hive.metastore.event.db.notification.api.auth</name>
    <value>false</value>
  </property>
  
  <!-- High availability parameters of hiveserver2. Enabling this parameter can improve the startup speed of hiveserver2. -->
<property>
  <name>hive.server2.active.passive.ha.enable</name>
  <value>true</value>
</property>

</configuration>

After pasting the modified content, save and exit

Start hive

Initialize the metadata database

mysql> create database metastore;
Query OK, 1 row affected (0.00 sec)

Exit and initialize the hive metabase

# Let hive create the initialized table in metastore [atguigu@hadoop102 software]$ schematool -initSchema -dbType mysql -verbos

Enter mysql again and check that it has been created (74 tables)

mysql> use metastore;
mysql> show tables;

Create a startup script, otherwise it would be too annoying to write such a long script every time to start it in the background.

[root@master bin]# vim hs.sh

Add the following content

#!/bin/bash
HIVE_LOG_DIR=$HIVE_HOME/logs
#Judge whether this directory exists, if not, create it if [ ! -d $HIVE_LOG_DIR ]
then
	mkdir -p $HIVE_LOG_DIR
fi
#Check whether the process is running normally, parameter 1 is the process name, parameter 2 is the process port #status
function check_process()
{
	#/dev/null Black hole unwanted streams are placed here for destruction pid=$(ps -ef 2>/dev/null | grep -v grep | grep -i $1 | awk '{print $2}')
  ppid=$(netstat -nltp 2>/dev/null | grep $2 | awk '{print $7}' | cut -d '/' -f 1)
  echo $pid
  [[ "$pid" =~ "$ppid" ]] && [ "$ppid" ] && return 0 || return 1
}

#start
function hive_start()
{
  metapid=$(check_process HiveMetastore 9083)
  #Just understand this line, this line is to start the service background cmd="nohup hive --service metastore >$HIVE_LOG_DIR/metastore.log 2>&1 &"
  cmd=$cmd" sleep 4; hdfs dfsadmin -safemode wait >/dev/null 2>&1"
  [ -z "$metapid" ] && eval $cmd || echo "Metastroe service has been started"
  server2pid=$(check_process HiveServer2 10000)
  #Just understand this line, this line is to start the service background cmd="nohup hive --service hiveserver2 >$HIVE_LOG_DIR/hiveServer2.log 2>&1 &"
  [ -z "$server2pid" ] && eval $cmd || echo "HiveServer2 service has started"
}

#stop
function hive_stop()
{
  metapid=$(check_process HiveMetastore 9083)
  [ "$metapid" ] && kill $metapid || echo "Metastore service is not started"
  server2pid=$(check_process HiveServer2 10000)
  [ "$server2pid" ] && kill $server2pid || echo "HiveServer2 service is not started"
}

case $1 in
"start")
  hive_start
  ;;
"stop")
  hive_stop
  ;;
"restart")
  hive_stop
  sleep 2
  hive_start
  ;;
"status")
  check_process HiveMetastore 9083 >/dev/null && echo "Metastore service is running normally" || echo "Metastore service is running abnormally"
  check_process HiveServer2 10000 >/dev/null && echo "HiveServer2 service is running normally" || echo "HiveServer2 service is running abnormally"
  ;;
*)
  echo Invalid Args!
  echo 'Usage: '$(basename $0)' start|stop|restart|status'
  ;;
esac

Save and exit, add permissions to the script

$ chmod +x hs.sh

Now let's start it (make sure that Hadoop is started and running normally before starting)

[root@master bin]# hs.sh start
[root@master bin]# jps
32177 SecondaryNameNode
514 NodeManager
20788 Jps
31396 NameNode
32652 ResourceManager
20253 RunJar
31757 DataNode
991 JobHistoryServer

You can see that the startup is successful. Let's use the script to see the status

[root@master bin]# hs.sh status
The Metastore service is running abnormally. The HiveServer2 service is running abnormally.

You may be surprised to find that there is an abnormal operation. What is going on? Actually, it is because it starts slowly. If you don’t believe me, try again after two minutes.

[root@master bin]# hs.sh status
The Metastore service is running normally. The HiveServer2 service is running normally.

hive jdbc access

beeline

If you did not use root to build hadoop, then change it according to your own settings.

#You must use root access because the jdbc administrator is root, otherwise you have no permission -n is followed by the user to log in [atguigu@hadoop102 bin]$ beeline -u jdbc:hive2://master:10000 -n root
Connecting to jdbc:hive2://master:10000
Connected to: Apache Hive (version 3.1.2)
Driver: Hive JDBC (version 3.1.2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 3.1.2 by Apache Hive
0: jdbc:hive2://master:10000>

This means you have successfully entered. If you exit

0: jdbc:hive2://master:10000> !quit
Closing: 0: jdbc:hive2://master:10000

hive command line

[atguigu@hadoop102 bin]$ hive
#A bunch of information hive>

This means you have successfully entered. To exit, just quit; Then you can play hive!

History

Their history records are in the home directory

$ cd ~
$ ls -a

You can see a .hivehistory file and a .beeline folder. The history records will be written after you quit the JDBC.

Hive operation log configuration

By default, Hive logs are stored under the current user name. We also put Hive's own operation logs in this logs for easy viewing later.

(1) Modify the file name of $HIVE_HOME/conf/hive-log4j.properties.template to hive-log4j.properties

$ mv hive-log4j2.properties.template hive-log4j.properties
$ vim hive-log4j.properties

(2) Modify the log storage location in the hive-log4j.properties file

property.hive.log.dir=/home/centos/module/hive/logs

Hive startup jvm heap memory settings

When the new version of Hive is started, the default jvm heap memory size requested is 256M. The jvm heap memory requested is too small, which leads to the error java.lang.OutOfMemoryError: Java heap space often reported when executing complex SQL when the local mode is turned on later. Therefore, it is best to adjust HADOOP_HEAPSIZE parameter in advance.

[atguigu@hadoop102 conf]$ cp hive-env.sh.template hive-env.sh
[atguigu@hadoop102 conf]$ vim hive-env.sh

Uncomment the parameter export HADOOP_HEAPSIZE=1024 in hive-env.sh

After completing all the above configurations, restart hive.

Connecting hive from windows

I recommend dbeaver. Remember to go to the security group and open port 10000 before connecting.

The specific configuration steps are as follows

Then

Test the connection and wait for about 1 minute. It will basically be successful. Then you can have fun with hive on Windows.

This is the end of this article about how to build hive3.1.2 on Tencent Cloud. For more information about building hive3.1.2 on Tencent Cloud, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!

You may also be interested in: