Hadoop 之 Hive 4.0.0-alpha-2 搭建（八）

系统管理员 2023-10-14 07:29 52阅读 0赞

### Hadoop 之 Hive 搭建与使用 ###

*  一.Hive 简介
 *  二.Hive 搭建
 *   *  1.下载
     *  2.安装
     *   *  1.解压并配置 HIVE
         *  2.修改 hive-site.xml
         *  3.修改 hadoop 的 core-site.xml
         *  4.启动
 *  三.Hive 测试
 *   *  1.基础测试
     *  2.建库建表
     *  3.Java 连接测试
     *   *  1.Pom依赖
         *  2.Yarm 配置文件
         *  3.启动类
         *  4.配置类
         *  5.测试类

# 一.Hive 简介 #

Hive 是基于 Hadoop 的数据仓库工具，可以提供类 SQL 查询能力

# 二.Hive 搭建 #

## 1.下载 ##

[Hive 官网][Hive]

[Hive 下载地址（自选版本）][Hive 1]

![在这里插入图片描述][acc80666d13d48578d59aaf0a7def2ae.png]

[MySQL Java 驱动下载][MySQL Java]

![在这里插入图片描述][d2790f3a77d74b9684f15460a0681804.png]

## 2.安装 ##

### 1.解压并配置 HIVE ###

## 1.创建安装目录
    mkdir -p /usr/local/hive
    ## 2.将压缩包拷贝到服务器并解压
    tar zxvf apache-hive-4.0.0-alpha-2-bin.tar.gz -C /usr/local/hive/
    ## 3.添加环境变量并刷新
    echo 'export HIVE_HOME=/usr/local/hive/apache-hive-4.0.0-alpha-2-bin' >> /etc/profile
    echo 'export PATH=${HIVE_HOME}/bin:${PATH}' >> /etc/profile
    source /etc/profile
    ## 4.进入安装目录
    cd $HIVE_HOME/conf
    ## 5.复制 hive-env.sh.template 并修改配置
    cp hive-env.sh.template hive-env.sh
    echo 'export JAVA_HOME=/usr/local/java/jdk-11.0.19' >> hive-env.sh
    echo 'export HADOOP_HOME=/usr/local/hadoop/hadoop-3.3.6' >> hive-env.sh
    echo 'export HADOOP_CONF_DIR=${HADOOP_HOME}/etc/hadoop' >> hive-env.sh
    echo 'export HIVE_HOME=/usr/local/hive/apache-hive-4.0.0-alpha-2-bin' >> hive-env.sh
    echo 'export HIVE_CONF_DIR=${HIVE_HOME}/conf' >> hive-env.sh
    echo 'export HIVE_AUX_JARS_PATH=${HIVE_HOME}/lib' >> hive-env.sh
    ## 6.复制 mysql-connector-j-8.0.33.jar 到 ${HIVE_HOME}/lib
    tar zxvf mysql-connector-j-8.0.33.tar.gz
    cp mysql-connector-j-8.0.33/mysql-connector-j-8.0.33.jar ${
        HIVE_HOME}/lib/
    ## 7.复制 cp hive-default.xml.template 并修改配置
    cp hive-default.xml.template hive-site.xml

### 2.修改 hive-site.xml ###

使用 MobaXterm 连接虚拟机，并用文本编辑工具打开 hive-site.xml 修改：

![在这里插入图片描述][75bcfdffcc2c4bec9de0d17080b1ae94.png]

注释掉同名的默认配置，或参考下面信息，直接修改默认配置值
    同时全局替换下面两个变量值，避免 hiveserver2 启动报错
    1.${system:java.io.tmpdir} => /tmp （Linux 系统默认的临时目录）
    2.${system:user.name}      => root （本系统当前操作用户名）
    3.hive.server2.thrift.client.user 配置用户 root , 同时需要修改 hadoop 的 core-site.xml 为 root  配置代理信息
    4.hadoop 集群搭建参考前面文章

<property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.1.201:3306/hive?characterEncoding=UTF8&createDatabaseIfNotExist=true&serverTimezone=GMT%2B8&useSSL=false&allowPublicKeyRetrieval=true</value>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.cj.jdbc.Driver</value>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>admin</value>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>12345678</value>
      </property>
      <property>
        <name>datanucleus.schema.autoCreateAll</name>
        <value>true</value>
      </property>
      
      <property>
        <name>hive.server2.thrift.bind.host</name>
        <value>nn</value>
      </property>
      <property>
        <name>hive.server2.thrift.client.user</name>
        <value>root</value>
        <description>Username to use against thrift client. default is 'anonymous'</description>
      </property>
      <property>
        <name>hive.server2.thrift.client.password</name>
        <value>123456</value>
        <description>Password to use against thrift client. default is 'anonymous'</description>
      </property> 
      <property>
        <name>hive.metastore.event.db.notification.api.auth</name>
        <value>false</value>
      </property>
      <property>
        <name>hive.server2.active.passive.ha.enable</name>
        <value>true</value>
      </property>
    
      <property>
        <name>hive.metastore.warehouse.dir</name>
        <value>hdfs://nn:9000/user/hive/warehouse</value>
        <description>hdfs 地址</description>
      </property>
      <property>
        <name>hive.metastore.schema.verification</name>
        <value>true</value>
      </property>
      <property>
        <name>hive.metastore.uris</name>
        <value>thrift://nn:9083</value>
      </property>

初始化元数据

![在这里插入图片描述][29155562d3c644b5b5a968f419440b5d.png]

查看库 Hive

![在这里插入图片描述][7aaf2d08a2274ba7bc5a162512ea4d0d.png]

### 3.修改 hadoop 的 core-site.xml ###

## 1.进入配置目录
    cd $HADOOP_HOME//etc/hadoop
    ## 2.修改 core-site.xml
    vim core-site.xml
    ## 3.增加以下内容
        <property>
          <name>hadoop.proxyuser.root.hosts</name>
          <value>*</value>
        </property>
        <property>
         <name>hadoop.proxyuser.root.groups</name>
         <value>*</value>
        </property>

![在这里插入图片描述][d54a6d5cf1eb42a4917f282a5b93ffd3.png]

### 4.启动 ###

## 1.初始化
    schematool -initSchema -dbType mysql
    ## 2.启动 hadoop 集群
    cd $HADOOP_HOME/sbin && start-all.sh
    ## 3.启动 hive
    mkdir -p /var/log/hive
    cd ${
        HIVE_HOME}/bin
    nohup hive --service metastore 2>&1 >> /var/log/hive/metastore.log &
    nohup hive --service hiveserver2 2>&1 >> /var/log/hive/hiveserver2.log &
    
    ## 4.杀死 hive 进程
    kill -9 `ps aux | grep hiveserver2 | grep -v grep | awk '{print $2}'`
    kill -9 `ps aux | grep metastore | grep -v grep | awk '{print $2}'`

查看 Java 进程：jps

![在这里插入图片描述][d23c0a0b5a7243f5a656b50f2cdbe338.png]

查看端口占用：lsof -i -P -n | grep LISTEN

![在这里插入图片描述][46ed78f39f5543b0b44c402ef26ec0f6.png]

查看默认日志：tail -200f /tmp/root/hive.log

![在这里插入图片描述][e59ea4fdc6464f488ab22de636139c1a.png]

日志发现一个报错：java.sql.SQLException: Referencing column 'ACTIVE_EXECUTION_ID' and referenced column 'SCHEDULED_EXECUTION_ID' in foreign key constraint 'SCHEDULED_EXECUTIONS_SCHQ_ACTIVE' are incompatible.
    因为 MySQL 版本为 8.0.33，可能导致了主外键字段类型不一致时的异常
    对应初始化后的表和字段分别为：
    SCHEDULED_EXECUTIONS - SCHEDULED_EXECUTION_ID
    SCHEDULED_QUERIES    - ACTIVE_EXECUTION_ID
    原来这两个字段一个为 int ，一个为 bigint 统一为 bigint 后就不报错了

# 三.Hive 测试 #

## 1.基础测试 ##

## 1.控制台连接
    hive
    ## 2.连接并输入用户名、密码
    !connect jdbc:hive2://nn:10000
    ## 3.查看库
    show databases;
    ## 4.退出
    !quit

![在这里插入图片描述][e4745559be6c48b0b5366e32ba7a96c2.png]

UI 访问：http://192.168.1.6:10002/

![在这里插入图片描述][4856e104a1b14eb0b2dd580c8e9e7146.png]

hdfs 查看: http://192.168.1.6:9870/explorer.html#/tmp/hive/root

![在这里插入图片描述][a98bb1085601442f817d0f6c1141da40.png]

## 2.建库建表 ##

## 1.建库并设置存储位置
    create database if not exists animal_db
    comment "This is animal database"
    location '/hive_database/animal_db';
    ## 2.查看库
    show databases;
    ## 3.切换库
    use animal_db;
    ## 4.创建表
    create table if not exists dog_tb(name string,breed string,area string,feature string)
    row format delimited fields terminated by ';';
    ## 5.从本地文件加载数据
    load data local inpath '/home/dog_tb.txt' into table dog_tb;
    ## 6.查看数据
    select * from dog_tb;
    ## 7.删除表
    drop table dog_tb;
    ## 7.强制删库
    drop database animal_db cascade ;

![在这里插入图片描述][d3434a892bbb47eb9f6cd44c33ea3026.png]

![在这里插入图片描述][e94b05da54a5448fb63e8223dab2a5a5.png]

dog_tb.txt 
    bomei;small;germany;white,small
    bianmu;big;scotland;clever
    tugou;all;china;clever,loyalty

## 3.Java 连接测试 ##

### 1.Pom依赖 ###

<?xml version="1.0" encoding="UTF-8"?>
    <project xmlns="http://maven.apache.org/POM/4.0.0"
             xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
             xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/xsd/maven-4.0.0.xsd">
        <modelVersion>4.0.0</modelVersion>
    
        <groupId>org.example</groupId>
        <artifactId>hive-demo</artifactId>
        <version>1.0-SNAPSHOT</version>
    
        <properties>
            <maven.compiler.source>11</maven.compiler.source>
            <maven.compiler.target>11</maven.compiler.target>
            <spring.version>2.7.8</spring.version>
            <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
        </properties>
    
        <dependencies>
            <dependency>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-starter-web</artifactId>
                <version>${spring.version}</version>
            </dependency>
            <dependency>
                <groupId>org.projectlombok</groupId>
                <artifactId>lombok</artifactId>
                <version>1.18.28</version>
            </dependency>
            <dependency>
                <groupId>com.alibaba</groupId>
                <artifactId>fastjson</artifactId>
                <version>2.0.32</version>
            </dependency>
    
            <dependency>
                <groupId>org.apache.hive</groupId>
                <artifactId>hive-jdbc</artifactId>
                <version>4.0.0-alpha-2</version>
            </dependency>
            <dependency>
                <groupId>org.apache.hadoop</groupId>
                <artifactId>hadoop-common</artifactId>
                <version>3.3.6</version>
            </dependency>
    
    
        </dependencies>
    
    </project>

### 2.Yarm 配置文件 ###

config:
        hivedriverClassName: org.apache.hive.jdbc.HiveDriver
        hiveurl: jdbc:hive2://192.168.1.6:10000/animal_db
        hiveusername: root
        hivepassword: 123456

### 3.启动类 ###

package org.example;
    
    import org.springframework.boot.SpringApplication;
    import org.springframework.boot.autoconfigure.SpringBootApplication;
    
    /**
     * @author Administrator
     */
    @SpringBootApplication
    public class HiveApp {
        
    
        public static void main(String[] args) {
        
            //启动触发
            SpringApplication.run(HiveApp.class,args);
        }
    }

### 4.配置类 ###

package org.example.config;
    
    import org.springframework.beans.factory.annotation.Qualifier;
    import org.springframework.boot.context.properties.ConfigurationProperties;
    import org.springframework.context.annotation.Bean;
    import org.springframework.stereotype.Component;
    
    import java.sql.Connection;
    import java.sql.DriverManager;
    import java.util.Properties;
    
    /**
     * @author Administrator
     * @Description
     * @create 2023-08-02 21:42
     */
    @Component
    public class HiveConfig {
        
    
        @Bean("hiveProperties")
        @ConfigurationProperties(prefix = "config")
        public Properties getConfig(){
        
            return new Properties();
        }
    
        @Bean
        public Connection start(@Qualifier("hiveProperties") Properties properties){
        
            try {
        
                String url = (String) properties.get("hiveurl");
                String user = (String) properties.get("hiveusername");
                String password = (String) properties.get("hivepassword");
                Connection conn = DriverManager.getConnection(url,user,password);
                conn.setAutoCommit(true);
                return conn;
            } catch (Exception e) {
        
                System.out.println(e);
            }
            return null;
        }
    }

### 5.测试类 ###

package org.example.controller;
    
    import lombok.extern.slf4j.Slf4j;
    import org.apache.commons.lang.StringUtils;
    import org.apache.hive.jdbc.HivePreparedStatement;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.web.bind.annotation.GetMapping;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RestController;
    
    import java.sql.Connection;
    import java.sql.ResultSet;
    import java.sql.SQLException;
    import java.util.ArrayList;
    import java.util.Arrays;
    import java.util.List;
    
    /**
     * @author Administrator
     * @Description
     * @create 2023-08-02 21:42
     */
    @Slf4j
    @RestController
    @RequestMapping("/hive")
    public class HiveController {
        
    
        /**
         * 注入连接类
         */
        @Autowired
        Connection conn;
    
        /**
         * 列
         */
        List<String> columns = Arrays.asList("name","breed","area","feature");
    
        /**
         * 插入
         * @throws SQLException
         */
        @GetMapping("/insert")
        public void insert() throws SQLException {
        
            String sql = "insert into dog_tb values (?,?,?,?)";
            HivePreparedStatement pStSm= (HivePreparedStatement) conn.prepareStatement(sql);
            pStSm.setString(1, "keji");
            pStSm.setString(2, "small");
            pStSm.setString(3, "welsh");
            pStSm.setString(4, "friendly");
            pStSm.executeUpdate();
    
        }
    
        /**
         * 查询
         * @return
         * @throws SQLException
         */
        @GetMapping("/query")
        public List<String> query(int index,String value) throws SQLException {
        
            List<String> list = new ArrayList<>();
            String sql = "select * from dog_tb";
            if (index > 0 && StringUtils.isNotEmpty(value)){
        
                sql = "select * from dog_tb where name = ?";
            }
            HivePreparedStatement pStSm= (HivePreparedStatement) conn.prepareStatement(sql);
            if (index >= 0 && StringUtils.isNotEmpty(value)){
        
                pStSm.setString(index, value);
            }
            ResultSet resultSet = pStSm.executeQuery();
            StringBuilder builder = new StringBuilder();
            while (resultSet.next()){
        
                builder.setLength(0);
                for (String col:columns){
        
                    builder.append(resultSet.getString(col)).append(";");
                }
                String result = builder.substring(0,builder.length()-1);
                list.add(result);
                log.info("row: {}",result);
            }
            return list;
        }
    
    }

![在这里插入图片描述][e8e0d41a40b740fcb6fbf71639aaf3d9.png]  
![在这里插入图片描述][e55597bff2d544999190179a5645f232.png]

如果插入或查询报错可通过如下位置查询报错信息

![在这里插入图片描述][d36caa879111425f939c5f72ab4de7b5.png]

[Hive]: https://hive.apache.org/
[Hive 1]: https://dlcdn.apache.org/hive/
[acc80666d13d48578d59aaf0a7def2ae.png]: https://img-blog.csdnimg.cn/acc80666d13d48578d59aaf0a7def2ae.png
[MySQL Java]: https://downloads.mysql.com/archives/c-j/
[d2790f3a77d74b9684f15460a0681804.png]: https://img-blog.csdnimg.cn/d2790f3a77d74b9684f15460a0681804.png
[75bcfdffcc2c4bec9de0d17080b1ae94.png]: https://img-blog.csdnimg.cn/75bcfdffcc2c4bec9de0d17080b1ae94.png
[29155562d3c644b5b5a968f419440b5d.png]: https://img-blog.csdnimg.cn/29155562d3c644b5b5a968f419440b5d.png
[7aaf2d08a2274ba7bc5a162512ea4d0d.png]: https://img-blog.csdnimg.cn/7aaf2d08a2274ba7bc5a162512ea4d0d.png
[d54a6d5cf1eb42a4917f282a5b93ffd3.png]: https://img-blog.csdnimg.cn/d54a6d5cf1eb42a4917f282a5b93ffd3.png
[d23c0a0b5a7243f5a656b50f2cdbe338.png]: https://img-blog.csdnimg.cn/d23c0a0b5a7243f5a656b50f2cdbe338.png
[46ed78f39f5543b0b44c402ef26ec0f6.png]: https://img-blog.csdnimg.cn/46ed78f39f5543b0b44c402ef26ec0f6.png
[e59ea4fdc6464f488ab22de636139c1a.png]: https://img-blog.csdnimg.cn/e59ea4fdc6464f488ab22de636139c1a.png
[e4745559be6c48b0b5366e32ba7a96c2.png]: https://img-blog.csdnimg.cn/e4745559be6c48b0b5366e32ba7a96c2.png
[4856e104a1b14eb0b2dd580c8e9e7146.png]: https://img-blog.csdnimg.cn/4856e104a1b14eb0b2dd580c8e9e7146.png
[a98bb1085601442f817d0f6c1141da40.png]: https://img-blog.csdnimg.cn/a98bb1085601442f817d0f6c1141da40.png
[d3434a892bbb47eb9f6cd44c33ea3026.png]: https://img-blog.csdnimg.cn/d3434a892bbb47eb9f6cd44c33ea3026.png
[e94b05da54a5448fb63e8223dab2a5a5.png]: https://img-blog.csdnimg.cn/e94b05da54a5448fb63e8223dab2a5a5.png
[e8e0d41a40b740fcb6fbf71639aaf3d9.png]: https://img-blog.csdnimg.cn/e8e0d41a40b740fcb6fbf71639aaf3d9.png
[e55597bff2d544999190179a5645f232.png]: https://img-blog.csdnimg.cn/e55597bff2d544999190179a5645f232.png
[d36caa879111425f939c5f72ab4de7b5.png]: https://img-blog.csdnimg.cn/d36caa879111425f939c5f72ab4de7b5.png