how to batchInsert using ClickhouseHttpClient ?

[usmanq8]

do we have any mechanism to batchinsert using java ClickhouseHttpClient? or its more deeper low level drivers need to be used here.
As JDBC driver has performance issues as compared to its client driver.

[zhicwu]

Of course - the JDBC driver is built on top of Java client. Although ClickHouseHttpClient was used by default, in case you want to switch to a different protocol like gRPC or TCP later, it's better to stay with ClickHouseClient, which is more generic.

Anyway, below is an example for you to start with:

client.connect(server).write().format(ClickHouseFormat.RowBinary).table("mytable").data(myInputStream).send();

[usmanq8]

Sorry for that, i am new to get into Clickhouse usage. But how would it bind it self to respective table fields then ? as in query params we know particularly where to specify. In this case, i have List of e.g. employees to insert in bulk into Table Employee.

[zhicwu]

Sorry for the late response.

It's a low-level Java client, so it does not support binding. Everything is streamed from client to server, but you'll have to use something like BinaryStreamUtils to write values one by one in binary format, which could be tedious. There's typeMappings property in JDBC driver but it does not support advanced data binding. I'll think about this and try to implement data binding in future releases.

[k3rnL]

I dig out the topic as I'm facing a linked problem. I'm doing the exact same kind of insert method as saw previously

client.connect(server).write().format(ClickHouseFormat.RowBinary).table("mytable").data(myInputStream).send();

My inputStream come from a file, and I noticed an unexpected behavior.
The reading from the inputStream is really fast (basically a copy to ram) and then everything is written to the http socket.
Why do we have this buffering ? Is not a good thing to mount all the post body in RAM where we could just write to the socket directly.

[zhicwu]

My inputStream come from a file, and I noticed an unexpected behavior.
The reading from the inputStream is really fast (basically a copy to ram) and then everything is written to the http socket.

This does not sound right. There's a small buffer(4096 or 8192 bytes) used in the library for streaming like below, but it won't load everything into memory and then write to server. Having said that, I'm adding support for different buffering mode(for both request and response). Let me create an issue with some test data to explain what it is.

https://github.com/ClickHouse/clickhouse-jdbc/blob/f9f9683f5dd914f9f7785499478ab7c50307a677/clickhouse-http-client/src/main/java/com/clickhouse/client/http/HttpUrlConnectionImpl.java#L233

[k3rnL]

I investigated on my side, it become obvious on the stack trace.

java.util.concurrent.CompletionException: java.lang.OutOfMemoryError: Required array length too large
	at java.base/java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:314)
	at java.base/java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:319)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1766)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
	at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: java.lang.OutOfMemoryError: Required array length too large
	at java.base/jdk.internal.util.ArraysSupport.hugeLength(ArraysSupport.java:650)
	at java.base/jdk.internal.util.ArraysSupport.newLength(ArraysSupport.java:644)
	at java.base/java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:100)
	at java.base/java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:130)
	at java.base/sun.net.www.http.PosterOutputStream.write(PosterOutputStream.java:78)
	at com.clickhouse.client.stream.WrappedOutputStream.flushBuffer(WrappedOutputStream.java:18)
	at com.clickhouse.client.stream.AbstractByteArrayOutputStream.writeBytes(AbstractByteArrayOutputStream.java:57)
	at com.clickhouse.client.ClickHouseOutputStream.write(ClickHouseOutputStream.java:115)
	at com.clickhouse.client.ClickHouseInputStream.pipe(ClickHouseInputStream.java:402)
	at com.clickhouse.client.ClickHouseInputStream.pipe(ClickHouseInputStream.java:378)
	at com.clickhouse.client.http.HttpUrlConnectionImpl.post(HttpUrlConnectionImpl.java:233)
	at com.clickhouse.client.http.ClickHouseHttpClient.postRequest(ClickHouseHttpClient.java:88)
	at com.clickhouse.client.http.ClickHouseHttpClient.lambda$execute$0(ClickHouseHttpClient.java:107)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1764)
	... 3 more
	Suppressed: java.lang.OutOfMemoryError: Required array length too large
		at java.base/jdk.internal.util.ArraysSupport.hugeLength(ArraysSupport.java:650)
		at java.base/jdk.internal.util.ArraysSupport.newLength(ArraysSupport.java:644)
		at java.base/java.io.ByteArrayOutputStream.ensureCapacity(ByteArrayOutputStream.java:100)
		at java.base/java.io.ByteArrayOutputStream.write(ByteArrayOutputStream.java:130)
		at java.base/sun.net.www.http.PosterOutputStream.write(PosterOutputStream.java:78)
		at com.clickhouse.client.stream.WrappedOutputStream.flushBuffer(WrappedOutputStream.java:18)
		at com.clickhouse.client.stream.WrappedOutputStream.flush(WrappedOutputStream.java:35)
		at com.clickhouse.client.ClickHouseOutputStream.close(ClickHouseOutputStream.java:134)
		at com.clickhouse.client.http.HttpUrlConnectionImpl.post(HttpUrlConnectionImpl.java:185)
		... 6 more

Just for scale, the buffer in question is already 6GB, for a file of ~4GB. It fails to allocate the new buffer's capacity

[zhicwu]

I see. Looks like it's the default behavior of HttpURLConnection in JDK - setChunkedStreamingMode should be used for chunked requests. Can you switch to HttpClient by adding connection property http_connection_provider=HTTP_CLIENT?

[k3rnL]

It seems fine, now I get a timeout from the server I guess.
I tried to get rid of it like htis :

HashMap<ClickHouseOption, Serializable> options = new HashMap<>();
        options.put(ClickHouseHttpOption.CONNECTION_PROVIDER, HttpConnectionProvider.HTTP_CLIENT);
        options.put(ClickHouseClientOption.CONNECTION_TIMEOUT, 60 * 10 * 1000);

        try (ClickHouseClient client = ClickHouseClient.newInstance(ClickHouseProtocol.HTTP)) {
            ClickHouseRequest.Mutation request = client.connect(server).write().table(table)
                    .query("insert into `" + table + "` (" + columnList + ") ")
                    .format(ClickHouseFormat.CSV)
                    .options(options);

But I still get

java.util.concurrent.CompletionException: com.clickhouse.client.ClickHouseException: java.net.http.HttpTimeoutException: request timed out, server ClickHouseNode(cluster=lo01, addr=any:172.29.100.83/<unresolved>:8123, db=archipel_kpi_collect_astellia_cem, weight=1)@674842322
	at com.clickhouse.client.http.ClickHouseHttpClient.lambda$execute$0(ClickHouseHttpClient.java:109)
	at java.base/java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1764)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
	at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: com.clickhouse.client.ClickHouseException: java.net.http.HttpTimeoutException: request timed out, server ClickHouseNode(cluster=lo01, addr=any:172.29.100.83/<unresolved>:8123, db=archipel_kpi_collect_astellia_cem, weight=1)@674842322
	at com.clickhouse.client.ClickHouseException.of(ClickHouseException.java:113)
	... 5 more
Caused by: java.util.concurrent.ExecutionException: java.net.http.HttpTimeoutException: request timed out
	at java.base/java.util.concurrent.CompletableFuture.reportGet(CompletableFuture.java:395)
	at java.base/java.util.concurrent.CompletableFuture.get(CompletableFuture.java:2063)
	at com.clickhouse.client.http.HttpClientConnectionImpl.postStream(HttpClientConnectionImpl.java:217)
	at com.clickhouse.client.http.HttpClientConnectionImpl.post(HttpClientConnectionImpl.java:263)
	at com.clickhouse.client.http.ClickHouseHttpClient.postRequest(ClickHouseHttpClient.java:88)
	at com.clickhouse.client.http.ClickHouseHttpClient.lambda$execute$0(ClickHouseHttpClient.java:107)
	... 4 more
Caused by: java.net.http.HttpTimeoutException: request timed out
	at java.net.http/jdk.internal.net.http.ResponseTimerEvent.handle(ResponseTimerEvent.java:63)
	at java.net.http/jdk.internal.net.http.HttpClientImpl.purgeTimeoutsAndReturnNextDeadline(HttpClientImpl.java:1247)
	at java.net.http/jdk.internal.net.http.HttpClientImpl$SelectorManager.run(HttpClientImpl.java:876)

Also, the RAM still go up to the size of the file in the firsts 2/4sec of execution. As I'm on an SSD of 500MB/s, this makes a total sense. It seems that the file is still mounted on RAM, could it be the "ClickHousePipedStream" ?

[k3rnL]

Oh ok I got it. The ClickHousePipedStream has a default value of 0 for the queueLength, in such cases it uses a LinkedBlockingDeque and does not limit the number of "waiting buffers", As the reading of a file is much faster than writing on my poor VPNed network, it fill up the RAM. I think it's a dangerous default value !

[zhicwu]

options.put(ClickHouseClientOption.CONNECTION_TIMEOUT, 60 * 10 * 1000);

The default connection timeout is 10 seconds, which should be long enough for LAN or even WAN. If the query takes time, it's better to increase socket timeout accordingly.

It seems that the file is still mounted on RAM, could it be the "ClickHousePipedStream" ?

It's possible. ClickHousePipedStream is basically an in-memory queue redirecting byte buffers from producer to consumer. In the case of slow consuming(it happens all the time unfortunately), memory could be quickly filled up. I think it's better to stay with chunked requests, which I should be able to fix quickly.

[zhicwu]

I think it's a dangerous default value !

Correct! It's been change to 512 in the upcoming new release :)

[k3rnL]

Super ! Thanks for your help :)

[zhicwu]

Super ! Thanks for your help :)

Also LinkedBlockingDeque has been corrected to LinkedBlockingQueue 😓 Let me try to release the patch tonight.

Update: I was able to load ~15GB file into database using -Xms24m -Xmx24m. You can customize request_chunk_size(defaults to 8192) if you have more memory to spare.