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 '	';

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)