# 下载Hive安装包
# wget https://archive.apache.org/dist/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz

# 解压安装包
# tar -xzvf apache-hive-3.1.3-bin.tar.gz -C /opt/
# mv /opt/apache-hive-3.1.3-bin /opt/hive

# 配置Hive环境变量
# echo “export HIVE_HOME=/opt/hive” >> ~/.bashrc
# echo “export PATH=$PATH:$HIVE_HOME/bin” >> ~/.bashrc
# source ~/.bashrc

# 创建Metastore数据库
# mysql -u root -p << EOF CREATE DATABASE metastore; CREATE USER 'hive'@'localhost' IDENTIFIED BY 'hive'; GRANT ALL PRIVILEGES ON metastore.* TO 'hive'@'localhost'; FLUSH PRIVILEGES; EOF # 配置hive-site.xml # vi /opt/hive/conf/hive-site.xml javax.jdo.option.ConnectionURL
jdbc:mysql://localhost:3306/metastore?createDatabaseIfNotExist=true javax.jdo.option.ConnectionDriverName
com.mysql.jdbc.Driver javax.jdo.option.ConnectionUserName
hive javax.jdo.option.ConnectionPassword
hive hive.metastore.warehouse.dir
/user/hive/warehouse hive.metastore.uris
thrift://localhost:9083

# 下载MySQL驱动
# wget https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-8.0.28.tar.gz
# tar -zxvf mysql-connector-java-8.0.28.tar.gz
# cp mysql-connector-java-8.0.28/mysql-connector-java-8.0.28.jar /opt/hive/lib/

# 初始化Metastore
# schematool -dbType mysql -initSchema -url jdbc:mysql://localhost:3306/metastore -username hive -password hive

# 启动Metastore服务
# nohup hive –service metastore > /var/log/hive/metastore.log 2>&1 &

# 启动HiveServer2
# nohup hive –service hiveserver2 > /var/log/hive/hiveserver2.log 2>&1 &

# 连接Hive
# beeline -u jdbc:hive2://localhost:10000 -n hive -p hive

# 创建测试表
CREATE TABLE test (id INT, name STRING);

# 插入测试数据
INSERT INTO test VALUES (1, ‘test’);

# 查询测试数据
SELECT * FROM test;

# 退出beeline
!quit

# 创建备份目录
# mkdir -p /backup/hive

# 备份Metastore数据库
# mysqldump -u hive -p metastore > /backup/hive/metastore_backup_$(date +%Y%m%d).sql

# 备份Hive配置
# cp -r /opt/hive/conf /backup/hive/conf_$(date +%Y%m%d)

# 配置自动备份
# 创建备份脚本
# vi /root/backup_hive.sh

#!/bin/bash

DATE=$(date +%Y%m%d)
BACKUP_DIR=”/backup/hive/$DATE”

# 创建备份目录
mkdir -p $BACKUP_DIR

# 备份Metastore数据库
mysqldump -u hive -p’your_password’ metastore > $BACKUP_DIR/metastore_backup.sql

# 备份Hive配置
cp -r /opt/hive/conf $BACKUP_DIR/

# 给脚本添加执行权限
# chmod +x /root/backup_hive.sh

# 添加到crontab
# crontab -e

# 添加以下内容（每天凌晨2点执行备份）
0 2 * * * /root/backup_hive.sh

# 升级Hive版本
# 1. 备份配置和数据
# /root/backup_hive.sh

# 2. 停止Hive服务
# pkill -f hive

# 3. 下载新版本安装包
# wget https://archive.apache.org/dist/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz

# 4. 解压安装包
# tar -xzvf apache-hive-3.1.3-bin.tar.gz -C /opt/
# mv /opt/hive /opt/hive-old
# mv /opt/apache-hive-3.1.3-bin /opt/hive

# 5. 复制配置文件和驱动
# cp -r /opt/hive-old/conf/* /opt/hive/conf/
# cp /opt/hive-old/lib/mysql-connector-java-8.0.28.jar /opt/hive/lib/

# 6. 升级Metastore Schema
# schematool -dbType mysql -upgradeSchema -url jdbc:mysql://localhost:3306/metastore -username hive -password hive

# 7. 启动Hive服务
# nohup hive –service metastore > /var/log/hive/metastore.log 2>&1 &
# nohup hive –service hiveserver2 > /var/log/hive/hiveserver2.log 2>&1 &

# 8. 验证升级结果
# hive –version

# 数据库迁移
# 从旧版本Hive迁移到Hive 3.1.3

# 1. 在旧系统上导出表结构
# hive -e “SHOW DATABASES;” > databases.txt
# while read db; do
# if [ “$db” != “default” ]; then
# hive -e “USE $db; SHOW TABLES;” > ${db}_tables.txt
# while read table; do
# hive -e “USE $db; SHOW CREATE TABLE $table;” > ${db}_${table}.sql
# done < ${db}_tables.txt # fi # done < databases.txt # 2. 将备份文件复制到新系统 # scp -r *.txt *.sql root@new-cluster:/backup/hive/ # 3. 在新系统上创建数据库和表 # while read db; do # if [ "$db" != "default" ]; then # hive -e "CREATE DATABASE IF NOT EXISTS $db;" # fi # done < databases.txt # while read db; do # if [ "$db" != "default" ]; then # while read table; do # hive -f ${db}_${table}.sql # done < ${db}_tables.txt # fi # done < databases.txt # 4. 从HDFS复制数据 # hadoop fs -cp hdfs://old-cluster:9000/user/hive/warehouse/* hdfs://new-cluster:9000/user/hive/warehouse/ # 5. 验证迁移结果 # hive -e "SHOW DATABASES;"