ClickHouse / ClickHouse/clickhouse-java
Need assistance with migration to either latest jdbc or preferably client-v2
- Dominant language
- Java
- Stars
- 1.6k
- Forks
- 636
- Avg merge
- 2d 16h
- Merged PRs (30d)
- 28
Description
### The documentation is a bit lacking so I'd appreciate assistance with migration from prepared statement old version jdbc to client v2 batch insert logic.
Clickhouse: on-prem installation version: clickhouse-server:23.8.9.54
We have a logic that inserts batch of records (50000) into table. Then we have materialised view that aggregates data into aggregated table.
### Table schema (field names obfuscated)
```
CREATE TABLE IF NOT EXISTS cloud.test_null_local ON CLUSTER testcluster
(
field_1 LowCardinality(String),
field_2 UInt64,
field_3 LowCardinality(String),
field_4 String,
field_5 Nullable(String),
field_6 Nullable(String),
field_7 Nullable(UInt64),
field_8 Nullable(String),
field_9 String,
field_10 String,
field_11 Nested(
key String,
value String
),
field_12 Nullable(String),
field_13 Nullable(String),
field_14 Nested(
key String,
value String
),
field_15 Nested(
name String,
id UInt16
),
field_16 Nested(
name String,
field String,
value String
),
field_17 Nullable(Float32),
field_18 Nullable(String),
field_19 Array(String),
field_20 String,
field_21 Nullable(String),
field_22 String,
field_23 Nullable(String),
field_24 Nullable(String),
field_25 String,
field_26 UInt32,
field_27 Array(String),
field_28 Nullable(DateTime),
field_29 Nullable(DateTime),
field_30 DateTime,
field_31 DateTime,
field_32 DateTime default now()
)
ENGINE = Null;
```
Here is java code
```java
public void save(List entities) {
if (entities.size() > 0) {
log.info("Store {} in DB", entities.size());
var query = """
INSERT INTO test_null_local (field_1, field_2, //... other columns omitted )
VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
""";
try (var connection = dataSource.getConnection();
var statement = connection.prepareStatement(query)) {
for (var entity : entities) {
statement.setString(1, entity.field1())
//Other fields setters
statement.addBatch();
}
statement.executeBatch();
} catch (SQLException e) {
log.error("SQL error insert ", e);
}
}
}
```
This logic works with jdbc version 0.5.0 + HikaridCP 5.0.1 and it takes ~90 seconds to insert 50_000 records.
Once I tried to simply update jdbc to version 0.9.0 the insert logic hangs (I've waited for 10 minutes and stopped the process).
Plus it prints to log:
```
line 4:15 extraneous input '.' expecting {',', ')'}
line 4:55 extraneous input '.' expecting {',', ')'}
```
So that version didn't help.
I've tried to migrate to client-v2 POJO insert like this.
Client configuration:
```java
client = new Client.Builder()
.addEndpoint(appConfig.dbUrl())
.setDefaultDatabase(appConfig.dbName())
.setUsername(appConfig.dbUser())
.setPassword(appConfig.dbPassword())
.setMaxConnections(10)
.compressClientRequest(true)
.useHttpCompression(true)
.setLZ4UncompressedBufferSize(10_058_576)
.setSocketRcvbuf(500_000)
.setSocketTcpNodelay(true)
.setSocketSndbuf(10_000_000)
.setClientNetworkBufferSize(10_000_000)
.allowBinaryReaderToReuseBuffers(true)
.build();
client.register(TestEntity.class, client.getTableSchema("test_null_local"));
```
Insert code:
```java
public void save(List entities) {
try (var response = client.insert(TABLE, entities).get()) {
} catch (Exception e) {
log.error("SQL error insert", e);
}
}
```
POJO code (getters and setters omitted):
```java
import java.time.LocalDateTime;
import java.util.List;
public class TestEntity {
private String field_1;
private long field_2;
private String field_3;
private String field_4;
private String field_5;
private String field_6;
private Long field_7;
private String field_8;
private String field_9;
private String field_10;
private List field_11_key;
private List field_11_value;
private String field_12;
private String field_13;
private List field_14_key;
private List field_14_value;
private List field_15_name;
private List field_15_id;
private List field_16_key;
private List field_16_field;
private List field_16_value;
private Float field_17;
private String field_18;
private List field_19;
private String field_20;
private String field_21;
private String field_22;
private String field_23;
private String field_24;
private String field_25;
private long field_26;
private List field_27;
private LocalDateTime field_28;
private LocalDateTime field_29;
private LocalDateTime field_30;
private LocalDateTime field_31;
private LocalDateTime field_32;
}
```
With this code I've got no http error 37 seconds after insert call.
```
c.c.c.a.Client:1250 Retrying. org.apache.hc.core5.http.NoHttpResponseException: [DBURL here] failed to respond
```
I've tried to reduce batch to 10_000 records but it didn't help.
Several hundreds records could be inserted successfully but it kinda contradicts batching guidelines.
Contributor guide
Assessment
This issue has not been assessed yet.