Hive教程（08）- JDBC操作Hive

beeline
!connect jdbc:hive2://localhost:10001

create database if not exists company_db;
use company_db;

<?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>com.ylw</groupId>
    <artifactId>java-hive-demo</artifactId>
    <version>1.0.0</version>
    <packaging>jar</packaging>
    <developers>
        <developer>
            <name>Yang Lin Wei</name>
            <url>https://yanglinwei.blog.csdn.net/</url>
        </developer>
    </developers>
    <dependencies>
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>2.3.3</version>
            <exclusions>
                <exclusion>
                    <groupId>org.eclipse.jetty.aggregate</groupId>
                    <artifactId>*</artifactId>
                </exclusion>
            </exclusions>
        </dependency>
    </dependencies>
</project>

package com.ylw.constant;
/**
 * 常量定义
 *
 * @author : YangLinWei
 * @createTime: 2022/2/23 9:23 上午
 */
public interface Const {
    /*** hive服务器地址 **/
    String HIVE_DB_URL = "jdbc:hive2://127.0.0.1:10001/company_db";
    /*** hive登录账号 **/
    String USER_NAME = "root";
    /*** hive登录密码 **/
    String PASSWORD = "123";
}

package com.ylw.util;
import com.ylw.constant.Const;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
/**
 * Hive连接工具类
 *
 * @author : YangLinWei
 * @createTime: 2022/2/23 9:22 上午
 */
public class HiveConnect {
    private static Connection connection = null;
    /**
     * 获取hive连接单例
     *
     * @return hive连接单例
     */
    public static Connection getConnection() {
        if (null == connection) {
            synchronized (HiveConnect.class) {
                if (null == connection) {
                    try {
                        Class.forName("org.apache.hive.jdbc.HiveDriver");
                        HiveConnect.connection = DriverManager.getConnection(Const.HIVE_DB_URL, Const.USER_NAME, Const.PASSWORD);
                    } catch (SQLException e) {
                        e.printStackTrace();
                    } catch (ClassNotFoundException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        return connection;
    }
}

public static boolean createEmployeeTable() throws SQLException {
        String sql = "CREATE TABLE IF NOT EXISTS " +
                "employee(name string," +
                "work_place array<string>," +
                "sex_age struct<sex:string, age:int>," +
                "score map<string, int>," +
                "depart_title map<string, string>) " +
                "row format delimited " +
                "fields terminated by '|' " +
                "collection items terminated by ',' " +
                "map keys terminated by ':'" +
                "lines terminated by '\n' " +
                "stored as textfile";
        Statement statement = HiveConnect.getConnection().createStatement();
        return statement.execute(sql);
}

Michael|Montreal,Toronto|Male,30|DB:80|Product:Developer
Will|Montreal|Male,35|Perl:85|Product:Lead,Test:Lead
Shelley|New York|Female,27|Python:80|Test:Lead,COE:Architect
Lucy|Vancouver|Female,57|Sales:89,HR:94|Sales:Lead

public static boolean loadData() throws SQLException {
     String loadSql = "load data local inpath '本地路径/employ.txt' into table employee";
     Statement statement = HiveConnect.getConnection().createStatement();
     return statement.execute(loadSql);
 }

public static void query() throws SQLException {
    String sql = "select name ,work_place[0] from employee";
    PreparedStatement pstm = HiveConnect.getConnection().prepareStatement(sql);
    ResultSet rs = pstm.executeQuery(sql);
    while (rs.next()) {
        System.out.println(rs.getString(1) + "  " + rs.getString(2));
    }
    pstm.close();
    rs.close();
}