내 MariaDB에서 (mysql)

mysql -u root #hmaster1 에서 root 유저로 진행함

**USE test;

CREATE TABLE region (
    REGION_CODE CHAR(20) NOT NULL,
    REGION_TYPE CHAR(20) NOT NULL,
    REGION_NAME VARCHAR(20) NOT NULL,
    CHOSUNG_REGION_NAME CHAR(20) NOT NULL,
    LATITUDE DOUBLE NOT NULL,
    LONGITUDE DOUBLE NOT NULL,
    PRIMARY KEY (REGION_CODE)
);

INSERT INTO region (REGION_CODE, REGION_TYPE, REGION_NAME, CHOSUNG_REGION_NAME, LATITUDE, LONGITUDE) VALUES
('00000001', 'A', 'Seoul', 'S', 37.5665, 126.978),
('00000002', 'B', 'Busan', 'B', 35.1796, 129.0756),
('00000003', 'C', 'Daegu', 'D', 35.8714, 128.6014),
('00000004', 'D', 'Incheon', 'I', 37.4563, 126.7052),
('00000005', 'E', 'Gwangju', 'G', 35.1595, 126.8526);**

**USE test;

CREATE TABLE 20240709_1442 (
    REGION_CODE CHAR(20) NOT NULL,
    REGION_TYPE CHAR(20) NOT NULL,
    REGION_NAME VARCHAR(20) NOT NULL,
    CHOSUNG_REGION_NAME CHAR(20) NOT NULL,
    LATITUDE DOUBLE NOT NULL,
    LONGITUDE DOUBLE NOT NULL,
    PRIMARY KEY (REGION_CODE)
);

INSERT INTO 20240709_1442 (REGION_CODE, REGION_TYPE, REGION_NAME, CHOSUNG_REGION_NAME, LATITUDE, LONGITUDE) VALUES
('10000001', 'A', 'Seoul', 'S', 37.5665, 126.978),
('10000002', 'B', 'Busan', 'B', 35.1796, 129.0756),
('10000003', 'C', 'Daegu', 'D', 35.8714, 128.6014),
('10000004', 'D', 'Incheon', 'I', 37.4563, 126.7052),
('10000005', 'E', 'Gwangju', 'G', 35.1595, 126.8526);**

**# 자바 컴파일하기
javac -cp "/home/hive/hive-3.1.3/lib/*:/home/hive/openjdk/*" HiveExtable.java

하면 HiveExtable.class 나옴
그럼 아래 입력해서 실행~!!

#실행하기
java -cp ".:/home/hive/hive-3.1.3/lib/*:/home/hive/openjdk/*" HiveExtable**

mv log4j-slf4j-impl-2.17.1.jar.notused log4j-slf4j-impl-2.17.1.jar

HiveExtable.java

**import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class HiveExtable {
    public static void main(String[] args) {
        Connection sourceConn = null;
        Connection hiveConn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            // MariaDB 연결 정보 설정
            String sourceJdbcUrl = "jdbc:mariadb://hmaster1/test?useSSL=false&verifyServerCertificate=false";
            String sourceDbUser = "hive";
            String sourceDbPassword = ""; // 필요한 경우 비밀번호 추가

            Class.forName("org.mariadb.jdbc.Driver");
            sourceConn = DriverManager.getConnection(sourceJdbcUrl, sourceDbUser, sourceDbPassword);

            // 테이블에서 데이터 가져오기
            String fetchDataSQL = "SELECT * FROM region";
            pstmt = sourceConn.prepareStatement(fetchDataSQL);
            rs = pstmt.executeQuery();

            // Hive 연결 정보 설정
            String hiveJdbcUrl = "jdbc:hive2://hmaster1:10000/sungho";
            String hiveDbUser = "hive";
            String hiveDbPassword = ""; // 필요한 경우 비밀번호 추가

            Class.forName("org.apache.hive.jdbc.HiveDriver");
            hiveConn = DriverManager.getConnection(hiveJdbcUrl, hiveDbUser, hiveDbPassword);

            // Hive에 External 테이블 생성
            String createExternalTableSQL = "CREATE EXTERNAL TABLE IF NOT EXISTS TBL_ADDRESS (" +
                    "REGION_CODE CHAR(20), " +
                    "REGION_TYPE CHAR(20), " +
                    "REGION_NAME VARCHAR(20), " +
                    "CHOSUNG_REGION_NAME CHAR(20), " +
                    "LATITUDE DOUBLE, " +
                    "LONGITUDE DOUBLE) " +
                    "STORED AS PARQUET " +
                    "LOCATION 'hdfs://hmaster1:9000/shtest'";
            pstmt = hiveConn.prepareStatement(createExternalTableSQL);
            pstmt.executeUpdate();

            // 데이터를 External 테이블에 삽입
            String insertSQL = "INSERT INTO TBL_ADDRESS VALUES (?, ?, ?, ?, ?, ?)";
            pstmt = hiveConn.prepareStatement(insertSQL);

            while (rs.next()) {
                pstmt.setString(1, rs.getString("REGION_CODE"));
                pstmt.setString(2, rs.getString("REGION_TYPE"));
                pstmt.setString(3, rs.getString("REGION_NAME"));
                pstmt.setString(4, rs.getString("CHOSUNG_REGION_NAME"));
                pstmt.setDouble(5, rs.getDouble("LATITUDE")); // rs.setDouble -> rs.getDouble
                pstmt.setDouble(6, rs.getDouble("LONGITUDE")); // rs.setDouble -> rs.getDouble
                pstmt.executeUpdate();
            }

            System.out.println("Hive의 TBL_ADDRESS External 테이블에 데이터가 삽입되었습니다.");
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("Hive의 TBL_ADDRESS External 테이블에 데이터 삽입 실패하였습니다.");
        } finally {
            // ResultSet 해제
            if (rs != null) {
                try {
                    rs.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            // PreparedStatement 해제
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            // MariaDB 연결 해제
            if (sourceConn != null) {
                try {
                    sourceConn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            // Hive 연결 해제
            if (hiveConn != null) {
                try {
                    hiveConn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }
}**

하이브 string 검증위한코드

결론 :string 코드문제는 아니였다!