linux hive +mysql（mysql用于hive元数据存储）
部署：hive
下载: wget http://mirror.bit.edu.cn/apache/hive/hive-3.1.2/apache-hive-3.1.2-bin.tar.gz
mv apache-hive-3.1.2-bin.tar.gz hive
配置环境变量：
# hive home
export HIVE_HOME=/data/projects/hive
export PATH=$PATH:$HIVE_HOME/bin
mysql安装：用户hive创建以及授权
create user 'hive' @'%' identified by ‘App@123456’;
create database hive charset utf8 ;
grant all privileges on *.* to 'hive'@'%';
flush privileges ;
1.修改hive配置
mv hive-env.sh.template hive-env.sh,添加hadoop home ，以及hive_conf home
#set hadoop master home by chen
export HADOOP_HOME=/data/projects/hadoop
# set hive conf home by chen
export HIVE_CONF_DIR=/data/projects/hive/conf
2.修改hive log4j
mv hive-log4j2.properties.template hive-log4j2.properties
3.修改日志保存位置：
# list of properties
property.hive.log.dir = /data/projects/hive/logs
4.修改hive-site.xml
mv hive-default.xml.template hive-site.xml
清空hive-site.xml 粘贴如下：
<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?><!--
Licensed to the Apache Software Foundation (ASF) under one or more
contributor license agreements. See the NOTICE file distributed with
this work for additional information regarding copyright ownership.
The ASF licenses this file to You under the Apache License, Version 2.0
(the "License"); you may not use this file except in compliance with
the License. You may obtain a copy of the License at
http://www.apache.org/licenses/LICENSE-2.0
Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
--><configuration>
<!-- hdfs存储目录 -->
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<!-- jdbc url配置 -->
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://192.168.110.151:3306/hive?createDatabaseIfNotExist=true</value>
</property>
<!-- 设置jdbc驱动 -->
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
</property>
<!-- jdbc连接用户名 -->
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hive</value>
</property>
<!-- jdbc连接密码-->
<property> <name>javax.jdo.option.ConnectionPassword</name> <value>App@123456</value> </property> </configuration>
数据库准备工作，安装mysql-connector-java ，先查看驱动jdbc需要版本
[hadoop@hadoop conf]$ mysqladmin --version
mysqladmin Ver 8.0.20 for Linux on x86_64 (MySQL Community Server - GPL)
然后去maven（https://mvnrepository.com/artifact/mysql/mysql-connector-java/8.0.20）下载：
wget https://repo1.maven.org/maven2/mysql/mysql-connector-java/8.0.20/mysql-connector-java-8.0.20.jar
移动 jar包 hive/lib
解决log4j 冲突：
SLF4J: Found binding in [jar:file:/data/projects/hive/lib/log4j-slf4j-impl-2.10.0.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/data/projects/hadoop/share/hadoop/common/lib/slf4j-log4j12-1.7.25.jar!/org/slf4j/impl/StaticLoggerBinder.class]
cd hive/lib && rm -f log4j-slf4j-impl-2.10.0.jar 删掉一个版本就可以了
最后初始hive元数据到mysql
cd hive/bin && ./schematool schematool -initSchema -dbType mysql
可能遇到问题 guava.jar hive 和hadoop guava.jar 版本不兼容
Exception in thread "main" java.lang.NoSuchMethodError: com.google.common.base.Preconditions.checkArgument(ZLjava/lang/String;Ljava/lang/Object;)V
解决办法将hadoop的share/hadoop/common/lib的 guava.jar 替换掉/hive/lib 下的guava.jar保持版本一致；
此外mysql.jdbc.driver过期问题：
Metastore connection URL: jdbc:mysql://192.168.110.151:3306/hive?createDatabaseIfNotExist=true Metastore Connection Driver : com.mysql.jdbc.Driver Metastore connection User: hive Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'. The driver is automatically registered via the SPI and manual loading of the driver class is generally unnecessary. Starting metastore schema initialization to 3.1.0 Initialization script hive-schema-3.1.0.mysql.sql Initialization script completed schemaTool completed
Loading class `com.mysql.jdbc.Driver'. This is deprecated. The new driver class is `com.mysql.cj.jdbc.Driver'.
用了最新的mysql 连接驱动出现的参考这个，可以不管自动会处理
application-dev.properties
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.jdbc.Driver
需要将 com.mysql.jdbc.Driver 改为 com.mysql.cj.jdbc.Driver
spring.datasource.url=jdbc:mysql://localhost:3306/test?serverTimezone=GMT%2B8 spring.datasource.username=root spring.datasource.password=root spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
验证元数据生成在mysql hive database:
mysql -u root -p
use hive ;
show tables ;
mysql> show tables ; +-------------------------------+ | Tables_in_hive | +-------------------------------+ | AUX_TABLE | | BUCKETING_COLS | | CDS | | COLUMNS_V2 | | COMPACTION_QUEUE | | COMPLETED_COMPACTIONS | | COMPLETED_TXN_COMPONENTS | | CTLGS | .......
.......
.......
启动hive:
创建库和表就可以愉快的玩耍了：
先创建一个toy库
drop database if exists toys; create database toys;
然后创建表
create table IF NOT EXISTS toys.emp( empno int, ename string, job string, mgr int, hiredate string, sal double, comm double, deptno int ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';
hive> use toys ;
OK
hive> desc emp ;
OK
empno int
ename string
job string
mgr int
hiredate string
sal double
comm double
deptno int
Time taken: 0.154 seconds, Fetched: 8 row(s)
