0

I am trying to write into clickhouse DB using Clickhouse JDBC client (0.4.0)

<!-- https://mvnrepository.com/artifact/com.clickhouse/clickhouse-jdbc -->
<dependency>
    <groupId>com.clickhouse</groupId>
    <artifactId>clickhouse-jdbc</artifactId>
    <version>0.4.0</version>
</dependency>

I am following this example to do batch insert

Basically,

try (PreparedStatement ps = conn.prepareStatement(
    "insert into mytable select col1, col2 from input('col1 String, col2 DateTime64(3), col3 Int32')")) {
    // the column definition will be parsed so the driver knows there are 3 parameters: col1, col2 and col3
    ps.setString(1, "test"); // col1
    ps.setObject(2, LocalDateTime.now()); // col2, setTimestamp is slow and not recommended
    ps.setInt(3, 123); // col3
    ps.addBatch(); // parameters will be write into stream in binary format
    ...
    ps.executeBatch(); // stream everything on-hand into ClickHouse
}

I am adding 10000 rows to the batch before executing ps.executeBatch(). Is it the right and correct way to do batch inserts to clickhouse ?

Is it similar as doing ? insert into mytable values(...)(...)...(...)

Just wanted to make sure that I am doing the batch inserts the right way.

kvb
  • 1
  • 1
  • Yes, this is the right way. From that GitHub comment example: `insert into mytable select col1, col2 from input('col1 String, col2 DateTime64(3), col3 Int32')` (#1) is not similar to `insert into mytable values(...)(...)...(...)` (#3) cause #3 will generate huge SQL statement while #1 won't. – silverthorne Jul 06 '23 at 11:04
  • A more correct approach would be to use `JdbcTemplate`, and use the `batchUpdate` method. – Gumada Yaroslav Jul 11 '23 at 16:37

0 Answers0