You can connect to ByteHouse Cloud Data Warehouse Edition using the open-source ClickHouse JDBC driver, and after connecting, perform database and table operations, data writing, querying, and more. This article introduces the main operational procedures and key points for connecting to ByteHouse Cloud Data Warehouse Edition using the ClickHouse JDBC driver.

Supports Java 1.8.0_261 or later (requires TLS v1.3 support).

It is recommended to download ClickHouse JDBC v0.4.6 and use OpenJDK 11 for Java.

• The JSONB and BitMap64 types of ByteHouse are not currently supported.
• If you encounter any other unknown restrictions during use, please contact the ByteHouse team.

Download ClickHouse JDBC basic driver:

Add as Maven dependency #

Example of using the ClickHouse JDBC driver via Maven as follows:

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.6</version>
</dependency>

Add a Gradle dependency #

Example of using the ClickHouse JDBC driver with Gradle:

// https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc
implementation 'com.clickhouse:clickhouse-jdbc:0.4.6'

ByteHouse supports connecting to the ClickHouse JDBC Driver through IAM users or database users. The differences between IAM users and database users are described as follows. You can choose according to your needs.

• IAM users are users created in Volcano Engine Access Control (IAM), whose permissions are determined by IAM permission policies as well as the ByteHouse resource and data permissions you grant. IAM users can access the ByteHouse console, and can also access ByteHouse through the CLI, connection drivers, ecosystem tools, APIs, and other methods.
• A database user is a database-level user created in ByteHouse and can be granted environment, resource, and data permissions. Database users cannot access the ByteHouse console, but they can access ByteHouse through CLI, connection drivers, ecosystem tools, APIs, and other methods.

For more information about IAM users and database users, please refer to the following documents:

• Overview
• User Management
• Manage Database Accounts

Use IAM users to connect #

Please refer to步骤三：获取 ByteHouse 连接串信息Learn how to connect to ByteHouse as an IAM user.
General parameters are as follows:

Use a database user account to connect #

You can use the following code to connect to ByteHouse and start developing on ByteHouse using standard statements for querying, writing, and reading data.

• Timeout configuration: socket_timeout defaults to 30000ms, connect_timeout defaults to 5s.
• By default, keepAlive is not supported. To use this feature, you need to create a new KeepAliveClickhouseSocketFactory class. For more information, refer to [reference].配置 KeepAliveChapter configuration requires JDK 11.

Connect ByteHouse #

You can refer to the code below to connect to ByteHouse. When using it, make sure to replace the connection information fields in the connection statement, such as {Host}, {Password}, {User}, {Database}, and {VIRTUAL_WAREHOUSE_ID}. For information on how to obtain these fields, see获取 ByteHouse 连接信息。

Use Properties to configure connections #

import com.clickhouse.jdbc.ClickHouseDriver;

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

public class CKJDBC {
    public static void main(String[] args) {
        String host = "{Host}";
        int port = 8123;
        String password = "{API_KEY}";
        String user = "{User}";
        String database = "{Database}";
        String virtual_warehoue_id = "{VIRTUAL_WAREHOUSE_ID}";

        // Set some basic parameters of links using properties
        String url = String.format("jdbc:clickhouse://%s:%d", host, port);
        Properties properties = new Properties();
        // Different computation groups can be set as needed
          properties.setProperty("custom_http_params", String.format("virtual_warehouse=%s",virtual_warehoue_id));
          properties.setProperty("ssl", "true");
          properties.setProperty("compress", "0");
          properties.setProperty("user", user);
          properties.setProperty("password", password);
          properties.setProperty("database", database);

        // Create a connection according to Java JDBC standard interface
//      try (Connection conn = DriverManager.getConnection(url, properties)) {
        // Create a connection using Clickhouse Driver's own interface
        Connection conn = null;
        Statement initStatement = null;
        try {
            conn = new ClickHouseDriver().connect(url, properties);
            dropDatabaseWithQueryId(conn);
            dropDatabase(conn);
            createDatabase(conn);
            createTable(conn);
            insertTable(conn);
            insertBatch(conn);
            selectTable(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (initStatement != null) initStatement.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Use URL param to configure connections #

import com.clickhouse.jdbc.ClickHouseDriver;

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

public class CKJDBC {
    public static void main(String[] args) {
        String host = "{Host}";
        int port = 8123;
        String password = "{API_KEY}";
        String user = "{User}";
        String database = "{Database}";
        String virtual_warehoue_id = "{VIRTUAL_WAREHOUSE_ID}";

        String url = String.format("jdbc:clickhouse://%s:%d/?ssl=true&compress=0&user=%s&password=%s&database=%s&custom_http_params=virtual_warehouse=%s",
                host, port, user, password, database, virtual_warehoue_id);


        // Create a connection according to the Java JDBC standard interface
//      try (Connection conn = DriverManager.getConnection(url, properties)) {
        // Create a connection using the Clickhouse Driver's interface
        Connection conn = null;
        Statement initStatement = null;
        try {
            conn = new ClickHouseDriver().connect(url, properties);
            dropDatabaseWithQueryId(conn);
            dropDatabase(conn);
            createDatabase(conn);
            createTable(conn);
            insertTable(conn);
            insertBatch(conn);
            selectTable(conn);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (initStatement != null) initStatement.close();
                if (conn != null) conn.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Configure KeepAlive #

To use the keepAlive feature, use JDK 11 and execute the following code to configure and enable it.

1. Create a KeepAliveClickhouseSocketFactory class.

   package com.bytehouse.factory;
   
   import com.clickhouse.client.AbstractSocketClient;
   import com.clickhouse.client.ClickHouseConfig;
   import com.clickhouse.client.ClickHouseSocketFactory;
   import com.clickhouse.client.ClickHouseSslContextProvider;
   import com.clickhouse.client.config.ClickHouseSslMode;
   import com.clickhouse.data.ClickHouseUtils;
   import jdk.net.ExtendedSocketOptions;
   import org.apache.hc.client5.http.socket.PlainConnectionSocketFactory;
   import org.apache.hc.client5.http.ssl.DefaultHostnameVerifier;
   import org.apache.hc.client5.http.ssl.SSLConnectionSocketFactory;
   import org.apache.hc.core5.http.protocol.HttpContext;
   import org.apache.hc.core5.ssl.SSLContexts;
   import javax.net.ssl.SSLContext;
   import javax.net.ssl.SSLException;
   import java.io.IOException;
   import java.net.Socket;
   import java.util.logging.Level;
   import java.util.logging.Logger;
   
   public class KeepAliveClickhouseSocketFactory implements ClickHouseSocketFactory {
   
       private static final Logger LOG = Logger.getLogger(KeepAliveClickhouseSocketFactory.class.getName());
   
       static final KeepAliveClickhouseSocketFactory instance = new KeepAliveClickhouseSocketFactory();
   
   
       // TCP Keep-Alive parameters (unit: seconds)
       private static final int KEEPALIVE_IDLE = 60;
       private static final int KEEPALIVE_INTERVAL = 30;
       private static final int KEEPALIVE_COUNT = 60;
   
   
       @Override
       public <T> T create(ClickHouseConfig config, Class<T> clazz) throws IOException, UnsupportedOperationException {
           if (config == null || clazz == null) {
               throw new IllegalArgumentException("Non-null configuration and class are required");
           } else if (SSLConnectionSocketFactory.class.equals(clazz)) {
               return clazz.cast(new SSLSocketFactory(config));
           } else if (PlainConnectionSocketFactory.class.equals(clazz)) {
               return clazz.cast(new SocketFactory(config));
           }
   
           throw new UnsupportedOperationException(ClickHouseUtils.format("Class %s is not supported", clazz));
       }
   
       private static void configureKeepAlive(Socket sock) {
           try {
               // Enable Keep-Alive
               sock.setKeepAlive(true);
   
               // Set TCP_KEEPIDLE (for example, 60 seconds)
               sock.setOption(ExtendedSocketOptions.TCP_KEEPIDLE, KEEPALIVE_IDLE);
   
               // Set TCP_KEEPINTVL (for example, 30 seconds)
               sock.setOption(ExtendedSocketOptions.TCP_KEEPINTERVAL, KEEPALIVE_INTERVAL);
   
               // Set TCP_KEEPCNT (for example, 5 probes)
               sock.setOption(ExtendedSocketOptions.TCP_KEEPCOUNT, KEEPALIVE_COUNT);
   
               LOG.info("TCP keep-alive options set successfully.");
           } catch (Exception e) {
               LOG.log(Level.WARNING, "can not configure Keep-Alive", e);
           }
       }
   
       @Override
       public boolean supports(Class<?> clazz) {
           return PlainConnectionSocketFactory.class.equals(clazz) || SSLConnectionSocketFactory.class.equals(clazz);
       }
   
       public KeepAliveClickhouseSocketFactory() {
       }
   
       static class SSLSocketFactory extends SSLConnectionSocketFactory {
           private final ClickHouseConfig config;
   
           private SSLSocketFactory(ClickHouseConfig config) throws SSLException {
               super(ClickHouseSslContextProvider.getProvider().getSslContext(SSLContext.class, config)
                               .orElse(SSLContexts.createDefault()),
                       config.getSslMode() == ClickHouseSslMode.STRICT
                               ? new DefaultHostnameVerifier()
                               : (hostname, session) -> true); // NOSONAR
               this.config = config;
           }
   
           @Override
           public Socket createSocket(HttpContext context) throws IOException {
               Socket sock = AbstractSocketClient.setSocketOptions(config, new Socket());
               configureKeepAlive(sock);
               return sock;
           }
   
           public static SSLSocketFactory create(ClickHouseConfig config) throws SSLException {
               return new SSLSocketFactory(config);
           }
       }
   
       static class SocketFactory extends PlainConnectionSocketFactory {
           private final ClickHouseConfig config;
   
           private SocketFactory(ClickHouseConfig config) {
               this.config = config;
           }
   
           @Override
           public Socket createSocket(final HttpContext context) throws IOException {
               Socket sock = AbstractSocketClient.setSocketOptions(config, new Socket());
               configureKeepAlive(sock);
               return sock;
           }
   
           public static SocketFactory create(ClickHouseConfig config) {
               return new SocketFactory(config);
           }
       }
   
   }
   

2. Add the custom parameters Factory and Enable socket_keepalive to the connection URL, and set the http_connection_provider to APACHE_HTTP_CLIENT.

   jdbc:clickhouse://<host>:8123/<database>?custom_socket_factory=com.bytehouse.factory.KeepAliveClickhouseSocketFactory&socket_keepalive=true&http_connection_provider=APACHE_HTTP_CLIENT
   

Create databases and tables #

After establishing a connection with ByteHouse, you can use the following code to create databases and tables.

public static void dropDatabase(Connection connection) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            String dropDbQuery = "Drop DATABASE IF EXISTS bhjdbctest";
            stmt.execute(dropDbQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void createDatabase(Connection connection) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            String createDbQuery = "CREATE DATABASE IF NOT EXISTS bhjdbctest";
            stmt.execute(createDbQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    public static void createTable(Connection connection) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            String createTableQuery = "CREATE TABLE IF NOT EXISTS bhjdbctest.orders\n (" +
                    "OrderID String, OrderName String, OrderPriority Int8)" +
                    " engine = CnchMergeTree() partition by OrderID order by OrderID";
            stmt.execute(createTableQuery);
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Write data #

You can use the following code to write data.

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

Batch write data #

Use PreparedStatement for batch data insertion.

public static void insertBatch(Connection connection) {
        PreparedStatement pstmt = null;
        String insertQuery = "INSERT INTO bhjdbctest.orders (OrderID, OrderName, OrderPriority) VALUES (?,'Apple',?)";
        try {
            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();
        } finally {
            try {
                if (pstmt != null) pstmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Query write results #

You can use the following code to query data.

public static void selectTable(Connection connection) {
        Statement stmt = null;
        try {
            stmt = connection.createStatement();
            String selectTableQuery = "SELECT * FROM bhjdbctest.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();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
}

Use a query ID #

Setting a query ID helps track query execution status and facilitates subsequent debugging or management of query tasks.
To set a query ID for a specific query statement, refer to the following code: stmt.unwrap(ClickHouseRequest.class).query(dropDbQuery,String. format("customized_%s", UUID. randomUUID())).execute();

public static void dropDatabaseWithQueryId(Connection connection) {
        ClickHouseStatement stmt = null;
        try {
            stmt = (ClickHouseStatement)connection.createStatement();
            String dropDbQuery = "Drop DATABASE IF EXISTS bhjdbctest";
            stmt.unwrap(ClickHouseRequest.class).query(dropDbQuery,String.format("customized_%s", UUID.randomUUID())).execute();
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (stmt != null) stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

Unsupported or unrecognized SSL message errors #

If you encounter the Unsupported or unrecognized SSL message error during use, this is caused by an outdated Java version. This error will occur in Java versions below 1.8.0_261 **** .
You can resolve this in the following ways:

• Upgrade the Java version to Java 11.
• To retain the Java 1.8 environment, upgrade to Java 1.8.0_261 or a later patch version to resolve this issue.

DB::Exception: No local available worker group for vw-xxxx #

If you encounter the DB::Exception: No local available worker group for vw-xxxx error during use, it is because the compute group has not yet started.
You can resolve this in the following ways:
Log in to the ByteHouse cloud data warehouse console, go to the compute group page, and check whether the compute group you are using is in the running state.

Code: UNAUTHENTICATED #

If you encounter the Exception: unknown error: Code: UNAUTHENTICATED error while using the system, this may be caused by incorrect configuration of user and password.
You can resolve the issue using the following methods:
Please confirm again that the user and password you configured are correct.

DB::Exception: Database xxx.yyy doesn't exist #

To address the error DB::Exception: Database xxx.yyy doesn't exist that may occur while using the system, ensure that the database name is configured correctly.
You can resolve it in the following ways:
Please verify that the database name configured in the connection information is correct.