本文介绍如何通过 JDBC 方式连接并访问 ByteHouse 云数仓。

环境要求

支持 Java 1.8.0_261 或更高版本 （需要 TLSv1.3 支持）

驱动兼容性

部分 SQL/ Function 受驱动版本影响，详情参阅下表。

安装流程

本地安装

请点击 这里 获取 ByteHouser JDBC Driver 最新版本的jar文件。
只需将编译后的jar文件添加到您的项目中，或者使用您选择的依赖项管理工具将此项目添加为依赖项。然后，您可以导入并使用Java程序中的类。

添加为 Gradle 依赖

repositories {
    // This is public bytedance repository for downloading artifacts
    maven {
        url "https://artifact.bytedance.com/repository/releases"
    }
}

dependencies {
    implementation "com.bytedance.bytehouse:driver-java:1.1.58:all"
}

添加为 Maven 依赖

// This is public bytedance repository for downloading artifacts
<repository>
    <id>bytedance</id>
    <name>ByteDance Public Repository</name>
    <url>https://artifact.bytedance.com/repository/releases</url>
</repository>

<dependency>
    <groupId>com.bytedance.bytehouse</groupId>
    <artifactId>driver-java</artifactId>
    <version>1.1.58</version>
    <classifier>all</classifier>
</dependency>

基本用法

JDBC API

连接信息

请参考获取 ByteHouse 连接信息，了解如何通过API Key或用户名+密码的方式连接到ByteHouse。

建表

可参考下面代码使用JDBC进行建表操作，注意替换连接语句中的Host 、Port 和 API_KEY 字段。

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

public class DDL {
    public static void main(String[] args) {
        String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?user=bytehouse&password={API_KEY}");
//      If user wishes to specify the database in url
//      String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?secure=true&user=bytehouse&password={API_KEY}&database={DATABASE}");

        Properties properties = new Properties();
//     如果需要指定计算组进行查询和写入，可以设置virtual_warehouse的Property参数。Value为计算组的ID
//      properties.setProperty("virtual_warehouse", {VIRTUAL_WAREHOUSE_ID});
        properties.setProperty("secure", "true");

        try (Connection conn = DriverManager.getConnection(url, properties)) {
            createDatabase(conn);
            createTable(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createDatabase(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String createDbQuery = "CREATE DATABASE IF NOT EXISTS inventory";
            stmt.execute(createDbQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {

            String createTableQuery = "CREATE TABLE IF NOT EXISTS inventory.orders\n (" +
                    "OrderID String, OrderName String, OrderPriority Int8)" +
                    " engine = CnchMergeTree() partition by OrderID order by OrderID";
            stmt.execute(
                    createTableQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

插入数据

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

public class DML {
    public static void main(String[] args) {
        String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?user=bytehouse&password={API_KEY}");
//      If user wishes to specify the database in url
//      String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?secure=true&user=bytehouse&password={API_KEY}&database={DATABASE}");

        Properties properties = new Properties();
//      如果需要指定计算组进行查询和写入，可以设置virtual_warehouse的Property参数。Value为计算组的ID
//      properties.setProperty("virtual_warehouse", {VIRTUAL_WAREHOUSE_ID});
        properties.setProperty("secure", "true");

        try (Connection conn = DriverManager.getConnection(url, properties)) {
            insertTable(conn);
            insertBatch(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String insertQuery = "INSERT INTO inventory.orders VALUES ('54895','Apple',12)";
            stmt.executeUpdate(
                    insertQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertBatch(Connection connection) {
        String insertQuery = "INSERT INTO inventory.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertQuery)) {
            int insertBatchSize = 10;

            for (int i = 0; i < insertBatchSize; i++) {
                pstmt.setString(1, "ID" + i);
                pstmt.setInt(2, i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

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

查询

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

public class DQL {
    public static void main(String[] args) {
        String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?user=bytehouse&password={API_KEY}");
//      If user wishes to specify the database in url
//      String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?secure=true&user=bytehouse&password={API_KEY}&database={DATABASE}");

        Properties properties = new Properties();
//      如果需要指定计算组进行查询和写入，可以设置virtual_warehouse的Property参数。Value为计算组的ID
//      properties.setProperty("virtual_warehouse", {VIRTUAL_WAREHOUSE_ID});
        properties.setProperty("secure", "true");

        try (Connection conn = DriverManager.getConnection(url, properties)) {
            selectTable(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void selectTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String selectTableQuery = "SELECT * FROM inventory.orders";
            ResultSet rs = stmt.executeQuery(selectTableQuery);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnsNumber; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue);
                }
                System.out.println();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

通过 HikariCP 进行 JDBC 连接

下面举例介绍如何通过 ByteHouse JDBC 驱动程序与 Hikari CP 连接。
首先，我们需要添加基本依赖项。

Maven 依赖

如果您使用 maven，请在 pom.xml 文件中添加以下依赖项以包含 HikariCP：

<dependency>
    <groupId>com.zaxxer</groupId>
    <artifactId>HikariCP</artifactId>
    <version>4.0.3</version>
</dependency>

Gradle 依赖

如果您使用 Gradle，请在 build.gradle 文件中添加以下依赖项以包含 HikariCP：

dependencies {
    implementation 'com.zaxxer:HikariCP:4.0.3'
} 

类名

While using HikariCP, the class you will mostly interact with is HikariDataSource. Follow the example below to configure and use HikariCP in your Java application to Create a Bytehouse Connection.
在使用 HikariCP 时，您最常与之交互的类是 HikariDataSource。您可以按照以下示例在Java 应用程序中配置和使用 HikariCP 来创建 Bytehouse 连接。

连接与查询

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

import java.sql.*;

public class HikariConnection {
    public static Connection getConnectionWithHikariConfig(String host, String port, String apiKey) throws Exception {

        HikariConfig config = new HikariConfig();
        String jdbcURL = String.format("jdbc:bytehouse://%s:%s/?api_key=%s", host, port, apiKey);
        
        // Standard Hikari configs
        config.setJdbcUrl(jdbcURL);
        config.setMinimumIdle(5); // Minimum number of idle connections in the pool
        config.setMaximumPoolSize(20); // Maximum number of connections in the pool
        config.setMaxLifetime(4 * 60 * 1000); // 4 minutes after which a connection will be retired, maximum allowed 5 minutes
        config.setIdleTimeout(2 * 60 * 1000); // 2 minutes of idle time before a connection is eligible for eviction

        config.setPoolName("BH-Pool");
        config.addDataSourceProperty("secure", "true");
        // config.addDataSourceProperty("virtual_warehouse", {VIRTUAL_WAREHOUSE_ID});

        HikariDataSource hds = new HikariDataSource(config);
        Connection conn = hds.getConnection();
        return conn;
    }

    public static void main(String[] args) {
        String host = "{HOST}";
        String apiKey = "{API_KEY}";
        String port = "19000"; // change if needed
        try {
            Connection connection = getConnectionWithHikariConfig(host, port, apiKey);
            createDatabase(connection);
            createTable(connection);
            insertTable(connection);
            insertBatch(connection);
            selectTable(connection);

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

    public static void createDatabase(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String createDbQuery = "CREATE DATABASE IF NOT EXISTS inventory";
            stmt.execute(createDbQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {

            String createTableQuery = "CREATE TABLE IF NOT EXISTS inventory.orders\n (" +
                    "OrderID String, OrderName String, OrderPriority Int8)" +
                    " engine = CnchMergeTree() partition by OrderID order by OrderID";
            stmt.execute(
                    createTableQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String insertQuery = "INSERT INTO inventory.orders VALUES ('54895','Apple',12)";
            stmt.executeUpdate(
                    insertQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertBatch(Connection connection) {
        String insertQuery = "INSERT INTO inventory.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertQuery)) {
            int insertBatchSize = 10;

            for (int i = 0; i < insertBatchSize; i++) {
                pstmt.setString(1, "ID" + i);
                pstmt.setInt(2, i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

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

    public static void selectTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String selectTableQuery = "SELECT * FROM inventory.orders";
            ResultSet rs = stmt.executeQuery(selectTableQuery);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnsNumber; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue);
                }
                System.out.println();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

通过 Druid 进行 JDBC 连接

您也可以通过阿里巴巴的 Druid连接池来连接 ByteHouse。

Maven 依赖

如果您使用 maven，请在 pom.xml 文件中添加以下依赖项以包含 Druid：

<dependency>
    <groupId>com.alibaba</groupId>
    <artifactId>druid</artifactId>
    <version>1.1.23</version>
</dependency>

Gradle 依赖

如果您使用 Gradle，请在 build.gradle 文件中添加以下依赖项以包含 Druid：

dependencies {
     implementation 'com.alibaba:druid:1.1.23' 
}

类名

While using Druid, the class you will mostly interact with is DruidDataSource. Follow the example below to configure and use Druid in your Java application to Create a Bytehouse Connection.
在使用 Druid 时，您最常与之交互的类是 DruidDataSource。您可以按照以下示例在Java 应用程序中配置和使用 Druid 来创建 Bytehouse 连接。

连接与查询

import com.alibaba.druid.pool.DruidDataSource;
import java.sql.*;
import java.util.Properties;

public class DruidConnection {
    public static Connection getConnectionWithDruidConfig(String host, String port, String apiKey) throws Exception {

        DruidDataSource druidDataSource = new DruidDataSource();
        String jdbcURL = String.format("jdbc:bytehouse://%s:%s/?api_key=%s", host, port, apiKey);
        druidDataSource.setUrl(jdbcURL);
        druidDataSource.setDriverClassName("com.bytedance.bytehouse.jdbc.ByteHouseDriver");
        druidDataSource.setMaxActive(10); // Maximum number of active connections
        druidDataSource.setInitialSize(2); // Initial number of connections
        druidDataSource.setMinIdle(2); // Minimum number of idle connections
        druidDataSource.setMaxWait(50000); // Maximum wait time for getting a connection
        Properties properties = druidDataSource.getConnectProperties();
        properties.setProperty("secure", "true");
        // properties.setProperty("virtual_warehouse", {VIRTUAL_WAREHOUSE_ID});

        druidDataSource.setConnectProperties(properties);
        Connection conn = druidDataSource.getConnection();

        return conn;
    }

    public static void main(String[] args) {
        String host = "{HOST}";
        String apiKey = "{API_KEY}";
        String port = "19000"; // change if needed
        try {
            Connection connection = getConnectionWithDruidConfig(host, port, apiKey);
            createDatabase(connection);
            createTable(connection);
            insertTable(connection);
            insertBatch(connection);
            selectTable(connection);

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

    public static void createDatabase(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String createDbQuery = "CREATE DATABASE IF NOT EXISTS inventory";
            stmt.execute(createDbQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void createTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {

            String createTableQuery = "CREATE TABLE IF NOT EXISTS inventory.orders\n (" +
                    "OrderID String, OrderName String, OrderPriority Int8)" +
                    " engine = CnchMergeTree() partition by OrderID order by OrderID";
            stmt.execute(
                    createTableQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String insertQuery = "INSERT INTO inventory.orders VALUES ('54895','Apple',12)";
            stmt.executeUpdate(
                    insertQuery
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }

    public static void insertBatch(Connection connection) {
        String insertQuery = "INSERT INTO inventory.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)";
        try (PreparedStatement pstmt = connection.prepareStatement(insertQuery)) {
            int insertBatchSize = 10;

            for (int i = 0; i < insertBatchSize; i++) {
                pstmt.setString(1, "ID" + i);
                pstmt.setInt(2, i);
                pstmt.addBatch();
            }
            pstmt.executeBatch();

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

    public static void selectTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            String selectTableQuery = "SELECT * FROM inventory.orders";
            ResultSet rs = stmt.executeQuery(selectTableQuery);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnsNumber = rsmd.getColumnCount();
            while (rs.next()) {
                for (int i = 1; i <= columnsNumber; i++) {
                    if (i > 1) System.out.print(", ");
                    String columnValue = rs.getString(i);
                    System.out.print(columnValue);
                }
                System.out.println();
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}