ByteHouse JDBC driver

📘
For the latest documents and releases, refer our github page.

The ByteHouse JDBC driver allows programs written in Java, Kotlin, Scala (JVM) languages to connect to ByteHouse.

The driver is a Type 4 JDBC driver written purely in Java, and is thus platform independent, and communicates with ByteHouse using its native network protocol. The driver implements the JDBC API.

ByteHouse supports Java database connectivity to Bytehouse, and ensures full compatibility. It is adapted from the open-source ClickHouse Native JDBC driver project.

Features

Data sent to/received from ByteHouse is compressed by default.
Implemented in TCP protocol, more performant than HTTP.
Supports secure connection using TLS.
Supports ByteHouse specific query settings.

Getting started

🚧
Requires Java 8 or higher

Installation

Get the compiled jar file from the github repo.
Simply add the compiled jar file into your project, or use your chosen dependency management tool to add this project as a dependency. You can then import and use the classes in your Java program.

Adding driver as a Gradle Dependency

implementation "com.bytedance.bytehouse:driver-java:0.1.0-SNAPSHOT"

Adding driver as a Maven Dependency

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

Usage

Your Java application can connect to ByteHouse using either one of two classes, as documented in the
JDBC docs - DriverManager and DataSource.

JDBC API

Implements	Class
java.sql.Driver	com.bytedance.bytehouse.jdbc.ByteHouseDriver
javax.sql.DataSource	com.bytedance.bytehouse.jdbc.ByteHouseDataSource

Connect using DataSource (Recommended)

# how to run
./gradlew -PmainClass=examples.SimpleQuery run

import com.bytedance.bytehouse.jdbc.ByteHouseDataSource;

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 SimpleQuery {
    public static void main(String[] args) throws Exception {
        String url = String.format("jdbc:bytehouse://gateway.aws-cn-north-1.bytehouse.cn:19000");
        Properties properties = new Properties();
        properties.setProperty("account", "AWS12345");
        properties.setProperty("user", "username");
        properties.setProperty("password", "YOUR_PASSWORD");
        properties.setProperty("secure", "true");

        DataSource dataSource = new BalancedByteHouseDataSource(url, properties);

        try (Connection connection = dataSource.getConnection()) {
            createDatabase(connection);
            createTable(connection);
            insertTable(connection);
            insertBatch(connection);
            selectTable(connection);
            dropDatabase(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();
        }
    }

    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();
        }
    }

    public static void dropDatabase(Connection connection) {
        try (Statement stmt = connection.createStatement()) {
            stmt.execute("DROP DATABASE inventory");
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
    }
}

Connect using DriverManager

# how to run
./gradlew -PmainClass=examples.Main run

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

public class Main {

    public static void main(String[] args) throws Exception {
        String url = "jdbc:bytehouse://localhost:9000";
        Properties properties = new Properties();
        properties.setProperty("account", "id");
        properties.setProperty("user", "test");
        properties.setProperty("password", "password");

        // Registers the ByteHouse JDBC driver with DriverManager
        Class.forName("com.bytedance.bytehouse.jdbc.ByteHouseDriver");
        
        // Obtain Connection with DriverManager
        try (Connection connection = DriverManager.getConnection(url, properties)) {
            try (Statement stmt = connection.createStatement()) {
                try (ResultSet rs = stmt.executeQuery("SELECT 5")) {
                    while (rs.next()) {
                        System.out.println(rs.getInt(1));
                    }
                }
            }
        }
    }
}

JDBC URL format

The following is the accepted URL format. Arguments in brackets [] are optional.

jdbc:bytehouse://host:port/[database]
    [?propertyName1][=propertyValue1]
    [&propertyName2][=propertyValue2]...

The driver only recognises a URL with the correct sub-protocol 'jdbc:bytehouse'.