Hive部署测试(4.0.0)

一.前言

hadoop版本3.3.6 mysql版本8.0.20

二.部署过程

2.1 解压安装包 创建软连接

tar -zxvf apache-hive-4.0.0-bin.tar.gz
ln -s /home/user1/jars/apache-hive-4.0.0-bin/ /home/user1/hive

2.2 mysql安装(忽略默认的Derby数据库 生产环境基本为mysql)

#1.解压后放置 /usr/local目录下
tar -xvf mysql-8.0.39-linux-glibc2.28-x86_64.tar.xz
#root执行
mv mysql-8.0.39-linux-glibc2.28-x86_64 /usr/local/
#创建mysql用户和用户组(避免mysql被攻击时也只具有mysql用户的权限)和赋权
groupadd mysql
#-r表示系统用户不可用于登录 创建mysql用户并放置在mysql组内
useradd -r -g mysql mysql
# 将文件的所有属性改为 mysql 用户
chown -R mysql /usr/local/mysql/ 
# 将组属性改为 mysql 组
chgrp -R mysql /usr/local/mysql/ 
#修改my.cnf
 > my.cnf
vim /etc/my.cnf
[mysqld]
user=root
datadir=/usr/local/mysql/data
basedir=/usr/local/mysql
port=3306
max_connections=200
max_connect_errors=10
character-set-server=utf8
default-storage-engine=INNODB
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
group_concat_max_len=102400
[mysql]
default-character-set=utf8
[client]
port=3306
default-character-set=utf8
#初始化数据库
cd /usr/local/mysql/bin
./mysqld --initialize
#如果报错./mysqld: error while loading shared libraries: libnuma.so.1: cannot open shared object file: No such file or directory
yum -y install numactl
#执行报错 需要安装如下依赖 显示
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.11' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.22' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by ./mysqld)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by ./mysqld)
./mysqld: /lib64/libm.so.6: version `GLIBC_2.27' not found (required by ./mysqld)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.28' not found (required by ./mysqld)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.25' not found (required by /usr/local/mysql/bin/../lib/private/libcrypto.so.3)
./mysqld: /lib64/libc.so.6: version `GLIBC_2.28' not found (required by /usr/local/mysql/bin/../lib/private/libprotobuf-lite.so.24.4.0)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.20' not found (required by /usr/local/mysql/bin/../lib/private/libprotobuf-lite.so.24.4.0)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.9' not found (required by /usr/local/mysql/bin/../lib/private/libprotobuf-lite.so.24.4.0)
./mysqld: /lib64/libstdc++.so.6: version `CXXABI_1.3.8' not found (required by /usr/local/mysql/bin/../lib/private/libprotobuf-lite.so.24.4.0)
./mysqld: /lib64/libstdc++.so.6: version `GLIBCXX_3.4.21' not found (required by /usr/local/mysql/bin/../lib/private/libprotobuf-lite.so.24.4.0)
#执行查看是否缺少动态库
strings /usr/lib64/libstdc++.so.6 | grep CXXABI
#需要root
wget http://ftp.de.debian.org/debian/pool/main/g/gcc-8/libstdc++6_8.3.0-6_amd64.deb
ar -x libstdc++6_8.3.0-6_amd64.deb
tar -xvf data.tar.xz
cp usr/lib/x86_64-linux-gnu/libstdc++.so.6.0.25 /usr/lib64/
find / -name "libstdc++*"
rm /usr/lib64/libstdc++.so.6
ll /usr/lib64/libstd*
ln -s /usr/lib64/libstdc++.so.6.0.25 /usr/lib64/libstdc++.so.6
#继续添加GLIBC_2.18
wget http://ftp.gnu.org/gnu/glibc/glibc-2.18.tar.gz
tar zxf glibc-2.18.tar.gz 
cd glibc-2.18/
mkdir build
cd build/
../configure --prefix=/usr
make -j2
make install
#添加mysqld服务到系统
cd /usr/local/mysql
cp -a ./support-files/mysql.server /etc/init.d/mysql
chmod +x /etc/init.d/mysql
chkconfig --add mysql
service mysql start
#将mysql添加到命令行
ln -s /usr/local/mysql/bin/mysql /usr/bin
#登录 密码为初始化完成时末尾提示的
mysql -uroot -p

2.3 mysql创建用户及赋权

#修改密码
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';
flush privileges;
#更改连接权限
use mysql;
update user set host='%' where user = 'root';
flush privileges;
#创建hive用户
CREATE USER 'hive_user'@'localhost' IDENTIFIED BY 'password';
#创建hive库
CREATE DATABASE hive_db;
GRANT ALL ON hive_db.* TO 'hive_user'@'localhost';

2.4 hive配置文件修改 hive-site.xml

<!-- jdbc连接的URL -->
    <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://localhost:3306/metastore?useSSL=false</value>
    </property>
    <!-- jdbc连接的Driver-->
    <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
    </property>    
    <!-- jdbc连接的username-->
    <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>hive_user</value>
    </property>
    <!-- jdbc连接的password -->
    <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>passwd</value>
    </property>
    <!-- Hive默认在HDFS的工作目录 -->
    <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>/user/hive/warehouse</value>
    </property>
</configuration>

2.5 mysql连接器上传

https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.29
#下载mysql连接器并上传至hive/lib目录下

2.6 增加hiveserver2的配置 hive-site.xml

#备注(如果hadoop没有配置超级用户代理,需要回去修改core-site.xml配置超级代理)

<!-- 配置访问hadoop超级代理 -->
    <property>
        <name>hadoop.proxyuser.user1.hosts</name>
        <value>*</value>
    </property>
    <property>
        <name>hadoop.proxyuser.user1.users</name>
        <value>*</value>
    </property>
hive-site.xml增加
<!-- 指定hiveserver2连接的host -->
<property>
    <name>hive.server2.thrift.bind.host</name>
    <value>linux1</value>
</property>
<!-- 指定hiveserver2连接的端口号 -->
<property>
    <name>hive.server2.thrift.port</name>
    <value>10000</value>
</property>

2.7 启动hive

#启动服务 metastore
nohup hive --service metastore 2>&1 &
#启动hiveserver2
nohup hive --service hiveserver2 2>&1 &
#备注 hive4.0废弃了旧的hivecli 直接输入hive的话也是连接beeline 所以连接方式直接采用beeline验证
beeline -u jdbc:hive2://linux1:10000 -n user1

三. hive测试

#创建 测试库
CREATE DATABASE IF NOT EXISTS test_db;
#使用库 
USE test_db;
#创建新表
CREATE TABLE test_table (id INT, name STRING);
#插入数据(此时开始为mr作业,去yarn界面查看任务执行情况)
INSERT INTO test_table VALUES (1, 'A'), (2, 'B');
INSERT INTO test_table VALUES (3, 'C'), (4, 'D');
#执行查询测试
SELECT * FROM test_table;
SELECT COUNT(*) FROM test_table;
#如果查询结果没问题 hive部署成功