JavaAPI操作Hive

文章目录

提示：以下是本篇文章正文内容

一、准备工作

启动好Hadoop集群，已安装hive。
接下来启动hive。

1. 启动元数据MetaStore

hive --service metastore

1. 启动HiveServer服务

hive --service hiveserver2

我自己启动的是HiveServer2服务， hiveserver默认端口是10000，之后的url填写的端口号就是这个。
hiveserver和hiveserver2的区别：

• 启动服务不一样

HiveServer—>hive --service hiveserver
HiveServer2—>hive --service hiveserver2

• 驱动名不一样

HiveServer—>org.apache.hadoop.hive.jdbc.HiveDriver
HiveServer2—>org.apache.hive.jdbc.HiveDriver

• url不一样

HiveServer—>jdbc:hive://<host>:10000"
HiveServer2—>jdbc:hive2://<host>:10000"

二、版本

Hadoop 2.6.0
hive 1.2.1

二、使用步骤

1.pom.xml文件

内容如下：

<?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>testToHDFSJavaAPI</artifactId><version>1.0-SNAPSHOT</version><properties><maven.compiler.source>8</maven.compiler.source><maven.compiler.target>8</maven.compiler.target></properties><dependencies><dependency><groupId>org.apache.hbase</groupId><artifactId>hbase-client</artifactId><version>1.3.6</version></dependency><dependency><groupId>org.apache.hbase</groupId><artifactId>hbase-server</artifactId><version>1.3.6</version></dependency><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-common</artifactId><version>2.6.5</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-client --><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-client</artifactId><version>2.6.5</version></dependency><!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-hdfs --><dependency><groupId>org.apache.hadoop</groupId><artifactId>hadoop-hdfs</artifactId><version>2.6.5</version></dependency><dependency><groupId>junit</groupId><artifactId>junit</artifactId><version>RELEASE</version></dependency><dependency><groupId>commons-logging</groupId><artifactId>commons-logging</artifactId><version>1.2</version></dependency><dependency><groupId>log4j</groupId><artifactId>log4j</artifactId><version>1.2.17</version></dependency><dependency><groupId>org.apache.hive</groupId><artifactId>hive-jdbc</artifactId><version>1.2.1</version></dependency></dependencies></project>

主要还是引入的hive依赖包：

<dependency><groupId>org.apache.hive</groupId><artifactId>hive-jdbc</artifactId><version>1.2.1</version></dependency>

这个依赖包的版本要和自己的hive版本对应，要不然后面会报错，之后会提。

2.JDBC操作

代码如下：

package test.Hive;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;

import java.sql.SQLException;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.DriverManager;

/**
 * @author Hanhan_Jun
 * @date 2022年09月21日 14:24
 */
public class utils {
    private static String driverName = "org.apache.hive.jdbc.HiveDriver";
    private Connection con;
    private ResultSet res;
    private Statement stmt;

    @Before
    public void before() throws SQLException, ClassNotFoundException{
        Class.forName(driverName);
        // get connection
        con = DriverManager.getConnection("jdbc:hive2://master:10000/default", "root", "root");
        stmt = con.createStatement();
    }

    //创建数据库
    @Test
    public void createDatabase() throws SQLException {
        stmt.execute("CREATE DATABASE userdb");
        System.out.println("Database userdb created successfully.");
    }

    //删除所有的数据库
    @Test
    public void dropDatabase() throws SQLException {
        stmt.execute("DROP DATABASE userdb");
        System.out.println("Drop userdb database successful");
    }

    //显示所有的数据库
    @Test
    public void showDataBases() throws SQLException {
        String sql="show databases";
        res=stmt.executeQuery(sql);
        while(res.next()){
            String name = res.getString(1);
            System.out.println(name);
        }
    }

    //创建表
    @Test
    public void createTable() throws SQLException {
        String sql = "create table tidanic2(" +
                "passengerid int,survived int," +
                "pclass int,name string," +
                "sex string,age int," +
                "sibSp int,parch int," +
                "ticket string,fare double," +
                "cabin string,embarked string )" +
                "row format delimited fields terminated by ','";
        stmt.execute(sql);

        System.out.println("Table created.");
    }

    //查询所有表
    @Test
    public void showTables() throws SQLException {
        res=stmt.executeQuery("show tables");
        while (res.next()){
            String tableName = res.getString(1);
            System.out.println(tableName);
        }
    }

    //删除表
    @Test
    public void dropTable() throws SQLException {
        stmt.execute("drop table if exists tidanic2");
        System.out.println("Drop table successful.");
    }

    // 加载数据
    @Test
    public void loadData() throws Exception {
        String filePath = "/home/hadoop/data/tidanic.txt";
        String sql = "load data local inpath '" + filePath + "' overwrite into table tidanic";
        stmt.execute(sql);
        System.out.println("Load successful.");
    }

    // 统计查询
    @Test
    public void countData() throws Exception {
        //统计数据有几行
        String sql = "select count(1) from timetest";
        res = stmt.executeQuery(sql);
        while (res.next()) {
            System.out.println(res.getInt(1) );
        }
    }

    //查询
    @Test
    public void selectTable() throws SQLException {
        Statement stmt = con.createStatement();
        ResultSet res = stmt.executeQuery("select * from timetest");
        while (res.next()) {
            System.out.println(res.getString(1));
        }
        System.out.println("select successfully.");
    }

    @After
    public void after() throws SQLException, ClassNotFoundException{
        if ( res != null) {
            res.close();
        }
        if (stmt != null) {
            stmt.close();
        }
        if (con!= null) {
            con.close();
        }

    }
}

遇到的报错

1. org.apache.thrift.TApplicationException: Required field 'client_protocol' is unset! 解决方法： 这是因为我引入的jar包，一开始是2.3.0的，后面改成自己hive1.2.1的版本，重新导入pom，解决。
2. java.sql.SQLException: The query did not generate a result set! 解决方法： 这是因为我执行sql语句的时候使用的是：

 stmt.executeQuery("DROP DATABASE userdb");

而executeQuery只适用于增删改查等DML语句，对于use db，create/drop db等DDL需调用execute()来执行。