Java client

​

Setup

• Maven Central (project web page): https://mvnrepository.com/artifact/com.clickhouse/client-v2
• Nightly builds (repository link): https://central.sonatype.com/repository/maven-snapshots/
• Old Nightly builds artifactory (repository link): https://s01.oss.sonatype.org/content/repositories/snapshots/

<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>client-v2</artifactId>
    <version>0.9.8</version>
</dependency>

// https://mvnrepository.com/artifact/com.clickhouse/client-v2
implementation("com.clickhouse:client-v2:0.9.8")

// https://mvnrepository.com/artifact/com.clickhouse/client-v2
implementation 'com.clickhouse:client-v2:0.9.8'

​

Initialization

 Client client = new Client.Builder()
                .addEndpoint("https://clickhouse-cloud-instance:8443/")
                .setUsername(user)
                .setPassword(password)
                .build();

​

Authentication

 Client client = new Client.Builder()
        .addEndpoint("https://clickhouse-cloud-instance:8443/")
        .setUsername(user)
        .setPassword(password)
        .build();

 Client client = new Client.Builder()
        .addEndpoint("https://clickhouse-cloud-instance:8443/")
        .setAccessToken(userAccessToken)
        .build();

Client client = new Client.Builder()
        .useSSLAuthentication(true)
        .setUsername("some_user")
        .setClientCertificate("some_user.crt")
        .setClientKey("some_user.key")

​

Configuration

​

Client Configuration

​

Client Identification

client.setClientName("my-app-01/1.0");

my-app-01/1.0 clickhouse-java-v2/0.9.6-SNAPSHOT (Linux; jvm:17.0.17) Apache-HttpClient/5.4.4

​

Operation Identification

QuerySettings querySettings = new QuerySettings();
querySettings.setQueryId("some-query-id");

QuerySettings querySettings = new QuerySettings();
querySettings.logComment("some-comment");

​

Server Settings

 try (Client client = new Client.Builder().addEndpoint(Protocol.HTTP, "localhost", mockServer.port(), false)
        .setUsername("default")
        .setPassword(ClickHouseServerForTest.getPassword())
        .compressClientRequest(true)

        // Client level
        .serverSetting("max_threads", "10")
        .serverSetting("async_insert", "1")
        .serverSetting("roles", Arrays.asList("role1", "role2"))

        .build()) {

	// Operation level
	QuerySettings querySettings = new QuerySettings();
	querySettings.serverSetting("session_timezone", "Europe/Zurich");

	...
}

​

Custom HTTP Header

QuerySettings settings = new QuerySettings()
    .httpHeader(HttpHeaders.REFERER, clientReferer)
    .setQueryId(qId);

​

Common Definitions

​

ClickHouseFormat

• raw - user should transcode raw data
• full - the client can transcode data by itself and accepts a raw data stream
• - - operation not supported by ClickHouse for this format

​

Insert API

​

insert(String tableName, InputStream data, ClickHouseFormat format)

CompletableFuture<InsertResponse> insert(String tableName, InputStream data, ClickHouseFormat format, InsertSettings settings)
CompletableFuture<InsertResponse> insert(String tableName, InputStream data, ClickHouseFormat format)

try (InputStream dataStream = getDataStream()) {
    try (InsertResponse response = client.insert(TABLE_NAME, dataStream, ClickHouseFormat.JSONEachRow,
            insertSettings).get(3, TimeUnit.SECONDS)) {

        log.info("Insert finished: {} rows written", response.getMetrics().getMetric(ServerMetrics.NUM_ROWS_WRITTEN).getLong());
    } catch (Exception e) {
        log.error("Failed to write JSONEachRow data", e);
        throw new RuntimeException(e);
    }
}

​

insert(String tableName, List<?> data, InsertSettings settings)

client.insert(String tableName, List<?> data, InsertSettings settings)
client.insert(String tableName, List<?> data)

// Important step (done once) - register class to pre-compile object serializer according to the table schema.
client.register(ArticleViewEvent.class, client.getTableSchema(TABLE_NAME));

List<ArticleViewEvent> events = loadBatch();

try (InsertResponse response = client.insert(TABLE_NAME, events).get()) {
    // handle response, then it will be closed and connection that served request will be released.
}

​

InsertSettings

​

InsertResponse

​

Query API

​

query(String sqlQuery)

CompletableFuture<QueryResponse> query(String sqlQuery, QuerySettings settings)
CompletableFuture<QueryResponse> query(String sqlQuery)

final String sql = "select * from " + TABLE_NAME + " where title <> '' limit 10";

// Default format is RowBinaryWithNamesAndTypesFormatReader so reader have all information about columns
try (QueryResponse response = client.query(sql).get(3, TimeUnit.SECONDS);) {

    // Create a reader to access the data in a convenient way
    ClickHouseBinaryFormatReader reader = client.newBinaryFormatReader(response);

    while (reader.hasNext()) {
        reader.next(); // Read the next record from stream and parse it

        // get values
        double id = reader.getDouble("id");
        String title = reader.getString("title");
        String url = reader.getString("url");

        // collecting data
    }
} catch (Exception e) {
    log.error("Failed to read data", e);
}

// put business logic outside of the reading block to release http connection asap.

​

query(String sqlQuery, Map<String, Object> queryParams, QuerySettings settings)

CompletableFuture<QueryResponse> query(String sqlQuery, Map<String, Object> queryParams, QuerySettings settings)

// define parameters. They will be sent to the server along with the request.
Map<String, Object> queryParams = new HashMap<>();
queryParams.put("param1", 2);

try (QueryResponse response =
        client.query("SELECT * FROM " + table + " WHERE col1 >= {param1:UInt32}", queryParams, new QuerySettings()).get()) {

    // Create a reader to access the data in a convenient way
    ClickHouseBinaryFormatReader reader = client.newBinaryFormatReader(response);

    while (reader.hasNext()) {
        reader.next(); // Read the next record from stream and parse it

        // reading data
    }

} catch (Exception e) {
    log.error("Failed to read data", e);
}

​

queryAll(String sqlQuery)

List<GenericRecord> queryAll(String sqlQuery)

try {
    log.info("Reading whole table and process record by record");
    final String sql = "select * from " + TABLE_NAME + " where title <> ''";

    // Read whole result set and process it record by record
    client.queryAll(sql).forEach(row -> {
        double id = row.getDouble("id");
        String title = row.getString("title");
        String url = row.getString("url");

        log.info("id: {}, title: {}, url: {}", id, title, url);
    });
} catch (Exception e) {
    log.error("Failed to read data", e);
}

​

QuerySettings

​

QueryResponse

​

Examples

• Example code is available in repo
• Reference Spring Service implementation

​

Common API

​

getTableSchema(String table)

TableSchema getTableSchema(String table)
TableSchema getTableSchema(String table, String database)

​

getTableSchemaFromQuery(String sql)

TableSchema getTableSchemaFromQuery(String sql)

​

TableSchema

​

register(Class<?> clazz, TableSchema schema)

void register(Class<?> clazz, TableSchema schema)

client.register(ArticleViewEvent.class, client.getTableSchema(TABLE_NAME));

​

Usage Examples

• client-v2 - main set of examples.
• demo-service - example of how to use the client in a Spring Boot application.
• demo-kotlin-service - example of how to use the client in Ktor (Kotlin) application.

​

Reading Data

• query() method that returns low-level QueryResponse object that contains InputStream with data. Usually combined with ClickHouseBinaryFormatReader for streaming reads but can be used with any other custom reader implementation. QueryResponse also provides access to the result set metadata and metrics.
• queryAll() method and using GenericRecord for convenient row access. In this case the whole result set is loaded into memory.
• queryRecords() method that returns com.clickhouse.client.api.query.Records - an iterator for GenericRecord objects. This method uses streaming approach (no data is loaded into memory) and utilises GenericRecord to access data.

​

Reading Arrays

• getList(...) - reads any Array(...) as List<T>. Good default for flexible typed reads. Supports nested arrays.
• getByteArray(...), getShortArray(...), getIntArray(...), getLongArray(...), getFloatArray(...), getDoubleArray(...), getBooleanArray(...) - best for 1D arrays of primitive-compatible values.
• getStringArray(...) - for Array(String) (and enum values represented as names).
• getObjectArray(...) - generic option for any Array(...) element type, including nested arrays. Use to read arrays with nullable values and nested arrays.

try (QueryResponse response = client.query("SELECT * FROM my_table").get()) {
    ClickHouseBinaryFormatReader reader = client.newBinaryFormatReader(response);
    while (reader.next() != null) {
        
        Object[] uint64 = reader.getObjectArray("uint64_arr"); // Array(UInt64) -> BigInteger[]
        Object[] arr2d = reader.getObjectArray("arr2d");       // Array(Array(Int64)) -> Object[]

        // nested arrays are returned as nested Object[]:
        Object[] firstInner = (Object[]) arr2d[0];
        Long firstValue = (Long) firstInner[0];
    }
}

• getList(...) - reads any Array(...) as List<T>. Good default for flexible typed reads. Supports nested arrays.
• getByteArray(...), getShortArray(...), getIntArray(...), getLongArray(...), getFloatArray(...), getDoubleArray(...), getBooleanArray(...) - best for 1D arrays of primitive-compatible values.
• getStringArray(...) - for Array(String) (and enum values represented as names).
• getObjectArray(...) - generic option for any Array(...) element type, including nested arrays. Use to read arrays with nullable values and nested arrays.

try (QueryResponse response = client.query("SELECT * FROM my_table").get()) {
    List<GenericRecord> rows = client.queryAll(
        "SELECT int_arr, arr2d_nullable FROM test_arrays ORDER BY id");

    for (GenericRecord row : rows) {
        Object[] intArr = row.getObjectArray("int_arr");                 // Array(Int32) -> Integer[]
        Object[] arr2d = row.getObjectArray("arr2d_nullable");           // Array(Array(Nullable(Int32)))

        Object[] inner = (Object[]) arr2d[0];
        Object maybeNull = inner[1]; // may be null
    }
}

​

Migration Guide

• no service loader is used to grab implementation. The com.clickhouse.client.api.Client is facade class for all kinds of implementation in the future.
• a fewer sources of configuration: one is provided to the builder and one is with operation settings (QuerySettings, InsertSettings). Previous version had configuration per node and was loading env. variables in some cases.

​

Configuration Parameters Match

• com.clickhouse.client.config.ClickHouseDefaults - configuration parameters that supposed to be set in most use cases. Like USER and PASSWORD.
• com.clickhouse.client.config.ClickHouseClientOption - configuration parameters specific for the client. Like HEALTH_CHECK_INTERVAL.
• com.clickhouse.client.http.config.ClickHouseHttpOption - configuration parameters specific for HTTP interface. Like RECEIVE_QUERY_PROGRESS.

​

General Differences

• Client V2 uses less proprietary classes to increase portability. For example, V2 works with any implementation of java.io.InputStream for writing data to a server.
• Client V2 async settings is off by default. It means no extra threads and more application control over client. This setting should be off for majority of use cases. Enabling async will create a separate thread for a request. It only make sense when using application controlled executor (see com.clickhouse.client.api.Client.Builder#setSharedOperationExecutor)

​

Writing Data

• use any implementation of java.io.InputStream. V1 com.clickhouse.data.ClickHouseInputStream is supported but NOT recommended.
• once end of input stream is detected it handled accordingly. Previously output stream of a request should be closed.

InputStream inData = getInData();
ClickHouseRequest.Mutation request = client.read(server)
        .write()
        .table(tableName)
        .format(ClickHouseFormat.TSV);
ClickHouseConfig config = request.getConfig();
CompletableFuture<ClickHouseResponse> future;
try (ClickHousePipedOutputStream requestBody = ClickHouseDataStreamFactory.getInstance()
        .createPipedOutputStream(config)) {
    // start the worker thread which transfer data from the input into ClickHouse
    future = request.data(requestBody.getInputStream()).execute();

    // Copy data from inData stream to requestBody stream

    // We need to close the stream before getting a response
    requestBody.close();

    try (ClickHouseResponse response = future.get()) {
        ClickHouseResponseSummary summary = response.getSummary();
        Assert.assertEquals(summary.getWrittenRows(), numRows, "Num of written rows");
    }
}

InputStream inData = getInData();
InsertSettings settings = new InsertSettings().setInputStreamCopyBufferSize(8198 * 2); // set copy buffer size
try (InsertResponse response = client.insert(tableName, inData, ClickHouseFormat.TSV, settings).get(30, TimeUnit.SECONDS)) {

  // Insert is complete at this point

} catch (Exception e) {
 // Handle exception
}

• there is a single method to call. No need to create an additional request object.
• request body stream is closed automatically when all data is copied.
• new low-level API is available com.clickhouse.client.api.Client#insert(java.lang.String, java.util.List<java.lang.String>, com.clickhouse.client.api.DataStreamWriter, com.clickhouse.data.ClickHouseFormat, com.clickhouse.client.api.insert.InsertSettings). com.clickhouse.client.api.DataStreamWriter is designed to implement custom data writing logic. For instance, reading data from a queue.

​

Reading Data

• Data is read in RowBinaryWithNamesAndTypes format by default. Currently only this format is supported when data binding is required.
• Data can be read as a collection of records using List<GenericRecord> com.clickhouse.client.api.Client#queryAll(java.lang.String) method. It will read data to a memory and release connection. No need for extra handling. GenericRecord gives access to data, implements some conversions.

Collection<GenericRecord> records = client.queryAll("SELECT * FROM table");
for (GenericRecord record : records) {
    int rowId = record.getInteger("rowID");
    String name = record.getString("name");
    LocalDateTime ts = record.getLocalDateTime("ts");
}