ClickHouse / ClickHouse/clickhouse-java

Need assistance with migration to either latest jdbc or preferably client-v2

Open
#2,526 15 comments 0 reactions 0 assignees View on GitHub
area:jdbc-insert jdbc-v2 v2-feedback
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.