java通过jdbc实现从clickhouse导数据到hive

最新推荐文章于 2024-04-04 21:07:05 发布

天有不测阳光

最新推荐文章于 2024-04-04 21:07:05 发布

阅读量413

点赞数 10

文章标签： java clickhouse hive

本文链接：https://blog.csdn.net/Master_Yiran/article/details/135201910

版权

package org.example;

import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.UUID;

public class Main {
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        // 加载ClickHouse的JDBC驱动程序
        Class.forName("com.clickhouse.jdbc.ClickHouseDriver");
        // 加载Hive的JDBC驱动程序
        Class.forName("org.apache.hive.jdbc.HiveDriver");

        // 创建ClickHouse数据库连接
        String clickHouseUrl = "jdbc:clickhouse://xx.xxx.x.xx:xxxx/xxx";
        String clickHouseUser = "xxx";
        String clickHousePassword = "xxx";
        Connection clickHouseConnection = DriverManager.getConnection(clickHouseUrl, clickHouseUser, clickHousePassword);
        System.out.println("连接ck");
        // 创建Hive数据库连接
        String hiveUrl = "jdbc:hive2://xxx:10000/default";
        String hiveUser = "xxxxx";
        String hivePassword = "xxxxx";
        Connection hiveConnection = DriverManager.getConnection(hiveUrl, hiveUser, hivePassword);
        System.out.println("连接hive");
        // 创建Statement对象
        Statement clickHouseStatement = clickHouseConnection.createStatement();
        Statement hiveStatement = hiveConnection.createStatement();

        // 定义目标Hive表名
        String hiveTableName = "cthtest";

        // 定义创建Hive表的查询语句
        String createTableQuery = "CREATE TABLE IF NOT EXISTS " + hiveTableName + " (assy_id STRING, removed STRING)";

        // 执行创建Hive表的语句
        hiveStatement.execute(createTableQuery);

        // 定义起始日期和结束日期
        String start_date = "2023-12-20";
        String end_date = "2023-12-23";

        // 循环遍历日期范围
        while (start_date.compareTo(end_date) <= 0) {
            System.out.println(start_date);
            // 执行ClickHouse SQL查询语句
            String sql = "SELECT * FROM xxx ";
            ResultSet resultSet = clickHouseStatement.executeQuery(sql);
            System.out.println(resultSet);
            // 创建临时表
            String tempTableName = "temp_" + UUID.randomUUID().toString().replace("-", "");
            String createTempTableQuery = "CREATE TABLE " + tempTableName + " (assy_id STRING, removed STRING)";
            hiveStatement.execute(createTempTableQuery);
            System.out.println(createTempTableQuery);
            // 将数据插入临时表
            String insertTempTableQuery = "INSERT INTO " + tempTableName + " VALUES (?, ?)";
            PreparedStatement tempTableInsertStatement = hiveConnection.prepareStatement(insertTempTableQuery);

            while (resultSet.next()) {
                String assy_id = resultSet.getString("assy_id");
                String removed = resultSet.getString("removed");
                tempTableInsertStatement.setString(1, assy_id);
                tempTableInsertStatement.setString(2, removed);
                tempTableInsertStatement.executeUpdate();
//                tempTableInsertStatement = (PreparedStatement) tempTableInsertStatement;
//                tempTableInsertStatement.addBatch();
//                break;
            }
            tempTableInsertStatement.executeBatch();

            // 将临时表数据插入目标表
            String insertIntoTargetTableQuery = "INSERT INTO " + hiveTableName + " SELECT * FROM " + tempTableName;
            hiveStatement.executeUpdate(insertIntoTargetTableQuery);

            // 删除临时表
            String dropTempTableQuery = "DROP TABLE " + tempTableName;
            hiveStatement.execute(dropTempTableQuery);

            // 关闭ResultSet对象
            resultSet.close();

            // 增加一天，更新 start_date
            start_date = addDays(start_date);
        }

        // 关闭Statement和Connection对象
        clickHouseStatement.close();
        clickHouseConnection.close();
        hiveStatement.close();
        hiveConnection.close();
    }

    // 辅助方法：增加一天
    public static String addDays(String date) {
        LocalDate localDate = LocalDate.parse(date);
        localDate = localDate.plusDays(1);
        return localDate.toString();
    }
}

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>ck_to_hive</artifactId>
    <version>1.0-SNAPSHOT</version>

    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
        <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
    </properties>
    <dependencies>
        <dependency>
            <groupId>com.clickhouse</groupId>
            <artifactId>clickhouse-jdbc</artifactId>
            <version>0.4.6</version>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hive/hive-jdbc -->
        <dependency>
            <groupId>org.apache.hive</groupId>
            <artifactId>hive-jdbc</artifactId>
            <version>1.1.0</version>
            <exclusions>
                <exclusion>
                    <groupId>org.slf4j</groupId>
                    <artifactId>slf4j-log4j12</artifactId>
                </exclusion>
            </exclusions>
        </dependency>

        <!-- https://mvnrepository.com/artifact/org.apache.hadoop/hadoop-common -->
        <dependency>
            <groupId>org.apache.hadoop</groupId>
            <artifactId>hadoop-common</artifactId>
            <version>3.3.6</version>
        </dependency>



        <dependency>
            <groupId>net.jpountz.lz4</groupId>
            <artifactId>lz4</artifactId>
            <version>1.3.0</version>
        </dependency>
    </dependencies>

</project>