Hive deployment

Install and configure MySQL

MySQL is used to store Hive’s metadata (table structure, database information, etc.), and requires installation, permission configuration, and Hive’s exclusive database creation.

Environmental preparation and installation

switch to/optcatalog, createmysqlfolder

cd /opt
sudo mkdir mysql

Update Ubuntu software sources

sudo apt update

Install MySQL 8.0 server (automatically handles all dependencies)

sudo apt install -y mysql-server

After the installation is complete, verify the service status (Active: active (running) means the launch was successful)

sudo systemctl status mysql

log in to MySQL

Log in to MySQL as root user (sudo ensures permissions, press enter for the first time to skip password entry)

sudo mysql -u root -p

ps: The prefix here is replaced by mysql> If you are prompted with an incorrect password, check the Debian temporary password

cat /etc/mysql/debian.cnf

Log in with a temporary account (enter the password field of [client] in the file)

sudo mysql -u debian-sys-maint -p

Change password and add permissions

Directly modify the root initial password

alter user 'root'@'localhost' identified by 'root123456';

Refresh permissions take effect

flush privileges;

Create a Hive metadata storage database

Create a Hive metadata database (set to latin1, compatible with Hive metadata storage format)

create database hive character set latin1;

Create a remote access user ( % means all IP connections are allowed, authentication plug-in is compatible with Hive)

create user 'hive'@'%' identified with mysql_native_password by 'hive123456'; 

Grant this user all rights to the hive database (including authorizing others)

grant all privileges on hive.* to 'hive'@'%' with grant option;

Create hive local access users (only local access, improved security)

create user 'hive'@'localhost' identified with mysql_native_password by 'hive123456';

Granting local permissions

grant all privileges on hive.* to 'hive'@'localhost' with grant option;

Refresh permissions

flush privileges;

verify permissions

show grants for 'hive'@'%';

Verify user information (password is stored in the authentication_string field)

use mysql;
select host, user, authentication_string as password from user where user = 'hive';

After execution is complete, enter exit to exit the MySQL command line.

Install Hive

This article selects Hive version 4.0.1 (adapted to Hadoop 3.4.0), and needs to complete the download, decompression, and environment variable configuration.

Download and extract Hive installation package

Switch to the archive storage directory

cd /export/software

Download Hive 4.0.1 compressed package from Huawei image sources (faster and avoids official source timeouts)

wget -P /export/software/ https://repo.huaweicloud.com/apache/hive/hive-4.0.1/apache-hive-4.0.1-bin.tar.gz

Decompress the compressed package

tar -zxvf /export/software/apache-hive-4.0.1-bin.tar.gz -C /export/servers/

Rename the decompression directory (simplifies subsequent path configuration, or you can directly hive but need to change later)

mv /export/servers/apache-hive-4.0.1-bin /export/servers/hive-4.0.1

If the decompression fails (indicating that the file is corrupt), re-execute the wget command to download the installation package (the file may be incomplete due to a network interruption during the download process).

Configure environment variables for hive

You need to add a Hive path to the system environment variables to ensure that the Hive command can be called globally:

Edit user environment variable configuration file

sudo vi /etc/profile

Add the following configuration at the end of the file (the path must be consistent with the actual Hive installation directory)

export HIVE_HOME=/export/servers/hive-4.0.1 
export HIVE_CONF_DIR=$HIVE_HOME/conf 
export HCAT_HOME=$HIVE_HOME/hcatalog 
export PATH=$HIVE_HOME/bin:$PATH 
export CLASSPATH=.:$JAVA_HOME/lib/dt.jar:$JAVA_HOME/lib/tools.jar:$HIVE_HOME/lib/*:$HIVE_CONF_DIR

save and exit

Make environment variable configuration effective

source /etc/profile

Verify the configuration (after execution, the Hive installation directory is displayed, indicating that the configuration was successful)

echo $HIVE_HOME

Hive’s core configuration

You need to modify the Hive configuration file, associate MySQL metadata, Hadoop clusters, configure temporary directories, etc. Give authority first

sudo chown -R hadooper:hadooper /export/servers/hive-4.0.1/

configured hive-env.sh(Associate Hadoop path)

Switch to Hive Configuration Directory

cd /export/servers/hive-4.0.1/conf 

Copy the template file as the official configuration file (Hive provides the template by default, which needs to be renamed and modified)

sudo cp /export/servers/hive-4.0.1/conf/hive-env.sh.template /export/servers/hive-4.0.1/conf /hive-env.sh 

edit the configuration file

sudo vi /export/servers/hive-4.0.1/conf/hive-env.sh 

Find line 48 (or search for HADOOP_HOME) and add the Hadoop installation path (it needs to be consistent with the actual Hadoop directory)

HADOOP_HOME=/export/servers/hadoop 

Save and exit (press Esc to enter

Enter)

configured hive-site.xml(Core Configuration File)

This file is used to configure MySQL connection information, HDFS storage path, temporary directory, etc. You need to manually create and add the following content:

create and edit hive-site.xml file

sudo vi /export/servers/hive-4.0.1/conf/hive-site.xml

Copy the following configuration

<?xml version="1.0" encoding="UTF-8" standalone="no"?> 
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?> 
<configuration> 
<!-- 1. MySQL 元数据库连接 URL（指定数据库、端口，不存在则自动创建） --> <property> 
<name>javax.jdo.option.ConnectionURL</name> <value>jdbc:mysql://localhost:3306/hive?createDatabaseIfNotExist=true&amp;useSSL=false&amp;serverTimezone=UTC&amp;allowPublicKeyRetrieval=true</value> 
</property> 
<!-- 2. MySQL JDBC 驱动类名（MySQL 8.0+ 用 com.mysql.cj.jdbc.Driver） --> <property> 
<name>javax.jdo.option.ConnectionDriverName</name> <value>com.mysql.cj.jdbc.Driver</value> 
</property> 
<!-- 3. 连接 MySQL 的用户名（前文创建的 hive 用户） --> 
<property> 
<name>javax.jdo.option.ConnectionUserName</name> 
<value>hive</value> 
</property> 
<!-- 4. 连接 MySQL 的密码（前文设置的 hive123456） --> 
<property> 
<name>javax.jdo.option.ConnectionPassword</name> 
<value>hive123456</value> </property> 
<!-- 5. Hive 数据仓库在 HDFS 中的存储路径 --> 
<property> 
<name>hive.metastore.warehouse.dir</name> 
<value>hdfs://mycluster/user/hive/warehouse</value> 
</property> 
<!-- 6. HBase 快照恢复临时目录（默认 /tmp） --> 
<property> 
<name>hive.hbase.snapshot.restoredir</name> 
<value>/tmp</value> </property> 
<!-- 7. Java 临时目录（Hive 运行时临时文件存储） --> 
<property> 
<name>system:java.io.tmpdir</name> 
<value>/tmp/hive/iotmp</value> 
</property> 
<!-- 8. Hive 运行用户（与前文 MySQL 授权用户一致） --> 
<property> 
<name>system:user.name</name> 
<value>hive</value> 
</property> 
<!-- 9. Hive 本地临时目录（基于 Java 临时目录和用户名） --> 
<property> 
<name>hive.exec.local.scratchdir</name> <value>${system:java.io.tmpdir}/${system:user.name}</value> 
</property> 
<!-- 10. 下载资源临时目录（会话级资源存储） --> 
<property> 
<name>hive.downloaded.resources.dir</name> <value>${system:java.io.tmpdir}/${hive.session.id}_resources</value> 
</property> 
<property> 
<name>hive.server2.enable.doAs</name> 
<value>false</value> 
<description>关闭用户模拟，HiveServer2直接以自身运行用户（hadooper）执行操作</description> 
</property> 
<!-- 确保权限校验相关配置匹配 --> 
<property> 
<name>hive.security.authorization.enabled</name> 
<value>false</value> 
</property> 
<property>
 <name>hive.server2.authentication</name>
 <value>NONE</value> 
</property> 
</configuration>

Key instructions: Add useSSL=false (close SSL connection to avoid warnings), serverTimezone=UTC (unify time zone to avoid timestamp exceptions), allowPublicKeyRetrieval=true (allow to obtain MySQL public key to solve connection failure problems) to the ConnectionURL

Configure log files (optional, optimize log output)

Hive provides a log configuration template, which needs to be copied into an official document: Switch to Hive Configuration Directory

cd /export/servers/hive-4.0.1/conf 

The copy log configuration template is an official document

sudo cp /export/servers/hive-4.0.1/conf/hive-log4j2.properties.template /export/servers/hive-4.0.1/conf/hive-log4j2.properties 
sudo cp /export/servers/hive-4.0.1/conf/hive-exec-log4j2.properties.template /export/servers/hive-4.0.1/conf/hive-exec-log4j2.properties

If you need to modify the log storage path and log level, you can edit the above two files (the default log is stored in /tmp/hive Contents).

Make sure hive is connected to hadoop

Copy the hadoop file to hive

sudo cp /export/servers/hadoop/etc/hadoop/core-site.xml /export/servers/hive-4.0.1/conf/ 

sudo cp /export/servers/hadoop/etc/hadoop/hdfs-site.xml /export/servers/hive-4.0.1/conf/

create a database file

hdfs dfs -mkdir -p /user/hive
hdfs dfs -mkdir -p /user/hive/warehouse

/user/hive The owners of all subdirectories and all subdirectories are changed to hadooper

hdfs dfs -chown -R hadooper:hadooper /user/hive

Integrated Spark configuration

Hive uses MapReduce as the execution engine by default. In order to improve computing efficiency, Spark needs to be configured as the execution engine, which includes two steps: Hive configuration modification and Spark configuration modification.

Modify Hive configuration file

Based on the configured Hive core configuration, add the relevant configuration of Spark execution engine:

Enter the migrated Hive configuration directory (the path has been migrated to /export/servers/hive）

cd /export/servers/hive-4.0.1/conf 

edithive-site.xmlFile (Add the following content based on the original configuration and keep XML indented consistently)

sudo vi /export/servers/hive-4.0.1/conf/hive-site.xml

Add <configuration>the following configurations to the label (strictly indented, at the same level as the original configuration):

<!-- 配置 Hive 执行引擎为 Spark --> 
<property> 
<name>hive.execution.engine</name> 
<value>spark</value> 
</property> 
<!-- 配置 Spark 运行模式为 YARN（依赖 Hadoop YARN 资源管理） --> 
<property> 
<name>spark.master</name> 
<value>yarn</value> 
</property> 
<!-- 配置 Spark 安装路径（需与实际 Spark 目录一致） --> 
<property> 
<name>spark.home</name> 
<value>/export/servers/spark</value> 
</property> 
<!-- 配置 Spark JAR 包在 HDFS 的存储路径（后续需上传 JAR 包至此路径） --> 
<property> 
<name>spark.yarn.jars</name> 
<value>hdfs://mycluster/spark/jars/*</value> 
</property> 
<!-- 配置 Spark 客户端与服务端连接超时时间（避免大集群环境下连接失败） -->
<property> 
<name>hive.spark.client.server.connect.timeout</name> 
<value>300000</value> 
</property>

Modify Spark configuration file

You need to configure Spark to associate Hive metadata, specify the encoding format, and add Hadoop classpath dependencies to ensure that Spark is compatible with Hive.

configured spark-defaults.conf(Associated Hive metadata)

Go to the Spark configuration directory

cd /export/servers/spark/conf 

Rename the template file to a formal configuration file

sudo cp /export/servers/spark/conf/spark-defaults.conf.template /export/servers/spark/conf/spark-defaults.conf 

edit spark-defaults.conf File, adding Hive metadata and encoding configuration

sudo vi /export/servers/spark/conf/spark-defaults.conf

Add the following at the end of the file (it needs to match the Hive path and HDFS address):

# 配置 Hive 元数据版本
spark.sql.hive.metastore.version 4.0.1
# 配置 Spark 加载 Hive 的 JAR 包路径
spark.sql.hive.metastore.jars = /export/servers/hive-4.0.1/lib/* 
# 配置 Hive Metastore 服务地址（hadoop01 为 metastore 所在主机名，默认端口 9083）
spark.sql.hive.metastore.uris thrift://hadoop01:9083 
# 配置 Spark SQL 数据仓库路径（与 Hive 数据仓库路径一致，避免数据分散）
spark.sql.warehouse.dir 
hdfs://hadoop01:8020/user/hive/warehouse 
# 配置 Spark 驱动与执行器的编码格式（解决中文乱码问题）
spark.driver.extraJavaOptions=-Dfile.encoding=UTF-8
spark.executor.extraJavaOptions=-Dfile.encoding=UTF-8

configured spark-env.sh(Add Hive and Hadoop dependencies)

Continue to configure in the Spark directory, edit spark-env.sh File (if the file does not exist, create it directly)

sudo vi /export/servers/spark/conf/spark-env.sh 

Add the following to the end of the file (associate Hive path with Hadoop classpath)

# 设置Hive配置文件的目录
export HIVE_HOME=/export/servers/hive-4.0.1
export HIVE_CONF_DIR=$HIVE_HOME/conf 
export SPARK_DIST_CLASSPATH=$(hadoop classpath) 

Configure Spark environment variables

You need to add the Spark path to the system environment variable to ensure that all users can call the Spark command: Edit the system global environment variable file (/etc/profile Effective for all users, requires sudo permission)

sudo vi /etc/profile 

Add a Spark environment variable at the end of the file (same level as Hive and Hadoop environment variables)

#Spark环境变量
export SPARK_HOME=/export/servers/spark
export PATH=$SPARK_HOME/bin:$SPARK_HOME/sbin:$PATH

Make environment variables take effect immediately (the current terminal takes effect, and if all terminals need to take effect, you need to restart or log in again)

source /etc/profile 

Verify Spark environment variables (output /export/servers/spark Indicates that the configuration was successful)

echo $SPARK_HOME

Dependent component configuration (HDFS directory + MySQL JDBC driver)

Create a hive directory in HDFS

Hive data warehouses are stored in HDFS and require manual creation of catalogs and authorization:

Ensure that the Hadoop cluster is started (if it is not started, execute the following command)

/export/servers/hadoop/sbin/start-all.sh 

Create database files in HDFS

hdfs dfs -mkdir -p /user/hive
hdfs dfs -mkdir -p /user/hive/warehouse

will/user/hive The owners of all subdirectories and all subdirectories are changed to hadooper

hdfs dfs -chown -R hadooper:hadooper /user/hive

Verification Catalog: Execution hadoop fs -ls /user/hive, display warehouse The directory indicates that the creation was successful.

Install MySQL JDBC Driver

Hive needs to connect to MySQL through a JDBC driver, and needs to download and copy it to Hive’s dependent library:

switch to /opt Contents, download MySQL JDBC Driver (version 8.0.33, compatible with MySQL 8.0+)

cd /opt 
sudo wget -P /opt/ https://repo1.maven.org/maven2/com/mysql/mysql-connector-j/8.0.33/mysql-connector-j-8.0.33.jar 

Copy the driver package to Hive’s lib directory (Hive automatically loads dependencies in this directory when startup)

cp /opt/mysql-connector-j-8.0.33.jar /export/servers/hive-4.0.1/lib/

If the download fails, you can manually access the link to download, upload it to the/opt directory, and then execute the copy command.

Create driver soft links

Go to Hive lib directory

cd /export/servers/hive-4.0.1/lib

Create compatible soft links (remove-j to match the filename recognized by Hive)

ln -s mysql-connector-j-8.0.33.jar mysql-connector-java-8.0.33.jar 

Verify that the soft link was created successfully

ls -l | grep mysql-connector 

It should be noted that:

lrwxrwxrwx 1 hadooper hadooper ... mysql-connector-java-8.0.33.jar -> mysql-connector-j-8.0.33.jar 
-rw-r--r-- 1 hadooper hadooper ... mysql-connector-j-8.0.33.jar 

Configure Hadoop proxy user rights

Hive runtime requires proxy users to access the Hadoop cluster, which requires authorization in the Hadoop configuration:

Editing Hadoop core-site.xml Configuration file (path needs to be consistent with the actual Hadoop directory)

sudo vi /export/servers/hadoop/etc/hadoop/core-site.xml 

Add <configuration>the following proxy user configurations to the tab (authorized frequent users)

<property> 
<name>hadoop.proxyuser.hadooper.hosts</name> 
<value>*</value> <!-- * 表示允许所有主机代理 --> 
</property> 
<property> 
<name>hadoop.proxyuser.hadooper.groups</name> 
<value>*</value> <!-- * 表示允许所有用户组代理 --> 
</property> 
<property> 
<name>hadoop.proxyuser.hadoop01.hosts</name> 
<value>*</value> 
</property> 
<property> 
<name>hadoop.proxyuser.hadoop01.groups</name> 
<value>*</value> 
</property> 
<property> 
<name>hadoop.proxyuser.hadooprunner.hosts</name> 
<value>*</value> 
</property> 
<property> 
<name>hadoop.proxyuser.hadooprunner.groups</name> 
<value>*</value> 
</property> 
<property> 
<name>hadoop.proxyuser.root.hosts</name> 
<value>*</value> 
</property> 
<property> 
<name>hadoop.proxyuser.root.groups</name> 
<value>*</value> 
</property> 

save and exit

Restart the Hadoop cluster (make the configuration take effect)

/export/servers/hadoop/sbin/stop-all.sh 
/export/servers/hadoop/sbin/start-all.sh 

Verify Hadoop status (ensure all core processes are started)

jps

Handling JAR package dependency conflicts and HDFS distribution

There may be JAR package version conflicts between Spark, Hive and Hadoop. The conflict needs to be resolved by “copying key JAR packages in both directions” and uploading the Spark JAR package to HDFS for shared calls by the YARN cluster.

Bidirectional copying of key JAR packages

Perform a bidirectional copy operation based on the specified JAR package list to ensure that Hive and Spark dependent versions are compatible:

Spark → Hive (Copy Spark dependency to Hive lib directory)

cd $SPARK_HOME/jars 
cp $SPARK_HOME/jars/spark-core_2.12-3.4.3.jar /export/servers/hive-4.0.1/lib/
cp $SPARK_HOME/jars/spark-network-common_2.12-3.4.3.jar /export/servers/hive-4.0.1/lib/
cp $SPARK_HOME/jars/spark-launcher_2.12-3.4.3.jar /export/servers/hive-4.0.1/lib/
cp $SPARK_HOME/jars/spark-unsafe_2.12-3.4.3.jar /export/servers/hive-4.0.1/lib/
cp $SPARK_HOME/jars/scala-library-2.12.17.jar /export/servers/hive-4.0.1/lib/

Hive → Spark (Copying Hive dependencies into the Spark jars directory)

cd /export/servers/hive-4.0.1/lib 
cp /export/servers/hive-4.0.1/lib/hive-exec-4.0.1.jar /export/servers/spark/jars/ 
cp /export/servers/hive-4.0.1/lib/hive-metastore-4.0.1.jar /export/servers/spark/jars/ 
cp /export/servers/hive-4.0.1/lib/hive-common-4.0.1.jar /export/servers/spark/jars/ 
cp /export/servers/hive-4.0.1/lib/hive-serde-4.0.1.jar /export/servers/spark/jars/ 
cp /export/servers/hive-4.0.1/lib/hive-cli-4.0.1.jar /export/servers/spark/jars/ 
cp /export/servers/hive-4.0.1/lib/jline-2.14.6.jar /export/servers/spark/jars/ 

Verify copy results

ls /export/servers/hive-4.0.1/lib | grep -E "spark|scala" 
ls /export/servers/spark/jars | grep -E "hive|jline"

Distribute Spark JAR packages to HDFS

Ensure that the Hadoop cluster is up

/export/servers/hadoop/sbin/start-all.sh 

Create an HDFS catalog

cd /export/servers
hdfs dfs -mkdir -p /spark/jars

Upload Spark JAR package

hdfs dfs -put $SPARK_HOME/jars/* /spark/jars 

Verify upload results

hdfs dfs -ls /spark/jars | wc -l 
ls $SPARK_HOME/jars | wc -l 

Clear redundant JAR packages (determined by reality)

Go to the Spark jars directory

cd /export/servers/spark/jars 

Remove redundant JAR packages from Hive 2.3.9

sudo rm /export/servers/spark/jars/hive-exec-2.3.9-core.jar /export/servers/spark/jars/hive-metastore-2.3.9.jar /export/servers/spark/jars/hive-common-2.3.9.jar /export/servers/spark/jars/hive-serde-2.3.9.jar /export/servers/spark/jars/hive-cli-2.3.9.jar /export/servers/spark/jars/hive-beeline-2.3.9.jar 

Verify remaining JAR packages (only Hive 4.0.1 version is retained)

ls /export/servers/spark/jars/ | grep -E "hive-exec|hive-metastore|hive-common|hive-serde|hive-cli|hive-beeline"

Initialize Hive metadata

The first deployment requires initialization of the metadata (the table structure required to create Hive):

Ensure that the Hadoop cluster is started (otherwise initialization fails)

jps 

Verify that NameNode, DataNode, ResourceManager, NodeManager processes exist

Execute Hive metadata initialization command (specify database type as MySQL)

schematool -dbType mysql -initSchema

Execution result: The terminal outputs a large number of logs (including table creation statements), and no error prompts (such as schemaTool completed) indicate that the initialization was successful; Exception handling: If you prompt “Unable to connect to MySQL”, check whether the MySQL service is started,hive-site.xml Whether the connection information in is correct and whether the JDBC driver has been successfully copied.

Launch and verify Hive

Hive initialization

Start HiveServer2 (JDBC/ODBC service)

nohup hive --service hiveserver2 > /tmp/hiveserver2.log 2>&1 &

Launch Metastore (metadata service)

nohup hive --service metastore -p 9084 > /tmp/metastore.log 2>&1 &

Wait for a while (about 15s) to verify the Metastore service (output port 9084 to listen to indicates success)

netstat -tuln | grep 9084

connect to Hive

Execute the hive or beeline command to enter Beeline interactive mode (the prompt changes to beeline&gt;)

hive

or

beeline 

If not, you can swap the environmental order of spark and hive, that is, spark comes first, and use which beeline to see if the beeline of hive is returned

Connect to HiveServer2 (local connection, no SASL certification required)

!connect jdbc:hive2://localhost:10000/default;auth=noSASL 

Enter user name and password (enter root for user name, press Enter for password, and no password is required for local connection)

Enter root and enter directly

root

Verify the connection (the prompt changes to 0: jdbc: hive2://localhost

/default&gt;, execute the following command)

view database

show databases;

View Hive system configuration

set -v;

Successful output of the default database means that Hive deployment is complete and can be used normally.