You need to enable JavaScript to run this app.
导航
ByteHouse JDBC Driver
最近更新时间:2024.06.04 17:22:53首次发布时间:2022.05.09 10:22:34

本文介绍如何通过 JDBC 方式连接并访问 ByteHouse 云数仓。
请访问 github 项目主页,获取ByteHouse JDBC 驱动最新的文档和发布版本。

版本要求

支持 Java 1.8.0_261 或更高版本 (需要 TLSv1.3 支持)

安装流程

通过 github repo 获取最新编译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.24'
    }

添加为 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.24</version>
    </dependency>

基本用法

JDBC API

Implements

Class

java.sql.Driver

com.bytedance.bytehouse.jdbc.ByteHouseDriver

连接信息

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

数据插入

可参考下面代码使用JDBC进行数据插入的操作,注意替换连接语句中的HostPortAPI_KEY 字段。

import com.bytedance.bytehouse.jdbc.ByteHouseDriver;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;

public class SimpleQuery {
    public static void main(String[] args) throws Exception {
        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();
//If user wishes to specify the vw to be used
//      properties.setProperty("vw", {VIRTUAL_WAREHOUSE})         
        properties.setProperty("secure", "true");

        Driver driver = new ByteHouseDriver();

        try (Connection connection = driver.connect(url, properties)) {
            createDatabase(connection);
            createTable(connection);
            insertTable(connection);
            insertBatch(connection);

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

    public static void createTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute(
                    "CREATE TABLE IF NOT EXISTS inventory.orders\n" +
                            "(" +
                            "    OrderID String," +
                            "    OrderName String," +
                            "    OrderPriority Int8" +
                            ")" +
                            "    engine = CnchMergeTree()" +
                            "    partition by OrderID" +
                            "    order by OrderID"
            );
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    
    public static void insertTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.executeUpdate(
                    "INSERT INTO inventory.orders VALUES ('54895','Apple',12)"
            );
        } 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();
        }
    }
}

数据查询

可参考下面代码使用JDBC进行数据查询的操作,注意替换连接语句中的HostPortAPI_KEY 字段。

import com.bytedance.bytehouse.jdbc.ByteHouseDriver;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;
import javax.sql.DataSource;

public class DQL {
    public static void main(String[] args) throws Exception {
        String url = String.format("jdbc:bytehouse://{HOST}:{PORT}/?api_key={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();
//If user wishes to specify the vw to be used
//      properties.setProperty("vw", {VIRTUAL_WAREHOUSE})        
        properties.setProperty("secure", "true");

        Driver driver = new ByteHouseDriver();

        try (Connection connection = driver.connect(url, properties)) {
            selectTable(connection);

        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
    public static void selectTable(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            ResultSet rs = stmt.executeQuery("SELECT * FROM inventory.orders");
            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();
        }
    }
}

当前 ByteHouse JDBC Driver 已经开源,详细使用方式和参数说明可以参考 GitHub

通过 JDBC Driver 连接 Hikari CP

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

<dependencies>
    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
        <exclusions>
            <exclusion>
                <groupId>org.apache.tomcat</groupId>
                <artifactId>tomcat-jdbc</artifactId>
            </exclusion>
            <exclusion>
                <groupId>org.slf4j</groupId>
                <artifactId>org.slf4j.api</artifactId>
            </exclusion>
        </exclusions>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-web</artifactId>
    </dependency>

    <dependency>
        <groupId>com.bytedance.bytehouse</groupId>
        <artifactId>driver-java</artifactId>
        <version>1.1.24</version>
    </dependency>

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-actuator</artifactId>
    </dependency>
</dependencies>
  • spring-boot-starter-jdbc 使用 JDBC 功能。 在生产环境中,我们还可以使用 spring-boot-starter-data-jpa 来使用 ORM 框架。
  • spring-boot-starter-web 为网络主机提供web服务。
  • driver-java ByteHouse JDBC 驱动程序。
  • spring-boot-starter-actuator Hikari 指标(可选)。

Web服务端

@RequestMapping("/query")
public List<Long> numbers() {
    List<Long> ans = new ArrayList<>(10);
    try (Connection connection = bhSource.getConnection();
         PreparedStatement pstmt = connection.prepareStatement("select count(*), toDate(start_time)\n" +
                 "from bytehouse.query_history\n" +
                 "group by toDate(start_time) limit 5;")
    ) {
        ResultSet rs = pstmt.executeQuery();
        while (rs.next()) {
            ans.add(rs.getLong(1));
        }
    } catch (Exception e) {
        log.error("Error hits when executing", e);
    }
    return ans;
}

配置

为了启用Hikari CP,我们需要让 Spring 引导获取Hikari Data Source的实例。以下是定义方式:

@Bean
public DataSource getDataSource() {
    HikariConfig config = new HikariConfig();
    //standard Hikari configs
    config.setJdbcUrl("jdbc:bytehouse://{HOST}:{PORT}");
    /*two different ways to set username
    First way:
    config.setUsername("<ACCOUNT_ID>::<USERNAME>");
    config.setPassword("<PASSWORD>");
    Second way:
    config.setUsername("bytehouse");
    config.setPassword("<API_KEY>");
    */
    config.setMaximumPoolSize(3);
    config.setMaximumPoolSize(5);
    config.setMinimumIdle(60000);
    config.setPoolName("BH-Pool");
    //additional driver property goes here
    config.addDataSourceProperty("secure", "true");
    return new HikariDataSource(config);
}

HikariConfig允许传递其他非jdbc属性,例如secure:true
可以使用一些与池相关的配置,例如max_pool_sizemin_pool_ssizemin_idle_timeout。我们可以引用com.zaxxer.hikari.HikariConfig.class来查看完整的配置列表。
请注意,这里我们将pool的名称定义为BH-Pool

代码示例

@SpringBootApplication
public class ConnectionPoolExample{
    public static void main(String[] args) {
        SpringApplication.run(ConnectionPoolExample.class, args);
    }
}

测试

一旦我们在本地运行项目,就可以通过 localhost:8080/query 访问。
在项目运行期间,我们可以查看到到主程序中的日志:

2023-09-04 10:55:39.750 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@34783ae9
2023-09-04 10:55:40.033 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@2acef7
2023-09-04 10:55:40.264 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@4a0a3261
2023-09-04 10:55:40.571 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Added connection com.bytedance.bytehouse.jdbc.ByteHouseConnection@55d992e1
2023-09-04 10:55:40.571 DEBUG 58183 --- [onnection adder] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - After adding stats (total=5, active=0, idle=5, waiting=0)
2023-09-04 10:56:09.544 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Pool stats (total=5, active=0, idle=5, waiting=0)
2023-09-04 10:56:09.548 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Fill pool skipped, pool is at sufficient level.
2023-09-04 10:56:39.551 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Pool stats (total=5, active=0, idle=5, waiting=0)
2023-09-04 10:56:39.552 DEBUG 58183 --- [ool housekeeper] com.zaxxer.hikari.pool.HikariPool        : BH-Pool - Fill pool skipped, pool is at sufficient level.

这表示连接池已准备就绪,并且每30秒打印一次池统计信息。
现在我们执行命令,例如:

time for i in {1..10000}; do
 curl -s http://localhost:8080/query  &
done;

上面的命令将向 ByteHouse 发送 10,000 个并发查询。
执行之后,我们可以使用以下命令来查看连接情况:

curl -s http://localhost:8080/actuator/metrics/hikaricp.connections.active

输出示例如下:

{"name":"hikaricp.connections.active","description":"Active connections","baseUnit":null,"measurements":[{"statistic":"VALUE","value":5.0}],"availableTags":[{"tag":"pool","values":["BH-Pool"]}]}%

我们可以看到连接限制为 5,这个值是在 max-pool-size 属性中定义的。