blog网站模板/天津seo数据监控
目录
前提条件
安装MySQL
卸载原有mysql及mariadb
下载mysql
解压mysql
安装mysql
启动mysql服务
开机自启动mysql服务
登录mysql
修改mysql密码
远程连接mysql
安装Hive
下载安装包
解压
设置环境变量
解决日志包冲突
将mysql驱动拷贝到lib目录
配置Hive
创建hive-site.xml
调整输出日志级别
初始化Hive元数据库
修改元数据库字符集
进入Hive命令行
简单使用Hive
退出Hive命令行
前提条件
Hive依赖于Hadoop，所以需要先安装好Hadoop，可参考：openEuler24.03 LTS下安装Hadoop3完全分布式
安装MySQL
Hive的元数据存储在关系型数据库中，这里使用的关系型数据库为MySQL，所以需要先安装好MySQL。
在node2安装MySQL。
卸载原有mysql及mariadb
sudo systemctl stop mysql mysqld 2>/dev/null sudo rpm -qa | grep -i 'mysql\|mariadb' | xargs -n1 sudo rpm -e --nodeps 2>/dev/null sudo rm -rf /var/lib/mysql /var/log/mysqld.log /usr/lib64/mysql /etc/my.cnf /usr/my.cnf
下载mysql
这里下载的mysql版本为mysql8.4.2，如果下载较旧的mysql版本可能与openEuler24.03不兼容。
cd /opt/software wget https://downloads.mysql.com/archives/get/p/23/file/mysql-8.4.2-1.el9.x86_64.rpm-bundle.tar
注意：如果命令行下载较慢，可以直接使用浏览器访问https链接地址下载，再上传安装文件到Linux的安装包存放目录，例如：/opt/software。
解压mysql
# 创建mysql目录 [liang@node2 software]$ mkdir mysql #解压 [liang@node2 software]$ tar -xvf mysql-8.4.2-1.el9.x86_64.rpm-bundle.tar -C mysql
删除不必要的rpm包
[liang@node2 software]$ cd mysql [liang@node2 mysql]$ ls mysql-community-client-8.4.2-1.el9.x86_64.rpm mysql-community-client-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-client-plugins-8.4.2-1.el9.x86_64.rpm mysql-community-client-plugins-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-common-8.4.2-1.el9.x86_64.rpm mysql-community-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-debugsource-8.4.2-1.el9.x86_64.rpm mysql-community-devel-8.4.2-1.el9.x86_64.rpm mysql-community-icu-data-files-8.4.2-1.el9.x86_64.rpm mysql-community-libs-8.4.2-1.el9.x86_64.rpm mysql-community-libs-compat-8.4.2-1.el9.x86_64.rpm mysql-community-libs-compat-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-libs-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-server-8.4.2-1.el9.x86_64.rpm mysql-community-server-debug-8.4.2-1.el9.x86_64.rpm mysql-community-server-debug-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-server-debuginfo-8.4.2-1.el9.x86_64.rpm mysql-community-test-8.4.2-1.el9.x86_64.rpm mysql-community-test-debuginfo-8.4.2-1.el9.x86_64.rpm [liang@node2 mysql]$ rm -f *debug* [liang@node2 mysql]$ ls mysql-community-client-8.4.2-1.el9.x86_64.rpm mysql-community-libs-8.4.2-1.el9.x86_64.rpm mysql-community-client-plugins-8.4.2-1.el9.x86_64.rpm mysql-community-libs-compat-8.4.2-1.el9.x86_64.rpm mysql-community-common-8.4.2-1.el9.x86_64.rpm mysql-community-server-8.4.2-1.el9.x86_64.rpm mysql-community-devel-8.4.2-1.el9.x86_64.rpm mysql-community-test-8.4.2-1.el9.x86_64.rpm mysql-community-icu-data-files-8.4.2-1.el9.x86_64.rpm
安装mysql
安装命令
[liang@node2 mysql]$ sudo rpm -ivh *.rpm --force --nodeps
安装过程
警告：mysql-community-client-8.4.2-1.el9.x86_64.rpm: 头 V4 RSA/SHA256 Signature, 密钥 ID a8d3785c: NOKEY Verifying... ################################# [100%] 准备中... ################################# [100%] 正在升级/安装...1:mysql-community-common-8.4.2-1.el################################# [ 11%]2:mysql-community-client-plugins-8.################################# [ 22%]3:mysql-community-libs-8.4.2-1.el9 ################################# [ 33%]4:mysql-community-client-8.4.2-1.el################################# [ 44%]5:mysql-community-icu-data-files-8.################################# [ 56%]6:mysql-community-server-8.4.2-1.el################################# [ 67%]7:mysql-community-test-8.4.2-1.el9 ################################# [ 78%]8:mysql-community-devel-8.4.2-1.el9################################# [ 89%]9:mysql-community-libs-compat-8.4.2################################# [100%] /usr/lib/tmpfiles.d/dbus.conf:13: Line references path below legacy directory /var/run/, updating /var/run/dbus/containers → /run/dbus/containers; please update the tmpfiles.d/ drop-in file accordingly. [liang@node1 mysql]$
启动mysql服务
[liang@node2 mysql]$ sudo systemctl start mysqld
开机自启动mysql服务
[liang@node2 mysql]$ sudo systemctl enable mysqld
登录mysql
查看临时密码
[liang@node2 mysql]$ sudo grep 'temporary password' /var/log/mysqld.log
输出如下
2025-03-19T06:43:36.958044Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: tFggwGvsx8=j
这里查看到的临时密码为：tFggwGvsx8=j
注意：查看到的临时密码可能不一样，请使用实际查到的临时密码登录mysql。
登录mysql
[liang@node2 mysql]$ mysql -uroot -p'tFggwGvsx8=j' mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 8.4.2 Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit; Bye [liang@node2 mysql]$
修改mysql密码
修改密码策略
若在内网环境使用MySQL，想使用简单密码，则需要设置密码策略，否则，不需要设置密码策略。
[liang@node2 mysql]$ sudo vim /etc/my.cnf
在[mysqld]下面添加如下语句
validate_password.length=4 validate_password.policy=0
重启mysql
[liang@node2 mysql]$ sudo systemctl restart mysqld
登录mysql并修改密码
[liang@node2 mysql]$ mysql -uroot -p'tFggwGvsx8=j' mysql> set password='000000'; Query OK, 0 rows affected (0.00 sec) mysql> update mysql.user set host='%' where user='root'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> alter user 'root'@'%' identified by '000000'; Query OK, 0 rows affected (0.01 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> exit; Bye
使用新密码登录
[liang@node2 mysql]$ mysql -uroot -p000000 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9 Server version: 8.4.2 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> exit; Bye [liang@node2 mysql]$
远程连接mysql
使用navicat等工具，通过ip及端口号远程连接
安装Hive
在node2安装Hive
下载安装包
浏览器访问以下链接下载Hive安装包
https://archive.apache.org/dist/hive/hive-3.1.3/apache-hive-3.1.3-bin.tar.gz
上传Hive安装包到Linux /opt/software目录
[liang@node2 software]$ ls | grep hive apache-hive-3.1.3-bin.tar.gz
解压
解压
[liang@node2 software]$ tar -zxvf /opt/software/apache-hive-3.1.3-bin.tar.gz -C /opt/module/
重命名
[liang@node2 software]$ ls /opt/module/ apache-hive-3.1.3-bin hadoop-3.3.4 jdk1.8.0_271 [liang@node2 software]$ mv /opt/module/apache-hive-3.1.3-bin /opt/module/hive-3.1.3
设置环境变量
[liang@node2 software]$ sudo vim /etc/profile.d/my_env.sh
末尾添加如下内容
#HIVE_HOME
export HIVE_HOME=/opt/module/hive-3.1.3
export PATH=$PATH:$HIVE_HOME/bin让环境变量生效
[liang@node2 software]$ source /etc/profile
解决日志包冲突
解决日志Jar包依赖与Hadoop日志Jar版本不同冲突，重命名hive的日志包为其他名字，这样就只有hadoop的日志Jar包生效，冲突解决。
[liang@node2 software]$ cd $HIVE_HOME/lib/ [liang@node2 lib]$ ls | grep slf4j log4j-slf4j-impl-2.17.1.jar [liang@node2 lib]$ mv log4j-slf4j-impl-2.17.1.jar log4j-slf4j-impl-2.17.1.jar.bak
将mysql驱动拷贝到lib目录
浏览器访问如下地址，下载mysql驱动包
https://mvnrepository.com/artifact/com.mysql/mysql-connector-j/8.4.0
点击jar下载，并上传jar包文件到Linux /opt/software目录
[liang@node2 lib]$ ls /opt/software | grep connect mysql-connector-j-8.4.0.jar
将jar文件复制到HIVE_HOME的lib目录
[liang@node2 lib]$ cp /opt/software/mysql-connector-j-8.4.0.jar $HIVE_HOME/lib/ [liang@node2 lib]$ ls | grep connect mysql-connector-j-8.4.0.jar
配置Hive
切换到Hive配置目录，查看配置文件
[liang@node2 lib]$ cd $HIVE_HOME/conf [liang@node2 conf]$ ls beeline-log4j2.properties.template ivysettings.xml hive-default.xml.template llap-cli-log4j2.properties.template hive-env.sh.template llap-daemon-log4j2.properties.template hive-exec-log4j2.properties.template parquet-logging.properties hive-log4j2.properties.template
创建hive-site.xml
[liang@node2 conf]$ vim hive-site.xml
配置内容
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration><!--配置Hive保存元数据信息所需的 MySQL URL地址--><property><name>javax.jdo.option.ConnectionURL</name><value>jdbc:mysql://node2:3306/metastore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true</value></property><!--配置Hive连接MySQL的驱动全类名--><property><name>javax.jdo.option.ConnectionDriverName</name><value>com.mysql.cj.jdbc.Driver</value></property><!--配置Hive连接MySQL的用户名 --><property><name>javax.jdo.option.ConnectionUserName</name><value>root</value></property><!--配置Hive连接MySQL的密码 --><property><name>javax.jdo.option.ConnectionPassword</name><value>000000</value></property><property><name>hive.metastore.warehouse.dir</name><value>/user/hive/warehouse</value></property><property><name>hive.metastore.schema.verification</name><value>false</value></property><property><name>hive.server2.thrift.port</name><value>10000</value></property><property><name>hive.server2.thrift.bind.host</name><value>node2</value></property><property><name>hive.metastore.event.db.notification.api.auth</name><value>false</value></property><property><name>hive.cli.print.header</name><value>true</value></property><property><name>hive.cli.print.current.db</name><value>true</value></property>
</configuration>配置文件中连接nod2机器mysql的metastore数据库，所以需要先把metastore数据库创建出来。
[liang@node2 conf]$ mysql -uroot -p000000 mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 13 Server version: 8.4.2 MySQL Community Server - GPL Copyright (c) 2000, 2024, Oracle and/or its affiliates. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> create database metastore; Query OK, 1 row affected (0.01 sec) mysql> exit; Bye [liang@node1 conf]$
调整输出日志级别
默认日志级别为INFO，执行HQL语句过程会输出太多日志，将Hive输出日志级别改为WARN减少日志输出。
cd $HIVE_HOME/conf
执行如下命令新建log4j.properties
cat > log4j.properties <<EOL
log4j.rootLogger=WARN, CAlog4j.appender.CA=org.apache.log4j.ConsoleAppenderlog4j.appender.CA.layout=org.apache.log4j.PatternLayout
log4j.appender.CA.layout.ConversionPattern=%-4r [%t] %-5p %c %x - %m%n
EOL初始化Hive元数据库
[liang@node2 conf]$ schematool -initSchema -dbType mysql -verbose
末尾部分输出内容如下 0: jdbc:mysql://node2:3306/metastore> !closeall Closing: 0: jdbc:mysql://node2:3306/metastore?useSSL=false&useUnicode=true&characterEncoding=UTF-8&allowPublicKeyRetrieval=true beeline> beeline> Initialization script completed schemaTool completed
看到schemaTool completed输出，说明初始化成功。
初始化本质是在元数据库里创建出相关的表及初始化相关表数据，可以到mysql的metastore数据库查看生成的表数据。
修改元数据库字符集
[liang@node2 conf]$ mysql -uroot -p000000 ... mysql> use metastore; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> alter table COLUMNS_V2 modify column COMMENT varchar(256) character set utf8; Query OK, 0 rows affected, 1 warning (0.03 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> alter table TABLE_PARAMS modify column PARAM_VALUE mediumtext character set utf8; Query OK, 0 rows affected, 1 warning (0.02 sec) Records: 0 Duplicates: 0 Warnings: 1 mysql> quit; Bye [liang@node2 conf]$
进入Hive命令行
进入Hive命令行之前，需要启动hadoop，如果还没启动，需要先启动hadoop
# 分别启动 [liang@node2 conf]$ start-dfs.sh [liang@node3 conf]$ start-yarn.sh # 或者使用脚本启动 [liang@node2 conf]$ hdp.sh start
进入hive命令行
[liang@node2 conf]$ hive which: no hbase in (/usr/local/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/opt/module/jdk1.8.0_271/bin:/opt/module/hadoop-3.3.4/bin:/opt/module/hadoop-3.3.4/sbin:/home/liang/bin:/opt/module/jdk1.8.0_271/bin:/opt/module/hadoop-3.3.4/bin:/opt/module/hadoop-3.3.4/sbin:/home/liang/bin:/opt/module/jdk1.8.0_271/bin:/opt/module/hadoop-3.3.4/bin:/opt/module/hadoop-3.3.4/sbin:/opt/module/hive-3.1.3/bin) Hive Session ID = a2b0c24d-7c45-4e95-9c86-1108b2606f3b Logging initialized using configuration in jar:file:/opt/module/hive-3.1.3/lib/hive-common-3.1.3.jar!/hive-log4j2.properties Async: true Hive-on-MR is deprecated in Hive 2 and may not be available in the future versions. Consider using a different execution engine (i.e. spark, tez) or using Hive 1.X releases. Hive Session ID = 5d3c533a-79e3-4690-b54c-1006d18d7aca hive (default)>
简单使用Hive
查看数据库
hive (default)> show databases; OK database_name default Time taken: 0.609 seconds, Fetched: 1 row(s)
查看数据表
hive (default)> show tables; OK tab_name Time taken: 0.051 seconds
创建表
hive (default)> CREATE TABLE IF NOT EXISTS test_students (id INT,name STRING,age INT ) ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';
插入数据
hive (default)> INSERT INTO TABLE test_students VALUES (103, 'Bob', 21);
查看表数据
hive (default)> select * from test_students;
退出Hive命令行
hive (default)> quit; [liang@node1 conf]$
完成！enjoy it！