ClickHouse JDBC Driver

您可以通过开源的 ClickHouse JDBC 驱动程序连接到 ByteHouse 云数仓版。

前提条件

1. ClickHouse Client 版本： 0.4.2 及以上版本；
2. OpenJDK 版本： 8 及以上版本；
3. 拥有 ByteHouse 账号。

安装流程

添加为 Maven 依赖

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.2</version>
    <classifier>http</classifier>
</dependency>

基本用法

ClickHouse JDBC API

连接信息

请参考通过驱动连接到 ByteHouse，了解如何通过API Token或用户名+密码的方式连接到ByteHouse。

数据插入与查询

您可以参考下面的代码示例来进行数据插入与查询，注意替换 API key 和 ClickHouse HTTP 的等连接信息。其中 Clickhouse HTTP 的字段，不用携带 "https://" 部分。

import com.clickhouse.client.config.ClickHouseClientOption;
import com.clickhouse.client.config.ClickHouseDefaults;

import java.sql.*;
import java.util.Properties;

public class GatewayConnection {

    private static Connection getGatewayConnection(String url) throws SQLException {
        Connection conn = DriverManager.getConnection(url, getProperties());
        System.out.println("Connected to: " + conn.getMetaData().getURL());
        return conn;
    }
    private static Properties getProperties() {
        Properties props = new Properties();
        props.setProperty(ClickHouseClientOption.SSL.getKey(), "true");
        props.setProperty(ClickHouseDefaults.USER.getKey(), "bytehouse");
        props.setProperty(ClickHouseDefaults.PASSWORD.getKey(), "<API_KEY>");
        props.setProperty(ClickHouseClientOption.COMPRESS.getKey(), "true");
        return props;
    }

    public static void main(String[] args) throws SQLException {

        String URL = "jdbc:ch://<CLICKHOUSE_HTTP>";
        Connection conn = getGatewayConnection(URL);
        
        try {

            String databaseName = "database_jdbc_test_" + System.currentTimeMillis();
            String tableName = "table_jdbc_test_" + System.currentTimeMillis();
            String databaseDDL = String.format("CREATE DATABASE %s", databaseName);
            String tableDDL = String.format("CREATE TABLE %s.%s (a String, b Nullable(String), c Int64)Engine=CnchMergeTree() order by tuple()", databaseName, tableName);
            String insertQuery = String.format("INSERT INTO %s.%s VALUES (?, ?, ?)", databaseName, tableName);
            String selectQuery = String.format("SELECT * FROM %s.%s", databaseName, tableName);
            String dropDatabaseQuery = String.format("DROP DATABASE %s", databaseName);
            Statement stmt = conn.createStatement();

            //create database
            stmt.execute(databaseDDL);
            assert stmt.getUpdateCount() == 1;
            //create table
            stmt.execute(tableDDL);
            assert stmt.getUpdateCount() == 1;

            System.out.println("Database "+databaseName+" and table "+tableName+" created");
            //insert
            int count = 0;
            PreparedStatement ps = conn.prepareStatement(insertQuery);
            ps.setString(1, "my_id");
            ps.setString(2, "hello world");
            ps.setInt(3,1);

            ps.addBatch();
            ps.setString(1, "row 2!!");
            ps.setString(2, "next row");
            ps.setInt(3,2);
            ps.addBatch();
            for (int i : ps.executeBatch()) {
                if (i > 0) {
                    count += i;
                }
            }
            assert count == 2;

            ResultSet rs = stmt.executeQuery(selectQuery);

            System.out.println("column count:" + rs.getMetaData().getColumnCount());

            System.out.println("Column names");
            for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                System.out.printf("%s ",rs.getMetaData().getColumnName(i));
            }

            System.out.println("Rows");

            while (rs.next()) {
                for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                    System.out.printf("%s ",rs.getString(i));
                }
                System.out.println();
            }

            stmt.execute(dropDatabaseQuery);
            assert stmt.getUpdateCount() == 1;

        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

输出结果样例