ClickHouse / ClickHouse/clickhouse-java
Need assistance with migration to either latest jdbc or preferably client-v2
- 主要言語
- Java
- スター
- 1.6k
- フォーク
- 636
- 平均マージ
- 2日 23時間
- マージ済み PR(30日)
- 29
説明
### 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.
コントリビューションガイド
調査の方向性
まず、説明されている Java コード、ClickHouse スキーマ、バッチサイズを使って、JDBC 0.9.0 の batch insert と client-v2 の POJO insert を再現します。パーサーエラー、retry/応答なしの挙動、および成功する小さいバッチを比較します。原因を特定し、サポートされている移行パスを文書化またはテストできれば完了です。
索引モデルが issue の本文から書いたものです。
評価
- 技術スタック
- java
- 領域
- databases
- issue の種類
- バグ
- 難易度
- 4/5
- 見積もり時間
- 3〜5日
- 活発さ
- 停滞
- 明瞭さ
- 説明が足りない
- 初心者へのやさしさ
- 35/100