Skip to main content
Skip to main content
Edit this page

Java Client

v0.8+

Java client library to communicate with a DB server through its protocols. The current implementation only supports the HTTP interface. The library provides its own API to send requests to a server. The library also provides tools to work with different binary data formats (RowBinary* & Native*).

Setup​


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

Initialization​

The Client object is initialized by com.clickhouse.client.api.Client.Builder#build(). Each client has its own context and no objects are shared between them. The Builder has configuration methods for convenient setup.

Example:

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

Client is AutoCloseable and should be closed when not needed anymore.

Authentication​

Authentication is configured per client at the initialization phase. There are three authentication methods supported: by password, by access token, by SSL Client Certificate.

Authentication by a password requires setting user name password by calling setUsername(String) and setPassword(String):

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

Authentication by an access token requires setting access token by calling setAccessToken(String):

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

Authentication by a SSL Client Certificate require setting username, enabling SSL Authentication, setting a client certificate and a client key by calling setUsername(String), useSSLAuthentication(boolean), setClientCertificate(String) and setClientKey(String) accordingly:

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

SSL Authentication may be hard to troubleshoot on production because many errors from SSL libraries provide not enough information. For example, if client certificate and key do not match then server will terminate connection immediately (in case of HTTP it will be connection initiation stage where no HTTP requests are send so no response is sent).

Please use tools like openssl to verify certificates and keys:

  • check key integrity: openssl rsa -in [key-file.key] -check -noout
  • check client certificate has matching CN for a user:
    • get CN from an user certificate - openssl x509 -noout -subject -in [user.cert]
    • verify same value is set in database select name, auth_type, auth_params from system.users where auth_type = 'ssl_certificate' (query will output auth_params with something like {"common_names":["some_user"]})

Configuration​

All settings are defined by instance methods (a.k.a configuration methods) that make the scope and context of each value clear. Major configuration parameters are defined in one scope (client or operation) and do not override each other.

Configuration is defined during client creation. See com.clickhouse.client.api.Client.Builder.

Client Configuration​

MethodArgumentsDescriptionDefaultKey
addEndpoint(String endpoint)endpoint - URL formatted server addressAdds a server endpoint to list of available servers. Currently only one endpoint is supported.nonenone
addEndpoint(Protocol protocol, String host, int port, boolean secure)protocol - connection protocol
host - IP or hostname
secure - use HTTPS		Adds a server endpoint to list of available servers. Currently only one endpoint is supported.nonenone
enableConnectionPool(boolean enable)enable - flag to enable/disableSets if a connection pool is enabledtrueconnection_pool_enabled
setMaxConnections(int maxConnections)maxConnections - number of connectionsSets how many connections can a client open to each server endpoint.10max_open_connections
setConnectionTTL(long timeout, ChronoUnit unit)timeout - timeout value
unit - time unit		Sets connection TTL after which connection will be considered as not active-1connection_ttl
setKeepAliveTimeout(long timeout, ChronoUnit unit)timeout - timeout value
unit - time unit		Sets HTTP connection keep-alive timeout. Set to 0 to disable Keep-Alive.-http_keep_alive_timeout
setConnectionReuseStrategy(ConnectionReuseStrategy strategy)strategy - LIFO or FIFOSelects which strategy connection pool should useFIFOconnection_reuse_strategy
setDefaultDatabase(String database)database - name of a databaseSets default database.defaultdatabase

Server Settings​

Server side settings can be set on the client level once while creation (see serverSetting method of the Builder) and on operation level (see serverSetting for operation settings class).

 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");

	...
}

When options are set via setOption method (either the Client.Builder or operation settings class) then server settings name should be prefixed with clickhouse_setting_. The com.clickhouse.client.api.ClientConfigProperties#serverSetting() may be handy in this case.

Custom HTTP Header​

Custom HTTP headers can be set for all operations (client level) or a single one (operation level).


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

When options are set via setOption method (either the Client.Builder or operation settings class) then custom header name should be prefixed with http_header_. Method com.clickhouse.client.api.ClientConfigProperties#httpHeader() may be handy in this case.

Common Definitions​

ClickHouseFormat​

Enum of supported formats. It includes all formats that ClickHouse supports.

  • 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

This client version supports:

FormatInputOutput
TabSeparatedrawraw
TabSeparatedRawrawraw
TabSeparatedWithNamesrawraw
TabSeparatedWithNamesAndTypesrawraw
TabSeparatedRawWithNamesrawraw
TabSeparatedRawWithNamesAndTypesrawraw
Templaterawraw
TemplateIgnoreSpacesraw-
CSVrawraw
CSVWithNamesrawraw
CSVWithNamesAndTypesrawraw
CustomSeparatedrawraw
CustomSeparatedWithNamesrawraw
CustomSeparatedWithNamesAndTypesrawraw
SQLInsert-raw
Valuesrawraw
Vertical-raw
JSONrawraw
JSONAsStringraw-
JSONAsObjectraw-
JSONStringsrawraw
JSONColumnsrawraw
JSONColumnsWithMetadatarawraw
JSONCompactrawraw
JSONCompactStrings-raw
JSONCompactColumnsrawraw
JSONEachRowrawraw
PrettyJSONEachRow-raw
JSONEachRowWithProgress-raw
JSONStringsEachRowrawraw
JSONStringsEachRowWithProgress-raw
JSONCompactEachRowrawraw
JSONCompactEachRowWithNamesrawraw
JSONCompactEachRowWithNamesAndTypesrawraw
JSONCompactStringsEachRowrawraw
JSONCompactStringsEachRowWithNamesrawraw
JSONCompactStringsEachRowWithNamesAndTypesrawraw
JSONObjectEachRowrawraw
BSONEachRowrawraw
TSKVrawraw
Pretty-raw
PrettyNoEscapes-raw
PrettyMonoBlock-raw
PrettyNoEscapesMonoBlock-raw
PrettyCompact-raw
PrettyCompactNoEscapes-raw
PrettyCompactMonoBlock-raw
PrettyCompactNoEscapesMonoBlock-raw
PrettySpace-raw
PrettySpaceNoEscapes-raw
PrettySpaceMonoBlock-raw
PrettySpaceNoEscapesMonoBlock-raw
Prometheus-raw
Protobufrawraw
ProtobufSinglerawraw
ProtobufListrawraw
Avrorawraw
AvroConfluentraw-
Parquetrawraw
ParquetMetadataraw-
Arrowrawraw
ArrowStreamrawraw
ORCrawraw
Oneraw-
Npyrawraw
RowBinaryfullfull
RowBinaryWithNamesfullfull
RowBinaryWithNamesAndTypesfullfull
RowBinaryWithDefaultsfull-
Nativefullraw
Null-raw
XML-raw
CapnProtorawraw
LineAsStringrawraw
Regexpraw-
RawBLOBrawraw
MsgPackrawraw
MySQLDumpraw-
DWARFraw-
Markdown-raw
Formraw-

Insert API​

insert(String tableName, InputStream data, ClickHouseFormat format)​

Accepts data as an InputStream of bytes in the specified format. It is expected that data is encoded in the format.

Signatures

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

Parameters

tableName - a target table name.

data - an input stream of an encoded data.

format - a format in which the data is encoded.

settings - request settings.

Return value

Future of InsertResponse type - result of the operation and additional information like server side metrics.

Examples

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)​

Sends a write request to database. The list of objects is converted into an efficient format and then is sent to a server. The class of the list items should be registered up-front using register(Class, TableSchema) method.

Signatures

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

Parameters

tableName - name of the target table.

data - collection DTO (Data Transfer Object) objects.

settings - request settings.

Return value

Future of InsertResponse type - the result of the operation and additional information like server side metrics.

Examples

// 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​

Configuration options for insert operations.

Configuration methods

MethodDescription
setQueryId(String queryId)Sets query ID that will be assigned to the operation. Default: null.
setDeduplicationToken(String token)Sets the deduplication token. This token will be sent to the server and can be used to identify the query. Default: null.
setInputStreamCopyBufferSize(int size)Copy buffer size. The buffer is used during write operations to copy data from user-provided input stream to an output stream. Default: 8196.
serverSetting(String name, String value)Sets individual server settings for an operation.
serverSetting(String name, Collection values)Sets individual server settings with multiple values for an operation. Items of the collection should be String values.
setDBRoles(Collection dbRoles)Sets DB roles to be set before executing an operation. Items of the collection should be String values.
setOption(String option, Object value)Sets a configuration option in raw format. This is not a server setting.

InsertResponse​

Response object that holds result of insert operation. It is only available if the client got response from a server.

Note

This object should be closed as soon as possible to release a connection because the connection cannot be re-used until all data of previous response is fully read.

MethodDescription
OperationMetrics getMetrics()Returns object with operation metrics.
String getQueryId()Returns query ID assigned for the operation by the application (through operation settings or by server).

Query API​

query(String sqlQuery)​

Sends sqlQuery as is. Response format is set by query settings. QueryResponse will hold a reference to the response stream that should be consumed by a reader for the supportig format.

Signatures

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

Parameters

sqlQuery - a single SQL statement. The Query is sent as is to a server.

settings - request settings.

Return value

Future of QueryResponse type - a result dataset and additional information like server side metrics. The Response object should be closed after consuming the dataset.

Examples

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)​

Sends sqlQuery as is. Additionally will send query parameters so the server can compile the SQL expression.

Signatures

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

Parameters

sqlQuery - sql expression with placeholders {}.

queryParams - map of variables to complete the sql expression on server.

settings - request settings.

Return value

Future of QueryResponse type - a result dataset and additional information like server side metrics. The Response object should be closed after consuming the dataset.

Examples


// 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)​

Queries a data in RowBinaryWithNamesAndTypes format. Returns the result as a collection. Read performance is the same as with the reader but more memory is required to hold the whole dataset.

Signatures

List<GenericRecord> queryAll(String sqlQuery)

Parameters

sqlQuery - sql expression to query data from a server.

Return value

Complete dataset represented by a list of GenericRecord objects that provide access in row style for the result data.

Examples

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​

Configuration options for query operations.

Configuration methods

MethodDescription
setQueryId(String queryId)Sets query ID that will be assigned to the operation.
setFormat(ClickHouseFormat format)Sets response format. See RowBinaryWithNamesAndTypes for the full list.
setMaxExecutionTime(Integer maxExecutionTime)Sets operation execution time on server. Will not affect read timeout.
waitEndOfQuery(Boolean waitEndOfQuery)Requests the server to wait for the end of the query before sending a response.
setUseServerTimeZone(Boolean useServerTimeZone)Server timezone (see client config) will be used to parse date/time types in the result of an operation. Default false.
setUseTimeZone(String timeZone)Requests server to use timeZone for time conversion. See session_timezone.
serverSetting(String name, String value)Sets individual server settings for an operation.
serverSetting(String name, Collection values)Sets individual server settings with multiple values for an operation. Items of the collection should be String values.
setDBRoles(Collection dbRoles)Sets DB roles to be set before executing an operation. Items of the collection should be String values.
setOption(String option, Object value)Sets a configuration option in raw format. This is not a server setting.

QueryResponse​

Response object that holds result of query execution. It is only available if the client got a response from a server.

Note

This object should be closed as soon as possible to release a connection because the connection cannot be re-used until all data of previous response is fully read.

MethodDescription
ClickHouseFormat getFormat()Returns a format in which data in the response is encoded.
InputStream getInputStream()Returns uncompressed byte stream of data in the specified format.
OperationMetrics getMetrics()Returns object with operation metrics.
String getQueryId()Returns query ID assigned for the operation by the application (through operation settings or by server).
TimeZone getTimeZone()Returns timezone that should be used for handling Date/DateTime types in the response.

Examples​

Common API​

getTableSchema(String table)​

Fetches table schema for the table.

Signatures

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

Parameters

table - table name for which schema data should be fetched.

database - database where the target table is defined.

Return value

Returns a TableSchema object with list of table columns.

getTableSchemaFromQuery(String sql)​

Fetches schema from a SQL statement.

Signatures

TableSchema getTableSchemaFromQuery(String sql)

Parameters

sql - "SELECT" SQL statement which schema should be returned.

Return value

Returns a TableSchema object with columns matching the sql expression.

TableSchema​

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

Compiles serialization and deserialization layer for the Java Class to use for writing/reading data with schema. The method will create a serializer and deserializer for the pair getter/setter and corresponding column. Column match is found by extracting its name from a method name. For example, getFirstName will be for the column first_name or firstname.

Signatures

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

Parameters

clazz - Class representing the POJO used to read/write data.

schema - Data schema to use for matching with POJO properties.

Examples

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

Usage Examples​

Complete examples code is stored in the repo in a 'example` folder:

Migration From V1 ( =< 0.7.x )​

Old client (V1) was using com.clickhouse.client.ClickHouseClient#builder as start point. The new client (V2) uses similar pattern with com.clickhouse.client.api.Client.Builder. Main differences are:

  • 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​

There are 3 enum classes related to configuration in V1:

  • 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.

They were designed to group parameters and provide clear separation. However in some cases it lead to a confusion (is there a difference between com.clickhouse.client.config.ClickHouseDefaults#ASYNC and com.clickhouse.client.config.ClickHouseClientOption#ASYNC). The new V2 client uses com.clickhouse.client.api.Client.Builder as single dictionary of all possible client configuration options.There is com.clickhouse.client.api.ClientConfigProperties where all configuration parameter names are listed.

Table below shows what old options are supported in the new client and their new meaning.

Legend: ✔ = supported, ✗ = dropped

V1 ConfigurationV2 Builder MethodComments
ClickHouseDefaults#HOSTClient.Builder#addEndpoint
ClickHouseDefaults#PROTOCOL✗Only HTTP supported in V2
ClickHouseDefaults#DATABASE
ClickHouseClientOption#DATABASE		Client.Builder#setDefaultDatabase
ClickHouseDefaults#USERClient.Builder#setUsername
ClickHouseDefaults#PASSWORDClient.Builder#setPassword
ClickHouseClientOption#CONNECTION_TIMEOUTClient.Builder#setConnectTimeout
ClickHouseClientOption#CONNECTION_TTLClient.Builder#setConnectionTTL
ClickHouseHttpOption#MAX_OPEN_CONNECTIONSClient.Builder#setMaxConnections
ClickHouseHttpOption#KEEP_ALIVE
ClickHouseHttpOption#KEEP_ALIVE_TIMEOUT		Client.Builder#setKeepAliveTimeout
ClickHouseHttpOption#CONNECTION_REUSE_STRATEGYClient.Builder#setConnectionReuseStrategy
ClickHouseHttpOption#USE_BASIC_AUTHENTICATIONClient.Builder#useHTTPBasicAuth

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.

// V2 Insert TSV formatted data.
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
}
  • 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");
}

Java client library to communicate with a DB server through its protocols. Current implementation supports only HTTP interface. The library provides own API to send requests to a server.

Deprecation

This library will be deprecated soon. Use the latest Java Client for new projects

Setup​

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-http-client -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-http-client</artifactId>
    <version>0.7.2</version>
</dependency>

Since version 0.5.0, the driver uses a new client http library that needs to be added as a dependency.

<!-- https://mvnrepository.com/artifact/org.apache.httpcomponents.client5/httpclient5 -->
<dependency>
    <groupId>org.apache.httpcomponents.client5</groupId>
    <artifactId>httpclient5</artifactId>
    <version>5.3.1</version>
</dependency>

Initialization​

Connection URL Format: protocol://host[:port][/database][?param[=value][&param[=value]][#tag[,tag]], for example:

  • http://localhost:8443?ssl=true&sslmode=NONE
  • https://(https://explorer@play.clickhouse.com:443

Connect to a single node:

ClickHouseNode server = ClickHouseNode.of("http://localhost:8123/default?compress=0");

Connect to a cluster with multiple nodes:

ClickHouseNodes servers = ClickHouseNodes.of(
    "jdbc:ch:http://server1.domain,server2.domain,server3.domain/my_db"
    + "?load_balancing_policy=random&health_check_interval=5000&failover=2");

Query API​

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers)
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("select * from numbers limit :limit")
        .params(1000)
        .executeAndWait()) {
            ClickHouseResponseSummary summary = response.getSummary();
            long totalRows = summary.getTotalRowsToRead();
}

Streaming Query API​

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers)
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("select * from numbers limit :limit")
        .params(1000)
        .executeAndWait()) {
            for (ClickHouseRecord r : response.records()) {
            int num = r.getValue(0).asInteger();
            // type conversion
            String str = r.getValue(0).asString();
            LocalDate date = r.getValue(0).asDate();
        }
}

See complete code example in the repo.

Insert API​


try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers).write()
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("insert into my_table select c2, c3 from input('c1 UInt8, c2 String, c3 Int32')")
        .data(myInputStream) // `myInputStream` is source of data in RowBinary format
        .executeAndWait()) {
            ClickHouseResponseSummary summary = response.getSummary();
            summary.getWrittenRows();
}

See complete code example in the repo.

RowBinary Encoding

RowBinary format is described on its page.

There is an example of code.

Features​

Compression​

The client will by default use LZ4 compression, which requires this dependency:

<!-- https://mvnrepository.com/artifact/org.lz4/lz4-java -->
<dependency>
    <groupId>org.lz4</groupId>
    <artifactId>lz4-java</artifactId>
    <version>1.8.0</version>
</dependency>

You can choose to use gzip instead by setting compress_algorithm=gzip in the connection URL.

Alternatively, you can disable compression a few ways.

  1. Disable by setting compress=0 in the connection URL: http://localhost:8123/default?compress=0
  2. Disable via the client configuration:
ClickHouseClient client = ClickHouseClient.builder()
   .config(new ClickHouseConfig(Map.of(ClickHouseClientOption.COMPRESS, false)))
   .nodeSelector(ClickHouseNodeSelector.of(ClickHouseProtocol.HTTP))
   .build();

See the compression documentation to learn more about different compression options.

Multiple queries​

Execute multiple queries in a worker thread one after another within same session:

CompletableFuture<List<ClickHouseResponseSummary>> future = ClickHouseClient.send(servers.apply(servers.getNodeSelector()),
    "create database if not exists my_base",
    "use my_base",
    "create table if not exists test_table(s String) engine=Memory",
    "insert into test_table values('1')('2')('3')",
    "select * from test_table limit 1",
    "truncate table test_table",
    "drop table if exists test_table");
List<ClickHouseResponseSummary> results = future.get();

Named Parameters​

You can pass parameters by name rather than relying solely on their position in the parameter list. This capability is available using params function.

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers)
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("select * from my_table where name=:name limit :limit")
        .params("Ben", 1000)
        .executeAndWait()) {
            //...
        }
}
Parameters

All params signatures involving String type (String, String[], Map<String, String>) assume the keys being passed are valid ClickHouse SQL strings. For instance:

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers)
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("select * from my_table where name=:name")
        .params(Map.of("name","'Ben'"))
        .executeAndWait()) {
            //...
        }
}

If you prefer not to parse String objects to ClickHouse SQL manually, you can use the helper function ClickHouseValues.convertToSqlExpression located at com.clickhouse.data:

try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP);
     ClickHouseResponse response = client.read(servers)
        .format(ClickHouseFormat.RowBinaryWithNamesAndTypes)
        .query("select * from my_table where name=:name")
        .params(Map.of("name", ClickHouseValues.convertToSqlExpression("Ben's")))
        .executeAndWait()) {
            //...
        }
}

In the example above, ClickHouseValues.convertToSqlExpression will escape the inner single quote, and surround the variable with a valid single quotes.

Other types, such as Integer, UUID, Array and Enum will be converted automatically inside params.

Node Discovery​

Java client provides the ability to discover ClickHouse nodes automatically. Auto-discovery is disabled by default. To manually enable it, set auto_discovery to true:

properties.setProperty("auto_discovery", "true");

Or in the connection URL:

jdbc:ch://my-server/system?auto_discovery=true

If auto-discovery is enabled, there is no need to specify all ClickHouse nodes in the connection URL. Nodes specified in the URL will be treated as seeds, and the Java client will automatically discover more nodes from system tables and/or clickhouse-keeper or zookeeper.

The following options are responsible for auto-discovery configuration:

PropertyDefaultDescription
auto_discoveryfalseWhether the client should discover more nodes from system tables and/or clickhouse-keeper/zookeeper.
node_discovery_interval0Node discovery interval in milliseconds, zero or negative value means one-time discovery.
node_discovery_limit100Maximum number of nodes that can be discovered at a time; zero or negative value means no limit.

Load Balancing​

The Java client chooses a ClickHouse node to send requests to, according to the load-balancing policy. In general, the load-balancing policy is responsible for the following things:

  1. Get a node from a managed node list.
  2. Managing node's status.
  3. Optionally schedule a background process for node discovery (if auto-discovery is enabled) and run a health check.

Here is a list of options to configure load balancing:

PropertyDefaultDescription
load_balancing_policy""The load-balancing policy can be one of:
  • firstAlive - request is sent to the first healthy node from the managed node list
  • random - request is sent to a random node from the managed node list
  • roundRobin - request is sent to each node from the managed node list, in turn.
  • full qualified class name implementing ClickHouseLoadBalancingPolicy - custom load balancing policy
    • If it is not specified the request is sent to the first node from the managed node list
    load_balancing_tags""Load balancing tags for filtering out nodes. Requests are sent only to nodes that have the specified tags
    health_check_interval0Health check interval in milliseconds, zero or negative value means one-time.
    health_check_methodClickHouseHealthCheckMethod.SELECT_ONEHealth check method. Can be one of:
  • ClickHouseHealthCheckMethod.SELECT_ONE - check with select 1 query
  • ClickHouseHealthCheckMethod.PING - protocol-specific check, which is generally faster
    • node_check_interval0Node check interval in milliseconds, negative number is treated as zero. The node status is checked if the specified amount of time has passed since the last check.
    The difference between health_check_interval and node_check_interval is that the health_check_interval option schedules the background job, which checks the status for the list of nodes (all or faulty), but node_check_interval specifies the amount of time has passed since the last check for the particular node
    check_all_nodesfalseWhether to perform a health check against all nodes or just faulty ones.

    Failover and retry​

    Java client provides configuration options to set up failover and retry behavior for failed queries:

    PropertyDefaultDescription
    failover0Maximum number of times a failover can happen for a request. Zero or a negative value means no failover. Failover sends the failed request to a different node (according to the load-balancing policy) in order to recover from failover.
    retry0Maximum number of times retry can happen for a request. Zero or a negative value means no retry. Retry sends a request to the same node and only if the ClickHouse server returns the NETWORK_ERROR error code
    repeat_on_session_locktrueWhether to repeat execution when the session is locked until timed out(according to session_timeout or connect_timeout). The failed request is repeated if the ClickHouse server returns the SESSION_IS_LOCKED error code

    Adding custom http headers​

    Java client support HTTP/S transport layer in case we want to add custom HTTP headers to the request. We should use the custom_http_headers property, and the headers need to be , separated. The header key/value should be divided using =

    Java Client support​

    options.put("custom_http_headers", "X-ClickHouse-Quota=test, X-ClickHouse-Test=test");

    JDBC Driver​

    properties.setProperty("custom_http_headers", "X-ClickHouse-Quota=test, X-ClickHouse-Test=test");